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