[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_CAP_PURPOSE_PVT
Source
1 PACKAGE BODY OKL_BPD_CAP_PURPOSE_PVT AS
2 /* $Header: OKLRCPUB.pls 120.8 2007/08/24 05:54:13 asawanka noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE create_purpose
5 ---------------------------------------------------------------------------
6 -- Start of comments
7 --
8 -- Procedure Name : create_purpose
9 -- Description : procedure for inserting the records in
10 -- table OKL_TXL_RCPT_APPS_B
11 -- Business Rules :
12 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
13 -- x_msg_data, p_strm_tbl, x_strm_tbl.
14 -- Version : 1.0
15 -- End of comments
16 ---------------------------------------------------------------------------
17
18 PROCEDURE create_purpose( p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,p_strm_tbl IN okl_cash_dtls_tbl_type
24 ,x_strm_tbl OUT NOCOPY okl_cash_dtls_tbl_type
25 ) IS
26
27 ---------------------------
28 -- DECLARE Local Variables
29 ---------------------------
30 l_strm_rec okl_cash_dtls_rec_type;
31 l_strm_tbl okl_cash_dtls_tbl_type;
32 l_cust_num AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL;
33 l_rct_id_details OKL_TXL_RCPT_APPS_B.RCT_ID_DETAILS%TYPE DEFAULT NULL;
34 l_total_amount_applied OKL_TXL_RCPT_APPS_B.AMOUNT%TYPE DEFAULT NULL;
35 l_receipt_amount OKL_TXL_RCPT_APPS_B.AMOUNT%TYPE DEFAULT NULL;
36 l_org_id OKL_TXL_RCPT_APPS_B.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
37 i NUMBER DEFAULT NULL;
38 j NUMBER DEFAULT NULL;
39 l_api_version NUMBER := 1.0;
40 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
41 l_return_status VARCHAR2(1);
42 l_msg_count NUMBER := 0;
43 l_msg_data VARCHAR2(2000);
44 l_api_name CONSTANT VARCHAR2(30) := 'create_purpose';
45
46 ------------------------------
47 -- DECLARE Record/Table Types
48 ------------------------------
49
50 -- Internal Trans
51
52 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
53 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
54
55 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
56 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
57
58 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
59 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
60
61 -------------------
62 -- DECLARE Cursors
63 -------------------
64
65 -- cursor to fetch the receipt id.
66 CURSOR c_get_int_recpt_id (cp_receipt_id IN l_strm_rec.receipt_id%TYPE)
67 IS
68 SELECT int.id
69 FROM OKL_TRX_CSH_RECEIPT_B int--,
70 -- OKL_EXT_CSH_RCPTS_B ext
71 -- WHERE int.id = ext.RCT_ID
72 -- and ext.ID = cp_receipt_id;
73 WHERE int.cash_receipt_id = cp_receipt_id;
74 ----------
75
76 -- cursor to fetch the amount for the receipt
77 --asawanka modified cursor for receipts project
78 CURSOR c_get_recpt_amt (cp_receipt_id IN l_strm_rec.receipt_id%TYPE)
79 IS
80 SELECT amount
81 FROM ar_cash_receipts_all b
82 WHERE b.cash_receipt_id = cp_receipt_id;
83 ----------
84
85 -- get org_id for contract
86 CURSOR c_get_org_id (cp_contract_id IN VARCHAR2) IS
87 SELECT authoring_org_id
88 FROM OKC_K_HEADERS_B
89 WHERE id = cp_contract_id;
90
91 ----------
92
93
94 BEGIN
95 OKC_API.init_msg_list(p_init_msg_list);
96 SAVEPOINT create_purpose_PVT;
97 -- Initialize....
98 l_strm_tbl := p_strm_tbl;
99 IF l_strm_tbl.COUNT = 0 THEN
100 -- Message Text: no allocation required if no records are found in the table...
101 x_return_status := OKC_API.G_RET_STS_ERROR;
102 OKC_API.set_message( p_app_name => G_APP_NAME,
103 p_msg_name =>'OKL_BPD_NO_ALLOC_REQ');
104 RAISE G_EXCEPTION_HALT_VALIDATION;
105 END IF;
106
107 -- check, amount applied = the receipt amount ...
108 j := l_strm_tbl.FIRST;
109 l_strm_rec := l_strm_tbl(j);
110
111 -- Check the amount against stream amounts
112 OPEN c_get_int_recpt_id (l_strm_rec.receipt_id);
113 FETCH c_get_int_recpt_id INTO l_rct_id_details;
114 CLOSE c_get_int_recpt_id;
115
116 OPEN c_get_recpt_amt (l_strm_rec.receipt_id);
117 FETCH c_get_recpt_amt INTO l_receipt_amount;
118 CLOSE c_get_recpt_amt;
119
120 IF l_strm_rec.contract_id IS NOT NULL THEN
121 OPEN c_get_org_id(l_strm_rec.contract_id);
122 FETCH c_get_org_id into l_org_id;
123 CLOSE c_get_org_id;
124 END IF;
125
126 l_total_amount_applied := 0;
127 LOOP
128 l_total_amount_applied := l_total_amount_applied + l_strm_tbl(j).amount;
129 EXIT WHEN j = (l_strm_tbl.LAST);
130 j := l_strm_tbl.next(j);
131 END LOOP;
132
133
134 IF l_receipt_amount < l_total_amount_applied THEN
135 -- Message Text: the amount applied must be equal to receipt amount
136 x_return_status := OKC_API.G_RET_STS_ERROR;
137 OKC_API.set_message( p_app_name => G_APP_NAME,
138 p_msg_name =>'OKL_BPD_RCPT_ALLOC_ERR');
139 RAISE G_EXCEPTION_HALT_VALIDATION;
140 END IF;
141
142
143 i := l_strm_tbl.FIRST;
144 j := l_strm_tbl.FIRST;
145
146 LOOP
147 l_rcav_tbl(i).rct_id_details := l_rct_id_details;
148 l_rcav_tbl(i).sty_id := l_strm_tbl(j).sty_id;
149 IF (l_rcav_tbl(i).sty_id = NULL OR
150 l_rcav_tbl(i).sty_id = Okl_Api.G_MISS_NUM ) THEN
151 x_return_status := OKC_API.G_RET_STS_ERROR;
152 OKC_API.set_message( p_app_name => G_APP_NAME,
153 p_msg_name =>'OKL_BPD_RCPT_ALLOC_ERR');
154 RAISE G_EXCEPTION_HALT_VALIDATION;
155 END IF;
156
157 l_rcav_tbl(i).amount := l_strm_tbl(j).amount;
158 l_rcav_tbl(i).ile_id := l_strm_tbl(j).customer_id;
159 l_rcav_tbl(i).khr_id := l_strm_tbl(j).contract_id;
160 l_rcav_tbl(i).org_id := l_org_id;
161 EXIT WHEN (j = l_strm_tbl.LAST);
162 i := l_strm_tbl.next(i);
163 j := i;
164 END LOOP;
165
166 -- Inserting record into the table.
167 okl_Txl_Rcpt_Apps_Pub.insert_txl_rcpt_apps( l_api_version,
168 l_init_msg_list,
169 l_return_status,
170 l_msg_count,
171 l_msg_data,
172 l_rcav_tbl,
173 x_rcav_tbl
174 );
175
176 x_return_status := l_return_status;
177 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
179 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
180 RAISE OKL_API.G_EXCEPTION_ERROR;
181 END IF;
182
183 -- exceptions........
184
185 EXCEPTION
186 WHEN G_EXCEPTION_HALT_VALIDATION THEN
187 ROLLBACK TO create_purpose_PVT;
188 x_return_status := Fnd_Api.G_RET_STS_ERROR;
189 x_msg_count := l_msg_count ;
190 x_msg_data := l_msg_data ;
191 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
192 ,p_data => x_msg_data);
193
194
195 WHEN OKC_API.G_EXCEPTION_ERROR THEN
196 ROLLBACK TO create_purpose_PVT;
197 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
198 x_msg_count := l_msg_count ;
199 x_msg_data := l_msg_data ;
200 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
201 ,p_data => x_msg_data);
202
203 WHEN OTHERS THEN
204 ROLLBACK TO create_purpose_PVT;
205 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
206 x_msg_count := l_msg_count ;
207 x_msg_data := l_msg_data ;
208 Fnd_Msg_Pub.ADD_EXC_MSG('Okl_Bpd_Cap_Purpose_Pvt','create_purpose');
209 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
210 ,p_data => x_msg_data);
211
212 END create_purpose;
213 ---------------------------------------------------------------------------
214 -- PROCEDURE update_purpose
215 ---------------------------------------------------------------------------
216 -- Start of comments
217 --
218 -- Procedure Name : update_purpose
219 -- Description : procedure for updating the records in
220 -- table OKL_TXL_RCPT_APPS_B
221 -- Business Rules :
222 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
223 -- x_msg_data, p_strm_tbl, x_strm_tbl.
224 -- Version : 1.0
225 -- End of comments
226 ---------------------------------------------------------------------------
227
228 PROCEDURE update_purpose( p_api_version IN NUMBER
229 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
230 ,x_return_status OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY NUMBER
232 ,x_msg_data OUT NOCOPY VARCHAR2
233 ,p_strm_tbl IN okl_cash_dtls_tbl_type
234 ,x_strm_tbl OUT NOCOPY okl_cash_dtls_tbl_type
235 ) IS
236
237 ---------------------------
238 -- DECLARE Local Variables
239 ---------------------------
240 l_strm_rec okl_cash_dtls_rec_type;
241 l_strm_tbl okl_cash_dtls_tbl_type;
242 l_org_id OKL_TXL_RCPT_APPS_B.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
243 i NUMBER DEFAULT NULL;
244 j NUMBER DEFAULT NULL;
245 l_api_version NUMBER := 1.0;
246 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
247 l_return_status VARCHAR2(1);
248 l_msg_count NUMBER := 0;
249 l_msg_data VARCHAR2(2000);
250 l_api_name CONSTANT VARCHAR2(30) := 'create_purpose';
251
252 ------------------------------
253 -- DECLARE Record/Table Types
254 ------------------------------
255
256 -- Internal Trans
257
258 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
259 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
260
261 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
262 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
263
264 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
265 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
266
267 -------------------
268 -- DECLARE Cursors
269 -------------------
270
271 -- get org_id for contract
272 CURSOR c_get_org_id (cp_contract_id IN VARCHAR2) IS
273 SELECT authoring_org_id
274 FROM OKC_K_HEADERS_B
275 WHERE id = cp_contract_id;
276
277 ----------
278
279
280 BEGIN
281 OKC_API.init_msg_list(p_init_msg_list);
282 SAVEPOINT update_purpose_PVT;
283 -- Initialize....
284 l_strm_tbl := p_strm_tbl;
285 IF l_strm_tbl.COUNT = 0 THEN
286 -- Message Text: no allocation required if no records are found in the table...
287 x_return_status := OKC_API.G_RET_STS_ERROR;
288 OKC_API.set_message( p_app_name => G_APP_NAME,
289 p_msg_name =>'OKL_BPD_NO_ALLOC_REQ');
290 RAISE G_EXCEPTION_HALT_VALIDATION;
291 END IF;
292
293 j := l_strm_tbl.FIRST;
294 l_strm_rec := l_strm_tbl(j);
295
296 IF l_strm_rec.contract_id IS NOT NULL THEN
297 OPEN c_get_org_id(l_strm_rec.contract_id);
298 FETCH c_get_org_id into l_org_id;
299 CLOSE c_get_org_id;
300 END IF;
301
302 i := l_strm_tbl.FIRST;
303 j := l_strm_tbl.FIRST;
304
305 LOOP
306 l_rcav_tbl(i).id := l_strm_tbl(j).id;
307 l_rcav_tbl(i).sty_id := l_strm_tbl(j).sty_id;
308 IF (l_rcav_tbl(i).sty_id = NULL OR
309 l_rcav_tbl(i).sty_id = Okl_Api.G_MISS_NUM ) THEN
310 x_return_status := OKC_API.G_RET_STS_ERROR;
311 OKC_API.set_message( p_app_name => G_APP_NAME,
312 p_msg_name =>'OKL_BPD_RCPT_ALLOC_ERR');
313 RAISE G_EXCEPTION_HALT_VALIDATION;
314 END IF;
315 l_rcav_tbl(i).amount := l_strm_tbl(j).amount;
316 l_rcav_tbl(i).org_id := l_org_id;
317 EXIT WHEN (j = l_strm_tbl.LAST);
318 i := l_strm_tbl.next(i);
319 j := i;
320 END LOOP;
321
322 -- Updating record into the table.
323 okl_Txl_Rcpt_Apps_Pub.update_txl_rcpt_apps( l_api_version,
324 l_init_msg_list,
325 l_return_status,
326 l_msg_count,
327 l_msg_data,
328 l_rcav_tbl,
329 x_rcav_tbl
330 );
331
332 x_return_status := l_return_status;
333 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
334 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
335 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
336 RAISE OKL_API.G_EXCEPTION_ERROR;
337 END IF;
338
339 -- exceptions........
340
341 EXCEPTION
342 WHEN G_EXCEPTION_HALT_VALIDATION THEN
343 ROLLBACK TO update_purpose_PVT;
344 x_return_status := Fnd_Api.G_RET_STS_ERROR;
345 x_msg_count := l_msg_count ;
346 x_msg_data := l_msg_data ;
347 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
348 ,p_data => x_msg_data);
349
350
351 WHEN OKC_API.G_EXCEPTION_ERROR THEN
352 ROLLBACK TO update_purpose_PVT;
353 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
354 x_msg_count := l_msg_count ;
355 x_msg_data := l_msg_data ;
356 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
357 ,p_data => x_msg_data);
358
359 WHEN OTHERS THEN
360 ROLLBACK TO update_purpose_PVT;
361 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
362 x_msg_count := l_msg_count ;
363 x_msg_data := l_msg_data ;
364 Fnd_Msg_Pub.ADD_EXC_MSG('Okl_Bpd_Cap_Purpose_Pvt','create_purpose');
365 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
366 ,p_data => x_msg_data);
367
368 END update_purpose;
369
370 ---------------------------------------------------------------------------
371 -- PROCEDURE delete_purpose
372 ---------------------------------------------------------------------------
373 -- Start of comments
374 --
375 -- Procedure Name : delete_purpose
376 -- Description : procedure for deleting the records in
377 -- table OKL_TXL_RCPT_APPS_B
378 -- Business Rules :
379 -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
380 -- x_msg_data, p_strm_tbl, x_strm_tbl.
381 -- Version : 1.0
382 -- End of comments
383 ---------------------------------------------------------------------------
384
385 PROCEDURE delete_purpose( p_api_version IN NUMBER
386 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
387 ,x_return_status OUT NOCOPY VARCHAR2
388 ,x_msg_count OUT NOCOPY NUMBER
389 ,x_msg_data OUT NOCOPY VARCHAR2
390 ,p_strm_tbl IN okl_cash_dtls_tbl_type
391 ,x_strm_tbl OUT NOCOPY okl_cash_dtls_tbl_type
392 ) IS
393
394 ---------------------------
395 -- DECLARE Local Variables
396 ---------------------------
397 l_strm_rec okl_cash_dtls_rec_type;
398 l_strm_tbl okl_cash_dtls_tbl_type;
399 l_org_id OKL_TXL_RCPT_APPS_B.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
400 i NUMBER DEFAULT NULL;
401 j NUMBER DEFAULT NULL;
402 l_api_version NUMBER := 1.0;
403 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
404 l_return_status VARCHAR2(1);
405 l_msg_count NUMBER := 0;
406 l_msg_data VARCHAR2(2000);
407 l_api_name CONSTANT VARCHAR2(30) := 'create_purpose';
408
409 ------------------------------
410 -- DECLARE Record/Table Types
411 ------------------------------
412
413 -- Internal Trans
414
415 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
416 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
417
418 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
419 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
420
421 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
422 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
423
424 -------------------
425 -- DECLARE Cursors
426 -------------------
427
428 -- get org_id for contract
429 CURSOR c_get_org_id (cp_contract_id IN VARCHAR2) IS
430 SELECT authoring_org_id
431 FROM OKC_K_HEADERS_B
432 WHERE id = cp_contract_id;
433
434 ----------
435
436
437 BEGIN
438 OKC_API.init_msg_list(p_init_msg_list);
439 SAVEPOINT delete_purpose_PVT;
440 -- Initialize....
441 l_strm_tbl := p_strm_tbl;
442 IF l_strm_tbl.COUNT = 0 THEN
443 -- Message Text: no allocation required if no records are found in the table...
444 x_return_status := OKC_API.G_RET_STS_ERROR;
445 OKC_API.set_message( p_app_name => G_APP_NAME,
446 p_msg_name =>'OKL_BPD_NO_ALLOC_REQ');
447 RAISE G_EXCEPTION_HALT_VALIDATION;
448 END IF;
449
450
451 j := l_strm_tbl.FIRST;
452 l_strm_rec := l_strm_tbl(j);
453
454 IF l_strm_rec.contract_id IS NOT NULL THEN
455 OPEN c_get_org_id(l_strm_rec.contract_id);
456 FETCH c_get_org_id into l_org_id;
457 CLOSE c_get_org_id;
458 END IF;
459
460
461 i := l_strm_tbl.FIRST;
462 j := l_strm_tbl.FIRST;
463
464 LOOP
465 l_rcav_tbl(i).id := l_strm_tbl(j).id;
466 l_rcav_tbl(i).org_id := l_org_id;
467 l_rcav_tbl(i).amount := l_strm_tbl(j).amount;
468 EXIT WHEN (j = l_strm_tbl.LAST);
469 i := l_strm_tbl.next(i);
470 j := i;
471 END LOOP;
472
473 -- deleting record into the table.
474 okl_Txl_Rcpt_Apps_Pub.delete_txl_rcpt_apps( l_api_version,
475 l_init_msg_list,
476 l_return_status,
477 l_msg_count,
478 l_msg_data,
479 l_rcav_tbl
480 );
481
482 x_return_status := l_return_status;
483 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
484 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
485 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
486 RAISE OKL_API.G_EXCEPTION_ERROR;
487 END IF;
488
489 -- exceptions........
490
491 EXCEPTION
492 WHEN G_EXCEPTION_HALT_VALIDATION THEN
493 ROLLBACK TO delete_purpose_PVT;
494 x_return_status := Fnd_Api.G_RET_STS_ERROR;
495 x_msg_count := l_msg_count ;
496 x_msg_data := l_msg_data ;
497 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
498 ,p_data => x_msg_data);
499
500
501 WHEN OKC_API.G_EXCEPTION_ERROR THEN
502 ROLLBACK TO delete_purpose_PVT;
503 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
504 x_msg_count := l_msg_count ;
505 x_msg_data := l_msg_data ;
506 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
507 ,p_data => x_msg_data);
508
509 WHEN OTHERS THEN
510 ROLLBACK TO delete_purpose_PVT;
511 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
512 x_msg_count := l_msg_count ;
513 x_msg_data := l_msg_data ;
514 Fnd_Msg_Pub.ADD_EXC_MSG('Okl_Bpd_Cap_Purpose_Pvt','create_purpose');
515 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
516 ,p_data => x_msg_data);
517
518 END delete_purpose;
519 END OKL_BPD_CAP_PURPOSE_PVT;
520