[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_DISB_RULES_PVT
Source
1 PACKAGE BODY OKL_SETUP_DISB_RULES_PVT AS
2 /* $Header: OKLRSDRB.pls 120.10 2007/08/17 09:35:12 gkhuntet noship $ */
3
4 PROCEDURE validate_rule_eff_dates( p_api_version IN NUMBER
5 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
6 , x_return_status OUT NOCOPY VARCHAR2
7 , x_msg_count OUT NOCOPY NUMBER
8 , x_msg_data OUT NOCOPY VARCHAR2
9 , p_drav_rec IN drav_rec_type
10 , p_drs_tbl IN drs_tbl_type
11 , p_drv_tbl IN drv_tbl_type
12 )
13 IS
14 l_api_name CONSTANT VARCHAR2(40) := 'validate_rule_eff_dates';
15 l_api_version CONSTANT NUMBER := 1;
16 l_init_msg_list VARCHAR2(1);
17 sty_count NUMBER;
18 vsite_count NUMBER;
19 l_disb_start_date OKL_DISB_RULES_B.START_DATE%TYPE;
20 l_disb_end_date OKL_DISB_RULES_B.END_DATE%TYPE;
21 l_vsite_start_date OKL_DISB_RULE_VENDOR_SITES.START_DATE%TYPE;
22 l_vsite_end_date OKL_DISB_RULE_VENDOR_SITES.END_DATE%TYPE;
23 l_exist_start_date OKL_DISB_RULE_VENDOR_SITES.START_DATE%TYPE;
24 l_exist_end_date OKL_DISB_RULE_VENDOR_SITES.END_DATE%TYPE;
25
26
27 CURSOR vendor_site_eff_dates_csr( p_sty_purpose IN OKL_DISB_RULE_STY_TYPES.STREAM_TYPE_PURPOSE%TYPE
28 , p_vendor_id IN OKL_DISB_RULE_VENDOR_SITES.VENDOR_ID%TYPE
29 , p_vendor_site_id IN OKL_DISB_RULE_VENDOR_SITES.VENDOR_SITE_ID%TYPE
30 , p_disb_rule_vendor_site_id IN OKL_DISB_RULE_VENDOR_SITES.DISB_RULE_VENDOR_SITE_ID%TYPE)
31 IS
32 SELECT disb_vsites.start_date
33 , disb_vsites.end_date
34 FROM okl_disb_rule_sty_types disb_sty,
35 okl_disb_rule_vendor_sites disb_vsites
36 WHERE disb_sty.stream_type_purpose = p_sty_purpose
37 AND disb_sty.disb_rule_id = disb_vsites.disb_rule_id
38 AND disb_vsites.vendor_id = p_vendor_id
39 AND disb_vsites.vendor_site_id = p_vendor_site_id
40 AND disb_vsites.disb_rule_vendor_site_id <> NVL (p_disb_rule_vendor_site_id , -1);
41
42 BEGIN
43 -- Initialization
44 x_return_status := OKL_API.G_RET_STS_SUCCESS;
45
46 x_return_status := OKL_API.START_ACTIVITY( l_api_name
47 , g_pkg_name
48 , p_init_msg_list
49 , l_api_version
50 , p_api_version
51 , '_PVT'
52 , x_return_status);
53 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
54 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
55 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
56 RAISE OKL_API.G_EXCEPTION_ERROR;
57 END IF;
58
59 -- Check whether disbursement rule end date is less than start date
60 l_disb_start_date := p_drav_rec.start_date;
61 l_disb_end_date := p_drav_rec.end_date;
62
63 ----g_debug_proc('DATE from DRAV ' || l_disb_start_date || ' ' || l_disb_end_date);
64 --DBMS_OUTPUT.PUT_LINE('Date count DRAV ' || l_disb_start_date || ' ' || l_disb_end_date);
65
66 IF ((l_disb_start_date IS NOT NULL) AND (l_disb_start_date <> FND_API.G_MISS_DATE)
67 AND (l_disb_end_date IS NOT NULL) AND (l_disb_end_date <> FND_API.G_MISS_DATE)) THEN
68 IF (l_disb_end_date < l_disb_start_date) THEN
69 OKL_API.SET_MESSAGE( p_app_name => g_app_name
70 , p_msg_name => G_OKL_ST_DISB_EFF_DATE_ERR);
71 RAISE OKL_API.G_EXCEPTION_ERROR;
72 END IF;
73 END IF;
74
75 -- Validate vendor sites effective dates
76 IF ( p_drv_tbl.COUNT > 0 ) THEN
77 FOR vsite_count IN p_drv_tbl.FIRST .. p_drv_tbl.LAST
78 LOOP
79 -- Check whether disbursement rule vendor site end date is less than start date
80
81 l_vsite_start_date := p_drv_tbl(vsite_count).start_date;
82 l_vsite_end_date := p_drv_tbl(vsite_count).end_date;
83
84
85 --DBMS_OUTPUT.PUT_LINE('Date count DRV ' || l_vsite_start_date || ' ' || l_vsite_end_date);
86 --DBMS_OUTPUT.PUT_LINE('Date count DRAV ' || l_disb_start_date || ' ' || l_disb_end_date);
87
88 IF ((l_vsite_start_date IS NOT NULL) AND (l_vsite_start_date <> FND_API.G_MISS_DATE)
89 AND (l_vsite_end_date IS NOT NULL) AND (l_vsite_end_date <> FND_API.G_MISS_DATE)) THEN
90 IF (l_vsite_end_date < l_vsite_start_date) THEN
91 OKL_API.SET_MESSAGE( p_app_name => g_app_name
92 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
93 RAISE OKL_API.G_EXCEPTION_ERROR;
94 END IF;
95 END IF;
96
97 ----g_debug_proc('DRV Check');
98
99 -- Check whether vendor sites effective dates are within disbursement rules
100 -- effective date range
101 ----g_debug_proc('Date from DRV ' || vsite_count || ' ' || l_vsite_start_date || ' ' || l_vsite_end_date);
102
103 --DBMS_OUTPUT.PUT_LINE('DATE COUNTE ');
104 IF ((l_vsite_start_date IS NOT NULL) AND (l_vsite_start_date <> FND_API.G_MISS_DATE)
105 AND (l_vsite_start_date < l_disb_start_date)) THEN
106 OKL_API.SET_MESSAGE( p_app_name => g_app_name
107 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
108 --g_debug_proc('1');
109 RAISE OKL_API.G_EXCEPTION_ERROR;
110 ELSIF ((l_disb_end_date IS NOT NULL) AND (l_disb_end_date <> FND_API.G_MISS_DATE)
111 AND (l_vsite_start_date > l_disb_end_date)) THEN
112 OKL_API.SET_MESSAGE( p_app_name => g_app_name
113 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
114 --g_debug_proc('2');
115 RAISE OKL_API.G_EXCEPTION_ERROR;
116 ELSIF ((l_vsite_end_date IS NOT NULL) AND (l_vsite_end_date <> FND_API.G_MISS_DATE)
117 AND (l_vsite_end_date > l_disb_end_date)) THEN
118 OKL_API.SET_MESSAGE( p_app_name => g_app_name
119 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
120 --g_debug_proc('3');
121 RAISE OKL_API.G_EXCEPTION_ERROR;
122 END IF;
123 END LOOP;
124 END IF;
125
126 --g_debug_proc('All Vendor sites effective dates are within disbursement rules dates.');
127
128 --DBMS_OUTPUT.PUT_LINE('D1');
129 -- Check whether effective dates overlaps with the existing vendor site effective dates
130 -- for the same stream type purpose in any other disbursement rule.
131 IF ( (p_drs_tbl.COUNT > 0) AND (p_drv_tbl.COUNT > 0) ) THEN
132 FOR sty_count IN p_drs_tbl.FIRST .. p_drs_tbl.LAST
133 LOOP
134 -- Loop through all the vendor sites for the disbursement rule
135 FOR vsite_count IN p_drv_tbl.FIRST .. p_drv_tbl.LAST
136 LOOP
137 FOR vsite_eff_dates_rec IN vendor_site_eff_dates_csr( p_drs_tbl(sty_count).stream_type_purpose
138 , p_drv_tbl(vsite_count).vendor_id
139 , p_drv_tbl(vsite_count).vendor_site_id
140 , p_drv_tbl(vsite_count).disb_rule_vendor_site_id)
141 LOOP
142 --DBMS_OUTPUT.PUT_LINE('D2 a');
143 l_exist_start_date := vsite_eff_dates_rec.start_date;
144 l_exist_end_date := vsite_eff_dates_rec.end_date;
145 l_vsite_start_date := p_drv_tbl(vsite_count).start_date;
146 l_vsite_end_date := p_drv_tbl(vsite_count).end_date;
147 IF ( (l_exist_start_date IS NOT NULL) AND (l_exist_start_date <> FND_API.G_MISS_DATE)) THEN
148 IF ( (l_exist_end_date IS NOT NULL) AND (l_exist_end_date <> FND_API.G_MISS_DATE)) THEN
149 IF ( ((l_vsite_start_date IS NOT NULL) AND (l_vsite_start_date <> FND_API.G_MISS_DATE)
150 AND (l_vsite_start_date BETWEEN l_exist_start_date AND l_exist_end_date))
151 OR
152 ((l_vsite_end_date IS NOT NULL) AND (l_vsite_end_date <> FND_API.G_MISS_DATE)
153 AND (l_vsite_end_date BETWEEN l_exist_start_date AND l_exist_end_date)) ) THEN
154 OKL_API.SET_MESSAGE( p_app_name => g_app_name
155 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
156 RAISE OKL_API.G_EXCEPTION_ERROR;
157 END IF;
158 ELSIF ((l_vsite_end_date IS NULL) OR (l_vsite_end_date = FND_API.G_MISS_DATE)
159 OR ( l_vsite_end_date > l_exist_start_date )) THEN
160 --DBMS_OUTPUT.PUT_LINE('D2 b');
161 OKL_API.SET_MESSAGE( p_app_name => g_app_name
162 , p_msg_name => G_OKL_ST_DISB_VSITE_DATE_ERR);
163 RAISE OKL_API.G_EXCEPTION_ERROR;
164 END IF;
165 END IF;
166 END LOOP;
167 END LOOP;
168 END LOOP;
169 END IF;
170 --g_debug_proc('effective dates overlaps with the existing vendor site effective dates');
171 --DBMS_OUTPUT.PUT_LINE('D3');
172 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
173
174 EXCEPTION
175 WHEN OKL_API.G_EXCEPTION_ERROR THEN
176 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
177 , g_pkg_name
178 , 'OKL_API.G_RET_STS_ERROR'
179 , x_msg_count
180 , x_msg_data
181 , '_PVT' );
182 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
183 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
184 , g_pkg_name
185 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
186 , x_msg_count
187 , x_msg_data
188 , '_PVT' );
189 WHEN OTHERS THEN
190 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
191 , g_pkg_name
192 , 'OTHERS'
193 , x_msg_count
194 , x_msg_data
195 , '_PVT' );
196 END validate_rule_eff_dates;
197
198
199 -- Start of comments
200 -- API name : validate_sequence_range
201 -- Pre-reqs : None
202 -- Function : validates disbursement rule vendor sites sequence range.
203 -- Parameters :
204 -- IN : p_api_version - Standard input parameter
205 -- p_init_msg_list - Standard input parameter
206 -- p_drv_rec - record type for OKL_DISB_RULE_VENDOR_SITES
207 -- Version : 1.0
208 -- History : gboomina created.
209 -- End of comments
210
211 PROCEDURE validate_sequence_range( p_api_version IN NUMBER
212 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
213 , x_return_status OUT NOCOPY VARCHAR2
214 , x_msg_count OUT NOCOPY NUMBER
215 , x_msg_data OUT NOCOPY VARCHAR2
216 , p_drv_tbl IN drv_tbl_type
217 )
218 IS
219 l_api_name CONSTANT VARCHAR2(40) := 'validate_sequence_range';
220 l_api_version CONSTANT NUMBER := 1;
221 l_init_msg_list VARCHAR2(1);
222 vsite_count NUMBER;
223 l_new_seq_start OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_START%TYPE;
224 l_new_seq_end OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_END%TYPE;
225 l_existing_seq_start OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_START%TYPE;
226 l_existing_seq_end OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_END%TYPE;
227
228
229 CURSOR vendor_site_seq_range_csr( p_vendor_id IN OKL_DISB_RULE_VENDOR_SITES.VENDOR_ID%TYPE
230 , p_vendor_site_id IN OKL_DISB_RULE_VENDOR_SITES.VENDOR_SITE_ID%TYPE
231 , p_disb_rule_vendor_site_id IN OKL_DISB_RULE_VENDOR_SITES.DISB_RULE_VENDOR_SITE_ID%TYPE)
232 IS
233 SELECT disb_vsites.invoice_seq_start
234 , disb_vsites.invoice_seq_end
235 FROM okl_disb_rule_vendor_sites disb_vsites
236 WHERE disb_vsites.vendor_id = p_vendor_id
237 AND disb_vsites.vendor_site_id = p_vendor_site_id
238 AND disb_vsites.disb_rule_vendor_site_id <> NVL (p_disb_rule_vendor_site_id , -1) ;
239
240 BEGIN
241 -- Initialization
242 x_return_status := OKL_API.G_RET_STS_SUCCESS;
243
244 x_return_status := OKL_API.START_ACTIVITY( l_api_name
245 , g_pkg_name
246 , p_init_msg_list
247 , l_api_version
248 , p_api_version
249 , '_PVT'
250 , x_return_status);
251 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
252 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
253 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
254 RAISE OKL_API.G_EXCEPTION_ERROR;
255 END IF;
256 --DBMS_OUTPUT.PUT_LINE('S1');
257 IF (p_drv_tbl.COUNT > 0) THEN
258 FOR vsite_count IN p_drv_tbl.FIRST .. p_drv_tbl.LAST
259 LOOP
260 l_new_seq_start := p_drv_tbl(vsite_count).invoice_seq_start;
261 l_new_seq_end := p_drv_tbl(vsite_count).invoice_seq_end;
262 -- Check whether new seq end is greater than seq start
263 IF ((l_new_seq_start IS NOT NULL) AND (l_new_seq_start <> FND_API.G_MISS_NUM)
264 AND (l_new_seq_end IS NOT NULL) AND (l_new_seq_end <> FND_API.G_MISS_NUM)
265 AND (l_new_seq_end < l_new_seq_start)) THEN
266 OKL_API.SET_MESSAGE( p_app_name => g_app_name
267 , p_msg_name => G_OKL_ST_DISB_SEQ_RANGE_ERR);
268 RAISE OKL_API.G_EXCEPTION_ERROR;
269 END IF;
270
271 --DBMS_OUTPUT.PUT_LINE('S2 ' || l_new_seq_start || ' ' || l_new_seq_end);
272 -- Check whether vendor site seq range overlaps with the any
273 -- existing rules vendor site seq range
274 FOR vendor_site_seq_range_rec IN vendor_site_seq_range_csr( p_drv_tbl(vsite_count).vendor_id
275 , p_drv_tbl(vsite_count).vendor_site_id
276 , p_drv_tbl(vsite_count).disb_rule_vendor_site_id)
277 LOOP
278 l_existing_seq_start := vendor_site_seq_range_rec.invoice_seq_start;
279 l_existing_seq_end := vendor_site_seq_range_rec.invoice_seq_end;
280 --DBMS_OUTPUT.PUT_LINE('S2 ' || l_existing_seq_start || ' ' || l_existing_seq_end);
281 IF ( (l_existing_seq_start IS NOT NULL) AND (l_existing_seq_start <> FND_API.G_MISS_NUM)) THEN
282 IF ( (l_existing_seq_end IS NOT NULL) AND (l_existing_seq_end <> FND_API.G_MISS_NUM)) THEN
283 IF ( ((l_new_seq_start IS NOT NULL) AND (l_new_seq_start <> FND_API.G_MISS_NUM)
284 AND (l_new_seq_start BETWEEN l_existing_seq_start AND l_existing_seq_end))
285 OR
286 ((l_new_seq_end IS NOT NULL) AND (l_new_seq_end <> FND_API.G_MISS_NUM)
287 AND (l_new_seq_end BETWEEN l_existing_seq_start AND l_existing_seq_end)) ) THEN
288 OKL_API.SET_MESSAGE( p_app_name => g_app_name
289 , p_msg_name => G_OKL_ST_DISB_SEQ_OVERLAP);
290 RAISE OKL_API.G_EXCEPTION_ERROR;
291 END IF;
292 ELSIF ((l_new_seq_end IS NULL) OR (l_new_seq_end = FND_API.G_MISS_NUM)
293 OR ( l_new_seq_end > l_existing_seq_start )) THEN
294 OKL_API.SET_MESSAGE( p_app_name => g_app_name
295 , p_msg_name => G_OKL_ST_DISB_SEQ_OVERLAP);
296 RAISE OKL_API.G_EXCEPTION_ERROR;
297 END IF;
298 END IF;
299 END LOOP;
300 END LOOP;
301 END IF;
302
303 --DBMS_OUTPUT.PUT_LINE('S3');
304 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
305
306 EXCEPTION
307 WHEN OKL_API.G_EXCEPTION_ERROR THEN
308 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
309 , g_pkg_name
310 , 'OKL_API.G_RET_STS_ERROR'
311 , x_msg_count
312 , x_msg_data
313 , '_PVT' );
314 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
315 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
316 , g_pkg_name
317 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
318 , x_msg_count
319 , x_msg_data
320 , '_PVT' );
321 WHEN OTHERS THEN
322 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
323 , g_pkg_name
324 , 'OTHERS'
325 , x_msg_count
326 , x_msg_data
327 , '_PVT' );
328 END validate_sequence_range;
329
330
331
332
333
334 -- Start of comments
335 -- API name : validate_rule_eff_dates
336 -- Pre-reqs : None
337 -- Function : validates disbursement rule vendor sites effective dates.
338 -- Parameters :
339 -- IN : p_api_version - Standard input parameter
340 -- p_init_msg_list - Standard input parameter
341 -- p_drs_tbl - record type for OKL_DISB_RULE_STY_TYPES
342 -- p_drv_rec - record type for OKL_DISB_RULE_VENDOR_SITES
343 -- Version : 1.0
344 -- History : gboomina created.
345 -- End of comments
346
347 PROCEDURE del_disb_rule_vendor_sites( p_api_version IN NUMBER
348 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
349 , x_return_status OUT NOCOPY VARCHAR2
350 , x_msg_count OUT NOCOPY NUMBER
351 , x_msg_data OUT NOCOPY VARCHAR2
352 , p_disb_rule_id IN okl_disb_rules_v.disb_rule_id%TYPE
353 , p_vendor_id IN OKL_DISB_RULE_VENDOR_SITES.vendor_id%TYPE
354 , p_new_drv_tbl IN drv_tbl_type
355 )
356 IS
357 l_api_name CONSTANT VARCHAR2(40) := 'del_disb_rule_vendor_sites';
358 l_api_version CONSTANT NUMBER := 1;
359 l_init_msg_list VARCHAR2(1);
360 i NUMBER;
361 del_count NUMBER := 0;
362 l_found VARCHAR2(1) := 'N';
363 flag BOOLEAN ;
364
365 l_drv_del_tbl drv_tbl_type;
366
367
368 /* CURSOR old_disb_vendor_sites_csr( p_disb_rule_id IN OKL_DISB_RULE_VENDOR_SITES.DISB_RULE_ID%TYPE )
369 IS
370 SELECT disb_rule_vendor_site_id
371 FROM okl_disb_rule_vendor_sites
372 WHERE disb_rule_id = p_disb_rule_id;
373 */
374
375 CURSOR old_disb_vendor_sites_csr( p_disb_rule_id IN OKL_DISB_RULE_VENDOR_SITES.DISB_RULE_ID%TYPE ,
376 p_vendor_id IN OKL_DISB_RULE_VENDOR_SITES.vendor_id%TYPE)
377 IS
378 SELECT disb_rule_vendor_site_id
379 FROM okl_disb_rule_vendor_sites
380 WHERE disb_rule_id = p_disb_rule_id
381 AND vendor_id = NVL(p_vendor_id , vendor_id);
382
383 old_vendor_site_rec old_disb_vendor_sites_csr%ROWTYPE;
384
385 BEGIN
386 -- Initialization
387 x_return_status := OKL_API.G_RET_STS_SUCCESS;
388
389 x_return_status := OKL_API.START_ACTIVITY( l_api_name
390 , g_pkg_name
391 , p_init_msg_list
392 , l_api_version
393 , p_api_version
394 , '_PVT'
395 , x_return_status);
396 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
397 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
398 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
399 RAISE OKL_API.G_EXCEPTION_ERROR;
400 END IF;
401
402 -- If vendor sites available for the disbursement rule from DB
403 -- is not found in the new vendor sites input table then delete it
404 -- from the DB table.
405 -- IF(p_vendor_id == NULL) THEN
406 FOR old_vendor_site_rec IN old_disb_vendor_sites_csr(p_disb_rule_id , p_vendor_id) LOOP
407 l_found := 'N';
408 IF (p_new_drv_tbl.COUNT > 0 ) THEN
409 FOR i IN p_new_drv_tbl.FIRST .. p_new_drv_tbl.LAST LOOP
410 IF (old_vendor_site_rec.disb_rule_vendor_site_id = p_new_drv_tbl(i).disb_rule_vendor_site_id) THEN
411 l_found := 'Y';
412 END IF;
413 END LOOP;
414 END IF;
415 IF (l_found = 'N') THEN
416 l_drv_del_tbl(del_count).disb_rule_vendor_site_id := old_vendor_site_rec.disb_rule_vendor_site_id;
417 l_drv_del_tbl(del_count).disb_rule_id := p_disb_rule_id;
418 del_count := del_count + 1;
419 END IF;
420 END LOOP;
421
422 IF ( l_drv_del_tbl.COUNT > 0 ) THEN
423 -- delete record from OKL_DISB_RULE_VENDOR_SITES
424 okl_drv_pvt.delete_row( p_api_version => l_api_version
425 , p_init_msg_list => p_init_msg_list
426 , x_return_status => x_return_status
427 , x_msg_count => x_msg_count
428 , x_msg_data => x_msg_data
429 , p_drv_tbl => l_drv_del_tbl);
430 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
431 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
432 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
433 RAISE OKL_API.G_EXCEPTION_ERROR;
434 END IF;
435 END IF;
436
437 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
438
439 EXCEPTION
440 WHEN OKL_API.G_EXCEPTION_ERROR THEN
441 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
442 , g_pkg_name
443 , 'OKL_API.G_RET_STS_ERROR'
444 , x_msg_count
445 , x_msg_data
446 , '_PVT' );
447 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
448 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
449 , g_pkg_name
450 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
451 , x_msg_count
452 , x_msg_data
453 , '_PVT' );
454 WHEN OTHERS THEN
455 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
456 , g_pkg_name
457 , 'OTHERS'
458 , x_msg_count
459 , x_msg_data
460 , '_PVT' );
461 END del_disb_rule_vendor_sites;
462
463
464 -- Start of comments
465
466 -- API name : create_v_disbursement_rulete_v_disbursement_rule
467 -- Pre-reqs : None
468 -- Function : Associate the Vendor with the Rule uses
469 -- OKL_DISB_RULES_B, OKL_DISB_RULES_TL,
470 -- OKL_DISB_RULE_STY_TYPES and OKL_DISB_RULE_VENDOR_SITES
471 -- Parameters :
472 -- IN : p_api_version - Standard input parameter
473 -- p_init_msg_list - Standard input parameter
474 -- p_drv_tbl - record type for OKL_DISB_RULE_VENDOR_SITES
475 -- Version : 1.0
476 -- History : gkhuntet created.
477 -- End of comments
478
479
480 PROCEDURE create_v_disbursement_rule( p_api_version IN NUMBER
481 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
482 , x_return_status OUT NOCOPY VARCHAR2
483 , x_msg_count OUT NOCOPY NUMBER
484 , x_msg_data OUT NOCOPY VARCHAR2
485 , p_drv_tbl IN drv_tbl_type
486 , x_drv_tbl OUT NOCOPY drv_tbl_type
487 )
488 IS
489
490 l_api_name CONSTANT VARCHAR2(40) := 'create_v_disbursement_rule';
491 l_api_version CONSTANT NUMBER := 1;
492 l_init_msg_list VARCHAR2(1);
493 i NUMBER;
494 crt_count NUMBER := 0;
495 updt_count NUMBER := 0;
496 count_csr NUMBER := 0;
497 count_vendor_site_id NUMBER :=0;
498 l_vendor_id NUMBER ;
499
500 l_drav_rec drav_rec_type;
501 l_drs_tbl drs_tbl_type;
502 l_drs_rec drs_rec_type;
503 l_disb_rule_id okl_disb_rules_v.disb_rule_id%TYPE;
504
505 lp_drv_tbl drv_tbl_type;
506 lp_drv_rec drv_rec_type;
507 lx_drv_tbl drv_tbl_type;
508 l_drv_Updt_tbl drv_tbl_type;
509 l_drv_Crt_tbl drv_tbl_type;
510 l_old_start_seq_no OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_START%TYPE;
511 l_next_seq_no OKL_DISB_RULE_VENDOR_SITES.NEXT_INV_SEQ%TYPE;
512 l_old_end_seq_no OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_END%TYPE;
513
514 flag VARCHAR(50);
515
516 CURSOR get_drav_rec(c_disb_rule_id OKL_DISB_RULES_B.DISB_RULE_ID%TYPE) IS
517 SELECT DISB_RULE_ID ,START_DATE ,end_date ,fee_basis ,fee_option ,fee_amount ,
518 fee_percent ,consolidate_by_due_date ,frequency ,day_of_month ,scheduled_month
519 FROM OKL_DISB_RULES_B
520 WHERE DISB_RULE_ID = c_disb_rule_id;
521
522 CURSOR get_drs_tbl(c_disb_rule_id OKL_DISB_RULE_STY_TYPES.DISB_RULE_ID%TYPE) IS
523 SELECT DISB_RULE_ID ,STREAM_TYPE_PURPOSE ,DISB_RULE_STY_TYPE_ID
524 FROM OKL_DISB_RULE_STY_TYPES
525 WHERE DISB_RULE_ID = c_disb_rule_id;
526
527 CURSOR get_seq_no(c_disb_rule_vendor_site_id OKL_DISB_RULE_VENDOR_SITES.INVOICE_SEQ_START%TYPE) IS
528 SELECT INVOICE_SEQ_START ,NEXT_INV_SEQ ,INVOICE_SEQ_END
529 FROM OKL_DISB_RULE_VENDOR_SITES
530 WHERE DISB_RULE_VENDOR_SITE_ID = c_disb_rule_vendor_site_id;
531
532
533 /*CURSOR get_count_disb_ruleid(c_disb_rule_vendor_site_id OKL_DISB_RULE_VENDOR_SITES.DISB_RULE_ID%TYPE) IS
534 SELECT COUNT(DISB_RULE_VENDOR_SITE_ID)
535 FROM OKL_DISB_RULE_VENDOR_SITES
536 WHERE DISB_RULE_VENDOR_SITE_ID = c_disb_rule_vendor_site_id;*/
537
538 v_disb_rule_rec get_drav_rec%ROWTYPE;
539 v_dsbrule_strm_prpse_rec get_drs_tbl%ROWTYPE;
540
541 BEGIN
542 --SAVEPOINT create_v_disbursement_rule;
543 ----g_debug_proc('START');
544 --DBMS_OUTPUT.PUT_LINE('START ');
545 x_return_status := OKL_API.G_RET_STS_SUCCESS;
546
547 x_return_status := OKL_API.START_ACTIVITY( l_api_name
548 , g_pkg_name
549 , p_init_msg_list
550 , l_api_version
551 , p_api_version
552 , '_PVT'
553 , x_return_status);
554 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
555 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
556 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
557 RAISE OKL_API.G_EXCEPTION_ERROR;
558 END IF;
559
560 -- lp_drv_tbl := p_drv_tbl;
561
562 -- ----g_debug_proc('Gaurav p_drv_tbl(0).disb_rule_id :'||p_drv_tbl.count);
563
564 ----g_debug_proc('a ' || p_drv_tbl.FIRST);
565 IF ( p_drv_tbl.COUNT > 0) THEN
566 i := p_drv_tbl.FIRST;
567 LOOP
568 lp_drv_tbl(i) := p_drv_tbl(i);
569 l_disb_rule_id :=p_drv_tbl(i).disb_rule_id;
570 -- l_vendor_id :=p_drv_tbl(i).vendor_id;
571
572 -- ----g_debug_proc('Gaurav lp_drv_tbl(i).disb_rule_id :'||l_disb_rule_id);
573 EXIT WHEN ( i = p_drv_tbl.LAST);
574 i := p_drv_tbl.NEXT(i);
575 END LOOP;
576 END IF;
577
578
579
580 IF(FND_API.G_MISS_NUM = p_drv_tbl(p_drv_tbl.FIRST).disb_rule_vendor_site_id) THEN
581 flag :='yes';
582 ELSE
583 flag :='no';
584 END IF;
585 ----g_debug_proc('Gaurav ' || flag || ' ' || p_drv_tbl(p_drv_tbl.FIRST).disb_rule_vendor_site_id);
586
587 --DBMS_OUTPUT.PUT_LINE('A ' || l_disb_rule_id );
588
589 OPEN get_drav_rec(l_disb_rule_id);
590 FETCH get_drav_rec INTO v_disb_rule_rec;
591 IF get_drav_rec%FOUND THEN
592 --DBMS_OUTPUT.PUT_LINE('Inside Fetch '||v_disb_rule_rec.disb_rule_id);
593 l_drav_rec.disb_rule_id := v_disb_rule_rec.disb_rule_id;
594 l_drav_rec.START_DATE := v_disb_rule_rec.START_DATE;
595 l_drav_rec.end_date := v_disb_rule_rec.end_date;
596 l_drav_rec.fee_basis := v_disb_rule_rec.fee_basis;
597 l_drav_rec.fee_option := v_disb_rule_rec.fee_option;
598 l_drav_rec.fee_amount := v_disb_rule_rec.fee_amount;
599 l_drav_rec.fee_percent := v_disb_rule_rec.fee_percent;
600 l_drav_rec.consolidate_by_due_date := v_disb_rule_rec.consolidate_by_due_date;
601 l_drav_rec.frequency := v_disb_rule_rec.frequency;
602 l_drav_rec.day_of_month := v_disb_rule_rec.day_of_month;
603 l_drav_rec.scheduled_month := v_disb_rule_rec.scheduled_month;
604 END IF;
605 CLOSE get_drav_rec;
606
607 --DBMS_OUTPUT.PUT_LINE('B ' || l_drav_rec.START_DATE || ' ' || l_drav_rec.end_date);
608 --DBMS_OUTPUT.PUT_LINE('B ' || v_disb_rule_rec.START_DATE || ' ' || v_disb_rule_rec.end_date);
609
610 ----g_debug_proc('Gaurav ' || l_drav_rec.disb_rule_id);
611 ------g_debug_proc('Gaurav ' || p_drv_tbl(0).START_DATE);
612 ------g_debug_proc('Gaurav ' || p_drv_tbl(0).end_date);
613
614 --OPEN get_drs_tbl(lp_drv_tbl(0).disb_rule_id);
615 FOR v_dsbrule_strm_prpse_rec in get_drs_tbl(l_disb_rule_id)
616 LOOP
617 l_drs_rec.disb_rule_id := v_dsbrule_strm_prpse_rec.disb_rule_id;
618 l_drs_rec.stream_type_purpose := v_dsbrule_strm_prpse_rec.stream_type_purpose;
619 l_drs_rec.disb_rule_sty_type_id := v_dsbrule_strm_prpse_rec.disb_rule_sty_type_id;
620 l_drs_tbl(count_csr) := l_drs_rec;
621 count_csr := count_csr + 1;
622 END LOOP;
623 --CLOSE get_drs_tbl;
624
625 --DBMS_OUTPUT.PUT_LINE('C');
626
627 ----g_debug_proc('Gaurav 3 ' || l_drs_rec.stream_type_purpose);
628
629 del_disb_rule_vendor_sites( p_api_version => l_api_version
630 , p_init_msg_list => p_init_msg_list
631 , x_return_status => x_return_status
632 , x_msg_count => x_msg_count
633 , x_msg_data => x_msg_data
634 , p_disb_rule_id => l_disb_rule_id
635 , p_vendor_id => lp_drv_tbl(lp_drv_tbl.FIRST).vendor_id
636 , p_new_drv_tbl => lp_drv_tbl);
637 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
638 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
639 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
640 RAISE OKL_API.G_EXCEPTION_ERROR;
641 END IF;
642
643
644 ----g_debug_proc('After Delete');
645
646 -- Do the required validations
647 validate_disbursement_rule( p_api_version => p_api_version
648 , p_init_msg_list => p_init_msg_list
649 , x_return_status => x_return_status
650 , x_msg_count => x_msg_count
651 , x_msg_data => x_msg_data
652 , p_drav_rec => l_drav_rec
653 , p_drs_tbl => l_drs_tbl
654 , p_drv_tbl => lp_drv_tbl);
655 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
656 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
658 RAISE OKL_API.G_EXCEPTION_ERROR;
659 END IF;
660
661
662
663
664 -- Only one disbursement rule can exist for one vendor site and stream type
665 -- combination for a given range of effective dates
666 validate_rule_eff_dates( p_api_version => p_api_version
667 , p_init_msg_list => p_init_msg_list
668 , x_return_status => x_return_status
669 , x_msg_count => x_msg_count
670 , x_msg_data => x_msg_data
671 , p_drav_rec => l_drav_rec
672 , p_drs_tbl => l_drs_tbl
673 , p_drv_tbl => lp_drv_tbl);
674 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
676 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 END IF;
679 --DBMS_OUTPUT.PUT_LINE('3');
680
681
682 -- Vendor site, Sequence range must not overlap accross rules
683
684 validate_sequence_range( p_api_version => p_api_version
685 , p_init_msg_list => p_init_msg_list
686 , x_return_status => x_return_status
687 , x_msg_count => x_msg_count
688 , x_msg_data => x_msg_data
689 , p_drv_tbl => lp_drv_tbl);
690 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
691 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
692 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
693 RAISE OKL_API.G_EXCEPTION_ERROR;
694 END IF;
695
696 ----g_debug_proc('Gaurav 4');
697
698 --If disb_rule_vendor_site_id = null then record is inserted else updated.
699 FOR i IN lp_drv_tbl.FIRST .. lp_drv_tbl.LAST LOOP
700 --gkhuntet added on 22-JUN-2007 START.
701
702 IF(lp_drv_tbl(i).disb_rule_vendor_site_id <> OKL_API.G_MISS_NUM
703 OR lp_drv_tbl(i).disb_rule_vendor_site_id IS NOT NULL) THEN
704 OPEN get_seq_no(lp_drv_tbl(i).DISB_RULE_VENDOR_SITE_ID);
705 FETCH get_seq_no INTO l_old_start_seq_no ,l_next_seq_no ,l_old_end_seq_no;
706 CLOSE get_seq_no;
707 IF(lp_drv_tbl(i).INVOICE_SEQ_START IS NOT NULL) THEN
708 IF(l_old_start_seq_no IS null) THEN
709 lp_drv_tbl(i).NEXT_INV_SEQ := lp_drv_tbl(i).INVOICE_SEQ_START;
710 END IF;
711 ELSIF(lp_drv_tbl(i).INVOICE_SEQ_END IS NOT NULL) THEN
712 OKL_API.SET_MESSAGE( p_app_name => g_app_name
713 , p_msg_name => G_OKL_ST_START_SEQ_NO_REQ);
714 RAISE OKL_API.G_EXCEPTION_ERROR;
715 END IF;
716
717 IF((l_old_end_seq_no IS NOT NULL AND lp_drv_tbl(i).INVOICE_SEQ_END IS NULL) OR
718 l_old_end_seq_no IS NOT NULL AND lp_drv_tbl(i).INVOICE_SEQ_END < l_old_end_seq_no) THEN
719 OKL_API.SET_MESSAGE( p_app_name => g_app_name
720 , p_msg_name => G_OKL_ST_END_SEQ_NO_LESS);
721 RAISE OKL_API.G_EXCEPTION_ERROR;
722 END IF;
723
724 IF((lp_drv_tbl(i).INVOICE_SEQ_START IS NULL AND l_old_start_seq_no IS NOT NULL) OR
725 (l_old_start_seq_no IS NOT NULL AND lp_drv_tbl(i).INVOICE_SEQ_START <> l_old_start_seq_no)) THEN
726 OKL_API.SET_MESSAGE( p_app_name => g_app_name
727 , p_msg_name => G_OKL_ST_START_SEQ_LOCK);
728 RAISE OKL_API.G_EXCEPTION_ERROR;
729 -- ELSE
730 -- lp_drv_tbl(i).INVOICE_SEQ_START := l_old_start_seq_no;
731
732
733 END IF;
734 END IF;
735 --gkhuntet added on 22-JUN-2007 END.
736
737 IF(lp_drv_tbl(i).disb_rule_vendor_site_id = OKL_API.G_MISS_NUM or
738 lp_drv_tbl(i).disb_rule_vendor_site_id is null) THEN
739 ----g_debug_proc('disb_rule_vendor_site_id ' || lp_drv_tbl(i).disb_rule_vendor_site_id);
740 l_drv_Crt_tbl(crt_count) := lp_drv_tbl(i);
741 crt_count := crt_count + 1;
742 ELSE
743 ----g_debug_proc('disb_rule_vendor_site_id ' || lp_drv_tbl(i).disb_rule_vendor_site_id);
744 l_drv_Updt_tbl(updt_count) := lp_drv_tbl(i);
745 updt_count := updt_count + 1;
746 END IF;
747 END LOOP;
748
749 --DBMS_OUTPUT.PUT_LINE('COUNT ' || l_drv_Updt_tbl.COUNT || ' ' ||l_drv_Crt_tbl.COUNT);
750 -- Update record in OKL_DISB_RULE_VENDOR_SITES
751 ----g_debug_proc('Gaurav 5 ' || l_drv_Updt_tbl.COUNT || ' ' ||l_drv_Crt_tbl.COUNT);
752
753 IF ( l_drv_Updt_tbl.COUNT > 0 ) THEN
754 okl_drv_pvt.update_row( p_api_version => l_api_version
755 , p_init_msg_list => p_init_msg_list
756 , x_return_status => x_return_status
757 , x_msg_count => x_msg_count
758 , x_msg_data => x_msg_data
759 , p_drv_tbl => l_drv_Updt_tbl
760 , x_drv_tbl => lx_drv_tbl);
761 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
762 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
764 RAISE OKL_API.G_EXCEPTION_ERROR;
765 END IF;
766 END IF;
767
768 ----g_debug_proc('Gaurav 6');
769
770 IF ( l_drv_Crt_tbl.COUNT > 0 ) THEN
771 -- Create record in OKL_DISB_RULE_VENDOR_SITES
772 okl_drv_pvt.insert_row( p_api_version => l_api_version
773 , p_init_msg_list => p_init_msg_list
774 , x_return_status => x_return_status
775 , x_msg_count => x_msg_count
776 , x_msg_data => x_msg_data
777 , p_drv_tbl => l_drv_Crt_tbl
778 , x_drv_tbl => lx_drv_tbl);
779 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
780 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
781 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
782 RAISE OKL_API.G_EXCEPTION_ERROR;
783 END IF;
784 END IF;
785
786 ----g_debug_proc('Gaurav 7');
787
788 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
789
790 EXCEPTION
791 WHEN OKL_API.G_EXCEPTION_ERROR THEN
792 --ROLLBACK TO create_v_disbursement_rule;
793 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
794 , g_pkg_name
795 , 'OKL_API.G_RET_STS_ERROR'
796 , x_msg_count
797 , x_msg_data
798 , '_PVT' );
799 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
800 --ROLLBACK TO create_v_disbursement_rule;
801 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
802 , g_pkg_name
803 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
804 , x_msg_count
805 , x_msg_data
806 , '_PVT' );
807 WHEN OTHERS THEN
808 --ROLLBACK TO create_v_disbursement_rule;
809 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
810 , g_pkg_name
811 , 'OTHERS'
812 , x_msg_count
813 , x_msg_data
814 , '_PVT' );
815
816 END create_v_disbursement_rule;
817
818
819
820
821
822
823
824 -- Start of comments
825 -- API name : validate_disb_rule_name
826 -- Pre-reqs : None
827 -- Function : validates disbursement rule name
828 -- Parameters :
829 -- IN : p_api_version - Standard input parameter
830 -- p_init_msg_list - Standard input parameter
831 -- p_disb_rule_name - disbursement rule name
832 -- Version : 1.0
833 -- History : gboomina created.
834 -- End of comments
835
836 PROCEDURE validate_disb_rule_name( p_api_version IN NUMBER
837 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
838 , x_return_status OUT NOCOPY VARCHAR2
839 , x_msg_count OUT NOCOPY NUMBER
840 , x_msg_data OUT NOCOPY VARCHAR2
841 , p_disb_rule_name IN OKL_DISB_RULES_V.RULE_NAME%TYPE
842 )
843 IS
844 l_api_name CONSTANT VARCHAR2(40) := 'validate_disb_rule_name';
845 l_api_version CONSTANT NUMBER := 1;
846 l_init_msg_list VARCHAR2(1);
847 i NUMBER;
848
849 -- Cursor to check the unique rule name
850 CURSOR disb_rule_name_csr IS
851 SELECT 'Y' FROM OKL_DISB_RULES_B
852 WHERE RULE_NAME = p_disb_rule_name;
853
854 l_found VARCHAR2(1) := 'N';
855
856 BEGIN
857 -- Initialization
858 x_return_status := OKL_API.G_RET_STS_SUCCESS;
859
860 x_return_status := OKL_API.START_ACTIVITY( l_api_name
861 , g_pkg_name
862 , p_init_msg_list
863 , l_api_version
864 , p_api_version
865 , '_PVT'
866 , x_return_status);
867 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
869 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
870 RAISE OKL_API.G_EXCEPTION_ERROR;
871 END IF;
872
873 -- Check whether Rule name is unique accross rules within org
874 OPEN disb_rule_name_csr;
875 FETCH disb_rule_name_csr INTO l_found;
876 CLOSE disb_rule_name_csr;
877
878 IF ( l_found = 'Y') THEN
879 OKL_API.SET_MESSAGE( p_app_name => g_app_name
880 , p_msg_name => G_OKL_ST_DISB_NAME_EXIST
881 , p_token1 => G_VALUE
882 , p_token1_value => p_disb_rule_name );
883 RAISE OKL_API.G_EXCEPTION_ERROR;
884 END IF;
885
886 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
887
888 EXCEPTION
889 WHEN OKL_API.G_EXCEPTION_ERROR THEN
890 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
891 , g_pkg_name
892 , 'OKL_API.G_RET_STS_ERROR'
893 , x_msg_count
894 , x_msg_data
895 , '_PVT' );
896 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
897 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
898 , g_pkg_name
899 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
900 , x_msg_count
901 , x_msg_data
902 , '_PVT' );
903 WHEN OTHERS THEN
904 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
905 , g_pkg_name
906 , 'OTHERS'
907 , x_msg_count
908 , x_msg_data
909 , '_PVT' );
910 END validate_disb_rule_name;
911
912
913
914 -- Start of comments
915 -- API name : validate_disbursement_rule
916 -- Pre-reqs : None
917 -- Function : validates disbursement rule
918 -- Parameters :
919 -- IN : p_api_version - Standard input parameter
920 -- p_init_msg_list - Standard input parameter
921 -- p_drav_rec - record type for OKL_DISB_RULES_V
922 -- p_drs_tbl - record type for OKL_DISB_RULE_STY_TYPES
923 -- p_drv_rec - record type for OKL_DISB_RULE_VENDOR_SITES
924 -- Version : 1.0
925 -- History : gboomina created.
926 -- End of comments
927
928 PROCEDURE validate_disbursement_rule( p_api_version IN NUMBER
929 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
930 , x_return_status OUT NOCOPY VARCHAR2
931 , x_msg_count OUT NOCOPY NUMBER
932 , x_msg_data OUT NOCOPY VARCHAR2
933 , p_drav_rec IN drav_rec_type
934 , p_drs_tbl IN drs_tbl_type
935 , p_drv_tbl IN drv_tbl_type
936 )
937 IS
938 l_api_name CONSTANT VARCHAR2(40) := 'validate_disbursement_rule';
939 l_api_version CONSTANT NUMBER := 1;
940 l_init_msg_list VARCHAR2(1);
941 i NUMBER;
942
943 BEGIN
944 -- Initialization
945
946 x_return_status := OKL_API.G_RET_STS_SUCCESS;
947
948 x_return_status := OKL_API.START_ACTIVITY( l_api_name
949 , g_pkg_name
950 , p_init_msg_list
951 , l_api_version
952 , p_api_version
953 , '_PVT'
954 , x_return_status);
955 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
956 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
957 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
958 RAISE OKL_API.G_EXCEPTION_ERROR;
959 END IF;
960
961
962 -- Rule must have atleast one Stream Type defined
963
964 IF ( p_drs_tbl.COUNT <= 0 ) THEN
965 OKL_API.SET_MESSAGE( p_app_name => g_app_name
966 , p_msg_name => G_OKL_ST_DISB_RUL_STY_MISSING );
967 RAISE OKL_API.G_EXCEPTION_ERROR;
968 END IF;
969 -- If Fee Basis is selected then Option is mandatory
970 IF ( p_drav_rec.fee_basis <> OKL_API.G_MISS_CHAR AND p_drav_rec.fee_basis IS NOT NULL ) THEN
971 IF ( p_drav_rec.fee_option = OKL_API.G_MISS_CHAR OR p_drav_rec.fee_option IS NULL ) THEN
972 OKL_API.SET_MESSAGE( p_app_name => g_app_name
973 , p_msg_name => G_OKL_ST_DISB_FEE_OPTION_REQ);
974 RAISE OKL_API.G_EXCEPTION_ERROR;
975 END IF;
976 --g_debug_proc('Fee Basis ' || p_drav_rec.fee_basis);
977 --g_debug_proc('Fee Amount ' || p_drav_rec.fee_amount);
978 --g_debug_proc('Fee Percent ' || p_drav_rec.fee_percent);
979 -- If Fee Basis is 'Amount' then Fee Amount is mandatory
980
981 -- Commented by gkhuntet For Disbursement phase 1.
982 /* IF (p_drav_rec.fee_basis = 'AMOUNT') THEN
983 IF ( p_drav_rec.fee_amount = OKL_API.G_MISS_NUM OR p_drav_rec.fee_amount IS NULL ) THEN
984 OKL_API.SET_MESSAGE( p_app_name => g_app_name
985 , p_msg_name => G_OKL_ST_DISB_FEE_AMNT_REQ);
986 RAISE OKL_API.G_EXCEPTION_ERROR;
987 END IF;
988 END IF;
989 IF (p_drav_rec.fee_basis = 'PERCENT') THEN -- If Fee Basis is 'Percent' then Fee Percent is mandatory
990 IF ( p_drav_rec.fee_percent = OKL_API.G_MISS_NUM OR p_drav_rec.fee_percent IS NULL ) THEN
991 OKL_API.SET_MESSAGE( p_app_name => g_app_name
992 , p_msg_name => G_OKL_ST_DISB_FEE_PERCENT_REQ);
993 RAISE OKL_API.G_EXCEPTION_ERROR;
994 -- Fee Percent should have value between 0 and 100
995 ELSIF ( p_drav_rec.fee_percent < 0 OR p_drav_rec.fee_percent > 100 ) THEN
996 OKL_API.SET_MESSAGE( p_app_name => g_app_name
997 , p_msg_name => G_OKL_ST_DISB_FEE_PERCENT_ERR);
998 RAISE OKL_API.G_EXCEPTION_ERROR;
999 END IF;
1000 END IF;
1001 */
1002 END IF;
1003 --DBMS_OUTPUT.PUT_LINE('1');
1004 -- If 'Consolidated by Invoice date' is checked then Frequency, Scheduled Day of Month and
1005 -- Scheduled Month are mandatory.
1006 IF ((p_drav_rec.consolidate_by_due_date <> OKL_API.G_MISS_CHAR) AND
1007 (p_drav_rec.consolidate_by_due_date IS NOT NULL) AND
1008 (p_drav_rec.consolidate_by_due_date = 'Y')) THEN
1009 IF (p_drav_rec.frequency = OKL_API.G_MISS_CHAR OR p_drav_rec.frequency IS NULL) THEN
1010 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1011 , p_msg_name => G_OKL_ST_DISB_FREQ_REQ);
1012 RAISE OKL_API.G_EXCEPTION_ERROR;
1013 END IF;
1014 IF (p_drav_rec.day_of_month = OKL_API.G_MISS_NUM OR p_drav_rec.day_of_month IS NULL) THEN
1015 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1016 , p_msg_name => G_OKL_ST_DISB_DAY_MON_REQ);
1017 RAISE OKL_API.G_EXCEPTION_ERROR;
1018 END IF;
1019 IF (p_drav_rec.scheduled_month = OKL_API.G_MISS_CHAR OR p_drav_rec.scheduled_month IS NULL) THEN
1020 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1021 , p_msg_name => G_OKL_ST_DISB_SCHED_MON_REQ);
1022 RAISE OKL_API.G_EXCEPTION_ERROR;
1023 END IF;
1024 END IF;
1025 --DBMS_OUTPUT.PUT_LINE('2 ..');
1026 -- Only one disbursement rule can exist for one vendor site and stream type
1027 -- combination for a given range of effective dates
1028 /* validate_rule_eff_dates( p_api_version => p_api_version
1029 , p_init_msg_list => p_init_msg_list
1030 , x_return_status => x_return_status
1031 , x_msg_count => x_msg_count
1032 , x_msg_data => x_msg_data
1033 , p_drav_rec => p_drav_rec
1034 , p_drs_tbl => p_drs_tbl
1035 , p_drv_tbl => p_drv_tbl);
1036 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1037 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1038 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1039 RAISE OKL_API.G_EXCEPTION_ERROR;
1040 END IF;
1041 --DBMS_OUTPUT.PUT_LINE('3');
1042 -- Vendor site, Sequence range must not overlap accross rules
1043 validate_sequence_range( p_api_version => p_api_version
1044 , p_init_msg_list => p_init_msg_list
1045 , x_return_status => x_return_status
1046 , x_msg_count => x_msg_count
1047 , x_msg_data => x_msg_data
1048 , p_drv_tbl => p_drv_tbl);
1049 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1050 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1051 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1052 RAISE OKL_API.G_EXCEPTION_ERROR;
1053 END IF;*/
1054 --DBMS_OUTPUT.PUT_LINE('4');
1055 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1056
1057 EXCEPTION
1058 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1059 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1060 , g_pkg_name
1061 , 'OKL_API.G_RET_STS_ERROR'
1062 , x_msg_count
1063 , x_msg_data
1064 , '_PVT' );
1065 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1066 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1067 , g_pkg_name
1068 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
1069 , x_msg_count
1070 , x_msg_data
1071 , '_PVT' );
1072 WHEN OTHERS THEN
1073 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1074 , g_pkg_name
1075 , 'OTHERS'
1076 , x_msg_count
1077 , x_msg_data
1078 , '_PVT' );
1079 END validate_disbursement_rule;
1080
1081
1082 -- Start of comments
1083 -- API name : create_disbursement_rule
1084 -- Pre-reqs : None
1085 -- Function : create disbursement rule in the following tables
1086 -- OKL_DISB_RULES_B, OKL_DISB_RULES_TL,
1087 -- OKL_DISB_RULE_STY_TYPES and OKL_DISB_RULE_VENDOR_SITES
1088 -- Parameters :
1089 -- IN : p_api_version - Standard input parameter
1090 -- p_init_msg_list - Standard input parameter
1091 -- p_drav_rec - record type for OKL_DISB_RULES_V
1092 -- p_drs_tbl - record type for OKL_DISB_RULE_STY_TYPES
1093 -- p_drv_rec - record type for OKL_DISB_RULE_VENDOR_SITES
1094 -- Version : 1.0
1095 -- History : gboomina created.
1096 -- End of comments
1097
1098 PROCEDURE create_disbursement_rule( p_api_version IN NUMBER
1099 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1100 , x_return_status OUT NOCOPY VARCHAR2
1101 , x_msg_count OUT NOCOPY NUMBER
1102 , x_msg_data OUT NOCOPY VARCHAR2
1103 , p_drav_rec IN drav_rec_type
1104 , p_drs_tbl IN drs_tbl_type
1105 , p_drv_tbl IN drv_tbl_type
1106 , x_drav_rec OUT NOCOPY drav_rec_type
1107 )
1108 IS
1109 l_api_name CONSTANT VARCHAR2(40) := 'create_disbursement_rule';
1110 l_api_version CONSTANT NUMBER := 1;
1111 l_init_msg_list VARCHAR2(1);
1112 i NUMBER;
1113
1114 l_drav_rec drav_rec_type;
1115 l_drs_tbl drs_tbl_type;
1116 l_drv_tbl drv_tbl_type;
1117
1118 lx_drav_rec drav_rec_type;
1119 lx_drs_tbl drs_tbl_type;
1120 lx_drv_tbl drv_tbl_type;
1121
1122 BEGIN
1123 -- Initialization
1124 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1125
1126 x_return_status := OKL_API.START_ACTIVITY( l_api_name
1127 , g_pkg_name
1128 , p_init_msg_list
1129 , l_api_version
1130 , p_api_version
1131 , '_PVT'
1132 , x_return_status);
1133 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1134 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1135 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1136 RAISE OKL_API.G_EXCEPTION_ERROR;
1137 END IF;
1138
1139
1140
1141 -- Validate disbursement rule before inserting
1142 -- Check whether Rule name is unique accross rules within org
1143 validate_disb_rule_name( p_api_version => p_api_version
1144 , p_init_msg_list => p_init_msg_list
1145 , x_return_status => x_return_status
1146 , x_msg_count => x_msg_count
1147 , x_msg_data => x_msg_data
1148 , p_disb_rule_name => p_drav_rec.rule_name);
1149 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1151 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1152 RAISE OKL_API.G_EXCEPTION_ERROR;
1153 END IF;
1154 --DBMS_OUTPUT.PUT_LINE('Validated Rule Name');
1155 --g_debug_proc('Validated Rule Name');
1156 -- Do the required validations
1157 validate_disbursement_rule( p_api_version => p_api_version
1158 , p_init_msg_list => p_init_msg_list
1159 , x_return_status => x_return_status
1160 , x_msg_count => x_msg_count
1161 , x_msg_data => x_msg_data
1162 , p_drav_rec => p_drav_rec
1163 , p_drs_tbl => p_drs_tbl
1164 , p_drv_tbl => p_drv_tbl);
1165 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1166 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1167 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1168 RAISE OKL_API.G_EXCEPTION_ERROR;
1169 END IF;
1170
1171 l_drav_rec := p_drav_rec;
1172
1173 --DBMS_OUTPUT.PUT_LINE('Validated Rule');
1174
1175 -- Only one disbursement rule can exist for one vendor site and stream type
1176 -- combination for a given range of effective dates
1177 validate_rule_eff_dates( p_api_version => p_api_version
1178 , p_init_msg_list => p_init_msg_list
1179 , x_return_status => x_return_status
1180 , x_msg_count => x_msg_count
1181 , x_msg_data => x_msg_data
1182 , p_drav_rec => p_drav_rec
1183 , p_drs_tbl => p_drs_tbl
1184 , p_drv_tbl => p_drv_tbl);
1185 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1188 RAISE OKL_API.G_EXCEPTION_ERROR;
1189 END IF;
1190
1191
1192
1193
1194 --g_debug_proc('Validated Rule');
1195 -- insert record in to OKL_DISB_RULES_B and TL
1196 okl_dra_pvt.insert_row( p_api_version => l_api_version
1197 , p_init_msg_list => p_init_msg_list
1198 , x_return_status => x_return_status
1199 , x_msg_count => x_msg_count
1200 , x_msg_data => x_msg_data
1201 , p_drav_rec => l_drav_rec
1202 , x_drav_rec => lx_drav_rec);
1203 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1204 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1205 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1206 RAISE OKL_API.G_EXCEPTION_ERROR;
1207 END IF;
1208 --DBMS_OUTPUT.PUT_LINE('Rule Inserted');
1209 --g_debug_proc('Rule Inserted');
1210 -- populate the OKL_DISB_RULE_STY_TYPES table with the
1211 -- disb rule id returned from the okl_dra_pvt.insert_row
1212 IF ( p_drs_tbl.COUNT > 0) THEN
1213 i := p_drs_tbl.FIRST;
1214 LOOP
1215 l_drs_tbl(i) := p_drs_tbl(i);
1216 l_drs_tbl(i).disb_rule_id := lx_drav_rec.disb_rule_id;
1217 EXIT WHEN ( i = p_drs_tbl.LAST);
1218 i := p_drs_tbl.NEXT(i);
1219 END LOOP;
1220
1221 -- insert record in to OKL_DISB_RULE_STY_TYPES
1222 okl_drs_pvt.insert_row( p_api_version => l_api_version
1223 , p_init_msg_list => p_init_msg_list
1224 , x_return_status => x_return_status
1225 , x_msg_count => x_msg_count
1226 , x_msg_data => x_msg_data
1227 , p_drs_tbl => l_drs_tbl
1228 , x_drs_tbl => lx_drs_tbl);
1229 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1230 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1232 RAISE OKL_API.G_EXCEPTION_ERROR;
1233 END IF;
1234
1235 END IF;
1236 --g_debug_proc('Stream Inserted');
1237 -- populate the OKL_DISB_RULE_VENDOR_SITES table with the
1238 -- disb rule id returned from the okl_dra_pvt.insert_row
1239 IF ( p_drv_tbl.COUNT > 0) THEN
1240 i := p_drv_tbl.FIRST;
1241 LOOP
1242 l_drv_tbl(i) := p_drv_tbl(i);
1243 l_drv_tbl(i).disb_rule_id := lx_drav_rec.disb_rule_id;
1244 EXIT WHEN ( i = p_drv_tbl.LAST);
1245 i := p_drv_tbl.NEXT(i);
1246 END LOOP;
1247
1248 -- insert record in to OKL_DISB_RULE_STY_TYPES
1249 okl_drv_pvt.insert_row( p_api_version => l_api_version
1250 , p_init_msg_list => p_init_msg_list
1251 , x_return_status => x_return_status
1252 , x_msg_count => x_msg_count
1253 , x_msg_data => x_msg_data
1254 , p_drv_tbl => l_drv_tbl
1255 , x_drv_tbl => lx_drv_tbl);
1256 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1257 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1258 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1259 RAISE OKL_API.G_EXCEPTION_ERROR;
1260 END IF;
1261
1262 END IF;
1263 --g_debug_proc('VendorSites Inserted');
1264 x_drav_rec := lx_drav_rec;
1265
1266 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1267
1268 EXCEPTION
1269 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1270 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1271 , g_pkg_name
1272 , 'OKL_API.G_RET_STS_ERROR'
1273 , x_msg_count
1274 , x_msg_data
1275 , '_PVT' );
1276 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1277 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1278 , g_pkg_name
1279 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
1280 , x_msg_count
1281 , x_msg_data
1282 , '_PVT' );
1283 WHEN OTHERS THEN
1284 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1285 , g_pkg_name
1286 , 'OTHERS'
1287 , x_msg_count
1288 , x_msg_data
1289 , '_PVT' );
1290 END create_disbursement_rule;
1291
1292
1293
1294 -- Start of comments
1295 -- API name : delete_disb_rule_sty_types
1296 -- Pre-reqs : None
1297 -- Function : deletes stream types which are not present in the input
1298 -- p_drs_tbl but in the OKL_DISB_RULE_STY_TYPES
1299 -- for a disbursement rule. This API deletes stream types from
1300 -- OKL_DISB_RULE_STY_TYPES table which user had deleted during
1301 -- update operation.
1302 -- Parameters :
1303 -- IN : p_api_version - Standard input parameter
1304 -- p_init_msg_list - Standard input parameter
1305 -- p_disb_rule_id - disbursement rule id
1306 -- p_drs_tbl - record type for OKL_DISB_RULE_STY_TYPES
1307 -- Version : 1.0
1308 -- History : gboomina created.
1309 -- End of comments
1310
1311 PROCEDURE delete_disb_rule_sty_types( p_api_version IN NUMBER
1312 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1313 , x_return_status OUT NOCOPY VARCHAR2
1314 , x_msg_count OUT NOCOPY NUMBER
1315 , x_msg_data OUT NOCOPY VARCHAR2
1316 , p_disb_rule_id IN okl_disb_rules_v.disb_rule_id%type
1317 , p_new_drs_tbl IN drs_tbl_type
1318 )
1319 IS
1320 l_api_name CONSTANT VARCHAR2(40) := 'delete_disb_rule_sty_types';
1321 l_api_version CONSTANT NUMBER := 1;
1322 l_init_msg_list VARCHAR2(1);
1323 i NUMBER;
1324 del_count NUMBER := 0;
1325 l_found VARCHAR2(1) := 'N';
1326
1327 l_drs_del_tbl drs_tbl_type;
1328
1329 CURSOR old_disb_sty_types_csr( p_disb_rule_id IN OKL_DISB_RULE_STY_TYPES.DISB_RULE_ID%TYPE )
1330 IS
1331 SELECT disb_rule_sty_type_id
1332 FROM okl_disb_rule_sty_types
1333 WHERE disb_rule_id = p_disb_rule_id;
1334
1335 old_sty_type_rec old_disb_sty_types_csr%rowtype;
1336
1337 BEGIN
1338 -- Initialization
1339 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1340
1341 x_return_status := OKL_API.START_ACTIVITY( l_api_name
1342 , g_pkg_name
1343 , p_init_msg_list
1344 , l_api_version
1345 , p_api_version
1346 , '_PVT'
1347 , x_return_status);
1348 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1349 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1350 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1351 RAISE OKL_API.G_EXCEPTION_ERROR;
1352 END IF;
1353
1354 -- If stream types available for the disbursement rule from DB
1355 -- is not found in the new stream types table then delete it
1356 -- from the DB table.
1357 FOR old_sty_type_rec IN old_disb_sty_types_csr(p_disb_rule_id) LOOP
1358 l_found := 'N';
1359 FOR i IN p_new_drs_tbl.FIRST .. p_new_drs_tbl.LAST LOOP
1360 IF (old_sty_type_rec.disb_rule_sty_type_id = p_new_drs_tbl(i).disb_rule_sty_type_id) THEN
1361 l_found := 'Y';
1362 END IF;
1363 END LOOP;
1364 IF (l_found = 'N') THEN
1365 l_drs_del_tbl(del_count).disb_rule_sty_type_id := old_sty_type_rec.disb_rule_sty_type_id;
1366 l_drs_del_tbl(del_count).disb_rule_id := p_disb_rule_id;
1367 del_count := del_count + 1;
1368 END IF;
1369 END LOOP;
1370
1371 IF ( l_drs_del_tbl.COUNT > 0 ) THEN
1372 -- delete record from OKL_DISB_RULE_STY_TYPES
1373 okl_drs_pvt.delete_row( p_api_version => l_api_version
1374 , p_init_msg_list => p_init_msg_list
1375 , x_return_status => x_return_status
1376 , x_msg_count => x_msg_count
1377 , x_msg_data => x_msg_data
1378 , p_drs_tbl => l_drs_del_tbl);
1379
1380 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1383 RAISE OKL_API.G_EXCEPTION_ERROR;
1384 END IF;
1385 END IF;
1386
1387 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1388
1389 EXCEPTION
1390 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1391 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1392 , g_pkg_name
1393 , 'OKL_API.G_RET_STS_ERROR'
1394 , x_msg_count
1395 , x_msg_data
1396 , '_PVT' );
1397 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1398 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1399 , g_pkg_name
1400 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
1401 , x_msg_count
1402 , x_msg_data
1403 , '_PVT' );
1404 WHEN OTHERS THEN
1405 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1406 , g_pkg_name
1407 , 'OTHERS'
1408 , x_msg_count
1409 , x_msg_data
1410 , '_PVT' );
1411 END delete_disb_rule_sty_types;
1412
1413
1414 -- Start of comments
1415 -- API name : del_disb_rule_vendor_sites
1416 -- Pre-reqs : None
1417 -- Function : deletes vendor sites which are not present in the input
1418 -- p_drv_tbl but in the OKL_DISB_RULE_VENDOR_SITES
1419 -- for a disbursement rule. This API deletes stream types from
1420 -- OKL_DISB_RULE_VENDOR_SITES table which user had deleted during
1421 -- update operation.
1422 -- Parameters :
1423 -- IN : p_api_version - Standard input parameter
1424 -- p_init_msg_list - Standard input parameter
1425 -- p_disb_rule_id - disbursement rule id
1426 -- p_drv_tbl - record type for OKL_DISB_RULE_VENDOR_SITES
1427 -- Version : 1.0
1428 -- History : gboomina created.
1429 -- End of comments
1430
1431
1432
1433 -- Start of comments
1434 -- API name : update_disbursement_rule
1435 -- Pre-reqs : None
1436 -- Function : update disbursement rule in the following tables
1437 -- OKL_DISB_RULES_B, OKL_DISB_RULES_TL,
1438 -- OKL_DISB_RULE_STY_TYPES and OKL_DISB_RULE_VENDOR_SITES
1439 -- Parameters :
1440 -- IN : p_api_version - Standard input parameter
1441 -- p_init_msg_list - Standard input parameter
1442 -- p_drav_rec - record type for OKL_DISB_RULES_V
1443 -- p_drs_tbl - record type for OKL_DISB_RULE_STY_TYPES
1444 -- p_drv_rec - record type for OKL_DISB_RULE_VENDOR_SITES
1445 -- Version : 1.0
1446 -- History : gboomina created.
1447 -- End of comments
1448
1449 PROCEDURE update_disbursement_rule( p_api_version IN NUMBER
1450 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1451 , x_return_status OUT NOCOPY VARCHAR2
1452 , x_msg_count OUT NOCOPY NUMBER
1453 , x_msg_data OUT NOCOPY VARCHAR2
1454 , p_drav_rec IN drav_rec_type
1455 , p_drs_tbl IN drs_tbl_type
1456 , p_drv_tbl IN drv_tbl_type
1457 , x_drav_rec OUT NOCOPY drav_rec_type
1458 )
1459 IS
1460 l_api_name CONSTANT VARCHAR2(40) := 'update_disbursement_rule';
1461 l_api_version CONSTANT NUMBER := 1;
1462 l_init_msg_list VARCHAR2(1);
1463 i NUMBER;
1464 ins_count NUMBER;
1465 upd_count NUMBER;
1466
1467 l_drav_rec drav_rec_type;
1468 l_drs_ins_tbl drs_tbl_type;
1469 l_drs_upd_tbl drs_tbl_type;
1470 l_drv_ins_tbl drv_tbl_type;
1471 l_drv_upd_tbl drv_tbl_type;
1472
1473 lx_drav_rec drav_rec_type;
1474 lx_drs_ins_tbl drs_tbl_type;
1475 lx_drs_upd_tbl drs_tbl_type;
1476 lx_drv_ins_tbl drv_tbl_type;
1477 lx_drv_upd_tbl drv_tbl_type;
1478
1479
1480 BEGIN
1481 -- Initialization
1482 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1483
1484 x_return_status := OKL_API.START_ACTIVITY( l_api_name
1485 , g_pkg_name
1486 , p_init_msg_list
1487 , l_api_version
1488 , p_api_version
1489 , '_PVT'
1490 , x_return_status);
1491 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1493 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1494 RAISE OKL_API.G_EXCEPTION_ERROR;
1495 END IF;
1496
1497 -- Do the required validations before updating
1498 validate_disbursement_rule( p_api_version => p_api_version
1499 , p_init_msg_list => p_init_msg_list
1500 , x_return_status => x_return_status
1501 , x_msg_count => x_msg_count
1502 , x_msg_data => x_msg_data
1503 , p_drav_rec => p_drav_rec
1504 , p_drs_tbl => p_drs_tbl
1505 , p_drv_tbl => p_drv_tbl);
1506 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1507 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1508 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1509 RAISE OKL_API.G_EXCEPTION_ERROR;
1510 END IF;
1511
1512 --g_debug_proc('Update Validated');
1513
1514
1515 -- Only one disbursement rule can exist for one vendor site and stream type
1516 -- combination for a given range of effective dates
1517 validate_rule_eff_dates( p_api_version => p_api_version
1518 , p_init_msg_list => p_init_msg_list
1519 , x_return_status => x_return_status
1520 , x_msg_count => x_msg_count
1521 , x_msg_data => x_msg_data
1522 , p_drav_rec => p_drav_rec
1523 , p_drs_tbl => p_drs_tbl
1524 , p_drv_tbl => p_drv_tbl);
1525 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1527 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1528 RAISE OKL_API.G_EXCEPTION_ERROR;
1529 END IF;
1530
1531
1532 l_drav_rec := p_drav_rec;
1533 --g_debug_proc('DRA PArams ' || l_drav_rec.description || ' , ' || l_drav_rec.scheduled_month);
1534
1535 -- update record in to OKL_DISB_RULES_B and TL
1536 okl_dra_pvt.update_row( p_api_version => l_api_version
1537 , p_init_msg_list => p_init_msg_list
1538 , x_return_status => x_return_status
1539 , x_msg_count => x_msg_count
1540 , x_msg_data => x_msg_data
1541 , p_drav_rec => l_drav_rec
1542 , x_drav_rec => lx_drav_rec);
1543 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1544 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1545 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1546 RAISE OKL_API.G_EXCEPTION_ERROR;
1547 END IF;
1548
1549 --g_debug_proc('Update DRA');
1550
1551 -- Delete the rows from OKL_DISB_RULES_STY_TYPES which
1552 -- are removed during update operation.
1553 delete_disb_rule_sty_types( p_api_version => l_api_version
1554 , p_init_msg_list => p_init_msg_list
1555 , x_return_status => x_return_status
1556 , x_msg_count => x_msg_count
1557 , x_msg_data => x_msg_data
1558 , p_disb_rule_id => p_drav_rec.disb_rule_id
1559 , p_new_drs_tbl => p_drs_tbl);
1560 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1561 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1562 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1563 RAISE OKL_API.G_EXCEPTION_ERROR;
1564 END IF;
1565 -- Delete the rows from OKL_DISB_RULES_VENDOR_SITES which
1566 -- are removed during update operation.
1567 del_disb_rule_vendor_sites( p_api_version => l_api_version
1568 , p_init_msg_list => p_init_msg_list
1569 , x_return_status => x_return_status
1570 , x_msg_count => x_msg_count
1571 , x_msg_data => x_msg_data
1572 , p_disb_rule_id => p_drav_rec.disb_rule_id
1573 , p_vendor_id => null
1574 , p_new_drv_tbl => p_drv_tbl);
1575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1578 RAISE OKL_API.G_EXCEPTION_ERROR;
1579 END IF;
1580
1581 --g_debug_proc('Update Del DRS');
1582 -- Check whether we need to create or update in to OKL_DISB_RULE_STY_TYPES table
1583 IF ( p_drs_tbl.COUNT > 0) THEN
1584 ins_count := 0;
1585 upd_count := 0;
1586 FOR i in p_drs_tbl.FIRST .. p_drs_tbl.LAST
1587 LOOP
1588 IF ( p_drs_tbl(i).disb_rule_sty_type_id = OKL_API.G_MISS_NUM OR
1589 p_drs_tbl(i).disb_rule_sty_type_id IS NULL ) THEN
1590 l_drs_ins_tbl(ins_count) := p_drs_tbl(i);
1591 --g_debug_proc('Insert Disb_Rule_Id ' || l_drs_ins_tbl(ins_count).disb_rule_id || ' , ' || l_drs_ins_tbl(ins_count).object_version_number || l_drs_ins_tbl(ins_count).DISB_RULE_STY_TYPE_ID);
1592 ins_count := ins_count + 1;
1593 ELSE
1594 l_drs_upd_tbl(upd_count) := p_drs_tbl(i);
1595 --g_debug_proc('Update Disb_Rule_Id ' || l_drs_upd_tbl(upd_count).disb_rule_id || ' , ' || l_drs_upd_tbl(upd_count).object_version_number || l_drs_upd_tbl(upd_count).DISB_RULE_STY_TYPE_ID);
1596 upd_count := upd_count + 1;
1597 END IF;
1598 END LOOP;
1599
1600 --g_debug_proc('Count ' || ins_count || ' , ' || upd_count );
1601
1602 IF ( l_drs_ins_tbl.COUNT > 0 ) THEN
1603 -- insert record in to OKL_DISB_RULE_STY_TYPES
1604 okl_drs_pvt.insert_row( p_api_version => l_api_version
1605 , p_init_msg_list => p_init_msg_list
1606 , x_return_status => x_return_status
1607 , x_msg_count => x_msg_count
1608 , x_msg_data => x_msg_data
1609 , p_drs_tbl => l_drs_ins_tbl
1610 , x_drs_tbl => lx_drs_ins_tbl);
1611 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1614 RAISE OKL_API.G_EXCEPTION_ERROR;
1615 END IF;
1616 END IF;
1617
1618 --g_debug_proc('Update Insert DRS');
1619 IF ( l_drs_upd_tbl.COUNT > 0 ) THEN
1620 -- update record in to OKL_DISB_RULE_STY_TYPES
1621 okl_drs_pvt.update_row( p_api_version => l_api_version
1622 , p_init_msg_list => p_init_msg_list
1623 , x_return_status => x_return_status
1624 , x_msg_count => x_msg_count
1625 , x_msg_data => x_msg_data
1626 , p_drs_tbl => l_drs_upd_tbl
1627 , x_drs_tbl => lx_drs_upd_tbl);
1628 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1629 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1630 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1631 RAISE OKL_API.G_EXCEPTION_ERROR;
1632 END IF;
1633 END IF;
1634
1635 END IF;
1636 --g_debug_proc('Update DRS');
1637
1638 -- Check whether we need to create or update in to OKL_DISB_RULE_VENDOR_SITES table
1639 IF ( p_drv_tbl.COUNT > 0) THEN
1640 ins_count := 0;
1641 upd_count := 0;
1642 FOR i in p_drv_tbl.FIRST .. p_drv_tbl.LAST
1643 LOOP
1644 IF ( p_drv_tbl(i).disb_rule_vendor_site_id = OKL_API.G_MISS_NUM OR
1645 p_drv_tbl(i).disb_rule_vendor_site_id IS NULL ) THEN
1646 l_drv_ins_tbl(ins_count) := p_drv_tbl(i);
1647 ins_count := ins_count + 1;
1648 ELSE
1649 l_drv_upd_tbl(upd_count) := p_drv_tbl(i);
1650 upd_count := upd_count + 1;
1651 END IF;
1652 END LOOP;
1653
1654 IF ( l_drv_ins_tbl.COUNT > 0 ) THEN
1655 -- insert record in to OKL_DISB_RULE_VENDOR_SITES
1656 okl_drv_pvt.insert_row( p_api_version => l_api_version
1657 , p_init_msg_list => p_init_msg_list
1658 , x_return_status => x_return_status
1659 , x_msg_count => x_msg_count
1660 , x_msg_data => x_msg_data
1661 , p_drv_tbl => l_drv_ins_tbl
1662 , x_drv_tbl => lx_drv_ins_tbl);
1663 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1664 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1665 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1666 RAISE OKL_API.G_EXCEPTION_ERROR;
1667 END IF;
1668 END IF;
1669
1670 --g_debug_proc('Update Insert DRV');
1671
1672 IF ( l_drv_upd_tbl.COUNT > 0 ) THEN
1673 -- update record in to OKL_DISB_RULE_VENDOR_SITES
1674 okl_drv_pvt.update_row( p_api_version => l_api_version
1675 , p_init_msg_list => p_init_msg_list
1676 , x_return_status => x_return_status
1677 , x_msg_count => x_msg_count
1678 , x_msg_data => x_msg_data
1679 , p_drv_tbl => l_drv_upd_tbl
1680 , x_drv_tbl => lx_drv_upd_tbl);
1681 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1682 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1683 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1684 RAISE OKL_API.G_EXCEPTION_ERROR;
1685 END IF;
1686 END IF;
1687 END IF;
1688
1689 -- g_debug_proc('Update DRV');
1690
1691 x_drav_rec := lx_drav_rec;
1692
1693 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1694
1695 EXCEPTION
1696 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1697 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1698 , g_pkg_name
1699 , 'OKL_API.G_RET_STS_ERROR'
1700 , x_msg_count
1701 , x_msg_data
1702 , '_PVT' );
1703 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1704 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1705 , g_pkg_name
1706 , 'OKL_API.G_RET_STS_UNEXP_ERROR'
1707 , x_msg_count
1708 , x_msg_data
1709 , '_PVT' );
1710 WHEN OTHERS THEN
1711 x_return_status := OKL_API.HANDLE_EXCEPTIONS( l_api_name
1712 , g_pkg_name
1713 , 'OTHERS'
1714 , x_msg_count
1715 , x_msg_data
1716 , '_PVT' );
1717 END update_disbursement_rule;
1718
1719
1720 END OKL_SETUP_DISB_RULES_PVT;