[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_ARINV_PVT
Source
1 PACKAGE BODY OKL_AM_ARINV_PVT AS
2 /* $Header: OKLRARVB.pls 120.4 2008/04/29 22:46:46 sechawla ship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : Create_Asset_Repair_Invoice
8 -- Description : Create the Asset Repair Invoice.
9 -- Remains for backward compatibility.
10 -- Business Rules :
11 -- Parameters :
12 -- Version : 1.0
13 --
14 -- End of comments
15 PROCEDURE Create_Asset_Repair_Invoice (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_ariv_tbl IN ariv_tbl_type) IS
22
23 l_taiv_tbl okl_trx_ar_invoices_pub.taiv_tbl_type;
24
25 BEGIN
26
27 okl_am_invoices_pvt.create_repair_invoice (
28 p_api_version => p_api_version,
29 p_init_msg_list => p_init_msg_list,
30 x_return_status => x_return_status,
31 x_msg_count => x_msg_count,
32 x_msg_data => x_msg_data,
33 p_ariv_tbl => p_ariv_tbl,
34 x_taiv_tbl => l_taiv_tbl);
35
36 END Create_Asset_Repair_Invoice;
37
38
39
40 -- Start of comments
41 --
42 -- Procedure Name : Validate_Repair_Approval
43 -- Description : Private API to Validate Repair Approval
44 -- Business Rules :
45 -- Parameters :
46 -- Version : 1.0
47 -- History : 29-APR-08 SECHAWLA 6797795 : Actual repair cost is required for approval
48 -- End of comments
49 PROCEDURE Validate_Repair_Approval (
50 x_return_status OUT NOCOPY VARCHAR2,
51 p_ariv_tbl IN ariv_tbl_type) IS
52
53
54 -- Get the asset condition details
55 CURSOR l_inv_csr ( p_acn_id IN NUMBER) IS
56 SELECT approved_yn,
57 acs_code,
58 part_name,
59 actual_repair_cost -- SECHAWLA 6797795 Added
60 FROM OKL_ASSET_CNDTN_LNS_V
61 WHERE id = p_acn_id;
62
63 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
64 l_approved_yn VARCHAR2(1);
65 l_acs_code VARCHAR2(200);
66 l_part_name VARCHAR2(200);
67 i NUMBER;
68 l_actual_repair_cost NUMBER; -- SECHAWLA 6797795 Added
69
70
71 BEGIN
72
73 -- *****************
74 -- Check all records
75 -- *****************
76
77 -- Initialize procedure variables
78 i := p_ariv_tbl.FIRST;
79
80 LOOP
81
82
83 -- *********************
84 -- Get/Set Database values
85 -- *********************
86
87 -- Get the database values for asset condition line
88 OPEN l_inv_csr (p_ariv_tbl(i).p_acn_id);
89 -- SECHAWLA 6797795 Added l_actual_repair_cost
90 FETCH l_inv_csr INTO l_approved_yn, l_acs_code, l_part_name, l_actual_repair_cost;
91 CLOSE l_inv_csr;
92
93 -- If value passed for approved_yn then take that else take DB value
94 IF p_ariv_tbl(i).p_approved_yn IS NOT NULL
95 AND p_ariv_tbl(i).p_approved_yn <> OKL_API.G_MISS_CHAR THEN
96 l_approved_yn := p_ariv_tbl(i).p_approved_yn;
97 END IF;
98
99 -- If value passed for acs_code then take that else take DB value
100 IF p_ariv_tbl(i).p_acs_code IS NOT NULL
101 AND p_ariv_tbl(i).p_acs_code <> OKL_API.G_MISS_CHAR THEN
102 l_acs_code := p_ariv_tbl(i).p_acs_code;
103 END IF;
104
105 -- If value passed for part_name then take that else take DB value
106 IF p_ariv_tbl(i).p_part_name IS NOT NULL
107 AND p_ariv_tbl(i).p_part_name <> OKL_API.G_MISS_CHAR THEN
108 l_part_name := p_ariv_tbl(i).p_part_name;
109 END IF;
110
111
112 -- *********************
113 -- Validate Approval
114 -- *********************
115
116 --29-APR-08 SECHAWLA 6797795
117 IF l_approved_yn = 'N' THEN
118 IF l_actual_repair_cost IS NULL THEN
119 IF l_part_name IS NULL THEN
120 --Message: You must enter Actual Repair Cost for this part.
121 OKL_API.set_message(p_app_name => G_APP_NAME,
122 p_msg_name => 'OKL_AM_REP_COST_REQ');
123 ELSE
124 -- Message: You must enter Actual Repair Cost for part PART_NAME
125 OKL_API.set_message(p_app_name => G_APP_NAME,
126 p_msg_name => 'OKL_AM_PART_REP_COST_REQ',
127 p_token1 => 'PART_NAME',
128 p_token1_value => l_part_name);
129
130 END IF;
131 l_return_status := OKL_API.G_RET_STS_ERROR;
132 END IF;
133 END IF;
134 --29-APR-08 SECHAWLA 6797795
135
136
137 -- Check if already approved
138 IF l_approved_yn = G_YES
139 OR l_acs_code = 'APPROVED' THEN
140 IF l_part_name IS NULL THEN
141 --added by rkuttiya Bug: 3528618
142 --Message: The invoice for this part is already approved.
143 OKL_API.set_message(p_app_name => G_APP_NAME,
144 p_msg_name => 'OKL_AM_ALRDY_APPROVED');
145 ELSE
146 -- Message: The invoice for part PART_NAME is already approved.
147 OKL_API.set_message(p_app_name => G_APP_NAME,
148 p_msg_name => 'OKL_AM_INV_ALRDY_APPROVED',
149 p_token1 => 'PART_NAME',
150 p_token1_value => l_part_name);
151
152 END IF;
153 l_return_status := OKL_API.G_RET_STS_ERROR;
154
155 END IF;
156
157 -- Check if waiting for approval
158 IF l_acs_code = 'WAITING_FOR_APPROVAL' THEN
159 IF l_part_name IS NULL THEN
160 --added by rkuttiya Bug:3528618
161 --Message: The invoice for this part is waiting for approval.
162 OKL_API.set_message(p_app_name => G_APP_NAME,
163 p_msg_name => 'OKL_AM_WAITING_APPROVAL');
164 ELSE
165 -- Message: The invoice for part PART_NAME is waiting for approval.
166 OKL_API.set_message(p_app_name => G_APP_NAME,
167 p_msg_name => 'OKL_AM_INV_WAITING_APPROVAL',
168 p_token1 => 'PART_NAME',
169 p_token1_value => l_part_name);
170
171 END IF;
172 l_return_status := OKL_API.G_RET_STS_ERROR;
173
174 END IF;
175
176
177 EXIT WHEN (i = p_ariv_tbl.LAST);
178 i := p_ariv_tbl.NEXT(i);
179 END LOOP;
180
181
182 -- *********************
183 -- Set return status
184 -- *********************
185
186 x_return_status := l_return_status;
187
188 EXCEPTION
189
190 WHEN OTHERS THEN
191
192 IF l_inv_csr%ISOPEN THEN
193 CLOSE l_inv_csr;
194 END IF;
195
196 -- Store SQL error message on message stack for caller
197 OKL_API.set_message(p_app_name => g_app_name,
198 p_msg_name => g_unexpected_error,
199 p_token1 => g_sqlcode_token,
200 p_token1_value => sqlcode,
201 p_token2 => g_sqlerrm_token,
202 p_token2_value => sqlerrm);
203
204 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
205 END Validate_Repair_Approval;
206
207
208
209 -- Start of comments
210 --
211 -- Procedure Name : Approve_Asset_Repair
212 -- Description : Approval of asset repair invoice
213 -- Business Rules :
214 -- Parameters :
215 -- Version : 1.0
216 --
217 -- End of comments
218 PROCEDURE Approve_Asset_Repair (
219 p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_ariv_tbl IN ariv_tbl_type,
225 x_ariv_tbl OUT NOCOPY ariv_tbl_type) IS
226
227
228 -- Get the condition header details
229 CURSOR l_acd_csr ( p_acn_id IN NUMBER) IS
230 SELECT id,
231 acd_id
232 FROM OKL_ASSET_CNDTN_LNS_V
233 WHERE id = p_acn_id;
234
235 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
236 l_api_name CONSTANT VARCHAR2(30):= 'Approve_Asset_Repair';
237 l_api_version CONSTANT NUMBER := 1;
238 i NUMBER;
239 l_event_name VARCHAR2(200);
240 l_acd_id NUMBER;
241 l_missing_lines BOOLEAN := FALSE;
242 l_id NUMBER := OKL_API.G_MISS_NUM;
243
244 lx_ariv_tbl ariv_tbl_type := p_ariv_tbl;
245 lp_acnv_tbl OKL_ASSET_CNDTN_LNS_PUB.acnv_tbl_type;
246 lx_acnv_tbl OKL_ASSET_CNDTN_LNS_PUB.acnv_tbl_type;
247
248 BEGIN
249
250 -- ***************************************************************
251 -- Check API version, initialize message list and create savepoint
252 -- ***************************************************************
253
254 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
255 G_PKG_NAME,
256 p_init_msg_list,
257 l_api_version,
258 p_api_version,
259 '_PVT',
260 x_return_status);
261
262
263 -- Raise exception when error
264 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
265 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
266 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
267 RAISE OKL_API.G_EXCEPTION_ERROR;
268 END IF;
269
270 -- *********************
271 -- Validate parameters
272 -- *********************
273
274 -- Check if atleast one record passed and valid value passed for p_acn_id
275 IF p_ariv_tbl.COUNT > 0 THEN
276
277 i := p_ariv_tbl.FIRST;
278 LOOP
279
280 -- Check if null or g_miss passed
281 IF ((p_ariv_tbl(i).p_acn_id IS NULL) OR
282 (p_ariv_tbl(i).p_acn_id = OKL_API.G_MISS_NUM)) THEN
283 l_missing_lines := TRUE;
284 END IF;
285
286 -- Get the record from table for acn_id passed
287 OPEN l_acd_csr ( p_ariv_tbl(i).p_acn_id);
288 FETCH l_acd_csr INTO l_id, l_acd_id;
289 CLOSE l_acd_csr;
290
291 -- Check right value for acn_id passed
292 IF l_id = OKL_API.G_MISS_NUM
293 OR l_id IS NULL THEN
294 l_missing_lines := TRUE;
295 END IF;
296
297 EXIT WHEN (i = p_ariv_tbl.LAST);
298 i := p_ariv_tbl.NEXT(i);
299 END LOOP;
300 ELSE
301 l_missing_lines := TRUE;
302 END IF;
303
304 -- If no records passed or if null/g_miss values passed then error
305 IF (l_missing_lines) THEN
306
307 -- Invalid value for p_acn_id.
308 OKL_API.SET_MESSAGE(p_app_name => OKC_API.G_APP_NAME,
309 p_msg_name => OKC_API.G_INVALID_VALUE,
310 p_token1 => G_COL_NAME_TOKEN,
311 p_token1_value => 'p_acn_id');
312
313 RAISE OKL_API.G_EXCEPTION_ERROR;
314 END IF;
315
316
317 -- *********************
318 -- Validate Repair Approval
319 -- *********************
320
321 Validate_Repair_Approval(
322 x_return_status => l_return_status,
323 p_ariv_tbl => p_ariv_tbl);
324
325 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
327 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
328 RAISE OKL_API.G_EXCEPTION_ERROR;
329 END IF;
330
331
332 -- *********************
333 -- Set Approval details
334 -- *********************
335
336 i := p_ariv_tbl.FIRST;
337 LOOP
338
339 -- Set the acnv_rec
340 lp_acnv_tbl(i).id := p_ariv_tbl(i).p_acn_id;
341 lp_acnv_tbl(i).acs_code := 'WAITING_FOR_APPROVAL';
342
343 lx_ariv_tbl(i).p_acs_code := 'WAITING_FOR_APPROVAL';
344
345 EXIT WHEN (i = p_ariv_tbl.LAST);
346 i := p_ariv_tbl.NEXT(i);
347 END LOOP;
348
349
350 -- *********************
351 -- Update Condition Lines
352 -- *********************
353
354 -- Update the asset condition line for the acs_code
355 OKL_ASSET_CNDTN_LNS_PUB.update_asset_cndtn_lns(
356 p_api_version => p_api_version,
357 p_init_msg_list => p_init_msg_list,
358 x_return_status => l_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 p_acnv_tbl => lp_acnv_tbl,
362 x_acnv_tbl => lx_acnv_tbl);
363
364 -- Raise exception when error
365 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
366 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
367 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
368 RAISE OKL_API.G_EXCEPTION_ERROR;
369 END IF;
370
371
372 -- *********************
373 -- Launch Approval WorkFlow
374 -- *********************
375
376 -- Launch the Approve Asset Repair WF
377 OKL_AM_WF.raise_business_event (
378 p_transaction_id => l_acd_id,
379 p_event_name => 'oracle.apps.okl.am.approveassetrepair');
380
381
382 -- *********************
383 -- Set message
384 -- *********************
385
386 -- Get the WF event name
387 l_event_name := OKL_AM_UTIL_PVT.get_wf_event_name(
388 p_wf_process_type => 'OKLAMAAR',
389 p_wf_process_name => 'APPROVE_ASSET_REPAIR_PROC',
390 x_return_status => l_return_status);
391
392 -- Raise exception when error
393 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
394 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
395 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
396 RAISE OKL_API.G_EXCEPTION_ERROR;
397 END IF;
398
399
400 -- Set message on stack
401 -- Message: Workflow event EVENT_NAME has been requested.
402 OKL_API.set_message(p_app_name => G_APP_NAME,
403 p_msg_name => 'OKL_AM_WF_EVENT_MSG',
404 p_token1 => 'EVENT_NAME',
405 p_token1_value => l_event_name);
406
407
408 -- *********************
409 -- Set return values
410 -- *********************
411
412 x_ariv_tbl := lx_ariv_tbl;
413 x_return_status := l_return_status;
414
415
416 -- *********************
417 -- End the transaction
418 -- *********************
419
420 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
421
422
423 EXCEPTION
424 WHEN OKL_API.G_EXCEPTION_ERROR THEN
425
426 IF l_acd_csr%ISOPEN THEN
427 CLOSE l_acd_csr;
428 END IF;
429
430 x_return_status := OKL_API.HANDLE_EXCEPTIONS
431 (
432 l_api_name,
433 G_PKG_NAME,
434 'OKL_API.G_RET_STS_ERROR',
435 x_msg_count,
436 x_msg_data,
437 '_PVT'
438 );
439 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
440
441 IF l_acd_csr%ISOPEN THEN
442 CLOSE l_acd_csr;
443 END IF;
444
445 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
446 (
447 l_api_name,
448 G_PKG_NAME,
449 'OKL_API.G_RET_STS_UNEXP_ERROR',
450 x_msg_count,
451 x_msg_data,
452 '_PVT'
453 );
454 WHEN OTHERS THEN
455
456 IF l_acd_csr%ISOPEN THEN
457 CLOSE l_acd_csr;
458 END IF;
459
460 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
461 (
462 l_api_name,
463 G_PKG_NAME,
464 'OTHERS',
465 x_msg_count,
466 x_msg_data,
467 '_PVT'
468 );
469 END Approve_Asset_Repair;
470
471 END OKL_AM_ARINV_PVT;