[Home] [Help]
PACKAGE BODY: APPS.OKL_ASSET_SUBSIDY_PVT
Source
1 PACKAGE BODY okl_asset_subsidy_pvt AS
2 /* $Header: OKLRASBB.pls 120.31.12020000.2 2013/03/07 05:11:40 racheruv ship $ */
3
4 ---------------------------------------------------------------------------
5 --GLOBAL Message Constants
6 ---------------------------------------------------------------------------
7 G_SUBSIDY_NOT_APPLICABLE CONSTANT Varchar2(200) := 'OKL_SUBSIDY_NOT_APPLICABLE';
8 G_SUBSIDY_TOKEN CONSTANT Varchar2(200) := 'SUBSIDY';
9 G_SUBSIDY_NAME_TOKEN CONSTANT Varchar2(200) := 'SUBSIDY_NAME'; -- cklee
10 G_ASSET_NUMBER_TOKEN CONSTANT Varchar2(200) := 'ASSET_NUMBER';
11
12 G_SUBSIDY_GREATER_THAN_COST CONSTANT Varchar2(200) := 'OKL_SUBSIDY_LIMIT_ERROR';
13 G_SUBSIDY_ALREADY_EXISTS CONSTANT Varchar2(200) := 'OKL_SUBSIDY_ALREADY_EXISTS';
14 G_SUBSIDY_EXCLUSIVE CONSTANT Varchar2(200) := 'OKL_SUBSIDY_EXCLUSIVE';
15 G_PARTY_UPDATE_INVALID CONSTANT Varchar2(200) := 'OKL_SUB_RBK_PARTY_UPDATE';
16 ----------------------------------------------------------------------------
17 --Global Constants
18 ----------------------------------------------------------------------------
19 G_FORMULA_OEC CONSTANT OKL_FORMULAE_V.NAME%TYPE := 'LINE_OEC';
20 G_FORMULA_CAP CONSTANT OKL_FORMULAE_V.NAME%TYPE := 'LINE_CAP_AMNT';
21
22 G_TRX_AMT_GT_TOT_BUDGET CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_TRX_AMT_MORE_THAN_TOT';
23 G_NO_CONVERSION_BASIS CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_NO_CONV_BASIS';
24
25 -- cklee, added global message constants as part of subsidy pools enhancement. START
26 G_SUB_POOL_NOT_ACTIVE CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_NOT_ACTIVE';
27 G_SUB_POOL_BALANCE_INVALID CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_INVALID_BAL';
28 G_SUB_POOL_ASSET_DATES_GAP CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_ASSET_DATES';
29 -- cklee, added global message constants as part of subsidy pools enhancement. END
30
31 /*
32 * sjalasut: aug 25, 04 added constants used in raising business event. BEGIN
33 */
34 G_WF_EVT_ASSET_SUBSIDY_CRTD CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.asset_subsidy_created';
35 G_WF_EVT_ASSET_SUBSIDY_RMVD CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.remove_asset_subsidy';
36 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(30) := 'CONTRACT_ID';
37 G_WF_ITM_ASSET_ID CONSTANT VARCHAR2(30) := 'ASSET_ID';
38 G_WF_ITM_SUBSIDY_ID CONSTANT VARCHAR2(30) := 'SUBSIDY_ID';
39 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
40 /*
41 * sjalasut: aug 25, 04 added constants used in raising business event. END
42 */
43
44
45 /*
46 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. BEGIN
47 *
48 */
49 -------------------------------------------------------------------------------
50 -- PROCEDURE raise_business_event
51 -------------------------------------------------------------------------------
52 -- Start of comments
53 --
54 -- Procedure Name : raise_business_event
55 -- Description : This procedure is a wrapper that raises a business event
56 -- : when ever asset subsidy is created or deleted.
57 -- Business Rules :
58 -- Parameters : p_chr_id,p_asset_id,p_subsidy_id,p_event_name along with other api params
59 -- Version : 1.0
60 -- History : 30-AUG-2004 SJALASUT created
61 -- End of comments
62
63 PROCEDURE raise_business_event(p_api_version IN NUMBER,
64 p_init_msg_list IN VARCHAR2,
65 p_chr_id IN okc_k_headers_b.id%TYPE,
66 p_asset_id IN okc_k_lines_b.id%TYPE,
67 p_subsidy_id IN okl_subsidies_b.id%TYPE,
68 p_event_name IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 ) IS
73 l_parameter_list wf_parameter_list_t;
74 l_contract_process VARCHAR2(20);
75 BEGIN
76 x_return_status := OKL_API.G_RET_STS_SUCCESS;
77
78 -- wrapper API to get contract process. this API determines in which status the
79 -- contract in question is.
80 l_contract_process := okl_lla_util_pvt.get_contract_process(p_chr_id => p_chr_id);
81 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID, p_chr_id, l_parameter_list);
82 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID, p_asset_id, l_parameter_list);
83 wf_event.AddParameterToList(G_WF_ITM_SUBSIDY_ID, p_subsidy_id, l_parameter_list);
84 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS, l_contract_process, l_parameter_list);
85 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
86 p_init_msg_list => p_init_msg_list,
87 x_return_status => x_return_status,
88 x_msg_count => x_msg_count,
89 x_msg_data => x_msg_data,
90 p_event_name => p_event_name,
91 p_parameters => l_parameter_list);
92 EXCEPTION
93 WHEN OTHERS THEN
94 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
95 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
96 END raise_business_event;
97
98 /*
99 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. END
100 */
101
102
103 ---------------------------------------------------------------------------
104 -- FUNCTION get_rec for: OKL_ASSET_SUBSIDIES_UV
105 ---------------------------------------------------------------------------
106 FUNCTION get_rec (
107 p_asb_rec IN asb_rec_type,
108 x_no_data_found OUT NOCOPY BOOLEAN
109 ) RETURN asb_rec_type IS
110 CURSOR asb_csr (p_id IN NUMBER) IS
111 SELECT
112 SUBSIDY_ID
113 ,SUBSIDY_CLE_ID
114 ,NAME
115 ,DESCRIPTION
116 ,AMOUNT
117 ,SUBSIDY_OVERRIDE_AMOUNT
118 ,DNZ_CHR_ID
119 ,ASSET_CLE_ID
120 ,CPL_ID
121 ,VENDOR_ID
122 ,VENDOR_NAME
123 FROM okl_asset_subsidy_uv asb
124 WHERE asb.subsidy_cle_id = p_id;
125 l_asb_rec asb_rec_type;
126 BEGIN
127 x_no_data_found := TRUE;
128 -- Get current database values
129 OPEN asb_csr (p_asb_rec.subsidy_cle_id);
130 FETCH asb_csr INTO
131 l_asb_rec.SUBSIDY_ID
132 ,l_asb_rec.SUBSIDY_CLE_ID
133 ,l_asb_rec.NAME
134 ,l_asb_rec.DESCRIPTION
135 ,l_asb_rec.AMOUNT
136 ,l_asb_rec.SUBSIDY_OVERRIDE_AMOUNT
137 ,l_asb_rec.DNZ_CHR_ID
138 ,l_asb_rec.ASSET_CLE_ID
139 ,l_asb_rec.CPL_ID
140 ,l_asb_rec.VENDOR_ID
141 ,l_asb_rec.VENDOR_NAME;
142 x_no_data_found := asb_csr%NOTFOUND;
143 CLOSE asb_csr;
144
145 RETURN(l_asb_rec);
146
147 END get_rec;
148
149 FUNCTION get_rec (
150 p_asb_rec IN asb_rec_type
151 ) RETURN asb_rec_type IS
152 l_row_notfound BOOLEAN := TRUE;
153 BEGIN
154
155 RETURN(get_rec(p_asb_rec, l_row_notfound));
156
157 END get_rec;
158 --------------------------------------------------------------------------------
159 --Start of comments
160 -- Procedure : Is_Rebook_Copy (Local)
161 -- Description : Return 'Y' if contract is a rebook copy contract, 'N' if it
162 -- is not
163 --Notes :
164 --Prameters : IN
165 -- p_chr_id - contract id
166 -- Return - 'Y' if rebook copy
167 -- 'N' if not rebook copy
168 --end of comments
169 --------------------------------------------------------------------------------
170 FUNCTION Is_Rebook_Copy (p_chr_id IN NUMBER) return varchar2 IS
171 --Cursor to check if the contract is a rebook copy
172 Cursor Rbk_Cpy_Csr(p_chr_id IN Number) is
173 Select 'Y'
174 From okc_k_headers_b chr
175 where chr.orig_system_source_code = 'OKL_REBOOK'
176 and chr.id = p_chr_id;
177
178 l_rbk_cpy Varchar2(1) default 'N';
179 Begin
180 l_rbk_cpy := 'N';
181 Open Rbk_Cpy_Csr(p_chr_id => p_chr_id);
182 Fetch Rbk_Cpy_Csr into l_rbk_cpy;
183 If Rbk_Cpy_Csr%NOTFOUND Then
184 Null;
185 End If;
186 Close Rbk_Cpy_Csr;
187 Return (l_rbk_cpy);
188 End Is_Rebook_Copy;
189 --------------------------------------------------------------------------------
190 --------------------------------------------------------------------------------
191 --Local procedure to fill up defaultvalues for lines and partyroles for insertion
192 --Name : Fill_up_defaults
193 --Creation : 20-Aug-2003
194 --Purpose : To fill up defaults in line and party record structures for
195 -- update of asset subsidy line
196 --------------------------------------------------------------------------------
197 PROCEDURE Fill_up_defaults(x_return_status OUT NOCOPY VARCHAR2,
198 p_asb_rec IN asb_rec_type,
199 p_db_asb_rec IN asb_rec_type,
200 x_clev_rec OUT NOCOPY OKL_OKC_MIGRATION_PVT.clev_rec_type,
201 x_klev_rec OUT NOCOPY OKL_CONTRACT_PUB.klev_rec_type,
202 x_cplv_rec OUT NOCOPY OKL_OKC_MIGRATION_PVT.cplv_rec_type) is
203
204 l_asb_rec asb_rec_type;
205 l_db_asb_rec asb_rec_type;
206
207 --cursor to get effectivity dates and stream type
208 cursor l_sub_csr (p_sub_id in number) is
209 select subb.stream_type_id,
210 subb.effective_from_date,
211 subb.effective_to_date,
212 subb.expire_after_days,
213 subb.maximum_term,
214 subb.name,
215 subt.short_description
216 from
217 okl_subsidies_tl subt,
218 okl_subsidies_b subb
219 where subt.id = subb.id
220 and subt.language = userenv('LANG')
221 and subb.id = p_sub_id;
222
223 l_sub_rec l_sub_csr%ROWTYPE;
224
225 --cursor to get defaults from asset line (top line)
226 cursor l_cleb_csr (p_asset_cle_id in number) is
227 select cleb.start_date,
228 cleb.end_date,
229 cleb.sts_code,
230 cleb.currency_code
231 from okc_k_lines_b cleb
232 where cleb.id = p_asset_cle_id;
233
234 l_cleb_rec l_cleb_csr%ROWTYPE;
235
236 --cursor to get vendor id if vendor name is given
237 cursor l_vendor_csr (p_vend_name in varchar2) is
238 select vendor_id
239 from po_vendors pov
240 where vendor_name = ltrim(rtrim(p_vend_name,' '),' ');
241
242 l_vendor_id number;
243
244 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
245 l_klev_rec okl_contract_pub.klev_rec_type;
246 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
247
248 l_temp_sub_id Number;
249
250 --cursor to get subsidy id from subsidy name
251 cursor l_subname_csr (p_subsidy_name in varchar2) is
252 select id
253 from okl_subsidies_b subb
254 where name = ltrim(rtrim(p_subsidy_name,' '),' ');
255
256 l_subsidy_id Number;
257
258 begin
259 x_return_status := OKL_API.G_RET_STS_SUCCESS;
260 l_asb_rec := p_asb_rec;
261 l_db_asb_rec := p_db_asb_rec;
262
263 --l_clev_rec.id := l_db_asb_rec.subsidy_cle_id;
264 --l_klev_rec.id := l_db_asb_rec.subsidy_cle_id;
265 --l_cplv_rec.id := l_db_asb_rec.cpl_id;
266
267 If l_clev_rec.id = OKL_API.G_MISS_NUM then
268 l_clev_rec.id := l_db_asb_rec.subsidy_cle_id;
269 l_klev_rec.id := l_db_asb_rec.subsidy_cle_id;
270 End If;
271
272 --get subsidy id from name if id has not been specified
273 If (l_asb_rec.subsidy_id is NULL) OR (l_asb_rec.subsidy_id = OKL_API.G_MISS_NUM) then
274 If (l_asb_rec.name is not NULL) AND (l_asb_rec.name <> OKL_API.G_MISS_CHAR) then
275 Open l_subname_csr(p_subsidy_name => l_asb_rec.name);
276 Fetch l_subname_csr into l_subsidy_id;
277 If l_subname_csr%NOTFOUND then
278 null;
279 else
280 l_asb_rec.subsidy_id := l_subsidy_id;
281 end if;
282 Close l_subname_csr;
283 End If;
284 End If;
285
286
287 --check if subsidy has changed
288 If l_asb_rec.subsidy_id <> OKL_API.G_MISS_NUM Then
289 l_klev_rec.subsidy_id := l_asb_rec.subsidy_id;
290 l_temp_sub_id := l_asb_rec.subsidy_id;
291 Else
292 l_klev_rec.subsidy_id := l_db_asb_rec.subsidy_id;
293 l_temp_sub_id := l_db_asb_rec.subsidy_id;
294 End If;
295
296 --fill start end dates and stream type
297 open l_sub_csr(p_sub_id => l_temp_sub_id);
298 fetch l_sub_csr into l_sub_rec;
299 If l_sub_csr%NOTFOUND then
300 null;
301 Else
302 l_klev_rec.sty_id := l_sub_rec.stream_type_id;
303 l_clev_rec.name := l_sub_rec.name;
304 l_clev_rec.item_description := l_sub_rec.short_description;
305 End If;
306 close l_sub_csr;
307
308 open l_cleb_csr(p_asset_cle_id => l_db_asb_rec.asset_cle_id);
309 fetch l_cleb_csr into l_cleb_rec;
310 If l_cleb_csr%NOTFOUND then
311 null;
312 Else
313 l_clev_rec.start_date := l_cleb_rec.start_date;
314 l_clev_rec.sts_code := l_cleb_rec.sts_code;
315 l_clev_rec.currency_code := l_cleb_rec.currency_code;
316 If l_sub_rec.maximum_term is not null then
317 If (add_months(l_cleb_rec.start_date ,l_sub_rec.maximum_term) - 1) < (l_cleb_rec.end_date) then
318 l_clev_rec.end_date := add_months(l_cleb_rec.start_date ,l_sub_rec.maximum_term) - 1;
319 Else
320 l_clev_rec.end_date := l_cleb_rec.end_date;
321 End If;
322 Else
323 l_clev_rec.end_date := l_cleb_rec.end_date;
324 End If;
325 End If;
326 close l_cleb_csr;
327
328 --amount
329 If nvl(l_asb_rec.amount,-1) <> OKL_API.G_MISS_NUM then
330 l_klev_rec.amount := l_asb_rec.amount;
331 Else
332 l_klev_rec.amount := l_db_asb_rec.amount;
333 End If;
334
335 --override amount
336 If nvl(l_asb_rec.subsidy_override_amount,-1) <> OKL_API.G_MISS_NUM then
337 l_klev_rec.subsidy_override_amount := l_asb_rec.subsidy_override_amount;
338 Else
339 l_klev_rec.subsidy_override_amount := l_db_asb_rec.subsidy_override_amount;
340 End If;
341
342 --subsidy vendor
343 If (l_asb_rec.vendor_id is NULL) OR (l_asb_rec.vendor_id = OKL_API.G_MISS_NUM) then
344 If (l_asb_rec.vendor_name is NOT NULL) and (l_asb_rec.vendor_name <> OKL_API.G_MISS_CHAR) then
345 open l_vendor_csr(p_vend_name => l_asb_rec.vendor_name);
346 fetch l_vendor_csr into l_vendor_id;
347 If l_vendor_csr%NOTFOUND then
348 null;
349 Else
350 l_asb_rec.vendor_id := l_vendor_id;
351 End If;
352 close l_vendor_csr;
353 End If;
354 End If;
355
356 If nvl(l_asb_rec.vendor_id,-1) <> OKL_API.G_MISS_NUM then
357 --If l_cplv_rec.id = OKL_API.G_MISS_NUM then
358 l_cplv_rec.id := l_db_asb_rec.cpl_id;
359 --End If;
360 If l_cplv_rec.id is Null then
361 l_cplv_rec.dnz_chr_id := l_db_asb_rec.dnz_chr_id;
362 l_cplv_rec.rle_code := 'OKL_VENDOR';
363 l_cplv_rec.object1_id1 := to_char(l_asb_rec.vendor_id);
364 l_cplv_rec.object1_id2 := '#';
365 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
366 ElsIf l_cplv_rec.id is not null then
367 l_cplv_rec.object1_id1 := to_char(l_asb_rec.vendor_id);
368 End If;
369 Else
370 l_cplv_rec.id := l_db_asb_rec.cpl_id;
371 --End If;
372 If l_cplv_rec.id is Null then
373 l_cplv_rec.dnz_chr_id := l_db_asb_rec.dnz_chr_id;
374 l_cplv_rec.rle_code := 'OKL_VENDOR';
375 l_cplv_rec.object1_id1 := to_char(l_db_asb_rec.vendor_id);
376 l_cplv_rec.object1_id2 := '#';
377 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
378 ElsIf l_cplv_rec.id is not null then
379 l_cplv_rec.object1_id1 := to_char(l_db_asb_rec.vendor_id);
380 End If;
381 End If;
382
383 x_clev_rec := l_clev_rec;
384 x_klev_rec := l_klev_rec;
385 x_cplv_rec := l_cplv_rec;
386
387 Exception
388 When Others then
389 If l_sub_csr%ISOPEN then
390 close l_sub_csr;
391 End If;
392 If l_cleb_csr%ISOPEN then
393 close l_cleb_csr;
394 End If;
395 If l_vendor_csr%ISOPEN then
396 close l_vendor_csr;
397 End If;
398 If l_subname_csr%ISOPEN then
399 close l_subname_csr;
400 End If;
401 x_return_status := OKL_API.G_RET_STS_ERROR;
402 End Fill_up_defaults;
403 --------------------------------------------------------------------------------
404 --Local procedure to initialize values for lines and partyroles for insertion
405 --Name : Initialize_records
406 --Creation : 20-Aug-2003
407 --Purpose : To initialize defaults in line and party record structures for
408 -- creation of asset subsidy line
409 --------------------------------------------------------------------------------
410 PROCEDURE Initialize_records(x_return_status OUT NOCOPY VARCHAR2,
411 p_asb_rec IN asb_rec_type,
412 x_clev_rec OUT NOCOPY OKL_OKC_MIGRATION_PVT.clev_rec_type,
413 x_klev_rec OUT NOCOPY OKL_CONTRACT_PUB.klev_rec_type,
414 x_cplv_rec OUT NOCOPY OKL_OKC_MIGRATION_PVT.cplv_rec_type) is
415
416 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
417 l_api_name CONSTANT varchar2(30) := 'INITIALIZE_RECORDS';
418 l_api_version CONSTANT NUMBER := 1.0;
419
420 l_asb_rec asb_rec_type;
421 l_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
422 l_klev_rec OKL_CONTRACT_PUB.klev_rec_type;
423 l_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
424
425 --cursor to get effectivity dates and stream type
426 cursor l_sub_csr (p_sub_id in number) is
427 select subb.stream_type_id,
428 subb.effective_from_date,
429 subb.effective_to_date,
430 subb.expire_after_days,
431 subb.maximum_term,
432 subb.name,
433 subt.short_description
434 from
435 okl_subsidies_tl subt,
436 okl_subsidies_b subb
437 where subt.id = subb.id
438 and subt.language = userenv('LANG')
439 and subb.id = p_sub_id;
440
441 l_sub_rec l_sub_csr%ROWTYPE;
442
443 --cursor to get defaults from asset line (top line)
444 cursor l_cleb_csr (p_asset_cle_id in number) is
445 select cleb.start_date,
446 cleb.end_date,
447 cleb.sts_code,
448 cleb.currency_code
449 from okc_k_lines_b cleb
450 where cleb.id = p_asset_cle_id;
451
452 l_cleb_rec l_cleb_csr%ROWTYPE;
453
454 --cursor to get lse_id
455 cursor l_lseb_csr(p_chr_id in number) is
456 Select lseb.id
457 from okc_line_styles_b lseb,
458 okc_line_styles_b top_lseb,
459 okc_subclass_top_line scs_lse,
460 okc_k_headers_b chrb
461 where lseb.lty_code = G_SUBLINE_LTY_CODE
462 and lseb.lse_parent_id = top_lseb.id
463 and top_lseb.lty_code = 'FREE_FORM1'
464 and lseb.lse_parent_id = scs_lse.lse_id
465 and scs_lse.scs_code = chrb.scs_code
466 and chrb.id = p_chr_id;
467
468 l_lse_id Number;
469
470 --cursor to get display sequence
471 cursor l_dispseq_csr(p_asset_cle_id in number, p_lse_id in number, p_chr_id in number) is
472 select nvl(max(cleb.display_sequence),0)+5
473 from okc_k_lines_b cleb
474 where cleb.cle_id = p_asset_cle_id
475 and cleb.dnz_chr_id = p_chr_id
476 and cleb.lse_id = p_lse_id;
477
478 l_display_sequence Number;
479
480 --cursor to get vendor id if vendor name is given
481 cursor l_vendor_csr (p_vend_name in varchar2) is
482 select vendor_id
483 from po_vendors pov
484 where vendor_name = ltrim(rtrim(p_vend_name,' '),' ');
485
486 l_vendor_id number;
487
488 --cursor to get subsidy id from subsidy name
489 cursor l_subname_csr (p_subsidy_name in varchar2) is
490 select id
491 from okl_subsidies_b subb
492 where name = ltrim(rtrim(p_subsidy_name,' '),' ');
493
494 l_subsidy_id Number;
495
496 --Cursor to find out Rebook date
497 Cursor l_rbk_date_csr (rbk_chr_id IN NUMBER) is
498 SELECT DATE_TRANSACTION_OCCURRED
499 FROM okl_trx_contracts ktrx
500 WHERE ktrx.KHR_ID_NEW = rbk_chr_id
501 AND ktrx.tsu_code = 'ENTERED'
502 AND ktrx.tcn_type = 'TRBK'
503 --rkuttiya added for 12.1.1 Multi GAAP Project
504 AND ktrx.representation_type = 'PRIMARY';
505 --
506
507 l_rbk_date okl_trx_contracts.DATE_TRANSACTION_OCCURRED%TYPE;
508
509 l_rbk_cpy varchar2(1) default 'N';
510
511
512 Begin
513 --dbms_output.put_line(to_char(p_asb_rec.amount));
514 x_return_status := l_return_Status;
515 l_asb_rec := p_asb_rec;
516
517 --get subsidy id from name if id has not been specified
518 If (l_asb_rec.subsidy_id is NULL) OR (l_asb_rec.subsidy_id = OKL_API.G_MISS_NUM) then
519 If (l_asb_rec.name is not NULL) AND (l_asb_rec.name <> OKL_API.G_MISS_CHAR) then
520 Open l_subname_csr(p_subsidy_name => l_asb_rec.name);
521 Fetch l_subname_csr into l_subsidy_id;
522 If l_subname_csr%NOTFOUND then
523 null;
524 else
525 l_asb_rec.subsidy_id := l_subsidy_id;
526 end if;
527 Close l_subname_csr;
528 End If;
529 End If;
530
531 --dbms_output.put_line(to_char(l_asb_rec.amount));
532 --fill up the defaults
533 l_klev_rec.subsidy_id := l_asb_rec.subsidy_id;
534 l_klev_rec.amount := l_asb_rec.amount;
535 l_klev_rec.subsidy_override_amount := l_asb_rec.subsidy_override_amount;
536 l_clev_rec.cle_id := l_asb_rec.asset_cle_id;
537 l_clev_rec.dnz_chr_id := l_asb_rec.dnz_chr_id;
538 l_clev_rec.exception_yn := 'N';
539
540 --dbms_output.put_line(to_char(l_klev_rec.amount));
541
542 --fill lse id
543 open l_lseb_csr(p_chr_id => l_asb_rec.dnz_chr_id);
544 fetch l_lseb_csr into l_lse_id;
545 If l_lseb_csr%NOTFOUND then
546 Null;
547 Else
548 l_clev_rec.lse_id := l_lse_id;
549 End If;
550 close l_lseb_csr;
551
552 --fill display sequence
553 open l_dispseq_csr(p_asset_cle_id => l_asb_rec.asset_cle_id,
554 p_lse_id => l_lse_id,
555 p_chr_id => l_asb_rec.dnz_chr_id);
556 fetch l_dispseq_csr into l_display_sequence;
557 If l_dispseq_csr%NOTFOUND then
558 null;
559 Else
560 l_clev_rec.display_sequence := l_display_sequence;
561 End If;
562 --l_clev_rec.lse_id := 10021;
563 --l_clev_rec.display_sequence := 1;
564
565 --fill start end dates and stream type
566 open l_sub_csr(p_sub_id => l_asb_rec.subsidy_id);
567 fetch l_sub_csr into l_sub_rec;
568 If l_sub_csr%NOTFOUND then
569 null;
570 Else
571 l_klev_rec.sty_id := l_sub_rec.stream_type_id;
572 l_clev_rec.name := l_sub_rec.name;
573 l_clev_rec.item_description := l_sub_rec.short_description;
574 End If;
575 close l_sub_csr;
576
577 open l_cleb_csr(p_asset_cle_id => l_asb_rec.asset_cle_id);
578 fetch l_cleb_csr into l_cleb_rec;
579 If l_cleb_csr%NOTFOUND then
580 null;
581 Else
582 l_clev_rec.start_date := l_cleb_rec.start_date;
583 l_clev_rec.sts_code := l_cleb_rec.sts_code;
584 l_clev_rec.currency_code := l_cleb_rec.currency_code;
585 ----------------------------------------
586 --check if it is a rebook copy contract
587 --if yes then get the rebook transaction
588 --date and make it as start date of subsidy
589 -------------------------------------------
590 l_rbk_cpy := is_rebook_copy(p_chr_id => l_asb_rec.dnz_chr_id);
591 If l_rbk_cpy = 'Y' then
592
593 open l_rbk_date_csr(rbk_chr_id => l_asb_rec.dnz_chr_id);
594 fetch l_rbk_date_csr into l_rbk_date;
595 if l_rbk_date_csr%NOTFOUND then
596 NULL;
597 end If;
598 close l_rbk_date_csr;
599
600 If l_rbk_date is NULL OR l_rbk_date = OKL_API.G_MISS_DATE then
601 NULL;
602 Else
603 l_clev_rec.start_date := l_rbk_date;
604 End If;
605 End If;
606 -----------------------------------------
607 --End of rebook check
608 -----------------------------------------
609 If l_sub_rec.maximum_term is not null then
610 If (add_months(l_clev_rec.start_date , l_sub_rec.maximum_term) - 1) < (l_cleb_rec.end_date) then
611 l_clev_rec.end_date := add_months(l_clev_rec.start_date , l_sub_rec.maximum_term) - 1;
612 Else
613 l_clev_rec.end_date := l_cleb_rec.end_date;
614 End If;
615 Else
616 l_clev_rec.end_date := l_cleb_rec.end_date;
617 End If;
618 End If;
619 close l_cleb_csr;
620
621 --fill party role record
622 --in case someone has passed id make it default for record creation
623 If l_cplv_rec.id <> OKL_API.G_MISS_NUM then
624 l_cplv_rec.id := OKL_API.G_MISS_NUM;
625 End If;
626 l_cplv_rec.dnz_chr_id := l_asb_rec.dnz_chr_id;
627 l_cplv_rec.rle_code := 'OKL_VENDOR';
628 --vendor id
629 If (l_asb_rec.vendor_id is NULL) OR (l_asb_rec.vendor_id = OKL_API.G_MISS_NUM) then
630 If (l_asb_rec.vendor_name is NOT NULL) and (l_asb_rec.vendor_name <> OKL_API.G_MISS_CHAR) then
631 --get vendor id
632 open l_vendor_csr(p_vend_name => l_asb_rec.vendor_name);
633 fetch l_vendor_csr into l_vendor_id;
634 If l_vendor_csr%NOTFOUND then
635 l_cplv_rec.object1_id1 := to_char(l_asb_rec.vendor_id);
636 End If;
637 Close l_vendor_csr;
638 End If;
639 Else
640 l_cplv_rec.object1_id1 := to_char(l_asb_rec.vendor_id);
641 End If;
642
643 l_cplv_rec.object1_id2 := '#';
644 l_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
645
646 x_clev_rec := l_clev_rec;
647 x_klev_rec := l_klev_rec;
648 x_cplv_rec := l_cplv_rec;
649
650 Exception
651 When Others then
652 If l_sub_csr%ISOPEN then
653 close l_sub_csr;
654 End If;
655 If l_cleb_csr%ISOPEN then
656 close l_cleb_csr;
657 End If;
658 If l_lseb_csr%ISOPEN then
659 close l_lseb_csr;
660 End If;
661 If l_dispseq_csr%ISOPEN then
662 close l_dispseq_csr;
663 End If;
664 If l_vendor_csr%ISOPEN then
665 close l_dispseq_csr;
666 End If;
667 If l_subname_csr%ISOPEN then
668 close l_subname_csr;
669 End If;
670 If l_rbk_date_csr%ISOPEN then
671 close l_rbk_date_csr;
672 End If;
673 x_return_status := OKL_API.G_RET_STS_ERROR;
674 End Initialize_records;
675 --------------------------------------------------------------------------------
676 --Name : recalculate_costs
677 --Creation : 29-Aug-2003
678 --Purpose : Local procedure to update subsidized costs
679 --------------------------------------------------------------------------------
680 PROCEDURE recalculate_costs(
681 p_api_version IN NUMBER,
682 p_init_msg_list IN VARCHAR2,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2,
686 p_chr_id IN NUMBER,
687 p_asset_cle_id IN NUMBER
688 ) is
689
690 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
691 l_api_name CONSTANT varchar2(30) := 'RECALCULATE_COSTS';
692 l_api_version CONSTANT NUMBER := 1.0;
693
694 l_oec number;
695 l_cap_amount number;
696 l_total_subsidy number;
697
698 l_sub_oec number;
699 l_sub_cap_amount number;
700
701 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
702 l_klev_rec okl_contract_pub.klev_rec_type;
703 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
704 lx_klev_rec okl_contract_pub.klev_rec_type;
705
706 --cursor to fetch asset number for exception
707 cursor l_clet_csr (p_cle_id in number) is
708 select clet.name
709 from okc_k_lines_tl clet
710 where clet.id = p_cle_id
711 and clet.language = userenv('LANG');
712
713 l_asset_number okc_k_lines_tl.name%TYPE;
714
715 begin
716
717 x_return_status := OKL_API.G_RET_STS_SUCCESS;
718 -- Call start_activity to create savepoint, check compatibility
719 -- and initialize message list
720 x_return_status := OKL_API.START_ACTIVITY (
721 l_api_name
722 ,p_init_msg_list
723 ,'_PVT'
724 ,x_return_status);
725 -- Check if activity started successfully
726 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
727 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
728 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
729 RAISE OKL_API.G_EXCEPTION_ERROR;
730 END IF;
731
732 --calculate and update subsidised OEC and Capital Amount
733 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
734 p_init_msg_list => p_init_msg_list,
735 x_return_status => x_return_status,
736 x_msg_count => x_msg_count,
737 x_msg_data => x_msg_data,
738 p_formula_name => G_FORMULA_OEC,
739 p_contract_id => p_chr_id,
740 p_line_id => p_asset_cle_id,
741 x_value => l_oec);
742
743 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
744 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
745 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
746 RAISE OKL_API.G_EXCEPTION_ERROR;
747 END IF;
748
749 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => p_api_version,
750 p_init_msg_list => p_init_msg_list,
751 x_return_status => x_return_status,
752 x_msg_count => x_msg_count,
753 x_msg_data => x_msg_data,
754 p_formula_name => G_FORMULA_CAP,
755 p_contract_id => p_chr_id,
756 p_line_id => p_asset_cle_id,
757 x_value => l_cap_amount);
758
759 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
761 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
762 RAISE OKL_API.G_EXCEPTION_ERROR;
763 END IF;
764
765 --get total subsidy
766 OKL_SUBSIDY_PROCESS_PVT.get_asset_subsidy_amount(
767 p_api_version => p_api_version,
768 p_init_msg_list => p_init_msg_list,
769 x_return_status => x_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_asset_cle_id => p_asset_cle_id,
773 --p_accounting_method => 'NET',
774 x_subsidy_amount => l_total_subsidy);
775
776 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
777 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
779 RAISE OKL_API.G_EXCEPTION_ERROR;
780 END IF;
781
782 l_sub_oec := (l_oec - l_total_subsidy);
783 l_sub_cap_amount := (l_cap_amount - l_total_subsidy);
784
785 If (l_sub_oec < 0) then
786 open l_clet_csr(p_cle_id => p_asset_cle_id);
787 Fetch l_clet_csr into l_asset_number;
788 if l_clet_csr%NOTFOUND then
789 null;
790 end if;
791 close l_clet_csr;
792 --raise error : total subsidy can not be greater than asset cost
793 OKL_API.set_message(p_app_name => G_APP_NAME,
794 p_msg_name => G_SUBSIDY_GREATER_THAN_COST,
795 p_token1 => G_ASSET_NUMBER_TOKEN,
796 p_token1_value => l_asset_number);
797 x_return_status := OKL_API.G_RET_STS_ERROR;
798 RAISE OKL_API.G_EXCEPTION_ERROR;
799 End If;
800
801 ----------------------------------------------------------------------
802 --call api to update costs on asset line
803 ----------------------------------------------------------------------
804 l_clev_rec.id := p_asset_cle_id;
805 l_klev_rec.id := p_asset_cle_id;
806 l_klev_rec.oec := l_oec;
807 l_klev_rec.capital_amount := l_cap_amount;
808
809 --we do not intend to maintain subsidized costs as discount is built in line_cap_amount
810 --l_klev_rec.subsidized_oec := l_sub_oec;
811 --l_klev_rec.subsidized_cap_amount := l_sub_cap_amount;
812
813 okl_contract_pub.update_contract_line
814 (p_api_version => p_api_version,
815 p_init_msg_list => p_init_msg_list,
816 x_return_status => x_return_status,
817 x_msg_count => x_msg_count,
818 x_msg_data => x_msg_data,
819 p_clev_rec => l_clev_rec,
820 p_klev_rec => l_klev_rec,
821 x_clev_rec => lx_clev_rec,
822 x_klev_rec => lx_klev_rec
823 );
824
825 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
828 RAISE OKL_API.G_EXCEPTION_ERROR;
829 END IF;
830
831 --Bug# 4899328
832 -- Recalculate Asset depreciation cost when there
833 -- is a change to Subsidy
834 okl_activate_asset_pvt.recalculate_asset_cost
835 (p_api_version => p_api_version,
836 p_init_msg_list => p_init_msg_list,
837 x_return_status => x_return_status,
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data,
840 p_chr_id => p_chr_id,
841 p_cle_id => p_asset_cle_id
842 );
843
844 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
845 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
846 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
847 RAISE OKL_API.G_EXCEPTION_ERROR;
848 END IF;
849 --Bug# 4899328
850
851 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
852 EXCEPTION
853 WHEN OKL_API.G_EXCEPTION_ERROR THEN
854 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
855 l_api_name,
856 G_PKG_NAME,
857 'OKL_API.G_RET_STS_ERROR',
858 x_msg_count,
859 x_msg_data,
860 '_PVT');
861 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
862 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
863 l_api_name,
864 G_PKG_NAME,
865 'OKL_API.G_RET_STS_UNEXP_ERROR',
866 x_msg_count,
867 x_msg_data,
868 '_PVT');
869 WHEN OTHERS THEN
870 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
871 l_api_name,
872 G_PKG_NAME,
873 'OTHERS',
874 x_msg_count,
875 x_msg_data,
876 '_PVT');
877 End recalculate_costs;
878
879 --------------------------------------------------------------------------------
880 --Name : calculate_asset_subsidy
881 --Creation : 20-Aug-2003
882 --Purpose : To calculate asset subsidy amount will call the calculation API
883 --------------------------------------------------------------------------------
884 PROCEDURE calculate_asset_subsidy(
885 p_api_version IN NUMBER,
886 p_init_msg_list IN VARCHAR2,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_msg_count OUT NOCOPY NUMBER,
889 x_msg_data OUT NOCOPY VARCHAR2,
890 p_asb_rec IN asb_rec_type,
891 x_asb_rec OUT NOCOPY asb_rec_type) is
892
893 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
894 l_api_name CONSTANT varchar2(30) := 'CALCULATE_ASSET_SUBSIDY';
895 l_api_version CONSTANT NUMBER := 1.0;
896
897 l_asb_rec asb_rec_type;
898 l_subsidy_amount number;
899
900
901 l_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
902 l_sub_klev_rec okl_contract_pub.klev_rec_type;
903 lx_sub_clev_rec okl_okc_migration_pvt.clev_rec_type;
904 lx_sub_klev_rec okl_contract_pub.klev_rec_type;
905
906 lv_subsidy_amount NUMBER;
907
908 begin
909 x_return_status := OKL_API.G_RET_STS_SUCCESS;
910 -- Call start_activity to create savepoint, check compatibility
911 -- and initialize message list
912 x_return_status := OKL_API.START_ACTIVITY (
913 l_api_name
914 ,p_init_msg_list
915 ,'_PVT'
916 ,x_return_status);
917 -- Check if activity started successfully
918 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
919 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
920 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
921 RAISE OKL_API.G_EXCEPTION_ERROR;
922 END IF;
923
924 l_asb_rec := p_asb_rec;
925 -----------------------------------------------
926 --call subsidy calculation API
927 -----------------------------------------------
928 okl_subsidy_process_pvt.calculate_subsidy_amount
929 (
930 p_api_version => p_api_version,
931 p_init_msg_list => p_init_msg_list,
932 x_return_status => x_return_status,
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_subsidy_cle_id => l_asb_rec.subsidy_cle_id,
936 x_subsidy_amount => l_subsidy_amount);
937
938 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
941 RAISE OKL_API.G_EXCEPTION_ERROR;
942 END IF;
943
944 l_asb_rec.amount := l_subsidy_amount;
945
946 --START: cklee 09/29/2005
947 -----------------------------------------------------------------------
948 -- verify pool balance if this subsidy is reduced from the pool balance
949 -- check added for subsidy pools enhancement
950 -----------------------------------------------------------------------
951 -- 28-Sep-2005 cklee - Fixed bug#4634871 and bug#4634792 v3 |
952 /* lv_subsidy_amount := NVL(l_asb_rec.subsidy_override_amount,NVL(l_asb_rec.amount,0));
953 is_balance_valid_after_add (p_subsidy_id => l_asb_rec.subsidy_id
954 ,p_asset_id => l_asb_rec.asset_cle_id
955 ,p_subsidy_amount => lv_subsidy_amount
956 ,p_subsidy_name => l_asb_rec.name
957 ,x_return_status => x_return_status
958 ,x_msg_count => x_msg_count
959 ,x_msg_data => x_msg_data);
960 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
961 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
962 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
963 RAISE OKL_API.G_EXCEPTION_ERROR;
964 END IF;
965 */
966 --END: cklee 09/29/2005
967
968 ----------------------------------------------------------------------
969 --call api to update subsidy amount on subsidy line
970 ----------------------------------------------------------------------
971
972 l_sub_clev_rec.id := l_asb_rec.subsidy_cle_id;
973 l_sub_klev_rec.id := l_asb_rec.subsidy_cle_id;
974 l_sub_klev_rec.amount := l_subsidy_amount;
975
976 okl_contract_pub.update_contract_line
977 (p_api_version => p_api_version,
978 p_init_msg_list => p_init_msg_list,
979 x_return_status => x_return_status,
980 x_msg_count => x_msg_count,
981 x_msg_data => x_msg_data,
982 p_clev_rec => l_sub_clev_rec,
983 p_klev_rec => l_sub_klev_rec,
984 x_clev_rec => lx_sub_clev_rec,
985 x_klev_rec => lx_sub_klev_rec
986 );
987
988 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
989 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
991 RAISE OKL_API.G_EXCEPTION_ERROR;
992 END IF;
993
994 ---------------------------------------------------------
995 --Call API to recalculate asset oec and cap amounts
996 ----------------------------------------------------------
997 recalculate_costs(
998 p_api_version => p_api_version,
999 p_init_msg_list => p_init_msg_list,
1000 x_return_status => x_return_status,
1001 x_msg_count => x_msg_count,
1002 x_msg_data => x_msg_data,
1003 p_chr_id => lx_sub_clev_rec.dnz_chr_id,
1004 p_asset_cle_id => lx_sub_clev_rec.cle_id);
1005
1006 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1007 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1009 RAISE OKL_API.G_EXCEPTION_ERROR;
1010 END IF;
1011
1012 --START: cklee 09/29/2005
1013 -----------------------------------------------------------------------
1014 -- verify pool balance if this subsidy is reduced from the pool balance
1015 -- check added for subsidy pools enhancement
1016 -----------------------------------------------------------------------
1017 -- 28-Sep-2005 cklee - Fixed bug#4634871 and bug#4634792 v3 |
1018 -- move to here after the subsidy amount on subsidy line has been in this DB transaction
1019 -- so that the function can get the total amount up to now for a specific subsidy pool
1020 lv_subsidy_amount := NVL(l_asb_rec.subsidy_override_amount,NVL(l_asb_rec.amount,0));
1021 is_balance_valid_after_add (p_subsidy_id => l_asb_rec.subsidy_id
1022 ,p_asset_id => l_asb_rec.asset_cle_id
1023 ,p_subsidy_amount => lv_subsidy_amount
1024 ,p_subsidy_name => l_asb_rec.name
1025 ,x_return_status => x_return_status
1026 ,x_msg_count => x_msg_count
1027 ,x_msg_data => x_msg_data);
1028 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1029 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1030 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1031 RAISE OKL_API.G_EXCEPTION_ERROR;
1032 END IF;
1033 --END: cklee 09/29/2005
1034
1035
1036 x_asb_rec := l_asb_rec;
1037 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1038 EXCEPTION
1039 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1040 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1041 l_api_name,
1042 G_PKG_NAME,
1043 'OKL_API.G_RET_STS_ERROR',
1044 x_msg_count,
1045 x_msg_data,
1046 '_PVT');
1047 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1048 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1049 l_api_name,
1050 G_PKG_NAME,
1051 'OKL_API.G_RET_STS_UNEXP_ERROR',
1052 x_msg_count,
1053 x_msg_data,
1054 '_PVT');
1055 WHEN OTHERS THEN
1056 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1057 l_api_name,
1058 G_PKG_NAME,
1059 'OTHERS',
1060 x_msg_count,
1061 x_msg_data,
1062 '_PVT');
1063 End calculate_asset_subsidy;
1064 --------------------------------------------------------------------------------
1065 --Function to validate whether subsdy is applicable on an asset
1066 --------------------------------------------------------------------------------
1067 FUNCTION validate_subsidy_applicability(p_subsidy_id IN NUMBER
1068 ,p_asset_cle_id IN NUMBER
1069 ,p_qa_checker_call IN VARCHAR2 DEFAULT 'N') RETURN VARCHAR2 IS
1070 --cursor : to check applicability at contract header ORG_ID
1071 cursor l_chr_csr (p_subsidy_id in number,
1072 p_asset_cle_id in number) is
1073 Select 'Y'
1074 from okl_subsidies_b sub,
1075 okc_k_headers_b chrb,
1076 okc_k_lines_b cleb
1077 where sub.id = p_subsidy_id
1078 and chrb.id = cleb.chr_id
1079 and chrb.id = cleb.dnz_chr_id
1080 and cleb.id = p_asset_cle_id
1081 --check for authoring org id
1082 and chrb.authoring_org_id = sub.org_id
1083 --check for currency code
1084 and chrb.currency_code = sub.currency_code;
1085
1086
1087 --cursor : to check whether contract is release Kand subsidy is applicable
1088 -- on release
1089 cursor l_relk_csr (p_subsidy_id in number,
1090 p_asset_cle_id in number) is
1091 Select 'Y'
1092 from okl_subsidies_b sub,
1093 okc_k_headers_b chrb,
1094 okc_k_lines_b cleb
1095 where sub.id = p_subsidy_id
1096 and chrb.id = cleb.chr_id
1097 and chrb.id = cleb.dnz_chr_id
1098 and cleb.id = p_asset_cle_id
1099 and decode(chrb.orig_system_source_code,
1100 'OKL_RELEASE','Y',
1101 sub.APPLICABLE_TO_RELEASE_YN) = sub.APPLICABLE_TO_RELEASE_YN;
1102
1103 --cursor : to check whether contract is release Asset and subsidy is applicable
1104 -- on release
1105 --Bug# 15992711 : Query is modified to move the validation from contract header level
1106 -- to re-leased asset line level
1107
1108 cursor l_rela_csr (p_subsidy_id in number,
1109 p_asset_cle_id in number) is
1110 -- Bug# 15992711 : Commented Original query
1111 /*
1112 Select 'Y'
1113 from okl_subsidies_b sub,
1114 okc_rules_b rulb,
1115 okc_k_lines_b cleb
1116 where sub.id = p_subsidy_id
1117 and rulb.dnz_chr_id = cleb.chr_id
1118 and rulb.rule_information_category = 'LARLES'
1119 and cleb.id = p_asset_cle_id
1120 and decode(ltrim(rtrim(rulb.RULE_INFORMATION1,' '),' '),
1121 'Y','Y',
1122 sub.APPLICABLE_TO_RELEASE_YN) = sub.APPLICABLE_TO_RELEASE_YN
1123 union
1124 -- to take care of S and O where release yes-no flag is not applicable
1125 Select 'Y'
1126 from okl_subsidies_b sub,
1127 okc_k_lines_b cleb
1128 where sub.id = p_subsidy_id
1129 and cleb.id = p_asset_cle_id
1130 and not exists (select 1
1131 from okc_rules_b rulb
1132 where rulb.dnz_chr_id = cleb.chr_id
1133 and rulb.rule_information_category = 'LARLES');
1134 */
1135 Select 'Y'
1136 from okl_subsidies_b sub,
1137 okc_k_lines_b cleb,
1138 okl_k_lines kle_fin
1139 where sub.id = p_subsidy_id
1140 and kle_fin.id = cleb.id
1141 and cleb.id = p_asset_cle_id
1142 and decode(NVL(kle_fin.re_lease_yn,'N'),
1143 'Y','Y',
1144 sub.applicable_to_release_yn) = sub.applicable_to_release_yn;
1145
1146
1147 --Bug# 15992711 , End
1148
1149 --cursor : to check applicability at line dates
1150 cursor l_cle_csr (p_subsidy_id in number,
1151 p_asset_cle_id in number) is
1152 Select 'Y'
1153 from okl_subsidies_b sub,
1154 okc_k_lines_b cleb
1155 where sub.id = p_subsidy_id
1156 and cleb.id = p_asset_cle_id
1157 -- start: okl.h cklee
1158 -- and cleb.start_date between sub.effective_from_date
1159 -- and nvl(sub.effective_to_date,cleb.start_date);
1160 and TRUNC(cleb.start_date) between TRUNC(sub.effective_from_date)
1161 and TRUNC(nvl(sub.effective_to_date,cleb.start_date));
1162 -- end: okl.h cklee
1163
1164 --cursor : to check existence of criteria
1165 cursor l_suc_csr (p_subsidy_id in number) is
1166 select 'Y'
1167 from okl_subsidies_b sub
1168 where sub.id = p_subsidy_id
1169 and exists (select 1
1170 from okl_subsidy_criteria suc
1171 where suc.subsidy_id = sub.id);
1172
1173 --cursor : to check that inv check is required
1174 cursor l_invreq_csr (p_subsidy_id in number) is
1175 select 'Y'
1176 from okl_subsidies_b sub
1177 Where sub.id = p_subsidy_id
1178 and exists (select 1
1179 from okl_subsidy_criteria suc
1180 where suc.organization_id is not null
1181 and suc.subsidy_id = sub.id);
1182
1183 --cursor : check for inv item
1184 cursor l_invitm_csr (p_subsidy_id in number,
1185 p_asset_cle_id in number) is
1186 Select 'Y'
1187 From
1188 --inv item and org
1189 okc_k_lines_b cleb,
1190 okc_k_lines_b cle_model,
1191 okc_line_styles_b lse_model,
1192 okc_k_items cim_model,
1193 okl_subsidy_criteria suc
1194 where cim_model.cle_id = cle_model.id
1195 And cim_model.dnz_chr_id = cleb.dnz_chr_id
1196 And cle_model.cle_id = cleb.id
1197 And cle_model.dnz_chr_id = cleb.dnz_chr_id
1198 And cle_model.lse_id = lse_model.id
1199 And lse_model.lty_code = 'ITEM'
1200 And cleb.id = p_asset_cle_id
1201 And (to_char(suc.organization_id) = cim_model.object1_id2
1202 And to_char(nvl(suc.inventory_item_id,cim_model.object1_id1)) = cim_model.object1_id1
1203 )
1204 And suc.subsidy_id = p_subsidy_id
1205 And suc.organization_id is not null;
1206
1207 --cursor : to check that credit class check is required
1208 cursor l_clsreq_csr (p_subsidy_id in number) is
1209 select 'Y'
1210 from okl_subsidies_b sub
1211 Where sub.id = p_subsidy_id
1212 and exists (select 1
1213 from okl_subsidy_criteria suc
1214 where suc.credit_classification_code is not null
1215 -- start: okl.h cklee
1216 -- And suc.id = sub.id);
1217 And suc.subsidy_id = sub.id);
1218 -- end: okl.h cklee
1219
1220 --cursor to check cutomer credit class
1221 cursor l_cclass_csr (p_subsidy_id in number,
1222 p_asset_cle_id in number) is
1223 select 'Y'
1224 from okc_k_headers_b chrb,
1225 hz_cust_accounts cust,
1226 okc_k_lines_b cleb,
1227 okl_subsidy_criteria suc
1228 where chrb.id = cleb.chr_id
1229 And cleb.dnz_chr_id = chrb.id
1230 And cleb.id = p_asset_cle_id
1231 -- start: okl.h cklee
1232 -- And chrb.cust_acct_id = to_char(cust.cust_account_id)
1233 And chrb.cust_acct_id = cust.cust_account_id
1234 -- end: okl.h cklee
1235 And suc.subsidy_id = p_subsidy_id
1236 And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
1237 And SUC.CREDIT_CLASSIFICATION_CODE is not null;
1238
1239
1240 --cursor : to check that territory check is required
1241 cursor l_terrreq_csr (p_subsidy_id in number) is
1242 select 'Y'
1243 from okl_subsidies_b sub
1244 Where sub.id = p_subsidy_id
1245 and exists (select 1
1246 from okl_subsidy_criteria suc
1247 --cklee 03/16/2004
1248 -- where suc.SALES_TERRITORY_CODE is not null
1249 where suc.SALES_TERRITORY_ID is not null
1250 And suc.subsidy_id = sub.id);
1251
1252 --Bug# : 3320760
1253 --cursor to check territory
1254 /*
1255 cursor l_terr_csr (p_subsidy_id in number,
1256 p_asset_cle_id in number) is
1257 select 'Y'
1258 from hz_locations loc,
1259 hz_party_sites hzps,
1260 hz_party_site_uses hzpsu,
1261 okl_txl_itm_insts iti,
1262 --csi_item_instances csii,
1263 --okc_k_items cim_ib,
1264 okc_k_lines_b cle_ib,
1265 okc_line_styles_b lse_ib,
1266 okc_k_lines_b cle_inst,
1267 okc_line_styles_b lse_inst,
1268 OKC_K_LINES_B cleb,
1269 okl_subsidy_criteria suc
1270 Where cle_inst.cle_id = cleb.id
1271 And cle_inst.dnz_chr_id = cleb.dnz_chr_id
1272 And cle_inst.lse_id = lse_inst.id
1273 And lse_inst.lty_code = 'FREE_FORM2'--'FREE_FORM1' cklee 21-Jan-04 bug#3375789
1274 And cle_ib.cle_id = cle_inst.id
1275 And cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
1276 And lse_ib.id = cle_ib.lse_id
1277 And lse_ib.lty_code = 'INST_ITEM'
1278 And iti.kle_id = cle_ib.id
1279 And hzpsu.party_site_use_id = to_number(iti.object_id1_new)
1280 And hzps.party_site_id = hzpsu.party_site_id
1281 And loc.location_id = hzps.location_id
1282 --And cim_ib.cle_id = cle_ib.id
1283 --And cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
1284 --And cim_ib.object1_id1 = csii.instance_id
1285 --And loc.location_id = csii.location_id
1286 And SUC.SUBSIDY_ID = p_subsidy_id
1287 And SUC.SALES_TERRITORY_CODE = loc.country
1288 And SUC.SALES_TERRITORY_CODE is not null
1289 And cleb.id = p_asset_cle_id; -- 'FREE_FORM1'
1290 */
1291 -- Bug#3508166
1292 cursor l_terr_csr (p_subsidy_id in number,
1293 p_asset_cle_id in number) is
1294 select 'Y'
1295 from RA_SALESREP_TERRITORIES rst,
1296 OKC_CONTACTS cro,
1297 OKC_K_PARTY_ROLES_B cplb,
1298 OKC_K_LINES_B cleb,
1299 okl_subsidy_criteria suc
1300 Where
1301 rst.salesrep_id = cro.object1_id1
1302 And cro.object1_id2 = '#'
1303 And cro.jtot_object1_code = 'OKX_SALEPERS'
1304 And cro.cro_code = 'SALESPERSON'
1305 And cro.cpl_id = cplb.id
1306 And cro.dnz_chr_id = cplb.dnz_chr_id
1307 And cplb.chr_id = cleb.dnz_chr_id
1308 And cplb.dnz_chr_id = cleb.dnz_chr_id
1309 And cplb.rle_code = 'LESSOR'
1310 And SUC.SUBSIDY_ID = p_subsidy_id
1311 And SUC.SALES_TERRITORY_ID = rst.territory_id
1312 -- And SUC.SALES_TERRITORY_CODE is not null
1313 And cleb.id = p_asset_cle_id;
1314
1315
1316 --cursor : to check that product check is required
1317 cursor l_pdtreq_csr (p_subsidy_id in number) is
1318 select 'Y'
1319 from okl_subsidies_b sub
1320 Where sub.id = p_subsidy_id
1321 and exists (select 1
1322 from okl_subsidy_criteria suc
1323 where suc.PRODUCT_ID is not null
1324 And suc.subsidy_id = sub.id);
1325
1326 --cursor to check financial product
1327 cursor l_pdt_csr (p_subsidy_id in number,
1328 p_asset_cle_id in number) is
1329 select 'Y'
1330 from okl_k_headers khr,
1331 okc_k_lines_b cleb,
1332 okl_subsidy_criteria suc
1333 Where khr.id = cleb.chr_id
1334 And SUC.subsidy_id = p_subsidy_id
1335 And SUC.product_id = khr.pdt_id
1336 And SUC.product_id is not null
1337 And cleb.id = p_asset_cle_id;
1338
1339 --cursor : to check that sic_code check is required
1340 cursor l_sicreq_csr (p_subsidy_id in number) is
1341 select 'Y'
1342 from okl_subsidies_b sub
1343 Where sub.id = p_subsidy_id
1344 and exists (select 1
1345 from okl_subsidy_criteria suc
1346 where suc.INDUSTRY_CODE is not null
1347 And suc.INDUSTRY_CODE_TYPE is not null
1348 And suc.subsidy_id = sub.id);
1349
1350 --cursor to check service industry code
1351 cursor l_sic_csr (p_subsidy_id in number,
1352 p_asset_cle_id in number) is
1353 select 'Y'
1354 from hz_parties hp,
1355 hz_cust_accounts_all hca,
1356 okc_k_lines_b cleb,
1357 okc_k_headers_b chrb,
1358 okl_subsidy_criteria suc
1359 where hp.party_id = hca.party_id
1360 And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
1361 And chrb.id = cleb.chr_id
1362 And SUC.subsidy_id = p_subsidy_id
1363 And SUC.industry_code = hp.sic_code
1364 And SUC.industry_code_type = hp.sic_code_type
1365 And SUC.industry_code is not null
1366 And SUC.industry_code_type is not null
1367 And cleb.id = p_asset_cle_id;
1368
1369
1370 --cursor : to check that subsidy expiration
1371 cursor l_not_expire_csr (p_subsidy_id in number) is
1372 select 'Y'
1373 from okl_subsidies_b sub
1374 Where sub.id = p_subsidy_id
1375
1376 -- Start : Bug 6050165 : prasjain
1377 -- and TRUNC(nvl(sub.EFFECTIVE_TO_DATE,sysdate) + nvl(sub.EXPIRE_AFTER_DAYS,0)) >= TRUNC(sysdate);
1378 and TRUNC(sysdate) between TRUNC(sub.effective_from_date) and
1379 TRUNC(nvl(sub.EFFECTIVE_TO_DATE,sysdate) + nvl(sub.EXPIRE_AFTER_DAYS,0));
1380
1381 -- cursor to check if it is a rebook contract
1382 cursor l_chr_rebook (p_asset_cle_id in number) is
1383 Select 'Y'
1384 from okc_k_headers_b chrb,
1385 okc_k_lines_b cleb,
1386 okl_trx_contracts ktrx
1387 where chrb.id = cleb.chr_id
1388 and chrb.id = cleb.dnz_chr_id
1389 and chrb.orig_system_source_code = 'OKL_REBOOK'
1390 and cleb.id = p_asset_cle_id
1391 and ktrx.khr_id_new = chrb.id
1392 AND ktrx.tsu_code = 'ENTERED'
1393 AND ktrx.rbr_code is NOT NULL
1394 AND ktrx.tcn_type = 'TRBK'
1395 --rkuttiya added for 12.1.1. Multi GAAP Project
1396 AND ktrx.representation_type = 'PRIMARY';
1397 --
1398
1399 l_chr_rbk varchar2(1);
1400
1401 --cursor to check if it is a mass rebook
1402 cursor l_chr_mass_rebook (p_asset_cle_id in number) is
1403 Select 'Y'
1404 from okc_k_headers_b chrb,
1405 okc_k_lines_b cleb,
1406 okl_trx_contracts ktrx
1407 where chrb.id = cleb.chr_id
1408 and chrb.id = cleb.dnz_chr_id
1409 and cleb.id = p_asset_cle_id
1410 and ktrx.KHR_ID = chrb.id
1411 AND ktrx.tsu_code = 'ENTERED'
1412 AND ktrx.rbr_code is NOT NULL
1413 AND ktrx.tcn_type = 'TRBK'
1414 --rkuttiya added for 12.1.1 MUlti GAAP Project
1415 AND ktrx.representation_type = 'PRIMARY'
1416 --
1417 AND EXISTS (SELECT '1'
1418 FROM okl_rbk_selected_contract rbk_khr
1419 WHERE rbk_khr.KHR_ID = chrb.id
1420 AND rbk_khr.STATUS <> 'PROCESSED');
1421
1422 l_chr_mass_rbk varchar2(1);
1423
1424 --cursor to get split asset transactions
1425 CURSOR get_split_trn_csr (p_asset_cle_id IN NUMBER) IS
1426 SELECT 'Y'
1427 FROM OKL_TXL_ASSETS_B TXL,
1428 OKL_TRX_ASSETS TRX,
1429 OKC_K_LINES_B KLE_FIN,
1430 OKC_K_LINES_B KLE_FIX,
1431 OKC_LINE_STYLES_B LTY_FIN,
1432 OKC_LINE_STYLES_B LTY_FIX
1433 WHERE TXL.TAL_TYPE = 'ALI' -- identifies split transaction
1434 AND TRX.TSU_CODE = 'ENTERED' -- split transaction in progress
1435 AND TXL.TAS_ID = TRX.ID
1436 AND KLE_FIN.LSE_ID = LTY_FIN.ID
1437 AND LTY_FIN.LTY_CODE = 'FREE_FORM1'
1438 AND KLE_FIN.ID = KLE_FIX.CLE_ID
1439 AND KLE_FIX.LSE_ID = LTY_FIX.ID
1440 AND LTY_FIX.LTY_CODE = 'FIXED_ASSET'
1441 AND TXL.KLE_ID = KLE_FIX.ID
1442 AND ( KLE_FIN.ID = p_asset_cle_id -- original asset during split
1443 OR KLE_FIN.ID = (SELECT ORIG_SYSTEM_ID1
1444 FROM OKC_K_LINES_B CLE_TMP
1445 WHERE CLE_TMP.ID = p_asset_cle_id) -- new asset generated during split
1446 );
1447
1448 l_cle_split varchar2(1);
1449 -- End : Bug 6050165 : prasjain
1450
1451 l_chk_required varchar2(1);
1452 l_applicable varchar2(10);
1453
1454 halt_validation exception;
1455
1456 Begin
1457 --Checks on header line and existence of applicability criteria
1458
1459 ---------------------------------------------------------------------------
1460 --A.1. check subsidy expiration
1461 ---------------------------------------------------------------------------
1462 -- cklee 01/23/04
1463 l_applicable := 'N';
1464
1465 --Start : Bug 6050165 : prasjain
1466 l_chr_rbk := 'N';
1467 open l_chr_rebook( p_asset_cle_id => p_asset_cle_id );
1468 fetch l_chr_rebook into l_chr_rbk;
1469 close l_chr_rebook;
1470
1471 l_chr_mass_rbk := 'N';
1472 open l_chr_mass_rebook( p_asset_cle_id => p_asset_cle_id );
1473 fetch l_chr_mass_rebook into l_chr_mass_rbk;
1474 close l_chr_mass_rebook;
1475
1476 l_cle_split := 'N';
1477 open get_split_trn_csr(p_asset_cle_id => p_asset_cle_id);
1478 fetch get_split_trn_csr into l_cle_split;
1479 close get_split_trn_csr;
1480
1481 if(l_chr_rbk = 'N' AND l_chr_mass_rbk = 'N' AND l_cle_split = 'N') then
1482 --End : Bug 6050165 : prasjain
1483
1484 open l_not_expire_csr(p_subsidy_id => p_subsidy_id);
1485 Fetch l_not_expire_csr into l_applicable;
1486 If l_not_expire_csr%NOTFOUND then
1487 Null;
1488 End If;
1489 close l_not_expire_csr;
1490 If l_applicable = 'N' then
1491 Raise halt_validation;
1492 End If;
1493
1494 --Start : Bug 6050165 : prasjain
1495 end if;
1496 --End : Bug 6050165 : prasjain
1497
1498 ---------------------------------------------------------------------------
1499 --A. check whether subsidy can be applied to contract (org id match)
1500 ---------------------------------------------------------------------------
1501 l_applicable := 'N';
1502 open l_chr_csr(p_subsidy_id => p_subsidy_id,
1503 p_asset_cle_id => p_asset_cle_id);
1504 Fetch l_chr_csr into l_applicable;
1505 If l_chr_csr%NOTFOUND then
1506 Null;
1507 End If;
1508 close l_chr_csr;
1509 If l_applicable = 'N' then
1510 Raise halt_validation;
1511 End If;
1512
1513 ---------------------------------------------------------------------------
1514 --B. check whether subsidy can be applied to re-lease contract
1515 -- if contract is a release k
1516 ---------------------------------------------------------------------------
1517 l_applicable := 'N';
1518 open l_relk_csr(p_subsidy_id => p_subsidy_id,
1519 p_asset_cle_id => p_asset_cle_id);
1520 Fetch l_relk_csr into l_applicable;
1521 If l_relk_csr%NOTFOUND then
1522 Null;
1523 End If;
1524 close l_relk_csr;
1525 If l_applicable = 'N' then
1526 Raise halt_validation;
1527 End If;
1528
1529 ---------------------------------------------------------------------------
1530 --C. check whether subsidy can be applied to re-lease assets
1531 -- if contract is a release asset k
1532 ---------------------------------------------------------------------------
1533 --Bug# 15992711 : The re-leased flag is no longer checked at contract header
1534 -- level instead validation is moved to re-leased asset line
1535 -- level, Cursor (l_rela_csr) query is modified to incorporate
1536 -- the same
1537
1538 l_applicable := 'N';
1539 open l_rela_csr(p_subsidy_id => p_subsidy_id,
1540 p_asset_cle_id => p_asset_cle_id);
1541 Fetch l_rela_csr into l_applicable;
1542 If l_rela_csr%NOTFOUND then
1543 Null;
1544 End If;
1545 close l_rela_csr;
1546 If l_applicable = 'N' then
1547 Raise halt_validation;
1548 End If;
1549
1550 ---------------------------------------------------------------------------
1551 --D. check whether subsidy can be applied to line (dates match)
1552 ---------------------------------------------------------------------------
1553 l_applicable := 'N';
1554 open l_cle_csr(p_subsidy_id => p_subsidy_id,
1555 p_asset_cle_id => p_asset_cle_id);
1556 Fetch l_cle_csr into l_applicable;
1557 If l_cle_csr%NOTFOUND then
1558 Null;
1559 End If;
1560 close l_cle_csr;
1561 If l_applicable = 'N' then
1562 Raise halt_validation;
1563 End If;
1564
1565 -- sjalasut added code to validate subsidy pool applicability as part of the subsidy pools enhancement. START
1566 -- for lease authoring the params p_ast_date_sq nad p_trx_curr_code_sq are passed as null always
1567 -- note that the param p_qa_checker_call is set to Y only when called from QA checker process
1568 -- for all the cases this param is not passed and defaulted as 'N'
1569 -- when called from the QA checker, the subsidypool applicability is not checked.
1570 -- the QA checker explicitly checks for the pool applicability. therefore the code is skipped in such case
1571 IF(p_qa_checker_call = 'N')THEN
1572 l_applicable := validate_subsidy_pool_applic(p_subsidy_id => p_subsidy_id
1573 ,p_asset_cle_id => p_asset_cle_id
1574 ,p_ast_date_sq => NULL -- always NULL for a Contract
1575 ,p_trx_curr_code_sq => NULL -- always NULL for a Contract
1576 );
1577 IF(l_applicable = 'NA')THEN
1578 l_applicable := 'Y';
1579 ELSIF l_applicable = 'N' THEN
1580 l_applicable := 'N';
1581 RAISE halt_validation;
1582 END IF;
1583 END IF;
1584 -- sjalasut added code to validate subsidy pool applicability as part of the subsidy pools enhancement. END
1585
1586
1587 ---------------------------------------------------------------------------
1588 --E. check whether any applicability criteria defined
1589 ---------------------------------------------------------------------------
1590 l_chk_required := 'N';
1591 open l_suc_csr(p_subsidy_id => p_subsidy_id);
1592 Fetch l_suc_csr into l_chk_required;
1593 If l_suc_csr%NOTFOUND then
1594 Null;
1595 End If;
1596 close l_suc_csr;
1597
1598 If l_chk_required = 'N' then
1599 l_applicable := 'Y';
1600 Raise halt_validation;
1601 End If;
1602
1603 --check applicability criterias
1604 l_applicable := 'Y';
1605 ----------------------------------------------------------------------------
1606 --1. check for inventory item and ORG
1607 ----------------------------------------------------------------------------
1608 l_chk_required := 'N';
1609 open l_invreq_csr(p_subsidy_id => p_subsidy_id);
1610 Fetch l_invreq_csr into l_chk_required;
1611 If l_invreq_csr%NOTFOUND then
1612 Null;
1613 End If;
1614 close l_invreq_csr;
1615
1616 If l_chk_required = 'Y' then
1617 --check for inv item and org
1618 l_applicable := 'N';
1619 open l_invitm_csr(p_subsidy_id => p_subsidy_id,
1620 p_asset_cle_id => p_asset_cle_id);
1621 --Bug# 3290648:
1622 fetch l_invitm_csr into l_applicable;
1623 If l_invitm_csr%NOTFOUND then
1624 Null;
1625 End If;
1626 close l_invitm_csr;
1627 If l_applicable = 'N' then
1628 Raise halt_validation;
1629 End If;
1630 End If;
1631
1632 ----------------------------------------------------------------------------
1633 --2. check for credit class
1634 ----------------------------------------------------------------------------
1635 l_chk_required := 'N';
1636 open l_clsreq_csr(p_subsidy_id => p_subsidy_id);
1637 Fetch l_clsreq_csr into l_chk_required;
1638 If l_clsreq_csr%NOTFOUND then
1639 Null;
1640 End If;
1641 close l_clsreq_csr;
1642
1643 If l_chk_required = 'Y' then
1644 --check for inv item and org
1645 l_applicable := 'N';
1646 open l_cclass_csr(p_subsidy_id => p_subsidy_id,
1647 p_asset_cle_id => p_asset_cle_id);
1648 --Bug# 3290648:
1649 fetch l_cclass_csr into l_applicable;
1650 If l_cclass_csr%NOTFOUND then
1651 Null;
1652 End If;
1653 close l_cclass_csr;
1654 If l_applicable = 'N' then
1655 Raise halt_validation;
1656 End If;
1657 End If;
1658
1659 ----------------------------------------------------------------------------
1660 --3. check for territory
1661 ----------------------------------------------------------------------------
1662 l_chk_required := 'N';
1663 open l_terrreq_csr(p_subsidy_id => p_subsidy_id);
1664 Fetch l_terrreq_csr into l_chk_required;
1665 If l_terrreq_csr%NOTFOUND then
1666 Null;
1667 End If;
1668 close l_terrreq_csr;
1669
1670 If l_chk_required = 'Y' then
1671 --check for inv item and org
1672 l_applicable := 'N';
1673 open l_terr_csr(p_subsidy_id => p_subsidy_id,
1674 p_asset_cle_id => p_asset_cle_id);
1675 --Bug# 3290648:
1676 fetch l_terr_csr into l_applicable;
1677 If l_terr_csr%NOTFOUND then
1678 Null;
1679 End If;
1680 close l_terr_csr;
1681 If l_applicable = 'N' then
1682 Raise halt_validation;
1683 End If;
1684 End If;
1685
1686 ----------------------------------------------------------------------------
1687 --4. check for product
1688 ----------------------------------------------------------------------------
1689 l_chk_required := 'N';
1690 open l_pdtreq_csr(p_subsidy_id => p_subsidy_id);
1691 Fetch l_pdtreq_csr into l_chk_required;
1692 If l_pdtreq_csr%NOTFOUND then
1693 Null;
1694 End If;
1695 close l_pdtreq_csr;
1696
1697 If l_chk_required = 'Y' then
1698 --check for inv item and org
1699 l_applicable := 'N';
1700 open l_pdt_csr(p_subsidy_id => p_subsidy_id,
1701 p_asset_cle_id => p_asset_cle_id);
1702 --Bug# 3290648:
1703 fetch l_pdt_csr into l_applicable;
1704 If l_pdt_csr%NOTFOUND then
1705 Null;
1706 End If;
1707 close l_pdt_csr;
1708 If l_applicable = 'N' then
1709 Raise halt_validation;
1710 End If;
1711 End If;
1712
1713 ----------------------------------------------------------------------------
1714 --5. check for SIC code
1715 ----------------------------------------------------------------------------
1716 l_chk_required := 'N';
1717 open l_sicreq_csr(p_subsidy_id => p_subsidy_id);
1718 Fetch l_sicreq_csr into l_chk_required;
1719 If l_sicreq_csr%NOTFOUND then
1720 Null;
1721 End If;
1722 close l_sicreq_csr;
1723
1724 If l_chk_required = 'Y' then
1725 --check for inv item and org
1726 l_applicable := 'N';
1727 open l_sic_csr(p_subsidy_id => p_subsidy_id,
1728 p_asset_cle_id => p_asset_cle_id);
1729 --Bug# 3290648:
1730 fetch l_sic_csr into l_applicable;
1731 If l_sic_csr%NOTFOUND then
1732 Null;
1733 End If;
1734 close l_sic_csr;
1735 If l_applicable = 'N' then
1736 Raise halt_validation;
1737 End If;
1738 End If;
1739
1740 Return(l_applicable);
1741 Exception
1742 When halt_validation then
1743 Return(l_applicable);
1744 When others then
1745 l_applicable := 'N';
1746 Return(l_applicable);
1747 End validate_subsidy_applicability;
1748 --Bug# 3320760 :
1749 ------------------------------------------------------------------------------
1750 --Function to validate whether subsdy is applicable on an asset overloaded for SO
1751 --------------------------------------------------------------------------------
1752 Function validate_subsidy_applicability(p_subsidy_id IN NUMBER,
1753 p_chr_id IN NUMBER,
1754 p_start_date IN DATE,
1755 p_inv_item_id IN NUMBER,
1756 p_inv_org_id IN NUMBER,
1757 p_install_site_use_id IN NUMBER
1758 ) Return Varchar2 is
1759
1760 --cursor : to check applicability at contract header ORG_ID
1761 cursor l_chr_csr (p_subsidy_id in number,
1762 p_chr_id in number) is
1763 Select 'Y'
1764 from okl_subsidies_b sub,
1765 okc_k_headers_b chrb
1766 where sub.id = p_subsidy_id
1767 and chrb.id = p_chr_id
1768 --check for authoring org id
1769 and chrb.authoring_org_id = sub.org_id
1770 --check for currency code
1771 and chrb.currency_code = sub.currency_code;
1772
1773
1774 --cursor : to check applicability at line dates
1775 cursor l_cle_csr (p_subsidy_id in number,
1776 p_start_date in date
1777 ) is
1778 Select 'Y'
1779 from okl_subsidies_b sub
1780 where sub.id = p_subsidy_id
1781 and p_start_date between sub.effective_from_date
1782 and nvl(sub.effective_to_date,p_start_date);
1783
1784 --cursor : to check existence of criteria
1785 cursor l_suc_csr (p_subsidy_id in number) is
1786 select 'Y'
1787 from okl_subsidies_b sub
1788 where sub.id = p_subsidy_id
1789 and exists (select 1
1790 from okl_subsidy_criteria suc
1791 where suc.subsidy_id = sub.id);
1792
1793 --cursor : to check that inv check is required
1794 cursor l_invreq_csr (p_subsidy_id in number) is
1795 select 'Y'
1796 from okl_subsidies_b sub
1797 Where sub.id = p_subsidy_id
1798 and exists (select 1
1799 from okl_subsidy_criteria suc
1800 where suc.organization_id is not null
1801 and suc.subsidy_id = sub.id);
1802
1803 --cursor : check for inv item
1804 cursor l_invitm_csr (p_subsidy_id in number,
1805 p_inv_item_id in number,
1806 p_inv_org_id in number) is
1807 Select 'Y'
1808 From
1809 --inv item and org
1810 okl_subsidy_criteria suc
1811 where (suc.organization_id = p_inv_org_id
1812 And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
1813 )
1814 And suc.subsidy_id = p_subsidy_id
1815 And suc.organization_id is not null;
1816
1817 --cursor : to check that credit class check is required
1818 cursor l_clsreq_csr (p_subsidy_id in number) is
1819 select 'Y'
1820 from okl_subsidies_b sub
1821 Where sub.id = p_subsidy_id
1822 and exists (select 1
1823 from okl_subsidy_criteria suc
1824 where suc.credit_classification_code is not null
1825 -- start: okl.h cklee
1826 -- And suc.id = sub.id);
1827 And suc.subsidy_id = sub.id);
1828 -- end: okl.h cklee
1829
1830 --cursor to check cutomer credit class
1831 -- nikshah -- Bug # 5484903 Fixed,
1832 -- Changed cursor l_cclass_csr (p_subsidy_id in number, p_chr_id in number) SQL definition
1833 cursor l_cclass_csr (p_subsidy_id in number,
1834 p_chr_id in number) is
1835 select 'Y'
1836 from okc_k_headers_all_b chrb,
1837 hz_cust_accounts cust,
1838 okl_subsidy_criteria suc
1839 where chrb.id = p_chr_id
1840 And chrb.cust_acct_id = cust.cust_account_id
1841 And suc.subsidy_id = p_subsidy_id
1842 And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
1843 And SUC.CREDIT_CLASSIFICATION_CODE is not null;
1844
1845
1846 --cursor : to check that territory check is required
1847 cursor l_terrreq_csr (p_subsidy_id in number) is
1848 select 'Y'
1849 from okl_subsidies_b sub
1850 Where sub.id = p_subsidy_id
1851 and exists (select 1
1852 from okl_subsidy_criteria suc
1853 where suc.SALES_TERRITORY_CODE is not null
1854 And suc.subsidy_id = sub.id);
1855
1856 --cursor to check territory
1857 cursor l_terr_csr (p_subsidy_id in number,
1858 p_install_site_use_id in number) is
1859 select 'Y'
1860 from hz_locations loc,
1861 hz_party_sites hzps,
1862 hz_party_site_uses hzpsu,
1863 okl_subsidy_criteria suc
1864 Where hzpsu.party_site_use_id = p_install_site_use_id
1865 And hzps.party_site_id = hzpsu.party_site_id
1866 And loc.location_id = hzps.location_id
1867 And SUC.SUBSIDY_ID = p_subsidy_id
1868 And SUC.SALES_TERRITORY_CODE = loc.country
1869 And SUC.SALES_TERRITORY_CODE is not null;
1870
1871 --cursor : to check that product check is required
1872 cursor l_pdtreq_csr (p_subsidy_id in number) is
1873 select 'Y'
1874 from okl_subsidies_b sub
1875 Where sub.id = p_subsidy_id
1876 and exists (select 1
1877 from okl_subsidy_criteria suc
1878 where suc.PRODUCT_ID is not null
1879 And suc.subsidy_id = sub.id);
1880
1881 --cursor to check financial product
1882 cursor l_pdt_csr (p_subsidy_id in number,
1883 p_chr_id in number) is
1884 select 'Y'
1885 from okl_k_headers khr,
1886 okl_subsidy_criteria suc
1887 Where khr.id = p_chr_id
1888 And SUC.subsidy_id = p_subsidy_id
1889 And SUC.product_id = khr.pdt_id
1890 And SUC.product_id is not null;
1891
1892 --cursor : to check that sic_code check is required
1893 cursor l_sicreq_csr (p_subsidy_id in number) is
1894 select 'Y'
1895 from okl_subsidies_b sub
1896 Where sub.id = p_subsidy_id
1897 and exists (select 1
1898 from okl_subsidy_criteria suc
1899 where suc.INDUSTRY_CODE is not null
1900 And suc.INDUSTRY_CODE_TYPE is not null
1901 And suc.subsidy_id = sub.id);
1902
1903 --cursor to check service industry code
1904 cursor l_sic_csr (p_subsidy_id in number,
1905 p_chr_id in number) is
1906 select 'Y'
1907 from hz_parties hp,
1908 hz_cust_accounts_all hca,
1909 okc_k_headers_b chrb,
1910 okl_subsidy_criteria suc
1911 where hp.party_id = hca.party_id
1912 And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
1913 And chrb.id = p_chr_id
1914 And SUC.subsidy_id = p_subsidy_id
1915 And SUC.industry_code = hp.sic_code
1916 And SUC.industry_code_type = hp.sic_code_type
1917 And SUC.industry_code is not null
1918 And SUC.industry_code_type is not null;
1919
1920 CURSOR c_get_trx_csr IS
1921 SELECT currency_code
1922 FROM okc_k_headers_b
1923 WHERE id = p_chr_id;
1924 l_trx_currency_code okc_k_headers_b.currency_code%TYPE;
1925
1926 l_chk_required varchar2(1);
1927 l_applicable varchar2(10);
1928
1929 lx_conversion_rate NUMBER;
1930 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
1931 lx_subsidy_pool_status okl_subsidy_pools_b.decision_status_code%TYPE;
1932 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
1933 lx_sub_pool_balance NUMBER;
1934
1935 halt_validation exception;
1936
1937 Begin
1938 --Checks on header line and existence of applicability criteria
1939 ---------------------------------------------------------------------------
1940 --A. check whether subsidy can be applied to contract (org id match)
1941 ---------------------------------------------------------------------------
1942 l_applicable := 'N';
1943 open l_chr_csr(p_subsidy_id => p_subsidy_id,
1944 p_chr_id => p_chr_id);
1945 Fetch l_chr_csr into l_applicable;
1946 If l_chr_csr%NOTFOUND then
1947 Null;
1948 End If;
1949 close l_chr_csr;
1950 If l_applicable = 'N' then
1951 Raise halt_validation;
1952 End If;
1953
1954 ---------------------------------------------------------------------------
1955 --D. check whether subsidy can be applied to line (dates match)
1956 ---------------------------------------------------------------------------
1957 l_applicable := 'N';
1958 open l_cle_csr(p_subsidy_id => p_subsidy_id,
1959 p_start_date => p_start_date);
1960 Fetch l_cle_csr into l_applicable;
1961 If l_cle_csr%NOTFOUND then
1962 Null;
1963 End If;
1964 close l_cle_csr;
1965 If l_applicable = 'N' then
1966 Raise halt_validation;
1967 End If;
1968
1969 --START: 24-Oct-2005 cklee - Fixed bug#4865580 |
1970 /**
1971 * sjalasut, added validations as part of subsidy pools enhancement. START
1972 * for sales quote, the parameter p_asset_cle_id is passed as NULL
1973 * the asset start date and the contract currency code must be passed in case of sales quote
1974 */
1975 OPEN c_get_trx_csr; FETCH c_get_trx_csr INTO l_trx_currency_code;
1976 CLOSE c_get_trx_csr;
1977 l_applicable := validate_subsidy_pool_applic(p_subsidy_id => p_subsidy_id
1978 ,p_asset_cle_id => null
1979 ,p_ast_date_sq => p_start_date
1980 ,p_trx_curr_code_sq => l_trx_currency_code
1981 );
1982 IF(l_applicable = 'NA')THEN
1983 l_applicable := 'Y';
1984 ELSIF l_applicable = 'N' THEN
1985 Raise halt_validation;
1986 END IF;
1987 /**
1988 * sjalasut, added validations as part of subsidy pools enhancement. END
1989 */
1990
1991 --END: 24-Oct-2005 cklee - Fixed bug#4865580 |
1992
1993 ---------------------------------------------------------------------------
1994 --E. check whether any applicability criteria defined
1995 ---------------------------------------------------------------------------
1996 l_chk_required := 'N';
1997 open l_suc_csr(p_subsidy_id => p_subsidy_id);
1998 Fetch l_suc_csr into l_chk_required;
1999 If l_suc_csr%NOTFOUND then
2000 Null;
2001 End If;
2002 close l_suc_csr;
2003
2004 If l_chk_required = 'N' then
2005 l_applicable := 'Y';
2006 Raise halt_validation;
2007 End If;
2008
2009 --check applicability criterias
2010 l_applicable := 'Y';
2011 ----------------------------------------------------------------------------
2012 --1. check for inventory item and ORG
2013 ----------------------------------------------------------------------------
2014 l_chk_required := 'N';
2015 open l_invreq_csr(p_subsidy_id => p_subsidy_id);
2016 Fetch l_invreq_csr into l_chk_required;
2017 If l_invreq_csr%NOTFOUND then
2018 Null;
2019 End If;
2020 close l_invreq_csr;
2021
2022 If l_chk_required = 'Y' then
2023 --check for inv item and org
2024 l_applicable := 'N';
2025 open l_invitm_csr(p_subsidy_id => p_subsidy_id,
2026 p_inv_item_id => p_inv_item_id,
2027 p_inv_org_id => p_inv_org_id);
2028 --Bug# 3290648:
2029 fetch l_invitm_csr into l_applicable;
2030 If l_invitm_csr%NOTFOUND then
2031 Null;
2032 End If;
2033 close l_invitm_csr;
2034 If l_applicable = 'N' then
2035 Raise halt_validation;
2036 End If;
2037 End If;
2038
2039 ----------------------------------------------------------------------------
2040 --2. check for credit class
2041 ----------------------------------------------------------------------------
2042 l_chk_required := 'N';
2043 open l_clsreq_csr(p_subsidy_id => p_subsidy_id);
2044 Fetch l_clsreq_csr into l_chk_required;
2045 If l_clsreq_csr%NOTFOUND then
2046 Null;
2047 End If;
2048 close l_clsreq_csr;
2049
2050 If l_chk_required = 'Y' then
2051 --check for inv item and org
2052 l_applicable := 'N';
2053 open l_cclass_csr(p_subsidy_id => p_subsidy_id,
2054 p_chr_id => p_chr_id);
2055 --Bug# 3290648:
2056 fetch l_cclass_csr into l_applicable;
2057 If l_cclass_csr%NOTFOUND then
2058 Null;
2059 End If;
2060 close l_cclass_csr;
2061 If l_applicable = 'N' then
2062 Raise halt_validation;
2063 End If;
2064 End If;
2065
2066 ----------------------------------------------------------------------------
2067 --3. check for territory
2068 ----------------------------------------------------------------------------
2069 /*comment out for bug##3508166: cklee 03/16/2004
2070 l_chk_required := 'N';
2071 open l_terrreq_csr(p_subsidy_id => p_subsidy_id);
2072 Fetch l_terrreq_csr into l_chk_required;
2073 If l_terrreq_csr%NOTFOUND then
2074 Null;
2075 End If;
2076 close l_terrreq_csr;
2077
2078 If l_chk_required = 'Y' then
2079 --check for inv item and org
2080 l_applicable := 'N';
2081 open l_terr_csr(p_subsidy_id => p_subsidy_id,
2082 p_install_site_use_id => p_install_site_use_id);
2083 --Bug# 3290648:
2084 fetch l_terr_csr into l_applicable;
2085 If l_terr_csr%NOTFOUND then
2086 Null;
2087 End If;
2088 close l_terr_csr;
2089 If l_applicable = 'N' then
2090 Raise halt_validation;
2091 End If;
2092 End If;
2093 */
2094 ----------------------------------------------------------------------------
2095 --4. check for product
2096 ----------------------------------------------------------------------------
2097 l_chk_required := 'N';
2098 open l_pdtreq_csr(p_subsidy_id => p_subsidy_id);
2099 Fetch l_pdtreq_csr into l_chk_required;
2100 If l_pdtreq_csr%NOTFOUND then
2101 Null;
2102 End If;
2103 close l_pdtreq_csr;
2104
2105 If l_chk_required = 'Y' then
2106 --check for inv item and org
2107 l_applicable := 'N';
2108 open l_pdt_csr(p_subsidy_id => p_subsidy_id,
2109 p_chr_id => p_chr_id);
2110 --Bug# 3290648:
2111 fetch l_pdt_csr into l_applicable;
2112 If l_pdt_csr%NOTFOUND then
2113 Null;
2114 End If;
2115 close l_pdt_csr;
2116 If l_applicable = 'N' then
2117 Raise halt_validation;
2118 End If;
2119 End If;
2120
2121 ----------------------------------------------------------------------------
2122 --5. check for SIC code
2123 ----------------------------------------------------------------------------
2124 l_chk_required := 'N';
2125 open l_sicreq_csr(p_subsidy_id => p_subsidy_id);
2126 Fetch l_sicreq_csr into l_chk_required;
2127 If l_sicreq_csr%NOTFOUND then
2128 Null;
2129 End If;
2130 close l_sicreq_csr;
2131
2132 If l_chk_required = 'Y' then
2133 --check for inv item and org
2134 l_applicable := 'N';
2135 open l_sic_csr(p_subsidy_id => p_subsidy_id,
2136 p_chr_id => p_chr_id);
2137 --Bug# 3290648:
2138 fetch l_sic_csr into l_applicable;
2139 If l_sic_csr%NOTFOUND then
2140 Null;
2141 End If;
2142 close l_sic_csr;
2143 If l_applicable = 'N' then
2144 Raise halt_validation;
2145 End If;
2146 End If;
2147
2148 /**
2149 * sjalasut, added validations as part of subsidy pools enhancement. START
2150 * for sales quote, the parameter p_asset_cle_id is passed as NULL
2151 * the asset start date and the contract currency code must be passed in case of sales quote
2152 */
2153 --START: 24-Oct-2005 cklee - Fixed bug#4865580
2154 --Commented for the following and move to above check criteria exists |
2155 -- OPEN c_get_trx_csr; FETCH c_get_trx_csr INTO l_trx_currency_code;
2156 -- CLOSE c_get_trx_csr;
2157 -- l_applicable := validate_subsidy_pool_applic(p_subsidy_id => p_subsidy_id
2158 -- ,p_asset_cle_id => null
2159 -- ,p_ast_date_sq => p_start_date
2160 -- ,p_trx_curr_code_sq => l_trx_currency_code
2161 -- );
2162 -- IF(l_applicable = 'NA')THEN
2163 -- l_applicable := 'Y';
2164 -- ELSIF l_applicable = 'N' THEN
2165 -- l_applicable := 'N';
2166 -- END IF;
2167 --END: 24-Oct-2005 cklee - Fixed bug#4865580 |
2168 /**
2169 * sjalasut, added validations as part of subsidy pools enhancement. END
2170 */
2171
2172 Return(l_applicable);
2173 Exception
2174 When halt_validation then
2175 Return(l_applicable);
2176 When others then
2177 l_applicable := 'N';
2178 Return(l_applicable);
2179 End validate_subsidy_applicability;
2180 --End Bug# 3320760
2181
2182 -- start 29-June-2005 cklee - okl.h Sales Quote IA Subsidies
2183 -------------------------------------------------------------------------------
2184 -- FUNCTION validate_subsidy_applicability
2185 -------------------------------------------------------------------------------
2186 -- Start of comments
2187 --
2188 -- Function Name : validate_subsidy_applicability
2189 -- Description : function returns Y if the subsidy is applicable for the
2190 -- : passed in Sales Quote/Lease Application asset
2191 -- : N otherwise
2192 --
2193 -- Parameters : requires parameters:
2194 -- p_subsidy_id : Subsidy ID
2195 -- p_start_date : Sales Quote/Lease App's asset start date
2196 -- p_inv_item_id : Inventory Item ID
2197 --obsolete p_install_site_use_id: Install Site use ID
2198 -- p_currency_code : Sales Quote/Lease App's currency code
2199 -- p_authoring_org_id : Sales Quote/Lease App's operating unit ID
2200 -- p_cust_account_id : Sales Quote/Lease App's customer account ID
2201 -- p_pdt_id : Financial product ID
2202 -- p_sales_rep_id : Sales Representative ID
2203 --
2204 -- p_tot_subsidy_amount : The total asset subsidy amount for the Quote/Lease
2205 -- application up to the validation point.
2206 --
2207 -- For example,
2208 -- Quote has 3 assets with subsidy
2209 -- Asset1, sub1, $1,000 : p_tot_subsidy_amount = $1,000
2210 -- Asset2, sub1, $1,000 : p_tot_subsidy_amount = $2,000
2211 -- Asset3, sub1, $1,000 : p_tot_subsidy_amount = $3,000
2212 --
2213 -- API will check if the accumulated subsidy amount exceed
2214 -- the pool balance.
2215 -- p_subsidy_amount : Calculated subsidy amount based on Quote/Lease
2216 -- application system. API will also check if
2217 -- subsidy amount exceed the balance of the pool
2218 -- p_filter_flag : Y/N to indicate if used for LOV filterring
2219 -- p_dnz_asset_number : Quote/Lease app asset number used for error message
2220 --
2221 -- Validation rules:
2222 -- System will not have FK check for the passed in parameters.
2223 -- Instead, system will check the applicability between the passed
2224 -- in parametrs and the details criteria for the passed in
2225 -- Subsidy.
2226 --
2227 -- Version : 1.0
2228 -- History : 29-June-2005 cklee created
2229 -- End of comments
2230 Function validate_subsidy_applicability(p_subsidy_id IN NUMBER,
2231 p_start_date IN DATE,
2232 p_inv_item_id IN NUMBER,
2233 p_inv_org_id IN NUMBER,
2234 --obsolete p_install_site_use_id IN NUMBER,
2235 p_currency_code IN VARCHAR2,
2236 p_authoring_org_id IN NUMBER,
2237 p_cust_account_id IN NUMBER,
2238 p_pdt_id IN NUMBER,
2239 p_sales_rep_id IN NUMBER,
2240 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2241 p_tot_subsidy_amount IN NUMBER,
2242 p_subsidy_amount IN NUMBER,
2243 p_filter_flag IN VARCHAR2,
2244 p_dnz_asset_number IN VARCHAR2
2245 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2246 ) Return Varchar2 is
2247
2248 -- start: okl.h cklee
2249 /* --cursor : to check applicability at contract header ORG_ID
2250 cursor l_chr_csr (p_subsidy_id in number,
2251 p_chr_id in number) is
2252 Select 'Y'
2253 from okl_subsidies_b sub,
2254 okc_k_headers_b chrb
2255 where sub.id = p_subsidy_id
2256 and chrb.id = p_chr_id
2257 --check for authoring org id
2258 and chrb.authoring_org_id = sub.org_id
2259 --check for currency code
2260 and chrb.currency_code = sub.currency_code;
2261 */
2262 --cursor : to check applicability for the Sales Quote/Lease App ORG_ID, currency code
2263 cursor l_chr_csr (p_subsidy_id in number,
2264 p_authoring_org_id in number,
2265 p_currency_code in varchar2) is
2266 Select 'Y'
2267 from okl_subsidies_b sub
2268 where sub.id = p_subsidy_id
2269 --check for authoring org id
2270 and sub.org_id = p_authoring_org_id
2271 --check for currency code
2272 and sub.currency_code = p_currency_code;
2273 -- end: okl.h cklee
2274
2275
2276 --cursor : to check applicability at line dates
2277 cursor l_cle_csr (p_subsidy_id in number,
2278 p_start_date in date
2279 ) is
2280 Select 'Y'
2281 from okl_subsidies_b sub
2282 where sub.id = p_subsidy_id
2283 -- start: okl.h cklee
2284 -- and p_start_date between sub.effective_from_date
2285 -- and nvl(sub.effective_to_date,p_start_date);
2286 and TRUNC(p_start_date) between TRUNC(sub.effective_from_date)
2287 and TRUNC(nvl(sub.effective_to_date,p_start_date));
2288 -- end: okl.h cklee
2289
2290 --cursor : to check existence of criteria
2291 cursor l_suc_csr (p_subsidy_id in number) is
2292 select 'Y'
2293 from okl_subsidies_b sub
2294 where sub.id = p_subsidy_id
2295 and exists (select 1
2296 from okl_subsidy_criteria suc
2297 where suc.subsidy_id = sub.id);
2298
2299 --cursor : to check that inv check is required
2300 cursor l_invreq_csr (p_subsidy_id in number) is
2301 select 'Y'
2302 from okl_subsidies_b sub
2303 Where sub.id = p_subsidy_id
2304 and exists (select 1
2305 from okl_subsidy_criteria suc
2306 where suc.organization_id is not null
2307 and suc.subsidy_id = sub.id);
2308
2309 --cursor : check for inv item
2310 cursor l_invitm_csr (p_subsidy_id in number,
2311 p_inv_item_id in number,
2312 p_inv_org_id in number) is
2313 Select 'Y'
2314 From
2315 --inv item and org
2316 okl_subsidy_criteria suc
2317 where (suc.organization_id = p_inv_org_id
2318 And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
2319 )
2320 And suc.subsidy_id = p_subsidy_id
2321 And suc.organization_id is not null;
2322
2323 --cursor : to check that credit class check is required
2324 cursor l_clsreq_csr (p_subsidy_id in number) is
2325 select 'Y'
2326 from okl_subsidies_b sub
2327 Where sub.id = p_subsidy_id
2328 and exists (select 1
2329 from okl_subsidy_criteria suc
2330 where suc.credit_classification_code is not null
2331 -- start: okl.h cklee
2332 -- And suc.id = sub.id);
2333 And suc.subsidy_id = sub.id);
2334 -- end: okl.h cklee
2335
2336 -- start: okl.h cklee
2337 --cursor to check cutomer credit class
2338 /* cursor l_cclass_csr (p_subsidy_id in number,
2339 p_chr_id in number) is
2340 select 'Y'
2341 from okc_k_headers_b chrb,
2342 hz_cust_accounts cust,
2343 okl_subsidy_criteria suc
2344 where chrb.id = p_chr_id
2345 And chrb.cust_acct_id = to_char(cust.cust_account_id)
2346 And suc.subsidy_id = p_subsidy_id
2347 And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
2348 And SUC.CREDIT_CLASSIFICATION_CODE is not null;
2349 */
2350 --cursor to check cutomer credit class
2351 cursor l_cclass_csr (p_subsidy_id in number,
2352 p_cust_account_id in number) is
2353 select 'Y'
2354 from hz_cust_accounts cust,
2355 okl_subsidy_criteria suc
2356 where cust.cust_account_id = p_cust_account_id
2357 And suc.subsidy_id = p_subsidy_id
2358 And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
2359 And SUC.CREDIT_CLASSIFICATION_CODE is not null;
2360 -- end: okl.h cklee
2361
2362
2363 --cursor : to check that territory check is required
2364 cursor l_terrreq_csr (p_subsidy_id in number) is
2365 select 'Y'
2366 from okl_subsidies_b sub
2367 Where sub.id = p_subsidy_id
2368 and exists (select 1
2369 from okl_subsidy_criteria suc
2370 where suc.SALES_TERRITORY_ID is not null
2371 And suc.subsidy_id = sub.id);
2372
2373 -- start: okl.h cklee
2374 --cursor to check territory
2375 /* cursor l_terr_csr (p_subsidy_id in number,
2376 p_install_site_use_id in number) is
2377 select 'Y'
2378 from hz_locations loc,
2379 hz_party_sites hzps,
2380 hz_party_site_uses hzpsu,
2381 okl_subsidy_criteria suc
2382 Where hzpsu.party_site_use_id = p_install_site_use_id
2383 And hzps.party_site_id = hzpsu.party_site_id
2384 And loc.location_id = hzps.location_id
2385 And SUC.SUBSIDY_ID = p_subsidy_id
2386 And SUC.SALES_TERRITORY_CODE = loc.country
2387 And SUC.SALES_TERRITORY_CODE is not null;
2388 */
2389 cursor l_terr_csr (p_subsidy_id in number,
2390 p_sales_rep_id in number) is
2391 select 'Y'
2392 from RA_SALESREP_TERRITORIES rst,
2393 okl_subsidy_criteria suc
2394 Where rst.salesrep_id = p_sales_rep_id
2395 And SUC.SUBSIDY_ID = p_subsidy_id
2396 And SUC.SALES_TERRITORY_ID = rst.territory_id;
2397 -- end: okl.h cklee
2398
2399 --cursor : to check that product check is required
2400 cursor l_pdtreq_csr (p_subsidy_id in number) is
2401 select 'Y'
2402 from okl_subsidies_b sub
2403 Where sub.id = p_subsidy_id
2404 and exists (select 1
2405 from okl_subsidy_criteria suc
2406 where suc.PRODUCT_ID is not null
2407 And suc.subsidy_id = sub.id);
2408
2409 -- start: okl.h cklee
2410 --cursor to check financial product
2411 /* cursor l_pdt_csr (p_subsidy_id in number,
2412 p_chr_id in number) is
2413 select 'Y'
2414 from okl_k_headers khr,
2415 okl_subsidy_criteria suc
2416 Where khr.id = p_chr_id
2417 And SUC.subsidy_id = p_subsidy_id
2418 And SUC.product_id = khr.pdt_id
2419 And SUC.product_id is not null;
2420 */
2421 --cursor to check financial product
2422 cursor l_pdt_csr (p_subsidy_id in number,
2423 p_pdt_id in number) is
2424 select 'Y'
2425 from okl_subsidy_criteria suc
2426 where SUC.subsidy_id = p_subsidy_id
2427 And SUC.product_id = p_pdt_id
2428 And SUC.product_id is not null;
2429 -- end: okl.h cklee
2430
2431 --cursor : to check that sic_code check is required
2432 cursor l_sicreq_csr (p_subsidy_id in number) is
2433 select 'Y'
2434 from okl_subsidies_b sub
2435 Where sub.id = p_subsidy_id
2436 and exists (select 1
2437 from okl_subsidy_criteria suc
2438 where suc.INDUSTRY_CODE is not null
2439 And suc.INDUSTRY_CODE_TYPE is not null
2440 And suc.subsidy_id = sub.id);
2441
2442 -- start: okl.h cklee
2443 --cursor to check service industry code
2444 /* cursor l_sic_csr (p_subsidy_id in number,
2445 p_chr_id in number) is
2446 select 'Y'
2447 from ra_customers rac,
2448 okc_k_headers_b chrb,
2449 okl_subsidy_criteria suc
2450 where rac.customer_id = chrb.cust_acct_id
2451 And chrb.id = p_chr_id
2452 And SUC.subsidy_id = p_subsidy_id
2453 And SUC.industry_code = rac.sic_code
2454 And SUC.industry_code_type = rac.sic_code_type
2455 And SUC.industry_code is not null
2456 And SUC.industry_code_type is not null;
2457 */
2458 --cursor to check service industry code
2459 cursor l_sic_csr (p_subsidy_id in number,
2460 p_cust_account_id in number) is
2461 select 'Y'
2462 from hz_parties hp,
2463 hz_cust_accounts_all hca,
2464 okl_subsidy_criteria suc
2465 where hp.party_id = hca.party_id
2466 And hca.CUST_ACCOUNT_ID = p_cust_account_id
2467 And SUC.subsidy_id = p_subsidy_id
2468 And SUC.industry_code = hp.sic_code
2469 And SUC.industry_code_type = hp.sic_code_type
2470 And SUC.industry_code is not null
2471 And SUC.industry_code_type is not null;
2472 -- end: okl.h cklee
2473
2474
2475 -- start: okl.h cklee
2476 -- commented for the okl.h Sales Quote/Lease Application IA Subsidies
2477 /* CURSOR c_get_trx_csr IS
2478 SELECT currency_code
2479 FROM okc_k_headers_b
2480 WHERE id = p_chr_id;
2481
2482 l_trx_currency_code okc_k_headers_b.currency_code%TYPE;
2483 */
2484 -- end: okl.h cklee
2485
2486 l_chk_required varchar2(1);
2487 l_applicable varchar2(10);
2488
2489 lx_conversion_rate NUMBER;
2490 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
2491 lx_subsidy_pool_status okl_subsidy_pools_b.decision_status_code%TYPE;
2492 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
2493 lx_sub_pool_balance NUMBER;
2494
2495 halt_validation exception;
2496
2497 Begin
2498 --Checks on header line and existence of applicability criteria
2499 ---------------------------------------------------------------------------
2500 --A. check whether subsidy can be applied to contract (org id match)
2501 ---------------------------------------------------------------------------
2502 l_applicable := 'N';
2503 open l_chr_csr (p_subsidy_id => p_subsidy_id,
2504 p_authoring_org_id => p_authoring_org_id,
2505 p_currency_code => p_currency_code);
2506
2507 Fetch l_chr_csr into l_applicable;
2508 If l_chr_csr%NOTFOUND then
2509 Null;
2510 End If;
2511 close l_chr_csr;
2512 If l_applicable = 'N' then
2513 Raise halt_validation;
2514 End If;
2515
2516 ---------------------------------------------------------------------------
2517 --D. check whether subsidy can be applied to line (dates match)
2518 ---------------------------------------------------------------------------
2519 l_applicable := 'N';
2520 open l_cle_csr(p_subsidy_id => p_subsidy_id,
2521 p_start_date => p_start_date);
2522 Fetch l_cle_csr into l_applicable;
2523 If l_cle_csr%NOTFOUND then
2524 Null;
2525 End If;
2526 close l_cle_csr;
2527 If l_applicable = 'N' then
2528 Raise halt_validation;
2529 End If;
2530
2531 --START: 24-Oct-2005 cklee - Fixed bug#4865580 |
2532 l_applicable := validate_subsidy_pool_applic(p_subsidy_id => p_subsidy_id
2533 ,p_asset_cle_id => null
2534 ,p_ast_date_sq => p_start_date
2535 ,p_trx_curr_code_sq => p_currency_code
2536 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2537 ,p_tot_subsidy_amount => p_tot_subsidy_amount
2538 ,p_subsidy_amount => p_subsidy_amount
2539 ,p_filter_flag => p_filter_flag
2540 ,p_dnz_asset_number => p_dnz_asset_number
2541 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2542 );
2543 IF(l_applicable = 'NA')THEN
2544 l_applicable := 'Y';
2545 ELSIF l_applicable = 'N' THEN
2546 Raise halt_validation;
2547 END IF;
2548 --END: 24-Oct-2005 cklee - Fixed bug#4865580 |
2549
2550 ---------------------------------------------------------------------------
2551 --E. check whether any applicability criteria defined
2552 ---------------------------------------------------------------------------
2553 l_chk_required := 'N';
2554 open l_suc_csr(p_subsidy_id => p_subsidy_id);
2555 Fetch l_suc_csr into l_chk_required;
2556 If l_suc_csr%NOTFOUND then
2557 Null;
2558 End If;
2559 close l_suc_csr;
2560
2561 If l_chk_required = 'N' then
2562 l_applicable := 'Y';
2563 Raise halt_validation;
2564 End If;
2565
2566 --check applicability criterias
2567 l_applicable := 'Y';
2568 ----------------------------------------------------------------------------
2569 --1. check for inventory item and ORG
2570 ----------------------------------------------------------------------------
2571 l_chk_required := 'N';
2572 open l_invreq_csr(p_subsidy_id => p_subsidy_id);
2573 Fetch l_invreq_csr into l_chk_required;
2574 If l_invreq_csr%NOTFOUND then
2575 Null;
2576 End If;
2577 close l_invreq_csr;
2578
2579 If l_chk_required = 'Y' then
2580 --check for inv item and org
2581 l_applicable := 'N';
2582 open l_invitm_csr(p_subsidy_id => p_subsidy_id,
2583 p_inv_item_id => p_inv_item_id,
2584 p_inv_org_id => p_inv_org_id);
2585 --Bug# 3290648:
2586 fetch l_invitm_csr into l_applicable;
2587 If l_invitm_csr%NOTFOUND then
2588 Null;
2589 End If;
2590 close l_invitm_csr;
2591 If l_applicable = 'N' then
2592 Raise halt_validation;
2593 End If;
2594 End If;
2595
2596 ----------------------------------------------------------------------------
2597 --2. check for credit class
2598 ----------------------------------------------------------------------------
2599 l_chk_required := 'N';
2600 open l_clsreq_csr(p_subsidy_id => p_subsidy_id);
2601 Fetch l_clsreq_csr into l_chk_required;
2602 If l_clsreq_csr%NOTFOUND then
2603 Null;
2604 End If;
2605 close l_clsreq_csr;
2606
2607 If l_chk_required = 'Y' then
2608 --check for credit class
2609 l_applicable := 'N';
2610 open l_cclass_csr(p_subsidy_id => p_subsidy_id,
2611 p_cust_account_id => p_cust_account_id);
2612 --Bug# 3290648:
2613 fetch l_cclass_csr into l_applicable;
2614 If l_cclass_csr%NOTFOUND then
2615 Null;
2616 End If;
2617 close l_cclass_csr;
2618 If l_applicable = 'N' then
2619 Raise halt_validation;
2620 End If;
2621 End If;
2622
2623 ----------------------------------------------------------------------------
2624 --3. check for territory
2625 ----------------------------------------------------------------------------
2626 l_chk_required := 'N';
2627 open l_terrreq_csr(p_subsidy_id => p_subsidy_id);
2628 Fetch l_terrreq_csr into l_chk_required;
2629 If l_terrreq_csr%NOTFOUND then
2630 Null;
2631 End If;
2632 close l_terrreq_csr;
2633
2634 If l_chk_required = 'Y' then
2635 --check for territory
2636 l_applicable := 'N';
2637 open l_terr_csr(p_subsidy_id => p_subsidy_id,
2638 p_sales_rep_id => p_sales_rep_id);
2639 --Bug# 3290648:
2640 fetch l_terr_csr into l_applicable;
2641 If l_terr_csr%NOTFOUND then
2642 Null;
2643 End If;
2644 close l_terr_csr;
2645 If l_applicable = 'N' then
2646 Raise halt_validation;
2647 End If;
2648 End If;
2649
2650 ----------------------------------------------------------------------------
2651 --4. check for product
2652 ----------------------------------------------------------------------------
2653 l_chk_required := 'N';
2654 open l_pdtreq_csr(p_subsidy_id => p_subsidy_id);
2655 Fetch l_pdtreq_csr into l_chk_required;
2656 If l_pdtreq_csr%NOTFOUND then
2657 Null;
2658 End If;
2659 close l_pdtreq_csr;
2660
2661 If l_chk_required = 'Y' then
2662 --check for product
2663 l_applicable := 'N';
2664 open l_pdt_csr(p_subsidy_id => p_subsidy_id,
2665 p_pdt_id => p_pdt_id);
2666 --Bug# 3290648:
2667 fetch l_pdt_csr into l_applicable;
2668 If l_pdt_csr%NOTFOUND then
2669 Null;
2670 End If;
2671 close l_pdt_csr;
2672 If l_applicable = 'N' then
2673 Raise halt_validation;
2674 End If;
2675 End If;
2676
2677 ----------------------------------------------------------------------------
2678 --5. check for SIC code
2679 ----------------------------------------------------------------------------
2680 l_chk_required := 'N';
2681 open l_sicreq_csr(p_subsidy_id => p_subsidy_id);
2682 Fetch l_sicreq_csr into l_chk_required;
2683 If l_sicreq_csr%NOTFOUND then
2684 Null;
2685 End If;
2686 close l_sicreq_csr;
2687
2688 If l_chk_required = 'Y' then
2689 --check for SIC
2690 l_applicable := 'N';
2691 open l_sic_csr(p_subsidy_id => p_subsidy_id,
2692 p_cust_account_id => p_cust_account_id);
2693 --Bug# 3290648:
2694 fetch l_sic_csr into l_applicable;
2695 If l_sic_csr%NOTFOUND then
2696 Null;
2697 End If;
2698 close l_sic_csr;
2699 If l_applicable = 'N' then
2700 Raise halt_validation;
2701 End If;
2702 End If;
2703
2704 /**
2705 * sjalasut, added validations as part of subsidy pools enhancement. START
2706 * for sales quote, the parameter p_asset_cle_id is passed as NULL
2707 * the asset start date and the contract currency code must be passed in case of sales quote
2708 */
2709 -- start: okl.h cklee
2710 -- commented for the okl.h Sales Quote/Lease Application IA Subsidies
2711 /* OPEN c_get_trx_csr; FETCH c_get_trx_csr INTO l_trx_currency_code;
2712 CLOSE c_get_trx_csr;
2713 */
2714 -- end: okl.h cklee
2715 -- cklee: 06/26/05
2716 -- cklee: 06/29/2005
2717 -- l_applicable is used for the resturn status of the function. Do not confuse
2718 -- with the name of the variable. So, if l_applicable = 'NA' means subsidy is
2719 -- either associated with the subsidy pool and applicable or subsidy is a
2720 -- stand alone subsidy
2721 --
2722 --START: 24-Oct-2005 cklee - Fixed bug#4865580 |
2723 -- commented l_applicable := validate_subsidy_pool_applic(p_subsidy_id => p_subsidy_id
2724 -- commented ,p_asset_cle_id => null
2725 -- commented ,p_ast_date_sq => p_start_date
2726 --END 24-Oct-2005 cklee - Fixed bug#4865580 |
2727 -- start: okl.h cklee
2728 -- commented for the okl.h Sales Quote/Lease Application IA Subsidies
2729 -- ,p_trx_curr_code_sq => l_trx_currency_code
2730 -- commented ,p_trx_curr_code_sq => p_currency_code
2731 -- start: okl.h cklee
2732 -- commented );
2733 --END 24-Oct-2005 cklee - Fixed bug#4865580 |
2734
2735 --START: 24-Oct-2005 cklee - Fixed bug#4865580 |
2736 -- commented IF(l_applicable = 'NA')THEN
2737 -- commented l_applicable := 'Y';
2738 -- commented ELSIF l_applicable = 'N' THEN
2739 -- commented l_applicable := 'N';
2740 -- commented END IF;
2741 --END: 24-Oct-2005 cklee - Fixed bug#4865580 |
2742 /**
2743 * sjalasut, added validations as part of subsidy pools enhancement. END
2744 */
2745
2746 Return(l_applicable);
2747 Exception
2748 When halt_validation then
2749 Return(l_applicable);
2750 When others then
2751 l_applicable := 'N';
2752 Return(l_applicable);
2753 End validate_subsidy_applicability;
2754
2755 -- end: 29-June-2005 cklee - okl.h Sales Quote IA Subsidies
2756
2757
2758 -- sjalasut added new function for subsidy pools enhancement. START
2759 -- this functions returns
2760 -- Y if there exists a pool and is applicable,
2761 -- N if there exists a pool but not applicable,
2762 -- NA if the pool is not associated with the subsidy (standalone subsidy)
2763 -- the rules for applicability are
2764 -- 1. A subsidy is associated with a pool
2765 -- 2. the subsidy pool has decision_status_code = 'ACTIVE' and sysdate between effective dates of the subsidy pool
2766 -- 3. the subsidy pool is logically active as on the start date of the asset
2767 -- 4. there exists a valid currency conversion basis between the pool and the asset/contract
2768 -- 5. the pool balance is valid before addition of the subsidy amount
2769 FUNCTION validate_subsidy_pool_applic(p_subsidy_id IN okl_subsidies_b.id%TYPE,
2770 p_asset_cle_id IN okc_k_lines_b.id%TYPE,
2771 p_ast_date_sq IN okc_k_lines_b.start_date%TYPE,
2772 p_trx_curr_code_sq IN okc_k_lines_b.currency_code%TYPE,
2773 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2774 p_tot_subsidy_amount IN NUMBER,
2775 p_subsidy_amount IN NUMBER,
2776 p_filter_flag IN VARCHAR2,
2777 p_dnz_asset_number IN VARCHAR2
2778 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2779 ) RETURN VARCHAR2 IS
2780
2781 CURSOR c_get_asset_dtls_csr IS
2782 SELECT line.start_date, hdr.currency_code
2783 FROM okc_k_headers_b hdr,
2784 okc_k_lines_b line
2785 WHERE line.id = p_asset_cle_id
2786 AND line.dnz_chr_id = hdr.id;
2787
2788 --START: 09/29/2005 bug#4634871
2789 CURSOR c_get_asset_number_csr(p_asset_cle_id number) IS
2790 SELECT line.name
2791 FROM okc_k_lines_v line
2792 WHERE line.id = p_asset_cle_id;
2793
2794 CURSOR c_get_subsidy_name_csr(p_subsidy_id number) IS
2795 SELECT sub.name
2796 FROM okl_subsidies_b sub
2797 WHERE sub.id = p_subsidy_id;
2798
2799 CURSOR c_get_subsidy_pool_name_csr(p_subsidy_pool_id number) IS
2800 SELECT sub.subsidy_pool_name
2801 FROM okl_subsidy_pools_v sub
2802 WHERE sub.id = p_subsidy_pool_id;
2803 --END: 09/29/2005 bug#4634871
2804
2805
2806 l_applicable VARCHAR2(10);
2807 lx_conversion_rate NUMBER;
2808 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
2809 lx_subsidy_pool_status okl_subsidy_pools_b.decision_status_code%TYPE;
2810 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
2811 lx_sub_pool_balance NUMBER;
2812 -- local variables for sales quote usage. when called from LA, the p_asset_cle_id is passed and
2813 -- p_ast_date_sq is p_trx_curr_code are null, for SQ, the case is converse
2814 lv_asset_curr_code okc_k_lines_b.currency_code%TYPE;
2815 lv_start_date okc_k_lines_b.start_date%TYPE;
2816 --START: 09/29/2005 bug#4634871
2817 lv_asset_number okc_k_lines_v.name%TYPE;
2818 lv_subsidy_name okl_subsidies_b.name%TYPE;
2819 lv_subsidy_pool_name okl_subsidy_pools_v.subsidy_pool_name%TYPE;
2820 --END: 09/29/2005 bug#4634871
2821
2822 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_ASSET_SUBSIDY_PVT.VALIDATE_SUBSIDY_POOL_APPLIC';
2823 l_debug_enabled VARCHAR2(10);
2824 is_debug_statement_on BOOLEAN;
2825
2826 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2827 lx_return_status VARCHAR2(1);
2828 lx_msg_count NUMBER;
2829 lx_msg_data VARCHAR2(2000);
2830 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2831
2832 BEGIN
2833 l_applicable := 'NA';
2834
2835 -- check if debug is enabled
2836 l_debug_enabled := okl_debug_pub.check_log_enabled;
2837 -- check for logging on STATEMENT level
2838 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
2839
2840 -- cklee: 06/29/2005
2841 -- l_applicable is used for the resturn status of the function. Do not confuse
2842 -- with the name of the variable. So, if l_applicable = 'Y' means subsidy is
2843 -- associated with subsidy pool
2844 --
2845 l_applicable := is_sub_assoc_with_pool(p_subsidy_id => p_subsidy_id
2846 ,x_subsidy_pool_id => lx_subsidy_pool_id
2847 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
2848
2849 IF(l_applicable = 'Y' AND lx_subsidy_pool_id IS NOT NULL)THEN
2850 -- write to log
2851 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2852 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2853 l_module,
2854 'subsidy '||p_subsidy_id||' is attached to subsidy pool '||lx_subsidy_pool_id
2855 );
2856 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
2857
2858 -- the code check here is as good as the effective date check, but the code check is more
2859 -- economical in case the pool has already expired. date comparision is more costlier than
2860 -- reading a value from a column.
2861 l_applicable := is_sub_pool_active(p_subsidy_pool_id => lx_subsidy_pool_id
2862 ,x_pool_status => lx_subsidy_pool_status
2863 );
2864 IF(l_applicable = 'N')THEN
2865 -- write to log
2866 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2867 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2868 l_module,
2869 'subsidy pool '||lx_subsidy_pool_id||' is not active'
2870 );
2871 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
2872
2873 --START: 09/29/2005 bug#4634871
2874 OPEN c_get_asset_number_csr(p_asset_cle_id); FETCH c_get_asset_number_csr INTO lv_asset_number;
2875 CLOSE c_get_asset_number_csr;
2876
2877 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2878 -- Override asset number for Sales Quote to display proper error message
2879 IF p_filter_flag = 'N' THEN
2880 lv_asset_number := p_dnz_asset_number;
2881 END IF;
2882 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2883
2884 OPEN c_get_subsidy_name_csr(p_subsidy_id); FETCH c_get_subsidy_name_csr INTO lv_subsidy_name;
2885 CLOSE c_get_subsidy_name_csr;
2886 OKL_API.set_message(
2887 p_app_name => G_APP_NAME,
2888 p_msg_name => G_SUB_POOL_NOT_ACTIVE,
2889 p_token1 => 'SUBSIDY_NAME',
2890 p_token1_value => lv_subsidy_name ,
2891 p_token2 => 'ASSET_NUMBER',
2892 p_token2_value => lv_asset_number);
2893 RAISE G_EXCEPTION_HALT_VALIDATION;
2894 -- return l_applicable;
2895 --END: 09/29/2005 bug#4634871
2896 END IF;
2897 -- this determines whether the function is called from contracts or from sales quote
2898 -- for contracts, the asset_id is used to derive the transaction currency and start date
2899 -- for quotes, these values are passed as parameters and asset_id in case of quotes will be null
2900 IF(p_ast_date_sq IS NULL AND p_trx_curr_code_sq IS NULL)THEN
2901 OPEN c_get_asset_dtls_csr; FETCH c_get_asset_dtls_csr INTO lv_start_date, lv_asset_curr_code;
2902 CLOSE c_get_asset_dtls_csr;
2903 ELSE
2904 lv_start_date:= p_ast_date_sq;
2905 lv_asset_curr_code := p_trx_curr_code_sq;
2906 END IF;
2907 l_applicable := is_sub_pool_active_by_date(p_subsidy_pool_id => lx_subsidy_pool_id
2908 ,p_asset_date => lv_start_date
2909 );
2910 IF(l_applicable = 'N')THEN
2911 --START: 09/29/2005 bug#4634871
2912 OPEN c_get_asset_number_csr(p_asset_cle_id); FETCH c_get_asset_number_csr INTO lv_asset_number;
2913 CLOSE c_get_asset_number_csr;
2914
2915 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2916 -- Override asset number for Sales Quote to display proper error message
2917 IF p_filter_flag = 'N' THEN
2918 lv_asset_number := p_dnz_asset_number;
2919 END IF;
2920 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2921
2922 OPEN c_get_subsidy_name_csr(p_subsidy_id); FETCH c_get_subsidy_name_csr INTO lv_subsidy_name;
2923 CLOSE c_get_subsidy_name_csr;
2924 OKL_API.set_message(
2925 p_app_name => G_APP_NAME,
2926 p_msg_name => G_SUB_POOL_ASSET_DATES_GAP,
2927 p_token1 => 'SUBSIDY_NAME',
2928 p_token1_value => lv_subsidy_name ,
2929 p_token2 => 'ASSET_NUMBER',
2930 p_token2_value => lv_asset_number);
2931 RAISE G_EXCEPTION_HALT_VALIDATION;
2932 -- return l_applicable;
2933 --END: 09/29/2005 bug#4634871
2934 END IF;
2935 -- check for conversion basis only if the currency codes are different
2936 IF(lx_sub_pool_curr_code <> lv_asset_curr_code)THEN
2937 l_applicable := is_sub_pool_conv_rate_valid(p_subsidy_pool_id => lx_subsidy_pool_id
2938 ,p_asset_date => TRUNC(SYSDATE) -- lv_start_date, changed the date to sysdate as conversion should happen on sysdate
2939 ,p_trx_currency_code => lv_asset_curr_code
2940 ,x_conversion_rate => lx_conversion_rate
2941 );
2942 IF(l_applicable = 'N')THEN
2943 -- write to log
2944 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2945 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2946 l_module,
2947 'conversion basis does not exist for subsidy pool '||lx_subsidy_pool_id||' on '||trunc(sysdate)||
2948 ' between trx currency code '||lv_asset_curr_code||' and pool currency '||lx_sub_pool_curr_code
2949 );
2950 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
2951
2952 --START: 09/29/2005 bug#4634871
2953 OPEN c_get_subsidy_name_csr(p_subsidy_id); FETCH c_get_subsidy_name_csr INTO lv_subsidy_name;
2954 CLOSE c_get_subsidy_name_csr;
2955 OPEN c_get_subsidy_pool_name_csr(lx_subsidy_pool_id); FETCH c_get_subsidy_pool_name_csr INTO lv_subsidy_pool_name;
2956 CLOSE c_get_subsidy_pool_name_csr;
2957 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
2958 ,'SUBSIDY', lv_subsidy_name
2959 ,'POOL_NAME',lv_subsidy_pool_name);
2960 RAISE OKL_API.G_EXCEPTION_ERROR;
2961 -- return l_applicable;
2962 --END: 09/29/2005 bug#4634871
2963 END IF;
2964 -- write to log
2965 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2966 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2967 l_module,
2968 'conversion rate '|| lx_conversion_rate||' for pool '||lx_subsidy_pool_id||' on '||trunc(sysdate)||
2969 ' between trx currency code '||lv_asset_curr_code||' and pool currency '||lx_sub_pool_curr_code
2970 );
2971 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
2972 END IF;
2973
2974 l_applicable := is_balance_valid_before_add(p_subsidy_pool_id => lx_subsidy_pool_id
2975 ,x_pool_balance => lx_sub_pool_balance);
2976 IF(l_applicable = 'N')THEN
2977 -- write to log
2978 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
2979 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
2980 l_module,
2981 'subsidy pool balance is not valid before add '||lx_sub_pool_balance
2982 );
2983 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
2984
2985 --START: 09/29/2005 bug#4634871
2986 OPEN c_get_asset_number_csr(p_asset_cle_id); FETCH c_get_asset_number_csr INTO lv_asset_number;
2987 CLOSE c_get_asset_number_csr;
2988
2989 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
2990 -- Override asset number for Sales Quote to display proper error message
2991 IF p_filter_flag = 'N' THEN
2992 lv_asset_number := p_dnz_asset_number;
2993 END IF;
2994 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
2995
2996 OPEN c_get_subsidy_name_csr(p_subsidy_id); FETCH c_get_subsidy_name_csr INTO lv_subsidy_name;
2997 CLOSE c_get_subsidy_name_csr;
2998 OKL_API.set_message(
2999 p_app_name => G_APP_NAME,
3000 p_msg_name => G_SUB_POOL_BALANCE_INVALID,
3001 p_token1 => 'SUBSIDY_NAME',
3002 p_token1_value => lv_subsidy_name ,
3003 p_token2 => 'ASSET_NUMBER',
3004 p_token2_value => lv_asset_number);
3005 RAISE G_EXCEPTION_HALT_VALIDATION;
3006 -- return l_applicable;
3007 --END: 09/29/2005 bug#4634871
3008 END IF;
3009
3010 --START: bug#4874385 cklee 12/09/2005
3011 BEGIN
3012 okl_asset_subsidy_pvt.is_balance_valid_after_add (
3013 p_subsidy_id => p_subsidy_id
3014 ,p_currency_code => p_trx_curr_code_sq
3015 ,p_subsidy_amount => p_subsidy_amount
3016 ,p_tot_subsidy_amount => p_tot_subsidy_amount
3017 ,p_dnz_asset_number => p_dnz_asset_number
3018 ,x_return_status => lx_return_status
3019 ,x_msg_count => lx_msg_count
3020 ,x_msg_data => lx_msg_data);
3021
3022 -- write to log
3023 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
3024 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
3025 l_module,
3026 'okl_asset_subsidy_pvt.is_balance_valid_after_add returned with '|| lx_return_status||' x_msg_data '||lx_msg_data
3027 );
3028 END IF;
3029
3030 IF (lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3031 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3032 ELSIF (lx_return_status = OKL_API.G_RET_STS_ERROR) THEN
3033 RAISE OKL_API.G_EXCEPTION_ERROR;
3034 END IF;
3035
3036 EXCEPTION WHEN OTHERS THEN
3037 RAISE G_EXCEPTION_HALT_VALIDATION;
3038 END;
3039 --END: bug#4874385 cklee 12/09/2005
3040
3041 ELSE
3042 return 'NA';
3043 END IF;
3044 RETURN l_applicable;
3045 EXCEPTION
3046 WHEN OTHERS THEN
3047 l_applicable := 'N';
3048 RETURN(l_applicable);
3049 END validate_subsidy_pool_applic;
3050
3051
3052 FUNCTION is_sub_assoc_with_pool(p_subsidy_id IN okl_subsidies_b.id%TYPE
3053 ,x_subsidy_pool_id OUT NOCOPY okl_subsidy_pools_b.id%TYPE
3054 ,x_sub_pool_curr_code OUT NOCOPY okl_subsidy_pools_b.currency_code%TYPE) RETURN VARCHAR2 IS
3055 CURSOR c_subsidy_csr IS
3056 SELECT sub.subsidy_pool_id,pool.currency_code
3057 FROM okl_subsidies_b sub
3058 ,okl_subsidy_pools_b pool
3059 WHERE sub.id = p_subsidy_id
3060 AND sub.subsidy_pool_id = pool.id;
3061 cv_subsidy c_subsidy_csr%ROWTYPE;
3062 lv_return_status VARCHAR2(1);
3063 BEGIN
3064 lv_return_status := 'N';
3065 OPEN c_subsidy_csr; FETCH c_subsidy_csr INTO cv_subsidy; CLOSE c_subsidy_csr;
3066 IF(cv_subsidy.subsidy_pool_id IS NOT NULL AND cv_subsidy.subsidy_pool_id <> OKL_API.G_MISS_NUM)THEN
3067 x_subsidy_pool_id := cv_subsidy.subsidy_pool_id; -- this is the subsidy pool id
3068 x_sub_pool_curr_code := cv_subsidy.currency_code; -- this is the subsidy pool currency code
3069 lv_return_status := 'Y';
3070 END IF;
3071 return lv_return_status;
3072 END is_sub_assoc_with_pool;
3073
3074 FUNCTION is_sub_pool_active(p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
3075 ,x_pool_status OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE) RETURN VARCHAR2 IS
3076 CURSOR c_sub_pool_csr IS
3077 SELECT decision_status_code
3078 ,effective_from_date
3079 ,effective_to_date
3080 FROM okl_subsidy_pools_b
3081 WHERE id = p_subsidy_pool_id;
3082 cv_sub_pool c_sub_pool_csr%ROWTYPE;
3083 lv_return_status VARCHAR2(1);
3084 BEGIN
3085 lv_return_status := 'N';
3086 OPEN c_sub_pool_csr; FETCH c_sub_pool_csr INTO cv_sub_pool; CLOSE c_sub_pool_csr;
3087 x_pool_status := cv_sub_pool.decision_status_code;
3088 IF((cv_sub_pool.decision_status_code = 'ACTIVE') AND
3089 (TRUNC(SYSDATE) BETWEEN cv_sub_pool.effective_from_date AND NVL(cv_sub_pool.effective_to_date,okl_accounting_util.g_final_date)))THEN
3090 lv_return_status := 'Y';
3091 END IF;
3092 return lv_return_status;
3093 END is_sub_pool_active;
3094
3095 FUNCTION is_sub_pool_active_by_date(p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
3096 ,p_asset_date IN okc_k_lines_b.start_date%TYPE
3097 ) RETURN VARCHAR2 IS
3098 CURSOR c_sub_pool_csr IS
3099 SELECT effective_from_date
3100 ,effective_to_date
3101 FROM okl_subsidy_pools_b
3102 WHERE id = p_subsidy_pool_id;
3103 cv_sub_pool c_sub_pool_csr%ROWTYPE;
3104 lv_return_status VARCHAR2(1);
3105 x_return_status VARCHAR2(1);
3106 x_msg_count NUMBER;
3107 x_msg_data VARCHAR2(1000);
3108 BEGIN
3109 lv_return_status := 'N';
3110 OPEN c_sub_pool_csr; FETCH c_sub_pool_csr INTO cv_sub_pool; CLOSE c_sub_pool_csr;
3111 IF(TRUNC(p_asset_date) BETWEEN TRUNC(cv_sub_pool.effective_from_date)
3112 AND NVL(cv_sub_pool.effective_to_date,OKL_ACCOUNTING_UTIL.g_final_date))THEN
3113 lv_return_status := 'Y';
3114 END IF;
3115 return lv_return_status;
3116 END is_sub_pool_active_by_date;
3117
3118 FUNCTION is_sub_pool_conv_rate_valid(p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
3119 ,p_asset_date IN okc_k_lines_b.start_date%TYPE
3120 ,p_trx_currency_code IN okc_k_headers_b.currency_code%TYPE
3121 ,x_conversion_rate OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
3122 CURSOR c_sub_pool_csr IS
3123 SELECT currency_code
3124 ,currency_conversion_type
3125 FROM okl_subsidy_pools_b
3126 WHERE id = p_subsidy_pool_id;
3127 cv_sub_pool c_sub_pool_csr%ROWTYPE;
3128 lv_return_status VARCHAR2(1);
3129 x_return_status VARCHAR2(1);
3130 x_msg_count NUMBER;
3131 x_msg_data VARCHAR2(1000);
3132 lv_conversion_rate NUMBER;
3133 l_api_version CONSTANT NUMBER DEFAULT 1.0;
3134 BEGIN
3135 lv_return_status := 'N';
3136 OPEN c_sub_pool_csr; FETCH c_sub_pool_csr INTO cv_sub_pool; CLOSE c_sub_pool_csr;
3137 lv_conversion_rate := 0;
3138 okl_accounting_util.get_curr_con_rate(p_api_version => l_api_version
3139 ,p_init_msg_list => OKL_API.G_TRUE
3140 ,x_return_status => x_return_status
3141 ,x_msg_count => x_msg_count
3142 ,x_msg_data => x_msg_data
3143 ,p_from_curr_code => p_trx_currency_code
3144 ,p_to_curr_code => cv_sub_pool.currency_code
3145 ,p_con_date => NVL(p_asset_date,TRUNC(SYSDATE)) -- since no trx is done, conv date is sysdate per PM
3146 ,p_con_type => cv_sub_pool.currency_conversion_type
3147 ,x_conv_rate => lv_conversion_rate
3148 );
3149 IF(x_return_status = OKL_API.G_RET_STS_SUCCESS OR lv_conversion_rate > 0)THEN
3150 lv_return_status := 'Y';
3151 END IF;
3152 x_conversion_rate := lv_conversion_rate;
3153 return lv_return_status;
3154 END is_sub_pool_conv_rate_valid;
3155
3156 FUNCTION is_balance_valid_before_add (p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
3157 , x_pool_balance OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
3158 CURSOR c_sub_pool_csr IS
3159 SELECT NVL(total_budgets,0) total_budget_amount
3160 ,NVL(total_subsidy_amount,0) total_subsidy_amount
3161 FROM okl_subsidy_pools_b
3162 WHERE id = p_subsidy_pool_id;
3163 cv_sub_pool c_sub_pool_csr%ROWTYPE;
3164 lv_return_status VARCHAR2(1);
3165 BEGIN
3166 lv_return_status := 'N';
3167 OPEN c_sub_pool_csr; FETCH c_sub_pool_csr INTO cv_sub_pool; CLOSE c_sub_pool_csr;
3168 x_pool_balance := (cv_sub_pool.total_budget_amount - cv_sub_pool.total_subsidy_amount);
3169 IF(x_pool_balance > 0)THEN
3170 lv_return_status := 'Y';
3171 END IF;
3172 return lv_return_status;
3173 END is_balance_valid_before_add;
3174
3175 PROCEDURE is_balance_valid_after_add (p_subsidy_id okl_subsidies_b.id%TYPE,
3176 p_asset_id okc_k_lines_b.id%TYPE,
3177 p_subsidy_amount NUMBER,
3178 p_subsidy_name okl_subsidies_b.name%TYPE
3179 ,x_return_status OUT NOCOPY VARCHAR2
3180 ,x_msg_count OUT NOCOPY NUMBER
3181 ,x_msg_data OUT NOCOPY VARCHAR2
3182 ) IS
3183
3184 -- START: cklee 09/27/2005: 4634792
3185 -- This cursor will accumulated the total amount of the subsidy up to now
3186 -- for the current transaction
3187 CURSOR c_get_tot_sub_amt_csr(p_asset_id number, p_subsidy_pool_id number) IS
3188 select SUM(decode(kle_sub.SUBSIDY_OVERRIDE_AMOUNT,
3189 null, nvl(kle_sub.AMOUNT,0),
3190 kle_sub.SUBSIDY_OVERRIDE_AMOUNT))
3191 from okc_k_lines_b cleb_sub,
3192 okc_line_styles_b lseb_sub,
3193 okl_k_lines kle_sub,
3194 okl_subsidies_b sub
3195 where kle_sub.id = cleb_sub.id And
3196 cleb_sub.lse_id = lseb_sub.id And
3197 sub.id = kle_sub.subsidy_id And
3198 sub.subsidy_pool_id = p_subsidy_pool_id And
3199 lseb_sub.lty_code = 'SUBSIDY' And
3200 cleb_sub.sts_code <> 'ABANDONED' And
3201 cleb_sub.dnz_chr_id = (select dnz_chr_id
3202 from okc_k_lines_b cleb_sub1
3203 where cleb_sub1.id = p_asset_id);
3204 -- END: cklee 09/27/2005: 4634792
3205
3206 CURSOR c_get_asset_csr IS
3207 SELECT start_date, currency_code
3208 FROM okc_k_lines_b
3209 WHERE id = p_asset_id;
3210 cv_get_asset c_get_asset_csr%ROWTYPE;
3211
3212 CURSOR c_get_pool_amount_csr (p_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)IS
3213 SELECT nvl(total_budgets,0) total_budgets
3214 ,nvl(total_subsidy_amount,0) total_subsidy_amount
3215 , subsidy_pool_name
3216 FROM okl_subsidy_pools_b
3217 WHERE id = p_subsidy_pool_id;
3218
3219 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3220 CURSOR c_subsidy_name_csr(p_subsidy_id number) IS
3221 select sub.name
3222 from okl_subsidies_b sub
3223 where sub.id = p_subsidy_id;
3224
3225 l_subsidy_name okl_subsidies_b.name%TYPE;
3226 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3227
3228 cv_pool_amount c_get_pool_amount_csr%ROWTYPE;
3229
3230 lv_return_status VARCHAR2(1);
3231 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
3232 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
3233 lx_conversion_rate NUMBER;
3234 lx_conversion_round_amt NUMBER;
3235 l_amount_in_pool_curr NUMBER;
3236 l_api_version CONSTANT NUMBER DEFAULT 1.0;
3237
3238 -- START: cklee 09/27/2005: 4634792
3239 lx_conversion_rate_tot NUMBER;
3240 lx_conversion_round_amt_tot NUMBER;
3241 l_amount_in_pool_curr_tot NUMBER;
3242 l_subsidy_amount_tot NUMBER;
3243 -- END: cklee 09/27/2005: 4634792
3244
3245 BEGIN
3246 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3247 -- first determine if subsidy pool is applicable
3248 lv_return_status := is_sub_assoc_with_pool(p_subsidy_id => p_subsidy_id
3249 ,x_subsidy_pool_id => lx_subsidy_pool_id
3250 ,x_sub_pool_curr_code => lx_sub_pool_curr_code
3251 );
3252 IF(lv_return_status = 'Y' AND lx_subsidy_pool_id IS NOT NULL)THEN
3253 -- now that the subsidy is associated with the pool, check if the pool balance is valid after adding the
3254 -- total subsidy amount to the pool
3255 OPEN c_get_asset_csr; FETCH c_get_asset_csr INTO cv_get_asset;
3256 CLOSE c_get_asset_csr;
3257
3258 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3259 open c_subsidy_name_csr(p_subsidy_id);
3260 fetch c_subsidy_name_csr into l_subsidy_name;
3261 close c_subsidy_name_csr;
3262 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3263
3264 -- START: cklee 09/27/2005: 4634792
3265 open c_get_tot_sub_amt_csr(p_asset_id, lx_subsidy_pool_id);
3266 fetch c_get_tot_sub_amt_csr into l_subsidy_amount_tot;
3267 close c_get_tot_sub_amt_csr;
3268 -- END: cklee 09/27/2005: 4634792
3269
3270 IF(cv_get_asset.currency_code <> lx_sub_pool_curr_code)THEN
3271 lx_conversion_rate := 0;
3272 -- obtain the conversion rate for the pool, as on the asset start date
3273 lv_return_status := is_sub_pool_conv_rate_valid(p_subsidy_pool_id => lx_subsidy_pool_id
3274 ,p_asset_date => TRUNC(SYSDATE) -- since no trx is done, conv date is sysdate per PM
3275 ,p_trx_currency_code => cv_get_asset.currency_code
3276 ,x_conversion_rate => lx_conversion_rate
3277 );
3278 IF(lv_return_status = 'Y' AND lx_conversion_rate > 0)THEN
3279
3280 lx_conversion_round_amt := 0;
3281 l_amount_in_pool_curr := lx_conversion_rate * p_subsidy_amount;
3282 -- this converted amount should be rounded
3283 lx_conversion_round_amt := okl_accounting_util.cross_currency_round_amount(p_amount => l_amount_in_pool_curr
3284 ,p_currency_code => lx_sub_pool_curr_code);
3285 IF(lx_conversion_round_amt <= 0)THEN
3286 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3287 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3288 -- ,'SUBSIDY', p_subsidy_name
3289 ,'SUBSIDY', l_subsidy_name
3290 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3291 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3292 x_return_status := OKC_API.G_RET_STS_ERROR;
3293 RAISE OKL_API.G_EXCEPTION_ERROR;
3294 ELSE
3295 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3296 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3297 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3298 CLOSE c_get_pool_amount_csr;
3299 IF(cv_pool_amount.total_budgets < lx_conversion_round_amt + cv_pool_amount.total_subsidy_amount)THEN
3300 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3301 'TRX_AMOUNT', p_subsidy_amount
3302 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3303 -- ,'SUBSIDY', p_subsidy_name
3304 ,'SUBSIDY', l_subsidy_name
3305 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3306 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3307 x_return_status := OKC_API.G_RET_STS_ERROR;
3308 RAISE OKL_API.G_EXCEPTION_ERROR;
3309 END IF;
3310 END IF;
3311
3312 -- START: cklee 09/27/2005: 4634792
3313 -- Check accumulated subsidy amount up to now for a specific pool
3314 lx_conversion_round_amt_tot := 0;
3315 l_amount_in_pool_curr_tot := lx_conversion_rate * l_subsidy_amount_tot;
3316 -- this converted amount should be rounded
3317 lx_conversion_round_amt_tot := okl_accounting_util.cross_currency_round_amount(p_amount => l_amount_in_pool_curr_tot
3318 ,p_currency_code => lx_sub_pool_curr_code);
3319 IF(lx_conversion_round_amt_tot <= 0)THEN
3320 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3321 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3322 -- ,'SUBSIDY', p_subsidy_name
3323 ,'SUBSIDY', l_subsidy_name
3324 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3325 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3326 x_return_status := OKC_API.G_RET_STS_ERROR;
3327 RAISE OKL_API.G_EXCEPTION_ERROR;
3328 ELSE
3329 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3330 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3331 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3332 CLOSE c_get_pool_amount_csr;
3333 IF(cv_pool_amount.total_budgets < lx_conversion_round_amt_tot + cv_pool_amount.total_subsidy_amount)THEN
3334 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3335 'TRX_AMOUNT', p_subsidy_amount
3336 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3337 -- ,'SUBSIDY', p_subsidy_name
3338 ,'SUBSIDY', l_subsidy_name
3339 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3340 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3341 x_return_status := OKC_API.G_RET_STS_ERROR;
3342 RAISE OKL_API.G_EXCEPTION_ERROR;
3343 END IF;
3344 END IF;
3345 -- END: cklee 09/27/2005: 4634792
3346
3347 ELSE
3348 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3349 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3350 -- ,'SUBSIDY', p_subsidy_name
3351 ,'SUBSIDY', l_subsidy_name
3352 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3353 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3354 x_return_status := OKC_API.G_RET_STS_ERROR;
3355 RAISE OKL_API.G_EXCEPTION_ERROR;
3356 END IF; -- end of lv_return_status = 'Y' AND lx_conversion_rate > 0
3357 --START: cklee 09/12/2005
3358 ELSE
3359 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3360 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3361 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3362 CLOSE c_get_pool_amount_csr;
3363 IF(cv_pool_amount.total_budgets < p_subsidy_amount + cv_pool_amount.total_subsidy_amount)THEN
3364 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3365 'TRX_AMOUNT', p_subsidy_amount
3366 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3367 -- ,'SUBSIDY', p_subsidy_name
3368 ,'SUBSIDY', l_subsidy_name
3369 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3370 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3371 x_return_status := OKC_API.G_RET_STS_ERROR;
3372 RAISE OKL_API.G_EXCEPTION_ERROR;
3373 END IF;
3374 --END: cklee 09/12/2005
3375
3376 -- START: cklee 09/27/2005: 4634792
3377 -- Check accumulated subsidy amount up to now for a specific pool
3378 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3379 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3380 CLOSE c_get_pool_amount_csr;
3381 IF(cv_pool_amount.total_budgets < l_subsidy_amount_tot + cv_pool_amount.total_subsidy_amount)THEN
3382 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3383 'TRX_AMOUNT', p_subsidy_amount
3384 --START: 24-Oct-2005 cklee - Fixed bug#4687505 |
3385 -- ,'SUBSIDY', p_subsidy_name
3386 ,'SUBSIDY', l_subsidy_name
3387 --END: 24-Oct-2005 cklee - Fixed bug#4687505 |
3388 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3389 x_return_status := OKC_API.G_RET_STS_ERROR;
3390 RAISE OKL_API.G_EXCEPTION_ERROR;
3391 END IF;
3392 -- END: cklee 09/27/2005: 4634792
3393
3394 END IF; -- end of cv_get_asset.currency_code <> lx_sub_pool_curr_code
3395 END IF; -- end for lv_return_status = 'Y' AND lx_subsidy_pool_id IS NOT NULL
3396 END is_balance_valid_after_add;
3397
3398 -- sjalasut added new function for subsidy pools enhancement. END
3399
3400
3401 --START: 09-Dec-2005 cklee - Fixed bug#4874385 |
3402 -------------------------------------------------------------------------------
3403 -- PROCEDURE is_balance_valid_after_add : for Sales Quote and Lease application
3404 -------------------------------------------------------------------------------
3405 -- Start of comments
3406 --
3407 -- Function Name : is_balance_valid_after_add
3408 -- Description : for the context subsidy pool, this function returns Y if there exists a valid
3409 -- pool balance after adding the subsidy amount to the pool in pool currency, N otherwise
3410 -- Parameters : IN p_asb_rec asb_rec_type
3411 -- Version : 1.0
3412 -- History : 07-Dec-2005 cklee created
3413 -- End of comments
3414
3415 PROCEDURE is_balance_valid_after_add (p_subsidy_id IN okl_subsidies_b.id%TYPE
3416 ,p_currency_code IN VARCHAR2
3417 ,p_subsidy_amount IN NUMBER
3418 ,p_tot_subsidy_amount IN NUMBER
3419 ,p_dnz_asset_number IN VARCHAR2
3420 ,x_return_status OUT NOCOPY VARCHAR2
3421 ,x_msg_count OUT NOCOPY NUMBER
3422 ,x_msg_data OUT NOCOPY VARCHAR2
3423 ) IS
3424
3425
3426 CURSOR c_get_pool_amount_csr (p_subsidy_pool_id okl_subsidy_pools_b.id%TYPE)IS
3427 SELECT nvl(total_budgets,0) total_budgets
3428 ,nvl(total_subsidy_amount,0) total_subsidy_amount
3429 , subsidy_pool_name
3430 FROM okl_subsidy_pools_b
3431 WHERE id = p_subsidy_pool_id;
3432
3433 CURSOR c_subsidy_name_csr(p_subsidy_id number) IS
3434 select sub.name
3435 from okl_subsidies_b sub
3436 where sub.id = p_subsidy_id;
3437
3438 l_subsidy_name okl_subsidies_b.name%TYPE;
3439
3440 cv_pool_amount c_get_pool_amount_csr%ROWTYPE;
3441
3442 lv_return_status VARCHAR2(1);
3443 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
3444 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
3445 lx_conversion_rate NUMBER;
3446 lx_conversion_round_amt NUMBER;
3447 l_amount_in_pool_curr NUMBER;
3448 l_api_version CONSTANT NUMBER DEFAULT 1.0;
3449
3450 lx_conversion_rate_tot NUMBER;
3451 lx_conversion_round_amt_tot NUMBER;
3452 l_amount_in_pool_curr_tot NUMBER;
3453
3454 BEGIN
3455 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3456 -- first determine if subsidy pool is applicable
3457 lv_return_status := is_sub_assoc_with_pool(p_subsidy_id => p_subsidy_id
3458 ,x_subsidy_pool_id => lx_subsidy_pool_id
3459 ,x_sub_pool_curr_code => lx_sub_pool_curr_code
3460 );
3461 IF(lv_return_status = 'Y' AND lx_subsidy_pool_id IS NOT NULL)THEN
3462 -- now that the subsidy is associated with the pool, check if the pool balance is valid after adding the
3463 -- total subsidy amount to the pool
3464
3465 open c_subsidy_name_csr(p_subsidy_id);
3466 fetch c_subsidy_name_csr into l_subsidy_name;
3467 close c_subsidy_name_csr;
3468
3469 IF(p_currency_code <> lx_sub_pool_curr_code)THEN
3470 lx_conversion_rate := 0;
3471 -- obtain the conversion rate for the pool, as on the asset start date
3472 lv_return_status := is_sub_pool_conv_rate_valid(p_subsidy_pool_id => lx_subsidy_pool_id
3473 ,p_asset_date => TRUNC(SYSDATE) -- since no trx is done, conv date is sysdate per PM
3474 ,p_trx_currency_code => p_currency_code
3475 ,x_conversion_rate => lx_conversion_rate
3476 );
3477 IF(lv_return_status = 'Y' AND lx_conversion_rate > 0)THEN
3478
3479 lx_conversion_round_amt := 0;
3480 l_amount_in_pool_curr := lx_conversion_rate * p_subsidy_amount;
3481 -- this converted amount should be rounded
3482 lx_conversion_round_amt := okl_accounting_util.cross_currency_round_amount(p_amount => l_amount_in_pool_curr
3483 ,p_currency_code => lx_sub_pool_curr_code);
3484 IF(lx_conversion_round_amt <= 0)THEN
3485 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3486 ,'SUBSIDY', l_subsidy_name
3487 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3488 x_return_status := OKC_API.G_RET_STS_ERROR;
3489 RAISE OKL_API.G_EXCEPTION_ERROR;
3490 ELSE
3491 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3492 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3493 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3494 CLOSE c_get_pool_amount_csr;
3495 IF(cv_pool_amount.total_budgets < lx_conversion_round_amt + cv_pool_amount.total_subsidy_amount)THEN
3496 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3497 'TRX_AMOUNT', p_subsidy_amount
3498 ,'SUBSIDY', l_subsidy_name
3499 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3500 x_return_status := OKC_API.G_RET_STS_ERROR;
3501 RAISE OKL_API.G_EXCEPTION_ERROR;
3502 END IF;
3503 END IF;
3504
3505 -- Check accumulated subsidy amount up to now for a specific pool
3506 lx_conversion_round_amt_tot := 0;
3507 l_amount_in_pool_curr_tot := lx_conversion_rate * p_tot_subsidy_amount;
3508 -- this converted amount should be rounded
3509 lx_conversion_round_amt_tot := okl_accounting_util.cross_currency_round_amount(p_amount => l_amount_in_pool_curr_tot
3510 ,p_currency_code => lx_sub_pool_curr_code);
3511 IF(lx_conversion_round_amt_tot <= 0)THEN
3512 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3513 ,'SUBSIDY', l_subsidy_name
3514 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3515 x_return_status := OKC_API.G_RET_STS_ERROR;
3516 RAISE OKL_API.G_EXCEPTION_ERROR;
3517 ELSE
3518 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3519 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3520 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3521 CLOSE c_get_pool_amount_csr;
3522 IF(cv_pool_amount.total_budgets < lx_conversion_round_amt_tot + cv_pool_amount.total_subsidy_amount)THEN
3523 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3524 'TRX_AMOUNT', p_tot_subsidy_amount
3525 ,'SUBSIDY', l_subsidy_name
3526 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3527 x_return_status := OKC_API.G_RET_STS_ERROR;
3528 RAISE OKL_API.G_EXCEPTION_ERROR;
3529 END IF;
3530 END IF;
3531
3532 ELSE
3533 OKC_API.set_message(G_APP_NAME, G_NO_CONVERSION_BASIS
3534 ,'SUBSIDY', l_subsidy_name
3535 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3536 x_return_status := OKC_API.G_RET_STS_ERROR;
3537 RAISE OKL_API.G_EXCEPTION_ERROR;
3538 END IF; -- end of lv_return_status = 'Y' AND lx_conversion_rate > 0
3539
3540 ELSE
3541 -- the amount of subsidy in pool currency should not be more than the total budget as we reduce this from the pool balance
3542 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3543 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3544 CLOSE c_get_pool_amount_csr;
3545 IF(cv_pool_amount.total_budgets < p_subsidy_amount + cv_pool_amount.total_subsidy_amount)THEN
3546 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3547 'TRX_AMOUNT', p_subsidy_amount
3548 ,'SUBSIDY', l_subsidy_name
3549 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3550 x_return_status := OKC_API.G_RET_STS_ERROR;
3551 RAISE OKL_API.G_EXCEPTION_ERROR;
3552 END IF;
3553
3554 -- Check accumulated subsidy amount up to now for a specific pool
3555 OPEN c_get_pool_amount_csr (p_subsidy_pool_id =>lx_subsidy_pool_id);
3556 FETCH c_get_pool_amount_csr INTO cv_pool_amount;
3557 CLOSE c_get_pool_amount_csr;
3558 IF(cv_pool_amount.total_budgets < p_tot_subsidy_amount + cv_pool_amount.total_subsidy_amount)THEN
3559 OKC_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET,
3560 'TRX_AMOUNT', p_tot_subsidy_amount
3561 ,'SUBSIDY', l_subsidy_name
3562 ,'POOL_NAME',cv_pool_amount.subsidy_pool_name);
3563 x_return_status := OKC_API.G_RET_STS_ERROR;
3564 RAISE OKL_API.G_EXCEPTION_ERROR;
3565 END IF;
3566
3567 END IF; -- end of cv_get_asset.currency_code <> lx_sub_pool_curr_code
3568 END IF; -- end for lv_return_status = 'Y' AND lx_subsidy_pool_id IS NOT NULL
3569
3570 EXCEPTION WHEN OTHERS THEN
3571 x_return_status := OKL_API.G_RET_STS_ERROR;
3572
3573 END is_balance_valid_after_add;
3574 --END: 09-Dec-2005 cklee - Fixed bug#4874385 |
3575
3576 -----------------------------------
3577 --1.validate subsidy id
3578 -----------------------------------
3579 PROCEDURE validate_subsidy_id(
3580 x_return_status OUT NOCOPY VARCHAR2,
3581 p_subsidy_id IN NUMBER) IS
3582 cursor l_sub_csr (p_subsidy_id in number) is
3583 select 'Y'
3584 from okl_subsidies_b subb
3585 where subb.id = p_subsidy_id;
3586
3587 l_exists varchar2(1) default 'N';
3588 BEGIN
3589 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3590 IF (p_subsidy_id = OKL_API.G_MISS_NUM OR
3591 p_subsidy_id IS NULL)
3592 THEN
3593 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy Name');
3594 x_return_status := OKL_API.G_RET_STS_ERROR;
3595 RAISE G_EXCEPTION_HALT_VALIDATION;
3596 ELSE
3597 --check foreign key validation
3598 l_exists := 'N';
3599 open l_sub_csr(p_subsidy_id => p_subsidy_id);
3600 fetch l_sub_csr into l_exists;
3601 If l_sub_csr%NOTFOUND then
3602 null;
3603 End If;
3604 Close l_sub_csr;
3605 If l_exists = 'N' then
3606 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy Name');
3607 x_return_status := OKL_API.G_RET_STS_ERROR;
3608 RAISE G_EXCEPTION_HALT_VALIDATION;
3609 End If;
3610 END IF;
3611 EXCEPTION
3612 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3613 null;
3614 WHEN OTHERS THEN
3615 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
3616 ,p_msg_name => G_UNEXPECTED_ERROR
3617 ,p_token1 => G_SQLCODE_TOKEN
3618 ,p_token1_value => SQLCODE
3619 ,p_token2 => G_SQLERRM_TOKEN
3620 ,p_token2_value => SQLERRM);
3621 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3622 END validate_subsidy_id;
3623 -----------------------------------
3624 --2.validate subsidy_cle_id
3625 -----------------------------------
3626 PROCEDURE validate_subsidy_cle_id(
3627 x_return_status OUT NOCOPY VARCHAR2,
3628 p_subsidy_cle_id IN NUMBER) IS
3629 cursor l_subcle_csr (p_subsidy_cle_id in number) is
3630 select 'Y'
3631 from okc_k_lines_b cleb,
3632 okc_line_styles_b lseb
3633 where cleb.id = p_subsidy_cle_id
3634 and lseb.id = cleb.lse_id
3635 and lseb.lty_code = 'SUBSIDY'
3636 and cleb.sts_code <> 'ABANDONED';
3637
3638 l_exists varchar2(1) default 'N';
3639 BEGIN
3640 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3641 IF (p_subsidy_cle_id <> OKL_API.G_MISS_NUM AND
3642 p_subsidy_cle_id IS NOT NULL)
3643 THEN
3644 --check foreign key validation
3645 l_exists := 'N';
3646 open l_subcle_csr(p_subsidy_cle_id => p_subsidy_cle_id);
3647 fetch l_subcle_csr into l_exists;
3648 If l_subcle_csr%NOTFOUND then
3649 null;
3650 End If;
3651 Close l_subcle_csr;
3652 If l_exists = 'N' then
3653 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy line identifier');
3654 x_return_status := OKL_API.G_RET_STS_ERROR;
3655 RAISE G_EXCEPTION_HALT_VALIDATION;
3656 End If;
3657 END IF;
3658 EXCEPTION
3659 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3660 null;
3661 WHEN OTHERS THEN
3662 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
3663 ,p_msg_name => G_UNEXPECTED_ERROR
3664 ,p_token1 => G_SQLCODE_TOKEN
3665 ,p_token1_value => SQLCODE
3666 ,p_token2 => G_SQLERRM_TOKEN
3667 ,p_token2_value => SQLERRM);
3668 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3669 END validate_subsidy_cle_id;
3670 -----------------------------------
3671 --3.validate dnz_chr_id
3672 -----------------------------------
3673 PROCEDURE validate_dnz_chr_id(
3674 x_return_status OUT NOCOPY VARCHAR2,
3675 p_dnz_chr_id IN NUMBER) IS
3676
3677 cursor l_chr_csr (p_dnz_chr_id in number) is
3678 select 'Y'
3679 from okc_k_headers_b chrb
3680 where chrb.id = p_dnz_chr_id;
3681 --as per clarification by srawlings
3682 --and chrb.sts_code in ('NEW','COMPLETE','PASSED','INCOMPLETE','TERMINATED','APPROVED');
3683
3684 l_exists varchar2(1) default 'N';
3685 BEGIN
3686 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3687 IF (p_dnz_chr_id = OKL_API.G_MISS_NUM OR
3688 p_dnz_chr_id IS NULL)
3689 THEN
3690 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Contract Identifier');
3691 x_return_status := OKL_API.G_RET_STS_ERROR;
3692 RAISE G_EXCEPTION_HALT_VALIDATION;
3693 ELSE
3694 --check foreign key validation
3695 l_exists := 'N';
3696 open l_chr_csr(p_dnz_chr_id => p_dnz_chr_id);
3697 fetch l_chr_csr into l_exists;
3698 If l_chr_csr%NOTFOUND then
3699 null;
3700 End If;
3701 Close l_chr_csr;
3702 If l_exists = 'N' then
3703 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Contract Identifier');
3704 x_return_status := OKL_API.G_RET_STS_ERROR;
3705 RAISE G_EXCEPTION_HALT_VALIDATION;
3706 End If;
3707 END IF;
3708 EXCEPTION
3709 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3710 null;
3711 WHEN OTHERS THEN
3712 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
3713 ,p_msg_name => G_UNEXPECTED_ERROR
3714 ,p_token1 => G_SQLCODE_TOKEN
3715 ,p_token1_value => SQLCODE
3716 ,p_token2 => G_SQLERRM_TOKEN
3717 ,p_token2_value => SQLERRM);
3718 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3719 END validate_dnz_chr_id;
3720 -----------------------------------
3721 --4.validate asset_Cle_id
3722 -----------------------------------
3723 PROCEDURE validate_asset_cle_id(
3724 x_return_status OUT NOCOPY VARCHAR2,
3725 p_asset_cle_id IN NUMBER) IS
3726
3727 cursor l_cle_csr (p_asset_cle_id in number) is
3728 select 'Y'
3729 from okc_k_lines_b cleb
3730 where cleb.id = p_asset_cle_id;
3731 --as per clarification by srawlings
3732 --and cleb.sts_code in ('NEW','COMPLETE','PASSED','INCOMPLETE','TERMINATED','APPROVED');
3733
3734 l_exists varchar2(1) default 'N';
3735 BEGIN
3736 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3737 IF (p_asset_cle_id = OKL_API.G_MISS_NUM OR
3738 p_asset_cle_id IS NULL)
3739 THEN
3740 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Asset line identifier');
3741 x_return_status := OKL_API.G_RET_STS_ERROR;
3742 RAISE G_EXCEPTION_HALT_VALIDATION;
3743 ELSE
3744 --check foreign key validation
3745 l_exists := 'N';
3746 open l_cle_csr(p_asset_cle_id => p_asset_cle_id);
3747 fetch l_cle_csr into l_exists;
3748 If l_cle_csr%NOTFOUND then
3749 null;
3750 End If;
3751 Close l_cle_csr;
3752 If l_exists = 'N' then
3753 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Asset line identifier');
3754 x_return_status := OKL_API.G_RET_STS_ERROR;
3755 RAISE G_EXCEPTION_HALT_VALIDATION;
3756 End If;
3757 END IF;
3758 EXCEPTION
3759 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3760 null;
3761 WHEN OTHERS THEN
3762 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
3763 ,p_msg_name => G_UNEXPECTED_ERROR
3764 ,p_token1 => G_SQLCODE_TOKEN
3765 ,p_token1_value => SQLCODE
3766 ,p_token2 => G_SQLERRM_TOKEN
3767 ,p_token2_value => SQLERRM);
3768 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3769 END validate_asset_cle_id;
3770 -----------------------------------
3771 --5.validate vendor_id
3772 -----------------------------------
3773 PROCEDURE validate_vendor_id(
3774 x_return_status OUT NOCOPY VARCHAR2,
3775 p_vendor_id IN NUMBER) IS
3776
3777 cursor l_vendor_csr (p_vendor_id in number) is
3778 select 'Y'
3779 from po_vendors pov
3780 where pov.vendor_id = p_vendor_id;
3781
3782 l_exists varchar2(1) default 'N';
3783 BEGIN
3784 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3785 IF (p_vendor_id = OKL_API.G_MISS_NUM OR
3786 p_vendor_id IS NULL)
3787 THEN
3788 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Subsidy provider party');
3789 x_return_status := OKL_API.G_RET_STS_ERROR;
3790 RAISE G_EXCEPTION_HALT_VALIDATION;
3791 ELSE
3792 --check foreign key validation
3793 l_exists := 'N';
3794 open l_vendor_csr(p_vendor_id => p_vendor_id);
3795 fetch l_vendor_csr into l_exists;
3796 If l_vendor_csr%NOTFOUND then
3797 null;
3798 End If;
3799 Close l_vendor_csr;
3800 If l_exists = 'N' then
3801 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy provider party');
3802 x_return_status := OKL_API.G_RET_STS_ERROR;
3803 RAISE G_EXCEPTION_HALT_VALIDATION;
3804 End If;
3805 END IF;
3806 EXCEPTION
3807 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3808 null;
3809 WHEN OTHERS THEN
3810 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
3811 ,p_msg_name => G_UNEXPECTED_ERROR
3812 ,p_token1 => G_SQLCODE_TOKEN
3813 ,p_token1_value => SQLCODE
3814 ,p_token2 => G_SQLERRM_TOKEN
3815 ,p_token2_value => SQLERRM);
3816 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3817 END validate_vendor_id;
3818 -----------------------------------
3819 --6.validate record
3820 -----------------------------------
3821 PROCEDURE validate_record(
3822 x_return_status OUT NOCOPY VARCHAR2,
3823 p_asb_rec IN asb_rec_type) IS
3824
3825 --to check fin asset line id and chr id combo is valid
3826 cursor l_chrcle_csr (p_chr_id in number,
3827 p_asset_cle_id in number
3828 ) is
3829 select 'Y'
3830 from okc_k_lines_b cleb
3831 where cleb.id = p_asset_cle_id
3832 and cleb.dnz_chr_id = p_chr_id;
3833
3834 /* cklee check from validate_record_after()
3835 --to check subsidy line id is valid for fin asset line
3836 cursor l_subcle_csr (p_subsidy_cle_id in number,
3837 p_asset_cle_id in number
3838 ) is
3839 select 'Y'
3840 from okc_k_lines_b cleb
3841 where cleb.id = p_subsidy_cle_id
3842 and cleb.cle_id = p_asset_cle_id;
3843
3844 --tro check cpl_id is valid for subsidy line
3845 cursor l_cplb_csr (p_subsidy_cle_id in number,
3846 p_cpl_id in number
3847 ) is
3848 select 'Y'
3849 from okc_k_party_roles_b cplb
3850 where cplb.id = p_cpl_id
3851 and cplb.cle_id = p_subsidy_cle_id
3852 and cplb.rle_code = 'OKL_VENDOR';
3853
3854
3855 --to check that vendor is vendor on contract header
3856 --required only for lease.not for quote
3857 cursor l_vendor_csr (p_chr_id in number,
3858 p_vendor_id in number) is
3859 select 'Y'
3860 from okc_k_party_roles_b cplb
3861 where cplb.chr_id = p_chr_id
3862 and cplb.dnz_chr_id = p_chr_id
3863 and cplb.rle_code = 'OKL_VENDOR'
3864 and cplb.object1_id1 = to_char(p_vendor_id)
3865 and cplb.object1_id2 = '#'
3866 and cplb.jtot_object1_code = 'OKC_VENDOR';
3867
3868 --to check if same subsidy is already attached to the asset
3869 cursor l_subsidy_exists_csr(p_asset_cle_id in number,
3870 p_subsidy_id in number,
3871 p_subsidy_cle_id in number) is
3872 select 'Y',
3873 clet.name subsidy_name,
3874 clet_asst.name asset_number
3875 from okl_k_lines kle,
3876 okc_k_lines_tl clet,
3877 okc_k_lines_b cleb,
3878 okc_line_styles_b lseb,
3879 okc_k_lines_tl clet_asst
3880 where kle.id = cleb.id
3881 and kle.subsidy_id = p_subsidy_id
3882 and clet.id = cleb.id
3883 and clet.language = userenv('LANG')
3884 and cleb.cle_id = clet_asst.id
3885 and clet_asst.id = p_Asset_cle_id
3886 and clet_asst.language = userenv('LANG')
3887 and lseb.id = cleb.lse_id
3888 and lseb.lty_code = 'SUBSIDY'
3889 and cleb.sts_code <> 'ABANDOANED'
3890 and cleb.id <> nvl(p_subsidy_cle_id,-999);
3891
3892 --cursor to check if subsidy being attached is exclusive
3893 --and there are other subsidies attached to the contract
3894 cursor l_exclusive_csr(p_asset_cle_id in number,
3895 p_subsidy_id in number,
3896 p_subsidy_cle_id in number) is
3897 Select 'Y',
3898 subb.name
3899 from okl_subsidies_b subb
3900 where subb.id = p_subsidy_id
3901 and nvl(subb.exclusive_yn,'N') = 'Y'
3902 and exists (select '1'
3903 from okc_k_lines_b sub_cleb,
3904 okc_line_styles_b sub_lseb
3905 where sub_cleb.cle_id = p_asset_cle_id
3906 and sub_cleb.sts_code <> 'ABANDONED'
3907 and sub_cleb.id <> nvl(p_subsidy_cle_id,-999)
3908 and sub_lseb.id = sub_cleb.lse_id
3909 and sub_lseb.lty_code = 'SUBSIDY'
3910 );
3911 */
3912
3913 l_exists varchar2(1) default 'N';
3914
3915 -- l_subsidy_name okc_k_lines_tl.name%TYPE;
3916 -- l_asset_number okc_k_lines_tl.name%TYPE;
3917
3918
3919 BEGIN
3920 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3921 --check foreign key validation
3922 l_exists := 'N';
3923 open l_chrcle_csr(p_chr_id => p_asb_rec.dnz_chr_id,
3924 p_asset_cle_id => p_asb_rec.asset_cle_id);
3925 fetch l_chrcle_csr into l_exists;
3926 If l_chrcle_csr%NOTFOUND then
3927 null;
3928 End If;
3929 Close l_chrcle_csr;
3930 If l_exists = 'N' then
3931 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Contract identifier');
3932 x_return_status := OKL_API.G_RET_STS_ERROR;
3933 RAISE G_EXCEPTION_HALT_VALIDATION;
3934 End If;
3935
3936 /* cklee check from validate_record_after()
3937 If p_asb_rec.subsidy_cle_id is not null and p_asb_rec.subsidy_cle_id <> OKL_API.G_MISS_NUM then
3938 l_exists := 'N';
3939 open l_subcle_csr(p_subsidy_cle_id => p_asb_rec.subsidy_cle_id,
3940 p_asset_cle_id => p_asb_rec.asset_cle_id);
3941 fetch l_subcle_csr into l_exists;
3942 If l_subcle_csr%NOTFOUND then
3943 null;
3944 End If;
3945 Close l_subcle_csr;
3946 If l_exists = 'N' then
3947 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy line identifier');
3948 x_return_status := OKL_API.G_RET_STS_ERROR;
3949 RAISE G_EXCEPTION_HALT_VALIDATION;
3950 End If;
3951 End If;
3952
3953 If p_asb_rec.cpl_id is not null and p_asb_rec.cpl_id <> OKL_API.G_MISS_NUM then
3954 l_exists := 'N';
3955 open l_cplb_csr(p_subsidy_cle_id => p_asb_rec.subsidy_cle_id,
3956 p_cpl_id => p_asb_rec.cpl_id);
3957 fetch l_cplb_csr into l_exists;
3958 If l_cplb_csr%NOTFOUND then
3959 null;
3960 End If;
3961 Close l_cplb_csr;
3962 If l_exists = 'N' then
3963 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy party identifier');
3964 x_return_status := OKL_API.G_RET_STS_ERROR;
3965 RAISE G_EXCEPTION_HALT_VALIDATION;
3966 End If;
3967 End If;
3968
3969 --check if subsidy has not been already deined for this asset
3970 l_exists := 'N';
3971 Open l_subsidy_exists_csr (p_asset_cle_id => p_asb_rec.asset_cle_id,
3972 p_subsidy_id => p_asb_rec.subsidy_id,
3973 p_subsidy_cle_id => p_asb_rec.subsidy_cle_id);
3974 Fetch l_subsidy_exists_csr into l_exists, l_subsidy_name, l_asset_number;
3975 If l_subsidy_exists_csr%NOTFOUND then
3976 Null;
3977 End If;
3978 Close l_subsidy_exists_csr;
3979
3980 If l_exists = 'Y' then
3981 OKL_API.set_message(p_app_name => G_APP_NAME,
3982 p_msg_name => G_SUBSIDY_ALREADY_EXISTS,
3983 p_token1 => G_SUBSIDY_NAME_TOKEN,
3984 p_token1_value => l_subsidy_name,
3985 p_token2 => G_ASSET_NUMBER_TOKEN,
3986 p_token2_value => l_asset_number);
3987 x_return_status := OKL_API.G_RET_STS_ERROR; -- cklee
3988 RAISE G_EXCEPTION_HALT_VALIDATION;
3989 End If;
3990
3991 --cursor to check if exclusive subsidy has any other subsidy atched to the
3992 -- asset
3993 l_exists := 'N';
3994 Open l_exclusive_csr (p_asset_cle_id => p_asb_rec.asset_cle_id,
3995 p_subsidy_id => p_asb_rec.subsidy_id,
3996 p_subsidy_cle_id => p_asb_rec.subsidy_cle_id);
3997 Fetch l_exclusive_csr into l_exists, l_subsidy_name;
3998 If l_exclusive_csr%NOTFOUND then
3999 Null;
4000 End If;
4001 Close l_exclusive_csr;
4002
4003 If l_exists = 'Y' then
4004 OKL_API.set_message(p_app_name => G_APP_NAME,
4005 p_msg_name => G_SUBSIDY_EXCLUSIVE,
4006 p_token1 => G_SUBSIDY_NAME_TOKEN,
4007 p_token1_value => l_subsidy_name
4008 );
4009 x_return_status := OKL_API.G_RET_STS_ERROR;
4010 RAISE G_EXCEPTION_HALT_VALIDATION;
4011 End If;
4012 */
4013
4014 EXCEPTION
4015 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4016 null;
4017 WHEN OTHERS THEN
4018 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
4019 ,p_msg_name => G_UNEXPECTED_ERROR
4020 ,p_token1 => G_SQLCODE_TOKEN
4021 ,p_token1_value => SQLCODE
4022 ,p_token2 => G_SQLERRM_TOKEN
4023 ,p_token2_value => SQLERRM);
4024 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4025 END validate_record;
4026
4027 -- start cklee
4028 -----------------------------------
4029 --6.1 validate record_after
4030 -----------------------------------
4031 PROCEDURE validate_record_after(
4032 x_return_status OUT NOCOPY VARCHAR2,
4033 p_asb_rec IN asb_rec_type) IS
4034
4035 --to check subsidy line id is valid for fin asset line
4036 cursor l_subcle_csr (p_subsidy_cle_id in number,
4037 p_asset_cle_id in number
4038 ) is
4039 select 'Y'
4040 from okc_k_lines_b cleb
4041 where cleb.id = p_subsidy_cle_id
4042 and cleb.cle_id = p_asset_cle_id;
4043
4044 --to check cpl_id is valid for subsidy line
4045 cursor l_cplb_csr (p_subsidy_cle_id in number,
4046 p_cpl_id in number
4047 ) is
4048 select 'Y'
4049 from okc_k_party_roles_b cplb
4050 where cplb.id = p_cpl_id
4051 and cplb.cle_id = p_subsidy_cle_id
4052 and cplb.rle_code = 'OKL_VENDOR';
4053
4054 --to check if same subsidy is already attached to the asset
4055 cursor l_subsidy_exists_csr(p_asset_cle_id in number,
4056 p_subsidy_id in number,
4057 p_subsidy_cle_id in number) is
4058 select 'Y',
4059 clet.name subsidy_name,
4060 clet_asst.name asset_number
4061 from okl_k_lines kle,
4062 okc_k_lines_tl clet,
4063 okc_k_lines_b cleb,
4064 okc_line_styles_b lseb,
4065 okc_k_lines_tl clet_asst
4066 where kle.id = cleb.id
4067 and kle.subsidy_id = p_subsidy_id
4068 and clet.id = cleb.id
4069 and clet.language = userenv('LANG')
4070 and cleb.cle_id = clet_asst.id
4071 and clet_asst.id = p_Asset_cle_id
4072 and clet_asst.language = userenv('LANG')
4073 and lseb.id = cleb.lse_id
4074 and lseb.lty_code = 'SUBSIDY'
4075 and cleb.sts_code <> 'ABANDOANED'
4076 and cleb.id <> nvl(p_subsidy_cle_id,-999)
4077 -- cklee 03/15/2004
4078 group by clet.name, clet_asst.name
4079 having count(1) > 1;
4080 -- cklee 03/15/2004
4081
4082 --cursor to check if subsidy being attached is exclusive
4083 --and there are other subsidies attached to the contract
4084 /* cklee
4085 cursor l_exclusive_csr(p_asset_cle_id in number,
4086 p_subsidy_id in number,
4087 p_subsidy_cle_id in number) is
4088 Select 'Y',
4089 subb.name
4090 from okl_subsidies_b subb
4091 where subb.id = p_subsidy_id
4092 and nvl(subb.exclusive_yn,'N') = 'Y'
4093 and exists (select '1'
4094 from okc_k_lines_b sub_cleb,
4095 okc_line_styles_b sub_lseb
4096 where sub_cleb.cle_id = p_asset_cle_id
4097 and sub_cleb.sts_code <> 'ABANDONED'
4098 and sub_cleb.id <> nvl(p_subsidy_cle_id,-999)
4099 and sub_lseb.id = sub_cleb.lse_id
4100 and sub_lseb.lty_code = 'SUBSIDY'
4101 );
4102 */
4103 --cursor to check if subsidy attached is exclusive
4104 cursor l_exclusive_csr(p_asset_cle_id in number) is
4105 Select 'Y',
4106 subb.name
4107 from okl_subsidies_b subb,
4108 okc_k_lines_b sub_cleb,
4109 okc_line_styles_b sub_lseb,
4110 okl_k_lines sub_kleb
4111 where sub_cleb.cle_id = p_asset_cle_id
4112 and sub_cleb.sts_code <> 'ABANDONED'
4113 and sub_lseb.id = sub_cleb.lse_id
4114 and sub_lseb.lty_code = 'SUBSIDY'
4115 and sub_kleb.id = sub_cleb.id
4116 and subb.id = sub_kleb.subsidy_id
4117 and subb.exclusive_yn = 'Y';
4118
4119 -- cursor to check if multiple rows exist for
4120 -- a p_asset_cle_id ('FREE_FORM1')
4121 cursor l_exclusive_csr2(p_asset_cle_id in number) is
4122 select count(1)
4123 from okl_subsidies_b subb,
4124 okc_k_lines_b sub_cleb,
4125 okc_line_styles_b sub_lseb,
4126 okl_k_lines sub_kleb
4127 where sub_cleb.cle_id = p_asset_cle_id
4128 and sub_cleb.sts_code <> 'ABANDONED'
4129 and sub_lseb.id = sub_cleb.lse_id
4130 and sub_lseb.lty_code = 'SUBSIDY'
4131 and sub_kleb.id = sub_cleb.id
4132 and subb.id = sub_kleb.subsidy_id;
4133
4134 l_exists varchar2(1) default 'N';
4135 l_count number := 0;
4136
4137 l_subsidy_name okc_k_lines_tl.name%TYPE;
4138 l_asset_number okc_k_lines_tl.name%TYPE;
4139
4140
4141 BEGIN
4142 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4143
4144 If p_asb_rec.subsidy_cle_id is not null and p_asb_rec.subsidy_cle_id <> OKL_API.G_MISS_NUM then
4145 l_exists := 'N';
4146 open l_subcle_csr(p_subsidy_cle_id => p_asb_rec.subsidy_cle_id,
4147 p_asset_cle_id => p_asb_rec.asset_cle_id);
4148 fetch l_subcle_csr into l_exists;
4149 If l_subcle_csr%NOTFOUND then
4150 null;
4151 End If;
4152 Close l_subcle_csr;
4153 If l_exists = 'N' then
4154 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy line identifier');
4155 x_return_status := OKL_API.G_RET_STS_ERROR;
4156 RAISE G_EXCEPTION_HALT_VALIDATION;
4157 End If;
4158 End If;
4159
4160 If p_asb_rec.cpl_id is not null and p_asb_rec.cpl_id <> OKL_API.G_MISS_NUM then
4161 l_exists := 'N';
4162 open l_cplb_csr(p_subsidy_cle_id => p_asb_rec.subsidy_cle_id,
4163 p_cpl_id => p_asb_rec.cpl_id);
4164 fetch l_cplb_csr into l_exists;
4165 If l_cplb_csr%NOTFOUND then
4166 null;
4167 End If;
4168 Close l_cplb_csr;
4169 If l_exists = 'N' then
4170 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Subsidy party identifier');
4171 x_return_status := OKL_API.G_RET_STS_ERROR;
4172 RAISE G_EXCEPTION_HALT_VALIDATION;
4173 End If;
4174 End If;
4175
4176 --check if subsidy has not been already deined for this asset
4177 l_exists := 'N';
4178 Open l_subsidy_exists_csr (p_asset_cle_id => p_asb_rec.asset_cle_id,
4179 p_subsidy_id => p_asb_rec.subsidy_id,
4180 p_subsidy_cle_id => p_asb_rec.subsidy_cle_id);
4181 Fetch l_subsidy_exists_csr into l_exists, l_subsidy_name, l_asset_number;
4182
4183 If l_subsidy_exists_csr%NOTFOUND then
4184 Null;
4185 End If;
4186 Close l_subsidy_exists_csr;
4187
4188 If l_exists = 'Y' then
4189 OKL_API.set_message(p_app_name => G_APP_NAME,
4190 p_msg_name => G_SUBSIDY_ALREADY_EXISTS,
4191 p_token1 => G_SUBSIDY_NAME_TOKEN,
4192 p_token1_value => l_subsidy_name,
4193 p_token2 => G_ASSET_NUMBER_TOKEN,
4194 p_token2_value => l_asset_number);
4195 x_return_status := OKL_API.G_RET_STS_ERROR; -- cklee
4196 RAISE G_EXCEPTION_HALT_VALIDATION;
4197 End If;
4198
4199 --cursor to check if exclusive subsidy has any other subsidy atched to the
4200 -- asset
4201 l_exists := 'N';
4202 /* cklee
4203 Open l_exclusive_csr (p_asset_cle_id => p_asb_rec.asset_cle_id,
4204 p_subsidy_id => p_asb_rec.subsidy_id,
4205 p_subsidy_cle_id => p_asb_rec.subsidy_cle_id);
4206 */
4207 Open l_exclusive_csr (p_asset_cle_id => p_asb_rec.asset_cle_id);
4208 Fetch l_exclusive_csr into l_exists, l_subsidy_name;
4209
4210 If l_exclusive_csr%NOTFOUND then
4211 Null;
4212 End If;
4213 Close l_exclusive_csr;
4214
4215 Open l_exclusive_csr2 (p_asset_cle_id => p_asb_rec.asset_cle_id);
4216 Fetch l_exclusive_csr2 into l_count;
4217
4218 If l_exclusive_csr2%NOTFOUND then
4219 Null;
4220 End If;
4221 Close l_exclusive_csr2;
4222
4223 If (l_exists = 'Y' and l_count > 1) then
4224 OKL_API.set_message(p_app_name => G_APP_NAME,
4225 p_msg_name => G_SUBSIDY_EXCLUSIVE,
4226 p_token1 => G_SUBSIDY_NAME_TOKEN,
4227 p_token1_value => l_subsidy_name
4228 );
4229 x_return_status := OKL_API.G_RET_STS_ERROR; -- cklee
4230 RAISE G_EXCEPTION_HALT_VALIDATION;
4231 End If;
4232
4233 EXCEPTION
4234 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4235 null;
4236 WHEN OTHERS THEN
4237 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
4238 ,p_msg_name => G_UNEXPECTED_ERROR
4239 ,p_token1 => G_SQLCODE_TOKEN
4240 ,p_token1_value => SQLCODE
4241 ,p_token2 => G_SQLERRM_TOKEN
4242 ,p_token2_value => SQLERRM);
4243 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4244 END validate_record_after;
4245 -- end cklee
4246 --------------------------------------------------------------------------------
4247 --Name : validate_asset_subsidy
4248 --Creation : 20-Aug-2003
4249 --Purpose : To validate asset subsidy record along with associted party role
4250 --------------------------------------------------------------------------------
4251 PROCEDURE validate_asset_subsidy(
4252 p_api_version IN NUMBER,
4253 p_init_msg_list IN VARCHAR2,
4254 x_return_status OUT NOCOPY VARCHAR2,
4255 x_msg_count OUT NOCOPY NUMBER,
4256 x_msg_data OUT NOCOPY VARCHAR2,
4257 p_asb_rec IN asb_rec_type) is
4258
4259 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4260 l_api_name CONSTANT varchar2(30) := 'VALIDATE_ASSET_SUBSIDY';
4261 l_api_version CONSTANT NUMBER := 1.0;
4262
4263 l_asb_rec asb_rec_type;
4264 l_highest_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4265
4266 l_applicable VARCHAR2(1);
4267
4268 cursor l_clet_csr(p_cle_id in number) is
4269 select clet.name
4270 from okc_k_lines_tl clet
4271 where clet.id = p_cle_id
4272 and clet.language = userenv('LANG');
4273
4274 l_asset_number okc_k_lines_tl.name%TYPE;
4275
4276 cursor l_sub_csr(p_subsidy_id in number) is
4277 select subb.name
4278 from okl_subsidies_b subb
4279 where id = p_subsidy_id;
4280
4281 l_subsidy_name okl_subsidies_b.name%TYPE;
4282
4283 begin
4284 -----
4285 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4286 -- Call start_activity to create savepoint, check compatibility
4287 -- and initialize message list
4288 x_return_status := OKL_API.START_ACTIVITY (
4289 l_api_name
4290 ,p_init_msg_list
4291 ,'_PVT'
4292 ,x_return_status);
4293 -- Check if activity started successfully
4294 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4295 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4296 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4297 RAISE OKL_API.G_EXCEPTION_ERROR;
4298 END IF;
4299
4300 l_asb_rec := p_asb_rec;
4301 -----------------------------------------------
4302 --call validation routines
4303 -----------------------------------------------
4304
4305 l_highest_return_status := x_return_status;
4306 ---------------------------------------
4307 --1.validate subsidy_id
4308 ---------------------------------------
4309 /* cklee 1/21/04 call from validate_asset_subsidy_after()
4310 validate_subsidy_id(x_return_status,l_asb_rec.subsidy_id);
4311 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4312 l_highest_return_status := x_return_status;
4313 End If;
4314 */
4315 ---------------------------------------
4316 --2.validate subsidy_cle_id
4317 ---------------------------------------
4318 validate_subsidy_cle_id(x_return_status,l_asb_rec.subsidy_cle_id);
4319 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4320 l_highest_return_status := x_return_status;
4321 End If;
4322 ---------------------------------------
4323 --3.validate dnz_chr_id
4324 ---------------------------------------
4325 validate_dnz_chr_id(x_return_status,l_asb_rec.dnz_chr_id);
4326 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4327 l_highest_return_status := x_return_status;
4328 End If;
4329 ---------------------------------------
4330 --4.validate asset_cle_id
4331 ---------------------------------------
4332 validate_asset_cle_id(x_return_status,l_asb_rec.asset_cle_id);
4333 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4334 l_highest_return_status := x_return_status;
4335 End If;
4336 ---------------------------------------
4337 --5.validate vendor_id
4338 ---------------------------------------
4339 validate_vendor_id(x_return_status,l_asb_rec.vendor_id);
4340 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4341 l_highest_return_status := x_return_status;
4342 End If;
4343
4344 x_return_status := l_highest_return_status;
4345 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4346 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4347 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4348 RAISE OKL_API.G_EXCEPTION_ERROR;
4349 END IF;
4350
4351 ---------------------------------------
4352 --5.validate record
4353 ---------------------------------------
4354 validate_record(x_return_status,l_asb_rec);
4355
4356 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4358 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4359 RAISE OKL_API.G_EXCEPTION_ERROR;
4360 END IF;
4361
4362 ---------------------------------------
4363 --6.validate subsidy applicability
4364 ---------------------------------------
4365 l_applicable := validate_subsidy_applicability(p_subsidy_id => l_asb_rec.subsidy_id,
4366 p_asset_cle_id => l_asb_rec.asset_cle_id);
4367 If l_applicable = 'N' then
4368
4369 open l_clet_csr(p_cle_id => l_asb_rec.asset_cle_id);
4370 fetch l_clet_csr into l_asset_number;
4371 if l_clet_csr%NOTFOUND then
4372 null;
4373 end if;
4374 close l_clet_csr;
4375
4376 open l_sub_csr(p_subsidy_id => l_asb_rec.subsidy_id);
4377 fetch l_sub_csr into l_subsidy_name;
4378 if l_sub_csr%NOTFOUND then
4379 null;
4380 end if;
4381 close l_sub_csr;
4382
4383 --raise error
4384 OKL_API.set_message(p_app_name => G_APP_NAME,
4385 p_msg_name => G_SUBSIDY_NOT_APPLICABLE,
4386 p_token1 => G_SUBSIDY_TOKEN,
4387 p_token1_value => l_subsidy_name,
4388 p_token2 => G_ASSET_NUMBER_TOKEN,
4389 p_token2_value => l_asset_number
4390 );
4391 x_return_status := OKL_API.G_RET_STS_ERROR;
4392
4393 RAISE OKL_API.G_EXCEPTION_ERROR;
4394 End If;
4395
4396 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
4397 EXCEPTION
4398 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4399 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4400 l_api_name,
4401 G_PKG_NAME,
4402 'OKL_API.G_RET_STS_ERROR',
4403 x_msg_count,
4404 x_msg_data,
4405 '_PVT');
4406 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4407 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4408 l_api_name,
4409 G_PKG_NAME,
4410 'OKL_API.G_RET_STS_UNEXP_ERROR',
4411 x_msg_count,
4412 x_msg_data,
4413 '_PVT');
4414 WHEN OTHERS THEN
4415 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4416 l_api_name,
4417 G_PKG_NAME,
4418 'OTHERS',
4419 x_msg_count,
4420 x_msg_data,
4421 '_PVT');
4422 End validate_asset_subsidy;
4423 -- start : cklee
4424 --------------------------------------------------------------------------------
4425 --Name : validate_asset_subsidy_after
4426 --Creation : 22-Jan-2004
4427 --Purpose : To validate asset subsidy record after record has been created
4428 -- in this transaction
4429 --------------------------------------------------------------------------------
4430 PROCEDURE validate_asset_subsidy_after(
4431 p_api_version IN NUMBER,
4432 p_init_msg_list IN VARCHAR2,
4433 x_return_status OUT NOCOPY VARCHAR2,
4434 x_msg_count OUT NOCOPY NUMBER,
4435 x_msg_data OUT NOCOPY VARCHAR2,
4436 p_asb_rec IN asb_rec_type) is
4437
4438 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4439 l_api_name CONSTANT varchar2(30) := 'VALIDATE_ASSET_SUBSIDY';
4440 l_api_version CONSTANT NUMBER := 1.0;
4441
4442 l_asb_rec asb_rec_type;
4443 l_highest_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4444
4445 begin
4446 -----
4447 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4448 -- Call start_activity to create savepoint, check compatibility
4449 -- and initialize message list
4450 x_return_status := OKL_API.START_ACTIVITY (
4451 l_api_name
4452 ,p_init_msg_list
4453 ,'_PVT'
4454 ,x_return_status);
4455 -- Check if activity started successfully
4456 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4457 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4458 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4459 RAISE OKL_API.G_EXCEPTION_ERROR;
4460 END IF;
4461
4462 l_asb_rec := p_asb_rec;
4463 -----------------------------------------------
4464 --call validation routines
4465 -----------------------------------------------
4466
4467 l_highest_return_status := x_return_status;
4468 /*comment out by cklee 03/15/2004
4469 ---------------------------------------
4470 --2.validate subsidy_cle_id
4471 ---------------------------------------
4472 validate_subsidy_cle_id(x_return_status,l_asb_rec.subsidy_cle_id);
4473 If x_return_status <> OKL_API.G_RET_STS_SUCCESS then
4474 l_highest_return_status := x_return_status;
4475 End If;
4476
4477 x_return_status := l_highest_return_status;
4478 */
4479 ---------------------------------------
4480 --5.validate record
4481 ---------------------------------------
4482 validate_record_after(x_return_status,l_asb_rec);
4483
4484 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4485 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4486 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4487 RAISE OKL_API.G_EXCEPTION_ERROR;
4488 END IF;
4489
4490 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
4491
4492 EXCEPTION
4493 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4494 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4495 l_api_name,
4496 G_PKG_NAME,
4497 'OKL_API.G_RET_STS_ERROR',
4498 x_msg_count,
4499 x_msg_data,
4500 '_PVT');
4501 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4502 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4503 l_api_name,
4504 G_PKG_NAME,
4505 'OKL_API.G_RET_STS_UNEXP_ERROR',
4506 x_msg_count,
4507 x_msg_data,
4508 '_PVT');
4509 WHEN OTHERS THEN
4510 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4511 l_api_name,
4512 G_PKG_NAME,
4513 'OTHERS',
4514 x_msg_count,
4515 x_msg_data,
4516 '_PVT');
4517 End validate_asset_subsidy_after;
4518
4519 -- end : cklee
4520 --------------------------------------------------------------------------------
4521 --Name : create_asset_subsidy
4522 --Creation : 20-Aug-2003
4523 --Purpose : To create asset subsidy record along with associted party role
4524 --------------------------------------------------------------------------------
4525 PROCEDURE create_asset_subsidy(
4526 p_api_version IN NUMBER,
4527 p_init_msg_list IN VARCHAR2,
4528 x_return_status OUT NOCOPY VARCHAR2,
4529 x_msg_count OUT NOCOPY NUMBER,
4530 x_msg_data OUT NOCOPY VARCHAR2,
4531 p_asb_rec IN asb_rec_type,
4532 x_asb_rec OUT NOCOPY asb_rec_type) is
4533
4534 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4535 l_api_name CONSTANT varchar2(30) := 'CREATE_ASSET_SUBSIDY';
4536 l_api_version CONSTANT NUMBER := 1.0;
4537
4538 l_asb_rec asb_rec_type;
4539 lx_calc_asb_rec asb_rec_type;
4540
4541 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
4542 l_klev_rec okl_contract_pub.klev_rec_type;
4543 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4544
4545 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
4546 lx_klev_rec okl_contract_pub.klev_rec_type;
4547 lx_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4548
4549 lx_def_clev_rec okl_okc_migration_pvt.clev_rec_type;
4550 lx_def_klev_rec okl_contract_pub.klev_rec_type;
4551 lx_def_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4552
4553 --cursor to get vendor name
4554 cursor l_vendor_csr(p_vendor_id in number) is
4555 select pov.vendor_name
4556 from po_vendors pov
4557 where vendor_id = p_vendor_id;
4558
4559 l_vendor_name po_vendors.vendor_name%TYPE;
4560
4561 --Bug# 4558486
4562 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
4563 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
4564
4565 Begin
4566 ----
4567 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4568 -- Call start_activity to create savepoint, check compatibility
4569 -- and initialize message list
4570 x_return_status := OKL_API.START_ACTIVITY (
4571 l_api_name
4572 ,p_init_msg_list
4573 ,'_PVT'
4574 ,x_return_status);
4575 -- Check if activity started successfully
4576 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4577 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4578 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4579 RAISE OKL_API.G_EXCEPTION_ERROR;
4580 END IF;
4581
4582 l_asb_rec := p_asb_rec;
4583
4584 ---------------------------------------------------------
4585 --call local procedure to fill up the defaults
4586 ----------------------------------------------------------
4587 Initialize_records(x_return_status => x_return_status,
4588 p_asb_rec => l_asb_rec,
4589 x_clev_rec => lx_def_clev_rec,
4590 x_klev_rec => lx_def_klev_rec,
4591 x_cplv_rec => lx_def_cplv_rec);
4592
4593 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4594 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4595 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4596 RAISE OKL_API.G_EXCEPTION_ERROR;
4597 END IF;
4598
4599 l_clev_rec := lx_def_clev_rec;
4600 l_klev_rec := lx_def_klev_rec;
4601 l_cplv_rec := lx_def_cplv_rec;
4602
4603 ----------------------------------------------------------------------
4604 --fill up the defaults for validation
4605 ----------------------------------------------------------------------
4606 l_asb_rec.subsidy_id := l_klev_rec.subsidy_id;
4607 l_asb_rec.name := l_clev_rec.name;
4608 l_asb_rec.description := l_clev_rec.item_description;
4609 l_asb_rec.amount := l_klev_rec.amount;
4610 l_asb_rec.subsidy_override_amount := l_klev_rec.subsidy_override_amount;
4611 l_asb_rec.dnz_chr_id := l_clev_rec.dnz_chr_id;
4612 l_asb_rec.asset_cle_id := l_clev_rec.cle_id;
4613 If (l_cplv_rec.object1_id1 is not null) and (l_cplv_rec.object1_id1 <> OKL_API.G_MISS_CHAR) then
4614 l_asb_rec.vendor_id := to_number(l_cplv_rec.object1_id1);
4615 End If;
4616
4617 --Bug# 4959361
4618 OKL_LLA_UTIL_PVT.check_line_update_allowed
4619 (p_api_version => p_api_version,
4620 p_init_msg_list => p_init_msg_list,
4621 x_return_status => x_return_status,
4622 x_msg_count => x_msg_count,
4623 x_msg_data => x_msg_data,
4624 p_cle_id => l_asb_rec.asset_cle_id);
4625
4626 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4627 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4628 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4629 RAISE OKL_API.G_EXCEPTION_ERROR;
4630 END IF;
4631 --Bug# 4959361
4632
4633 ----------------------------------------------------
4634 --validate the subsidy asset record
4635 ----------------------------------------------------
4636 validate_asset_subsidy(
4637 p_api_version => p_api_version,
4638 p_init_msg_list => p_init_msg_list,
4639 x_return_status => x_return_status,
4640 x_msg_count => x_msg_count,
4641 x_msg_data => x_msg_data,
4642 p_asb_rec => l_asb_rec);
4643
4644 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4645 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4646 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4647 RAISE OKL_API.G_EXCEPTION_ERROR;
4648 END IF;
4649
4650 ---------------------------------------------------------
4651 --call complex API to create line instance
4652 ----------------------------------------------------------
4653 --dbms_output.put_line(to_char(l_klev_rec.amount));
4654
4655 OKL_CONTRACT_PUB.create_contract_line(
4656 p_api_version => p_api_version,
4657 p_init_msg_list => p_init_msg_list,
4658 x_return_status => x_return_status,
4659 x_msg_count => x_msg_count,
4660 x_msg_data => x_msg_data,
4661 p_clev_rec => l_clev_rec,
4662 p_klev_rec => l_klev_rec,
4663 x_clev_rec => lx_clev_rec,
4664 x_klev_rec => lx_klev_rec);
4665
4666 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4667 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4668 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4669 RAISE OKL_API.G_EXCEPTION_ERROR;
4670 END IF;
4671
4672 /* comment out by cklee 03/15/2004: move to l_asb_tbl level
4673 -- start cklee 1/22/04
4674 ----------------------------------------------------
4675 --validate the subsidy asset record after record has
4676 -- been created in this transaction
4677 ----------------------------------------------------
4678 -- subsidy line ID
4679 l_asb_rec.subsidy_cle_id := lx_clev_rec.id;
4680
4681 validate_asset_subsidy_after(
4682 p_api_version => p_api_version,
4683 p_init_msg_list => p_init_msg_list,
4684 x_return_status => x_return_status,
4685 x_msg_count => x_msg_count,
4686 x_msg_data => x_msg_data,
4687 p_asb_rec => l_asb_rec);
4688
4689 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4690 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4691 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4692 RAISE OKL_API.G_EXCEPTION_ERROR;
4693 END IF;
4694 -- end cklee 1/22/04
4695 */
4696
4697 ---------------------------------------------------------
4698 --call complex API to create party role instance
4699 ----------------------------------------------------------
4700 If (l_cplv_rec.object1_id1 is not null) and
4701 (l_cplv_rec.object1_id1 <> OKL_API.G_MISS_CHAR) then
4702 l_cplv_rec.cle_id := lx_clev_rec.id;
4703
4704 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
4705 -- to create records in tables
4706 -- okc_k_party_roles_b and okl_k_party_roles
4707 /*
4708 okl_okc_migration_pvt.create_k_party_role(
4709 p_api_version => p_api_version,
4710 p_init_msg_list => p_init_msg_list,
4711 x_return_status => x_return_status,
4712 x_msg_count => x_msg_count,
4713 x_msg_data => x_msg_data,
4714 p_cplv_rec => l_cplv_rec,
4715 x_cplv_rec => lx_cplv_rec);
4716 */
4717 okl_k_party_roles_pvt.create_k_party_role(
4718 p_api_version => p_api_version,
4719 p_init_msg_list => p_init_msg_list,
4720 x_return_status => x_return_status,
4721 x_msg_count => x_msg_count,
4722 x_msg_data => x_msg_data,
4723 p_cplv_rec => l_cplv_rec,
4724 x_cplv_rec => lx_cplv_rec,
4725 p_kplv_rec => l_kplv_rec,
4726 x_kplv_rec => lx_kplv_rec);
4727
4728 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4729 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4730 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4731 RAISE OKL_API.G_EXCEPTION_ERROR;
4732 END IF;
4733 End If;
4734
4735 --reinitialize asset subsidy record with final values for output
4736 l_asb_rec.subsidy_id := lx_klev_rec.subsidy_id;
4737 l_asb_rec.subsidy_cle_id := lx_clev_rec.id;
4738 l_asb_rec.name := lx_clev_rec.name;
4739 l_asb_rec.description := lx_clev_rec.item_description;
4740 l_asb_rec.amount := lx_klev_rec.amount;
4741 l_asb_rec.subsidy_override_amount := lx_klev_rec.subsidy_override_amount;
4742 l_asb_rec.dnz_chr_id := lx_clev_rec.dnz_chr_id;
4743 l_asb_rec.asset_cle_id := lx_clev_rec.cle_id;
4744
4745 IF lx_cplv_rec.object1_id1 is not NULL AND lx_cplv_rec.object1_id1 <> OKL_API.G_MISS_NUM then
4746 l_asb_rec.cpl_id := lx_cplv_rec.id;
4747 l_asb_rec.vendor_id := to_number(lx_cplv_rec.object1_id1);
4748 --get vendor name
4749 open l_vendor_csr(p_vendor_id => l_asb_rec.vendor_id);
4750 fetch l_vendor_csr into l_vendor_name;
4751 If l_vendor_csr%NOTFOUND then
4752 null;
4753 else
4754 l_asb_rec.vendor_name := l_vendor_name;
4755 end if;
4756 close l_vendor_csr;
4757 End If;
4758
4759
4760 ---------------------------------------------------------
4761 --Call API to calculate asset subsidy amounts
4762 ----------------------------------------------------------
4763 calculate_asset_subsidy(
4764 p_api_version => p_api_version,
4765 p_init_msg_list => p_init_msg_list,
4766 x_return_status => x_return_status,
4767 x_msg_count => x_msg_count,
4768 x_msg_data => x_msg_data,
4769 p_asb_rec => l_asb_rec,
4770 x_asb_rec => lx_calc_asb_rec);
4771
4772 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4773 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4774 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4775 RAISE OKL_API.G_EXCEPTION_ERROR;
4776 END IF;
4777
4778 --dbms_output.put_line(to_char(lx_calc_asb_rec.amount));
4779 l_asb_rec := lx_calc_asb_rec;
4780 --dbms_output.put_line(to_char(l_asb_rec.amount));
4781
4782 ---------------------------------------------------------
4783 --Call API to recalculate asset oec and cap amounts
4784 ----------------------------------------------------------
4785 --included in calculate asset subsidy
4786 ------------------------------------------------------------
4787 --assign values to out record
4788 ------------------------------------------------------------
4789 x_asb_rec := l_asb_rec;
4790
4791 /*
4792 * sjalasut: aug 25, 04 added code to enable business event. BEGIN
4793 */
4794 IF(OKL_LLA_UTIL_PVT.is_lease_contract(lx_clev_rec.dnz_chr_id)= OKL_API.G_TRUE)THEN
4795 raise_business_event(p_api_version => p_api_version,
4796 p_init_msg_list => p_init_msg_list,
4797 p_chr_id => lx_clev_rec.dnz_chr_id,
4798 p_asset_id => lx_clev_rec.cle_id,
4799 p_subsidy_id => x_asb_rec.subsidy_id,
4800 p_event_name => G_WF_EVT_ASSET_SUBSIDY_CRTD,
4801 x_return_status => x_return_status,
4802 x_msg_count => x_msg_count,
4803 x_msg_data => x_msg_data
4804 );
4805 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4806 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4807 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4808 RAISE OKL_API.G_EXCEPTION_ERROR;
4809 END IF;
4810 END IF;
4811 /*
4812 * sjalasut: aug 25, 04 added code to enable business event. END
4813 */
4814
4815
4816 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
4817 EXCEPTION
4818 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4819 If l_vendor_csr%ISOPEN then
4820 close l_vendor_csr;
4821 End If;
4822 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4823 l_api_name,
4824 G_PKG_NAME,
4825 'OKL_API.G_RET_STS_ERROR',
4826 x_msg_count,
4827 x_msg_data,
4828 '_PVT');
4829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4830 If l_vendor_csr%ISOPEN then
4831 close l_vendor_csr;
4832 End If;
4833 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4834 l_api_name,
4835 G_PKG_NAME,
4836 'OKL_API.G_RET_STS_UNEXP_ERROR',
4837 x_msg_count,
4838 x_msg_data,
4839 '_PVT');
4840 WHEN OTHERS THEN
4841 If l_vendor_csr%ISOPEN then
4842 close l_vendor_csr;
4843 End If;
4844 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4845 l_api_name,
4846 G_PKG_NAME,
4847 'OTHERS',
4848 x_msg_count,
4849 x_msg_data,
4850 '_PVT');
4851 End Create_asset_subsidy;
4852 --------------------------------------------------------------------------------
4853 --Name : create_asset_subsidy
4854 --Creation : 21-Aug-2003
4855 --Purpose : To create asset subsidy record along with associted party role
4856 --------------------------------------------------------------------------------
4857 PROCEDURE create_asset_subsidy(
4858 p_api_version IN NUMBER,
4859 p_init_msg_list IN VARCHAR2,
4860 x_return_status OUT NOCOPY VARCHAR2,
4861 x_msg_count OUT NOCOPY NUMBER,
4862 x_msg_data OUT NOCOPY VARCHAR2,
4863 p_asb_tbl IN asb_tbl_type,
4864 x_asb_tbl OUT NOCOPY asb_tbl_type) is
4865
4866 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4867 l_api_name CONSTANT varchar2(30) := 'CREATE_ASSET_SUBSIDY';
4868 l_api_version CONSTANT NUMBER := 1.0;
4869 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4870 i NUMBER := 0;
4871
4872 l_asb_tbl asb_tbl_type;
4873 Begin
4874 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4875
4876 l_asb_tbl := p_asb_tbl;
4877 If l_asb_tbl.COUNT > 0 then
4878 i := l_asb_tbl.FIRST;
4879 LOOP
4880 create_asset_subsidy(
4881 p_api_version => p_api_version,
4882 p_init_msg_list => p_init_msg_list,
4883 x_return_status => x_return_status,
4884 x_msg_count => x_msg_count,
4885 x_msg_data => x_msg_data,
4886 p_asb_rec => l_asb_tbl(i),
4887 x_asb_rec => x_asb_tbl(i));
4888 /***-- start cklee 11/15/04
4889 -- store the highest degree of error
4890 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
4891 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
4892 l_overall_status := x_return_status;
4893 End If;
4894 End If;
4895 -- end cklee 11/15/04
4896 */
4897 -- start cklee 11/15/04
4898 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4899 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4900 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4901 RAISE OKL_API.G_EXCEPTION_ERROR;
4902 END IF;
4903 -- end cklee 11/15/04
4904
4905 EXIT WHEN (i = l_asb_tbl.LAST);
4906 i := l_asb_tbl.NEXT(i);
4907 END LOOP;
4908 -- return overall status
4909 -- start cklee 11/15/04
4910 --cklee x_return_status := l_overall_status;
4911 -- end cklee 11/15/04
4912
4913 -- start cklee 03/15/04
4914 ----------------------------------------------------
4915 --validate the subsidy asset record after records have
4916 -- been created in this transaction
4917 ----------------------------------------------------
4918 validate_asset_subsidy_after(
4919 p_api_version => p_api_version,
4920 p_init_msg_list => p_init_msg_list,
4921 x_return_status => x_return_status,
4922 x_msg_count => x_msg_count,
4923 x_msg_data => x_msg_data,
4924 p_asb_rec => l_asb_tbl(l_asb_tbl.FIRST));
4925
4926 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4927 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4928 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4929 RAISE OKL_API.G_EXCEPTION_ERROR;
4930 END IF;
4931 -- end cklee 03/15/04
4932
4933 End If;
4934 EXCEPTION
4935 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4936 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4937 l_api_name,
4938 G_PKG_NAME,
4939 'OKL_API.G_RET_STS_ERROR',
4940 x_msg_count,
4941 x_msg_data,
4942 '_PVT');
4943 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4944 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4945 l_api_name,
4946 G_PKG_NAME,
4947 'OKL_API.G_RET_STS_UNEXP_ERROR',
4948 x_msg_count,
4949 x_msg_data,
4950 '_PVT');
4951 WHEN OTHERS THEN
4952 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4953 l_api_name,
4954 G_PKG_NAME,
4955 'OTHERS',
4956 x_msg_count,
4957 x_msg_data,
4958 '_PVT');
4959 End Create_asset_subsidy;
4960 --------------------------------------------------------------------------------
4961 --Name : update_asset_subsidy
4962 --Creation : 21-Aug-2003
4963 --Purpose : To update asset subsidy record along with associted party role
4964 --------------------------------------------------------------------------------
4965 PROCEDURE update_asset_subsidy(
4966 p_api_version IN NUMBER,
4967 p_init_msg_list IN VARCHAR2,
4968 x_return_status OUT NOCOPY VARCHAR2,
4969 x_msg_count OUT NOCOPY NUMBER,
4970 x_msg_data OUT NOCOPY VARCHAR2,
4971 p_asb_rec IN asb_rec_type,
4972 x_asb_rec OUT NOCOPY asb_rec_type) is
4973
4974 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
4975 l_api_name CONSTANT varchar2(30) := 'UPDATE_ASSET_SUBSIDY';
4976 l_api_version CONSTANT NUMBER := 1.0;
4977
4978 l_asb_rec asb_rec_type;
4979 l_db_asb_rec asb_rec_type;
4980 lx_calc_asb_rec asb_rec_type;
4981
4982
4983 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
4984 l_klev_rec okl_contract_pub.klev_rec_type;
4985 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4986
4987 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
4988 lx_klev_rec okl_contract_pub.klev_rec_type;
4989 lx_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4990
4991 lx_def_clev_rec okl_okc_migration_pvt.clev_rec_type;
4992 lx_def_klev_rec okl_contract_pub.klev_rec_type;
4993 lx_def_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
4994
4995 l_row_notfound BOOLEAN := TRUE;
4996
4997 --cursor to get vendor name
4998 cursor l_vendor_csr(p_vendor_id in number) is
4999 select pov.vendor_name
5000 from po_vendors pov
5001 where vendor_id = p_vendor_id;
5002
5003 l_vendor_name po_vendors.vendor_name%TYPE;
5004
5005 --cursor to fetch party refund details record
5006 cursor l_ppyd_csr (p_cpl_id in number) is
5007 select ppyd.id
5008 from okl_party_payment_dtls ppyd
5009 where ppyd.cpl_id = p_cpl_id;
5010
5011 l_ppyd_id number default null;
5012
5013 l_ppydv_rec OKL_PYD_PVT.ppydv_rec_type;
5014
5015 l_rbk_cpy varchar2(1) default 'N';
5016
5017 --Bug# 4558486
5018 l_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5019 lx_kplv_rec okl_k_party_roles_pvt.kplv_rec_type;
5020
5021 begin
5022 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5023 -- Call start_activity to create savepoint, check compatibility
5024 -- and initialize message list
5025 x_return_status := OKL_API.START_ACTIVITY (
5026 l_api_name
5027 ,p_init_msg_list
5028 ,'_PVT'
5029 ,x_return_status);
5030 -- Check if activity started successfully
5031 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5032 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5033 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5034 RAISE OKL_API.G_EXCEPTION_ERROR;
5035 END IF;
5036
5037 --Bug# 4959361
5038 OKL_LLA_UTIL_PVT.check_line_update_allowed
5039 (p_api_version => p_api_version,
5040 p_init_msg_list => p_init_msg_list,
5041 x_return_status => x_return_status,
5042 x_msg_count => x_msg_count,
5043 x_msg_data => x_msg_data,
5044 p_cle_id => p_asb_rec.subsidy_cle_id);
5045
5046 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5047 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5048 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5049 RAISE OKL_API.G_EXCEPTION_ERROR;
5050 END IF;
5051 --Bug# 4959361
5052
5053 l_asb_rec := p_asb_rec;
5054 l_db_asb_rec := get_rec(l_asb_rec,l_row_notfound);
5055
5056 IF (l_row_notfound) THEN
5057 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5058 END IF;
5059
5060 -------------------------------------------------------------------
5061 --Begin delete the party payment details if vendor changes
5062 -------------------------------------------------------------------
5063 If nvl(l_asb_rec.vendor_id,-1) <> OKL_API.G_MISS_NUM and
5064 nvl(l_asb_rec.vendor_id,-1) <> nvl(l_db_asb_rec.vendor_id,-1) Then
5065
5066 ----------------------------------------
5067 --check if it is a rebook copy contract
5068 --if yes then do not allow change in vendor
5069 -------------------------------------------
5070 l_rbk_cpy := is_rebook_copy(p_chr_id => l_db_asb_rec.dnz_chr_id);
5071 If l_rbk_cpy = 'Y' then
5072 If l_asb_rec.vendor_id <> l_db_asb_rec.vendor_id then
5073 okl_api.set_message(p_app_name => G_APP_NAME,
5074 p_msg_name => G_PARTY_UPDATE_INVALID,
5075 p_token1 => G_SUBSIDY_TOKEN,
5076 p_token1_value => l_db_asb_rec.name
5077 );
5078 x_return_status := OKL_API.G_RET_STS_ERROR;
5079 Raise OKL_API.G_EXCEPTION_ERROR;
5080 End If;
5081 End If;
5082 -----------------------------------------
5083 --End of rebook check
5084 -----------------------------------------
5085
5086 If l_db_asb_rec.cpl_id is not Null and
5087 l_db_asb_rec.cpl_id <> OKL_API.G_MISS_NUM Then
5088 --fetch if any party refund details record
5089 open l_ppyd_csr(p_cpl_id => l_db_asb_rec.cpl_id);
5090 fetch l_ppyd_csr into l_ppyd_id;
5091 If l_ppyd_csr%NOTFOUND then
5092 null;
5093 End If;
5094 Close l_ppyd_csr;
5095
5096 If l_ppyd_id is not null Then
5097
5098 l_ppydv_rec.id := l_ppyd_id;
5099
5100 OKL_PYD_PVT.delete_row(
5101 p_api_version => p_api_version,
5102 p_init_msg_list => p_init_msg_list,
5103 x_return_status => x_return_status,
5104 x_msg_count => x_msg_count,
5105 x_msg_data => x_msg_data,
5106 p_ppydv_rec => l_ppydv_rec);
5107
5108 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5109 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5110 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5111 RAISE OKL_API.G_EXCEPTION_ERROR;
5112 END IF;
5113 End If;
5114 End If;
5115 End If;
5116 -------------------------------------------------------------------
5117 --End delete the party payment details if vendor changes
5118 -------------------------------------------------------------------
5119
5120
5121 --dbms_output.put_line(to_char(l_asb_rec.amount));
5122 ---------------------------------------------------------
5123 --call local procedure to fill up the defaults
5124 ----------------------------------------------------------
5125 fill_up_defaults (x_return_status => x_return_status,
5126 p_asb_rec => l_asb_rec,
5127 p_db_asb_rec => l_db_asb_rec,
5128 x_clev_rec => lx_def_clev_rec,
5129 x_klev_rec => lx_def_klev_rec,
5130 x_cplv_rec => lx_def_cplv_rec);
5131
5132 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5134 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5135 RAISE OKL_API.G_EXCEPTION_ERROR;
5136 END IF;
5137
5138 l_clev_rec := lx_def_clev_rec;
5139 l_klev_rec := lx_def_klev_rec;
5140 l_cplv_rec := lx_def_cplv_rec;
5141
5142 ------------------------------------------------------------
5143 --fill up l_asb_rec for validation
5144 ------------------------------------------------------------
5145 If l_klev_rec.subsidy_id <> OKL_API.G_MISS_NUM then
5146 l_asb_rec.subsidy_id := l_klev_rec.subsidy_id;
5147 Else
5148 l_asb_rec.subsidy_id := l_db_asb_rec.subsidy_id;
5149 End If;
5150 l_asb_rec.subsidy_cle_id := l_db_asb_rec.subsidy_cle_id;
5151 l_asb_rec.name := l_clev_rec.name;
5152 l_asb_rec.description := l_clev_rec.item_description;
5153 l_asb_rec.amount := l_klev_rec.amount;
5154 l_asb_rec.subsidy_override_amount := l_klev_rec.subsidy_override_amount;
5155 l_asb_rec.dnz_chr_id := l_db_asb_rec.dnz_chr_id;
5156 l_asb_rec.asset_cle_id := l_db_asb_rec.asset_cle_id;
5157 If l_cplv_rec.id <> OKL_API.G_MISS_NUM then
5158 l_asb_rec.cpl_id := l_cplv_rec.id;
5159 Else
5160 l_asb_rec.cpl_id := l_db_asb_rec.cpl_id;
5161 End If;
5162 If (l_cplv_rec.object1_id1 is not null) and (l_cplv_rec.object1_id1 <> OKL_API.G_MISS_CHAR) then
5163 l_asb_rec.vendor_id := to_number(l_cplv_rec.object1_id1);
5164 --get vendor name
5165 open l_vendor_csr(p_vendor_id => l_asb_rec.vendor_id);
5166 fetch l_vendor_csr into l_vendor_name;
5167 If l_vendor_csr%NOTFOUND then
5168 null;
5169 else
5170 l_asb_rec.vendor_name := l_vendor_name;
5171 end if;
5172 close l_vendor_csr;
5173 Else
5174 l_asb_rec.vendor_id := l_db_asb_rec.vendor_id;
5175 l_asb_rec.vendor_name := l_db_asb_rec.vendor_name;
5176 End If;
5177
5178 ----------------------------------------------------
5179 --validate the subsidy asset record
5180 ----------------------------------------------------
5181 validate_asset_subsidy(
5182 p_api_version => p_api_version,
5183 p_init_msg_list => p_init_msg_list,
5184 x_return_status => x_return_status,
5185 x_msg_count => x_msg_count,
5186 x_msg_data => x_msg_data,
5187 p_asb_rec => l_asb_rec);
5188
5189 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5190 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5191 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5192 RAISE OKL_API.G_EXCEPTION_ERROR;
5193 END IF;
5194
5195 ---------------------------------------------------------
5196 --call complex API to update line instance
5197 ----------------------------------------------------------
5198 --dbms_output.put_line(to_char(l_klev_rec.amount));
5199 --dbms_output.put_line('before update ' ||to_char(l_klev_rec.subsidy_override_amount));
5200 If l_clev_rec.id <> OKL_API.G_MISS_NUM then
5201
5202 OKL_CONTRACT_PUB.update_contract_line(
5203 p_api_version => p_api_version,
5204 p_init_msg_list => p_init_msg_list,
5205 x_return_status => x_return_status,
5206 x_msg_count => x_msg_count,
5207 x_msg_data => x_msg_data,
5208 p_clev_rec => l_clev_rec,
5209 p_klev_rec => l_klev_rec,
5210 x_clev_rec => lx_clev_rec,
5211 x_klev_rec => lx_klev_rec);
5212
5213 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5214 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5215 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5216 RAISE OKL_API.G_EXCEPTION_ERROR;
5217 END IF;
5218 End If;
5219 --dbms_output.put_line('After update :'||to_char(l_klev_rec.subsidy_override_amount));
5220
5221 /* comment out by cklee 03/15/2004: move to l_asb_tbl level
5222 -- start cklee 01/22/04
5223 ----------------------------------------------------
5224 --validate the subsidy asset record
5225 ----------------------------------------------------
5226 validate_asset_subsidy_after(
5227 p_api_version => p_api_version,
5228 p_init_msg_list => p_init_msg_list,
5229 x_return_status => x_return_status,
5230 x_msg_count => x_msg_count,
5231 x_msg_data => x_msg_data,
5232 p_asb_rec => l_asb_rec);
5233
5234 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5235 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5236 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5237 RAISE OKL_API.G_EXCEPTION_ERROR;
5238 END IF;
5239 -- end cklee 01/22/04
5240 */
5241 ---------------------------------------------------------
5242 -- call complex API to create or update party role instance
5243 -- depending on existence
5244 ----------------------------------------------------------
5245 If l_cplv_rec.id <> OKL_API.G_MISS_NUM then
5246 If l_cplv_rec.id is null then
5247 --no record exists create
5248 If (l_cplv_rec.object1_id1 is not null) and
5249 (l_cplv_rec.object1_id1 <> OKL_API.G_MISS_CHAR) then
5250 l_cplv_rec.cle_id := l_clev_rec.id;
5251 l_cplv_rec.id := OKL_API.G_MISS_NUM;
5252
5253 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
5254 -- to create records in tables
5255 -- okc_k_party_roles_b and okl_k_party_roles
5256
5257 /*
5258 okl_okc_migration_pvt.create_k_party_role(
5259 p_api_version => p_api_version,
5260 p_init_msg_list => p_init_msg_list,
5261 x_return_status => x_return_status,
5262 x_msg_count => x_msg_count,
5263 x_msg_data => x_msg_data,
5264 p_cplv_rec => l_cplv_rec,
5265 x_cplv_rec => lx_cplv_rec);
5266 */
5267
5268 okl_k_party_roles_pvt.create_k_party_role(
5269 p_api_version => p_api_version,
5270 p_init_msg_list => p_init_msg_list,
5271 x_return_status => x_return_status,
5272 x_msg_count => x_msg_count,
5273 x_msg_data => x_msg_data,
5274 p_cplv_rec => l_cplv_rec,
5275 x_cplv_rec => lx_cplv_rec,
5276 p_kplv_rec => l_kplv_rec,
5277 x_kplv_rec => lx_kplv_rec);
5278
5279 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5280 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5281 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5282 RAISE OKL_API.G_EXCEPTION_ERROR;
5283 END IF;
5284 End If;
5285 Elsif l_cplv_rec.id is not null then
5286 --update
5287 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
5288 -- to update records in tables
5289 -- okc_k_party_roles_b and okl_k_party_roles
5290 /*
5291 okl_okc_migration_pvt.update_k_party_role(
5292 p_api_version => p_api_version,
5293 p_init_msg_list => p_init_msg_list,
5294 x_return_status => x_return_status,
5295 x_msg_count => x_msg_count,
5296 x_msg_data => x_msg_data,
5297 p_cplv_rec => l_cplv_rec,
5298 x_cplv_rec => lx_cplv_rec);
5299 */
5300
5301 l_kplv_rec.id := l_cplv_rec.id;
5302 okl_k_party_roles_pvt.update_k_party_role(
5303 p_api_version => p_api_version,
5304 p_init_msg_list => p_init_msg_list,
5305 x_return_status => x_return_status,
5306 x_msg_count => x_msg_count,
5307 x_msg_data => x_msg_data,
5308 p_cplv_rec => l_cplv_rec,
5309 x_cplv_rec => lx_cplv_rec,
5310 p_kplv_rec => l_kplv_rec,
5311 x_kplv_rec => lx_kplv_rec
5312 );
5313
5314 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5315 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5316 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5317 RAISE OKL_API.G_EXCEPTION_ERROR;
5318 END IF;
5319 End If;
5320 End If;
5321
5322 ---------------------------------------------------------
5323 --Call API to calculate asset subsidy amounts
5324 ----------------------------------------------------------
5325 calculate_asset_subsidy(
5326 p_api_version => p_api_version,
5327 p_init_msg_list => p_init_msg_list,
5328 x_return_status => x_return_status,
5329 x_msg_count => x_msg_count,
5330 x_msg_data => x_msg_data,
5331 p_asb_rec => l_asb_rec,
5332 x_asb_rec => lx_calc_asb_rec);
5333
5334 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5335 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5336 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5337 RAISE OKL_API.G_EXCEPTION_ERROR;
5338 END IF;
5339
5340 l_asb_rec := lx_calc_asb_rec;
5341
5342 ---------------------------------------------------------
5343 --Call API to recalculate asset oec and cap amounts
5344 ----------------------------------------------------------
5345 --included in calculate_asset_subsidy
5346 ----------------------------------------------------------------------------
5347 --assign values to out record
5348 ----------------------------------------------------------------------------
5349 x_asb_rec := l_asb_rec;
5350
5351 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
5352 EXCEPTION
5353 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5354 If l_vendor_csr%ISOPEN then
5355 close l_vendor_csr;
5356 End If;
5357 If l_ppyd_csr%ISOPEN then
5358 close l_ppyd_csr;
5359 End If;
5360 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5361 l_api_name,
5362 G_PKG_NAME,
5363 'OKL_API.G_RET_STS_ERROR',
5364 x_msg_count,
5365 x_msg_data,
5366 '_PVT');
5367 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5368 If l_vendor_csr%ISOPEN then
5369 close l_vendor_csr;
5370 End If;
5371 If l_ppyd_csr%ISOPEN then
5372 close l_ppyd_csr;
5373 End If;
5374 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5375 l_api_name,
5376 G_PKG_NAME,
5377 'OKL_API.G_RET_STS_UNEXP_ERROR',
5378 x_msg_count,
5379 x_msg_data,
5380 '_PVT');
5381 WHEN OTHERS THEN
5382 If l_vendor_csr%ISOPEN then
5383 close l_vendor_csr;
5384 End If;
5385 If l_ppyd_csr%ISOPEN then
5386 close l_ppyd_csr;
5387 End If;
5388 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5389 l_api_name,
5390 G_PKG_NAME,
5391 'OTHERS',
5392 x_msg_count,
5393 x_msg_data,
5394 '_PVT');
5395 End update_asset_subsidy;
5396 --------------------------------------------------------------------------------
5397 --Name : update_asset_subsidy
5398 --Creation : 21-Aug-2003
5399 --Purpose : To update asset subsidy record along with associted party role
5400 --------------------------------------------------------------------------------
5401 PROCEDURE update_asset_subsidy(
5402 p_api_version IN NUMBER,
5403 p_init_msg_list IN VARCHAR2,
5404 x_return_status OUT NOCOPY VARCHAR2,
5405 x_msg_count OUT NOCOPY NUMBER,
5406 x_msg_data OUT NOCOPY VARCHAR2,
5407 p_asb_tbl IN asb_tbl_type,
5408 x_asb_tbl OUT NOCOPY asb_tbl_type) is
5409
5410 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5411 l_api_name CONSTANT varchar2(30) := 'UPDATE_ASSET_SUBSIDY';
5412 l_api_version CONSTANT NUMBER := 1.0;
5413 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5414 i NUMBER := 0;
5415
5416 l_asb_tbl asb_tbl_type;
5417 Begin
5418 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5419
5420 l_asb_tbl := p_asb_tbl;
5421 If l_asb_tbl.COUNT > 0 then
5422 i := l_asb_tbl.FIRST;
5423 LOOP
5424 update_asset_subsidy(
5425 p_api_version => p_api_version,
5426 p_init_msg_list => p_init_msg_list,
5427 x_return_status => x_return_status,
5428 x_msg_count => x_msg_count,
5429 x_msg_data => x_msg_data,
5430 p_asb_rec => l_asb_tbl(i),
5431 x_asb_rec => x_asb_tbl(i));
5432
5433 /*-- start cklee 11/15/04
5434
5435 -- store the highest degree of error
5436 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
5437 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
5438 l_overall_status := x_return_status;
5439 End If;
5440 End If;
5441 -- end cklee 11/15/04
5442 */
5443 -- start cklee 11/15/04
5444 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5445 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5446 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5447 RAISE OKL_API.G_EXCEPTION_ERROR;
5448 END IF;
5449 -- end cklee 11/15/04
5450
5451 EXIT WHEN (i = l_asb_tbl.LAST);
5452 i := l_asb_tbl.NEXT(i);
5453 END LOOP;
5454 -- return overall status
5455 -- start cklee 11/15/04
5456 -- x_return_status := l_overall_status;
5457 -- end cklee 11/15/04
5458
5459 -- start cklee 03/15/04
5460 ----------------------------------------------------
5461 --validate the subsidy asset record after record has
5462 -- been created in this transaction
5463 ----------------------------------------------------
5464 validate_asset_subsidy_after(
5465 p_api_version => p_api_version,
5466 p_init_msg_list => p_init_msg_list,
5467 x_return_status => x_return_status,
5468 x_msg_count => x_msg_count,
5469 x_msg_data => x_msg_data,
5470 p_asb_rec => l_asb_tbl(l_asb_tbl.FIRST));
5471
5472 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5474 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5475 RAISE OKL_API.G_EXCEPTION_ERROR;
5476 END IF;
5477 -- end cklee 03/15/04
5478
5479 End If;
5480 EXCEPTION
5481 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5482 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5483 l_api_name,
5484 G_PKG_NAME,
5485 'OKL_API.G_RET_STS_ERROR',
5486 x_msg_count,
5487 x_msg_data,
5488 '_PVT');
5489 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5490 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5491 l_api_name,
5492 G_PKG_NAME,
5493 'OKL_API.G_RET_STS_UNEXP_ERROR',
5494 x_msg_count,
5495 x_msg_data,
5496 '_PVT');
5497 WHEN OTHERS THEN
5498 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5499 l_api_name,
5500 G_PKG_NAME,
5501 'OTHERS',
5502 x_msg_count,
5503 x_msg_data,
5504 '_PVT');
5505 End update_asset_subsidy;
5506 --------------------------------------------------------------------------------
5507 --Name : delete_asset_subsidy
5508 --Creation : 21-Aug-2003
5509 --Purpose : To delete asset subsidy record along with associted party role
5510 --------------------------------------------------------------------------------
5511 PROCEDURE delete_asset_subsidy(
5512 p_api_version IN NUMBER,
5513 p_init_msg_list IN VARCHAR2,
5514 x_return_status OUT NOCOPY VARCHAR2,
5515 x_msg_count OUT NOCOPY NUMBER,
5516 x_msg_data OUT NOCOPY VARCHAR2,
5517 p_asb_rec IN asb_rec_type) is
5518
5519 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5520 l_api_name CONSTANT varchar2(30) := 'DELETE_ASSET_SUBSIDY';
5521 l_api_version CONSTANT NUMBER := 1.0;
5522
5523 l_asb_rec asb_rec_type;
5524 l_db_asb_rec asb_rec_type;
5525 l_row_notfound BOOLEAN := TRUE;
5526
5527 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
5528 l_klev_rec okl_contract_pub.klev_rec_type;
5529 l_cplv_rec okl_okc_migration_pvt.cplv_rec_type;
5530
5531 --cursor to fetch party refund details record
5532 cursor l_ppyd_csr (p_cpl_id in number) is
5533 select ppyd.id
5534 from okl_party_payment_dtls ppyd
5535 where ppyd.cpl_id = p_cpl_id;
5536
5537 l_ppyd_id number default null;
5538
5539 l_ppydv_rec OKL_PYD_PVT.ppydv_rec_type;
5540
5541 --Bug# 4558486
5542 l_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
5543 begin
5544 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5545 -- Call start_activity to create savepoint, check compatibility
5546 -- and initialize message list
5547 x_return_status := OKL_API.START_ACTIVITY (
5548 l_api_name
5549 ,p_init_msg_list
5550 ,'_PVT'
5551 ,x_return_status);
5552 -- Check if activity started successfully
5553 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5554 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5555 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5556 RAISE OKL_API.G_EXCEPTION_ERROR;
5557 END IF;
5558
5559 l_asb_rec := p_asb_rec;
5560 l_db_asb_rec := get_rec(l_asb_rec,l_row_notfound);
5561
5562 IF (l_row_notfound) THEN
5563 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5564 END IF;
5565
5566 --Bug# 4959361
5567 OKL_LLA_UTIL_PVT.check_line_update_allowed
5568 (p_api_version => p_api_version,
5569 p_init_msg_list => p_init_msg_list,
5570 x_return_status => x_return_status,
5571 x_msg_count => x_msg_count,
5572 x_msg_data => x_msg_data,
5573 p_cle_id => l_db_asb_rec.subsidy_cle_id);
5574
5575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5578 RAISE OKL_API.G_EXCEPTION_ERROR;
5579 END IF;
5580 --Bug# 4959361
5581
5582 ----------------------------------------------------------------------------
5583 --Begin : Delete party refund details if they exist
5584 ----------------------------------------------------------------------------
5585 If l_db_asb_rec.cpl_id is not Null and
5586 l_db_asb_rec.cpl_id <> OKL_API.G_MISS_NUM Then
5587 --fetch if any party refund details record
5588 open l_ppyd_csr(p_cpl_id => l_db_asb_rec.cpl_id);
5589 fetch l_ppyd_csr into l_ppyd_id;
5590 If l_ppyd_csr%NOTFOUND then
5591 null;
5592 End If;
5593 Close l_ppyd_csr;
5594
5595 If l_ppyd_id is not null Then
5596
5597 l_ppydv_rec.id := l_ppyd_id;
5598
5599 OKL_PYD_PVT.delete_row(
5600 p_api_version => p_api_version,
5601 p_init_msg_list => p_init_msg_list,
5602 x_return_status => x_return_status,
5603 x_msg_count => x_msg_count,
5604 x_msg_data => x_msg_data,
5605 p_ppydv_rec => l_ppydv_rec);
5606
5607 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5609 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5610 RAISE OKL_API.G_EXCEPTION_ERROR;
5611 END IF;
5612 End If;
5613 End If;
5614
5615 ----------------------------------------------------------------------------
5616 --End : Delete party refund details if they exist
5617 ----------------------------------------------------------------------------
5618
5619 ----------------------------------------------------------------
5620 --call line api to delete line
5621 ----------------------------------------------------------------
5622 If (l_db_asb_rec.subsidy_cle_id is not null) and
5623 (l_db_asb_rec.subsidy_cle_id <> OKL_API.G_MISS_NUM) then
5624
5625 l_clev_rec.id := l_db_asb_rec.subsidy_cle_id;
5626 l_klev_rec.id := l_db_asb_rec.subsidy_cle_id;
5627
5628 OKL_CONTRACT_PUB.delete_contract_line(
5629 p_api_version => p_api_version,
5630 p_init_msg_list => p_init_msg_list,
5631 x_return_status => x_return_status,
5632 x_msg_count => x_msg_count,
5633 x_msg_data => x_msg_data,
5634 p_clev_rec => l_clev_rec,
5635 p_klev_rec => l_klev_rec);
5636
5637 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5638 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5639 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5640 RAISE OKL_API.G_EXCEPTION_ERROR;
5641 END IF;
5642 End If;
5643
5644 ----------------------------------------------------------------
5645 --call party api to delete party_role
5646 ----------------------------------------------------------------
5647 If (l_db_asb_rec.cpl_id is not null) and
5648 (l_db_asb_rec.cpl_id <> OKL_API.G_MISS_NUM) then
5649
5650 l_cplv_rec.id := l_db_asb_rec.cpl_id;
5651
5652 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
5653 -- to delete records in tables
5654 -- okc_k_party_roles_b and okl_k_party_roles
5655 /*
5656 OKL_OKC_MIGRATION_PVT.delete_k_party_role(
5657 p_api_version => p_api_version,
5658 p_init_msg_list => p_init_msg_list,
5659 x_return_status => x_return_status,
5660 x_msg_count => x_msg_count,
5661 x_msg_data => x_msg_data,
5662 p_cplv_rec => l_cplv_rec);
5663 */
5664
5665 l_kplv_rec.id := l_cplv_rec.id;
5666 OKL_K_PARTY_ROLES_PVT.delete_k_party_role(
5667 p_api_version => p_api_version,
5668 p_init_msg_list => p_init_msg_list,
5669 x_return_status => x_return_status,
5670 x_msg_count => x_msg_count,
5671 x_msg_data => x_msg_data,
5672 p_cplv_rec => l_cplv_rec,
5673 p_kplv_rec => l_kplv_rec);
5674
5675 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5677 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5678 RAISE OKL_API.G_EXCEPTION_ERROR;
5679 END IF;
5680 --also will delete the party payment details once they are in place
5681 End If;
5682 ---------------------------------------------------------
5683 --Call API to recalculate asset oec and cap amounts
5684 ----------------------------------------------------------
5685 recalculate_costs(
5686 p_api_version => p_api_version,
5687 p_init_msg_list => p_init_msg_list,
5688 x_return_status => x_return_status,
5689 x_msg_count => x_msg_count,
5690 x_msg_data => x_msg_data,
5691 p_chr_id => l_db_asb_rec.dnz_chr_id,
5692 p_asset_cle_id => l_db_asb_rec.asset_cle_id);
5693
5694 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5695 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5696 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5697 RAISE OKL_API.G_EXCEPTION_ERROR;
5698 END IF;
5699
5700 /*
5701 * sjalasut: aug 25, 04 added code to enable business event. BEGIN
5702 */
5703 IF(OKL_LLA_UTIL_PVT.is_lease_contract(l_db_asb_rec.dnz_chr_id)= OKL_API.G_TRUE)THEN
5704 raise_business_event(p_api_version => l_api_version,
5705 p_init_msg_list => p_init_msg_list,
5706 p_chr_id => l_db_asb_rec.dnz_chr_id,
5707 p_asset_id => l_db_asb_rec.asset_cle_id,
5708 p_subsidy_id => l_db_asb_rec.subsidy_id,
5709 p_event_name => G_WF_EVT_ASSET_SUBSIDY_RMVD,
5710 x_return_status => x_return_status,
5711 x_msg_count => x_msg_count,
5712 x_msg_data => x_msg_data
5713 );
5714 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5715 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5716 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5717 RAISE OKL_API.G_EXCEPTION_ERROR;
5718 END IF;
5719 END IF;
5720 /*
5721 * sjalasut: aug 25, 04 added code to enable business event. END
5722 */
5723
5724 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
5725 EXCEPTION
5726 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5727 If l_ppyd_csr%isopen then
5728 close l_ppyd_csr;
5729 End If;
5730 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5731 l_api_name,
5732 G_PKG_NAME,
5733 'OKL_API.G_RET_STS_ERROR',
5734 x_msg_count,
5735 x_msg_data,
5736 '_PVT');
5737 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5738 If l_ppyd_csr%isopen then
5739 close l_ppyd_csr;
5740 End If;
5741 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5742 l_api_name,
5743 G_PKG_NAME,
5744 'OKL_API.G_RET_STS_UNEXP_ERROR',
5745 x_msg_count,
5746 x_msg_data,
5747 '_PVT');
5748 WHEN OTHERS THEN
5749 If l_ppyd_csr%isopen then
5750 close l_ppyd_csr;
5751 End If;
5752 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5753 l_api_name,
5754 G_PKG_NAME,
5755 'OTHERS',
5756 x_msg_count,
5757 x_msg_data,
5758 '_PVT');
5759 End delete_asset_subsidy;
5760 --------------------------------------------------------------------------------
5761 --Name : delete_asset_subsidy
5762 --Creation : 21-Aug-2003
5763 --Purpose : To delete asset subsidy record along with associted party role
5764 --------------------------------------------------------------------------------
5765 PROCEDURE delete_asset_subsidy(
5766 p_api_version IN NUMBER,
5767 p_init_msg_list IN VARCHAR2,
5768 x_return_status OUT NOCOPY VARCHAR2,
5769 x_msg_count OUT NOCOPY NUMBER,
5770 x_msg_data OUT NOCOPY VARCHAR2,
5771 p_asb_tbl IN asb_tbl_type) is
5772
5773 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5774 l_api_name CONSTANT varchar2(30) := 'DELETE_ASSET_SUBSIDY';
5775 l_api_version CONSTANT NUMBER := 1.0;
5776 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5777 i NUMBER := 0;
5778
5779 l_asb_tbl asb_tbl_type;
5780 Begin
5781 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5782
5783 l_asb_tbl := p_asb_tbl;
5784 If l_asb_tbl.COUNT > 0 then
5785 i := l_asb_tbl.FIRST;
5786 LOOP
5787 delete_asset_subsidy(
5788 p_api_version => p_api_version,
5789 p_init_msg_list => p_init_msg_list,
5790 x_return_status => x_return_status,
5791 x_msg_count => x_msg_count,
5792 x_msg_data => x_msg_data,
5793 p_asb_rec => l_asb_tbl(i));
5794 /*-- start cklee 11/15/04
5795
5796 -- store the highest degree of error
5797 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
5798 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
5799 l_overall_status := x_return_status;
5800 End If;
5801 End If;
5802 -- end cklee 11/15/04
5803 */
5804 -- start cklee 11/15/04
5805 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5806 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5807 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5808 RAISE OKL_API.G_EXCEPTION_ERROR;
5809 END IF;
5810 -- end cklee 11/15/04
5811
5812 EXIT WHEN (i = l_asb_tbl.LAST);
5813 i := l_asb_tbl.NEXT(i);
5814 END LOOP;
5815 -- return overall status
5816 -- start cklee 11/15/04
5817 -- x_return_status := l_overall_status;
5818 -- end cklee 11/15/04
5819
5820 End If;
5821 EXCEPTION
5822 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5823 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5824 l_api_name,
5825 G_PKG_NAME,
5826 'OKL_API.G_RET_STS_ERROR',
5827 x_msg_count,
5828 x_msg_data,
5829 '_PVT');
5830 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5831 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5832 l_api_name,
5833 G_PKG_NAME,
5834 'OKL_API.G_RET_STS_UNEXP_ERROR',
5835 x_msg_count,
5836 x_msg_data,
5837 '_PVT');
5838 WHEN OTHERS THEN
5839 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5840 l_api_name,
5841 G_PKG_NAME,
5842 'OTHERS',
5843 x_msg_count,
5844 x_msg_data,
5845 '_PVT');
5846 End delete_asset_subsidy;
5847
5848 --------------------------------------------------------------------------------
5849 --Name : validate_asset_subsidy
5850 --Creation : 21-Aug-2003
5851 --Purpose : To validate asset subsidy record along with associted party role
5852 --------------------------------------------------------------------------------
5853 PROCEDURE validate_asset_subsidy(
5854 p_api_version IN NUMBER,
5855 p_init_msg_list IN VARCHAR2,
5856 x_return_status OUT NOCOPY VARCHAR2,
5857 x_msg_count OUT NOCOPY NUMBER,
5858 x_msg_data OUT NOCOPY VARCHAR2,
5859 p_asb_tbl IN asb_tbl_type) is
5860
5861 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5862 l_api_name CONSTANT varchar2(30) := 'VALIDATE_ASSET_SUBSIDY';
5863 l_api_version CONSTANT NUMBER := 1.0;
5864 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5865 i NUMBER := 0;
5866
5867 l_asb_tbl asb_tbl_type;
5868 Begin
5869 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5870
5871 l_asb_tbl := p_asb_tbl;
5872 If l_asb_tbl.COUNT > 0 then
5873 i := l_asb_tbl.FIRST;
5874 LOOP
5875 validate_asset_subsidy(
5876 p_api_version => p_api_version,
5877 p_init_msg_list => p_init_msg_list,
5878 x_return_status => x_return_status,
5879 x_msg_count => x_msg_count,
5880 x_msg_data => x_msg_data,
5881 p_asb_rec => l_asb_tbl(i));
5882
5883 /*-- start cklee 11/15/04
5884 -- store the highest degree of error
5885 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
5886 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
5887 l_overall_status := x_return_status;
5888 End If;
5889 End If;
5890 -- end cklee 11/15/04
5891 */
5892 -- start cklee 11/15/04
5893 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5894 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5895 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5896 RAISE OKL_API.G_EXCEPTION_ERROR;
5897 END IF;
5898 -- end cklee 11/15/04
5899
5900 EXIT WHEN (i = l_asb_tbl.LAST);
5901 i := l_asb_tbl.NEXT(i);
5902 END LOOP;
5903 -- return overall status
5904 -- start cklee 11/15/04
5905 -- x_return_status := l_overall_status;
5906 -- end cklee 11/15/04
5907
5908 End If;
5909 EXCEPTION
5910 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5911 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
5912 l_api_name,
5913 G_PKG_NAME,
5914 'OKL_API.G_RET_STS_ERROR',
5915 x_msg_count,
5916 x_msg_data,
5917 '_PVT');
5918 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5919 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5920 l_api_name,
5921 G_PKG_NAME,
5922 'OKL_API.G_RET_STS_UNEXP_ERROR',
5923 x_msg_count,
5924 x_msg_data,
5925 '_PVT');
5926 WHEN OTHERS THEN
5927 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
5928 l_api_name,
5929 G_PKG_NAME,
5930 'OTHERS',
5931 x_msg_count,
5932 x_msg_data,
5933 '_PVT');
5934 End validate_asset_subsidy;
5935 --------------------------------------------------------------------------------
5936 --Name : calculate_asset_subsidy
5937 --Creation : 21-Aug-2003
5938 --Purpose : To calculate asset subsidy for table of subsidy records
5939 --------------------------------------------------------------------------------
5940 PROCEDURE calculate_asset_subsidy(
5941 p_api_version IN NUMBER,
5942 p_init_msg_list IN VARCHAR2,
5943 x_return_status OUT NOCOPY VARCHAR2,
5944 x_msg_count OUT NOCOPY NUMBER,
5945 x_msg_data OUT NOCOPY VARCHAR2,
5946 p_asb_tbl IN asb_tbl_type,
5947 x_asb_tbl OUT NOCOPY asb_tbl_type) is
5948
5949
5950 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
5951 l_api_name CONSTANT varchar2(30) := 'CALCULATE_ASSET_SUBSIDY';
5952 l_api_version CONSTANT NUMBER := 1.0;
5953 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5954 i NUMBER := 0;
5955
5956 l_asb_tbl asb_tbl_type;
5957 Begin
5958 x_return_status := OKL_API.G_RET_STS_SUCCESS;
5959
5960 l_asb_tbl := p_asb_tbl;
5961 If l_asb_tbl.COUNT > 0 then
5962 i := l_asb_tbl.FIRST;
5963 LOOP
5964 calculate_asset_subsidy(
5965 p_api_version => p_api_version,
5966 p_init_msg_list => p_init_msg_list,
5967 x_return_status => x_return_status,
5968 x_msg_count => x_msg_count,
5969 x_msg_data => x_msg_data,
5970 p_asb_rec => l_asb_tbl(i),
5971 x_asb_rec => x_asb_tbl(i));
5972
5973 /*-- start cklee 11/15/04
5974 -- store the highest degree of error
5975 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
5976 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
5977 l_overall_status := x_return_status;
5978 End If;
5979 End If;
5980 -- end cklee 11/15/04
5981 */
5982
5983 -- start cklee 11/15/04
5984 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5985 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5986 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
5987 RAISE OKL_API.G_EXCEPTION_ERROR;
5988 END IF;
5989 -- end cklee 11/15/04
5990
5991
5992 EXIT WHEN (i = l_asb_tbl.LAST);
5993 i := l_asb_tbl.NEXT(i);
5994 END LOOP;
5995 -- return overall status
5996 -- start cklee 11/15/04
5997 -- x_return_status := l_overall_status;
5998 -- end cklee 11/15/04
5999
6000 End If;
6001
6002 EXCEPTION
6003 WHEN OKL_API.G_EXCEPTION_ERROR THEN
6004 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
6005 l_api_name,
6006 G_PKG_NAME,
6007 'OKL_API.G_RET_STS_ERROR',
6008 x_msg_count,
6009 x_msg_data,
6010 '_PVT');
6011 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6012 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
6013 l_api_name,
6014 G_PKG_NAME,
6015 'OKL_API.G_RET_STS_UNEXP_ERROR',
6016 x_msg_count,
6017 x_msg_data,
6018 '_PVT');
6019 WHEN OTHERS THEN
6020 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
6021 l_api_name,
6022 G_PKG_NAME,
6023 'OTHERS',
6024 x_msg_count,
6025 x_msg_data,
6026 '_PVT');
6027 End calculate_asset_subsidy;
6028 END OKL_ASSET_SUBSIDY_PVT;