1 PACKAGE BODY OKL_AM_CREATE_QUOTE_PVT AS
2 /* $Header: OKLRCQTB.pls 120.31.12020000.2 2012/08/28 14:09:21 racheruv ship $ */
3
4 -- GLOBAL VARIABLES
5 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_create_quote_pvt.';
9
10 SUBTYPE rulv_rec_type IS OKL_RULE_PUB.rulv_rec_type;
11 -- SUBTYPE asset_tbl_type IS OKL_AM_CALCULATE_QUOTE_PVT.asset_tbl_type;
12
13 -- Start of comments
14 --
15 -- Procedure Name : asset_number_exists
16 -- Desciption : Returns via x_asset_exists if the asset_number exists in FA
17 -- Business Rules :
18 -- Parameters :
19 -- Version : 1.0
20 -- History : RMUNJULU 2757312 created
21 -- : RMUNJULU 3241502 Added p_control + major revamp of the processing
22 -- IS NOW ALSO CALLED FROM OKL_AM_CNTRCT_LN_TRMNT_PVT
23 -- : RMUNJULU 3241502 Added UPPER to asset_number
24 --
25 -- End of comments
26 FUNCTION asset_number_exists(p_asset_number IN VARCHAR2,
27 p_control IN VARCHAR2 DEFAULT NULL,
28 x_asset_exists OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
29
30 l_asset_exists VARCHAR2(1) DEFAULT 'N';
31 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
32
33 --chk for asset in FA
34 --Updated the sql statement for performance issue #5484903
35 -- by excluding UPPER function to do Index scan
36 CURSOR asset_chk_curs1 (p_asset_number IN VARCHAR2) IS
37 SELECT 'Y' a
38 FROM okx_assets_v okx
39 WHERE UPPER(okx.asset_number) = p_asset_number
40 and
41 ( okx.asset_number like Initcap(substr(p_asset_number,1,2))||'%'
42 or
43 okx.asset_number like lower(substr(p_asset_number,1))||Upper(substr(p_asset_number,2,1))||'%'
44 or
45 okx.asset_number like Upper(substr(p_asset_number,1,2))||'%'
46 or
47 okx.asset_number like lower(substr(p_asset_number,1,2))||'%'
48 );
49
50
51
52 --chk for asset on asset line
53 --Updated the sql statement for performance issue #5484903
54 -- by excluding UPPER function to do Index scan
55 CURSOR asset_chk_curs2 (p_asset_number IN VARCHAR2) IS
56 SELECT 'Y' a
57 FROM okc_k_lines_v kle,
58 okc_line_styles_b lse
59 WHERE kle.lse_id = lse.id
60 AND lse.lty_code = 'FREE_FORM1'
61 AND UPPER(kle.NAME) = p_asset_number -- RMUNJULU 3241502
62 AND ( kle.NAME like Initcap(substr(p_asset_number,1,2))||'%'
63 or
64 kle.NAME like lower(substr(p_asset_number,1))||Upper(substr(p_asset_number,2,1))||'%'
65 or
66 kle.NAME like Upper(substr(p_asset_number,1,2))||'%'
67 or
68 kle.NAME like lower(substr(p_asset_number,1,2))||'%'
69 ) ;
70
71
72
73 --check for asset on an split asset transaction
74 CURSOR asset_chk_curs3 (p_asset_number IN VARCHAR2) is
75 SELECT 'Y' a
76 FROM okl_txd_assets_b txd
77 WHERE NVL(UPPER(txd.asset_number),'-999999999999999') = UPPER(p_asset_number) -- RMUNJULU 3241502
78 AND EXISTS (SELECT NULL
79 FROM okl_trx_Assets trx,
80 okl_trx_types_tl ttyp,
81 okl_txl_assets_b txl
82 WHERE trx.id = txl.tas_id
83 AND trx.try_id = ttyp.id
84 AND ttyp.name = 'Split Asset'
85 AND ttyp.language = 'US'
86 AND txl.id = txd.tal_id);
87
88 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'asset_number_exists';
89 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
90 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
91 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
92
93
94 BEGIN
95
96 IF (is_debug_procedure_on) THEN
97 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
98 END IF;
99
100 FOR asset_chk_rec1 IN asset_chk_curs1 (p_asset_number) LOOP
101 l_asset_exists := asset_chk_rec1.a;
102 END LOOP;
103 IF l_asset_exists <> 'Y' THEN
104 FOR asset_chk_rec2 IN asset_chk_curs2 (p_asset_number) LOOP
105 l_asset_exists := asset_chk_rec2.a;
106 END LOOP;
107 END IF;
108 IF p_control = 'QUOTE' THEN
109 IF l_asset_exists <> 'Y' THEN
110 FOR asset_chk_rec3 IN asset_chk_curs3 (p_asset_number) LOOP
111 l_asset_exists := asset_chk_rec3.a;
112 END LOOP;
113 END IF;
114 END IF;
115 x_asset_exists := l_asset_exists;
116
117
118 IF (is_debug_procedure_on) THEN
119 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
120 END IF;
121
122 RETURN(l_return_status);
123 EXCEPTION
124 WHEN OTHERS THEN
125 -- store SQL error message on message stack for caller
126 OKL_API.set_message(
127 p_app_name => g_app_name,
128 p_msg_name => g_unexpected_error,
129 p_token1 => g_sqlcode_token,
130 p_token1_value => sqlcode,
131 p_token2 => g_sqlerrm_token,
132 p_token2_value => sqlerrm);
133 l_return_status := OKL_API.G_RET_STS_ERROR;
134 RETURN(l_return_status);
135 END asset_number_exists;
136
137
138
139 -------------------------------------------------------------------------------
140 -- Start of Commnets
141 -- Badrinath Kuchibholta
142 -- Procedure Name : advance_contract_search
143 -- Description : Procedure used for doing advance search on contract
144 -- details
145 -- Business Rules : The search critriea will be given thru achr_rec_type
146 -- in the form either contract number,
147 -- contract start date,contract end date,
148 -- asset number,serial number or customer name.
149 -- The output will PL/sql table of record of
150 -- achr_rec_type and will contain contract number,
151 -- chr_id, contract start date, contract end date,
152 -- customer name,authoring org id of the contract
153 -- or status of the contract.
154 -- To avoid the performance problem we use
155 -- this PKG instead of View.
156 -- Parameters : IN record of achr_rec_type
157 -- OUT PL/SQL Table of Record achr_tbl_type
158 -- x_return_status OUT NOCOPY VARCHAR2
159 -- Version : 1.0
160 -- History : BAKUCHIB 28-DEC-2002 - 2699412 created
161 -- SECHAWLA 02-JAN-03 - Moved this procedure from okl_am_util_pvt to this API
162 -- GKADARKA 06-JAN-03 2736875 Added order by contract number asc in advance_contract_search
163 -- porcedure cursor.
164 -- BAKUCHIB 16-JAN-03 2748110
165 -- Modified the Advance_contract_search procedure
166 -- by checking for addtionaly Contract Status and
167 -- Line Status. The Addional Contract Status and
168 -- Line status are 'EVERGREEN','BANKRUPTCY_HOLD',
169 -- 'LITIGATION_HOLD','TERMINATION_HOLD' other than
170 -- 'BOOKED'.Also modified the queries by adding
171 -- stripping by org_id.If the input org_id is null
172 -- then we set the org_id with the client info
173 -- BAKUCHIB 17-JAN-03 2748110
174 -- Modified the Advance_contract_search procedure
175 -- by removing Line Status checking.
176 -- BAKUCHIB 04-FEB-03 2781134
177 -- Modified the Advance_contract_search procedure
178 -- by adding case insenstive search on columns contract
179 -- number, asset number, start date, end date and party
180 -- name.
181 -- BAKUCHIB 19-Feb-2003 2807201
182 -- Modified the advance serach by removing the upper
183 -- in the where clause on start date and end date of all the cursors.
184 -- End of Commnets
185
186 Procedure advance_contract_search(
187 p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 p_achr_rec IN achr_rec_type,
193 x_achr_tbl OUT NOCOPY achr_tbl_type) IS
194 l_api_version CONSTANT NUMBER := 1;
195 l_api_name CONSTANT VARCHAR2(30) := 'ADVANCE_CONTRACT_SEARCH';
196 i NUMBER := 0;
197 l_achr_rec achr_rec_type := p_achr_rec;
198 l_achr_tbl achr_tbl_type;
199
200 -- Get the contract details
201 CURSOR get_chr_dtls_csr(p_achr_rec IN achr_rec_type)
202 IS
203 SELECT chr.id chr_id,
204 chr.contract_number contract_number,
205 chr.start_date from_start_date,
206 chr.end_date from_end_date,
207 stl.code sts_code,
208 stl.meaning sts_meaning,
209 chr.authoring_org_id org_id,
210 hp.party_name party_name
211 FROM okc_statuses_tl stl,
212 hz_parties hp,
213 okc_k_party_roles_b cpl,
214 okc_k_headers_b chr
215 -- BAKUCHIB 2781134 start
216 WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
217 -- BAKUCHIB 2807201 start
218 AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
219 AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
220 -- BAKUCHIB 2807201 end
221 -- BAKUCHIB 2781134 end
222 -- BAKUCHIB 2748110 Start
223 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
224 AND chr.scs_code IN ('LEASE', 'LOAN')
225 AND chr.authoring_org_id = p_achr_rec.org_id
226 -- BAKUCHIB 2748110 end
227 AND chr.id = cpl.dnz_chr_id
228 AND cpl.chr_id = cpl.dnz_chr_id
229 AND cpl.object1_id1 = hp.party_id
230 AND cpl.object1_id2 = '#'
231 AND cpl.jtot_object1_code = 'OKX_PARTY'
232 AND cpl.rle_code = 'LESSEE'
233 AND cpl.cle_id IS NULL
234 -- BAKUCHIB 2781134 start
235 AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
236 -- BAKUCHIB 2781134 end
237 AND hp.party_type IN ( 'PERSON','ORGANIZATION')
238 AND chr.sts_code = stl.code
239 AND stl.LANGUAGE = userenv('LANG')
240 ORDER BY contract_number ASC;
241
242 -- Get the contract details for asset number
243 CURSOR get_for_asset_csr(p_achr_rec IN achr_rec_type)
244 IS
245 SELECT chr.id chr_id,
246 chr.contract_number contract_number,
247 chr.start_date from_start_date,
248 chr.end_date from_end_date,
249 stl.code sts_code,
250 stl.meaning sts_meaning,
251 chr.authoring_org_id org_id,
252 hp.party_name party_name
253 FROM okc_statuses_tl stl,
254 hz_parties hp,
255 okc_k_party_roles_b cpl,
256 okc_k_headers_b chr
257 -- BAKUCHIB 2781134 start
258 WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
259 -- BAKUCHIB 2781134 end
260 -- BAKUCHIB 2748110 Start
261 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
262 AND chr.scs_code IN ('LEASE', 'LOAN')
263 AND chr.authoring_org_id = p_achr_rec.org_id
264 -- BAKUCHIB 2748110 End
265 -- BAKUCHIB 2781134 start
266 -- BAKUCHIB 2807201 start
267 AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
268 AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
269 -- BAKUCHIB 2807201 end
270 -- BAKUCHIB 2781134 end
271 AND chr.id = cpl.dnz_chr_id
272 AND cpl.chr_id = cpl.dnz_chr_id
273 AND cpl.object1_id1 = hp.party_id
274 AND cpl.object1_id2 = '#'
275 AND cpl.jtot_object1_code = 'OKX_PARTY'
276 AND cpl.rle_code = 'LESSEE'
277 AND cpl.cle_id IS NULL
278 -- BAKUCHIB 2781134 start
279 AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
280 -- BAKUCHIB 2781134 end
281 AND hp.party_type IN ( 'PERSON','ORGANIZATION')
282 AND chr.sts_code = stl.code
283 AND stl.LANGUAGE = userenv('LANG')
284 AND chr.id IN (SELECT DISTINCT cle_fin.dnz_chr_id chr_id
285 FROM okc_line_styles_b lse_fin,
286 okc_k_lines_tl clet_fin,
287 okc_k_lines_b cle_fin,
288 okc_k_headers_b chr
289 WHERE cle_fin.cle_id IS NULL
290 AND cle_fin.chr_id = cle_fin.dnz_chr_id
291 AND cle_fin.dnz_chr_id = chr.id
292 AND cle_fin.id = clet_fin.id
293 AND clet_fin.LANGUAGE = userenv('LANG')
294 AND lse_fin.id = cle_fin.lse_id
295 AND lse_fin.lty_code = 'FREE_FORM1'
296 -- BAKUCHIB 2748110 Start
297 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
298 AND chr.scs_code IN ('LEASE', 'LOAN')
299 AND chr.authoring_org_id = p_achr_rec.org_id
300 -- BAKUCHIB 2748110 End
301 -- BAKUCHIB 2781134 start
302 AND upper(nvl(clet_fin.name,'x')) LIKE upper(nvl(p_achr_rec.asset_number,nvl(clet_fin.name,'x'))))
303 -- BAKUCHIB 2781134 end
304 ORDER BY contract_number ASC;
305
306 -- Get the chr_id for serial number
307 CURSOR get_for_sno_csr(p_achr_rec IN achr_rec_type)
308 IS
309 SELECT chr.id chr_id,
310 chr.contract_number contract_number,
311 chr.start_date from_start_date,
312 chr.end_date from_end_date,
313 stl.code sts_code,
314 stl.meaning sts_meaning,
315 chr.authoring_org_id org_id,
316 hp.party_name party_name
317 FROM okc_statuses_tl stl,
318 hz_parties hp,
319 okc_k_party_roles_b cpl,
320 okc_k_headers_b chr
321 -- BAKUCHIB 2781134 start
322 WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
323 -- BAKUCHIB 2781134 end
324 -- BAKUCHIB 2748110 Start
325 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
326 AND chr.scs_code IN ('LEASE', 'LOAN')
327 AND chr.authoring_org_id = p_achr_rec.org_id
328 -- BAKUCHIB 2748110 End
329 -- BAKUCHIB 2781134 start
330 -- BAKUCHIB 2807201 start
331 AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
332 AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
333 -- BAKUCHIB 2807201 end
334 -- BAKUCHIB 2781134 end
335 AND chr.id = cpl.dnz_chr_id
336 AND cpl.chr_id = cpl.dnz_chr_id
337 AND cpl.object1_id1 = hp.party_id
338 AND cpl.object1_id2 = '#'
339 AND cpl.jtot_object1_code = 'OKX_PARTY'
340 AND cpl.rle_code = 'LESSEE'
341 AND cpl.cle_id IS NULL
342 -- BAKUCHIB 2781134 start
343 AND upper(hp.party_name) LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
344 -- BAKUCHIB 2781134 end
345 AND hp.party_type IN ( 'PERSON','ORGANIZATION')
346 AND chr.sts_code = stl.code
347 AND stl.LANGUAGE = userenv('LANG')
348 AND chr.id IN (SELECT DISTINCT cim_ib.dnz_chr_id chr_id
349 FROM csi_item_instances csi,
350 okc_k_items cim_ib,
351 okc_line_styles_b lse_ib,
352 okc_k_lines_b cle_ib,
353 okc_k_headers_b chr
354 WHERE cle_ib.lse_id = lse_ib.id
355 AND lse_ib.lty_code = 'INST_ITEM'
356 AND cim_ib.cle_id = cle_ib.id
357 AND cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
358 AND cle_ib.dnz_chr_id = chr.id
359 -- BAKUCHIB 2748110 Start
360 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
361 AND chr.scs_code IN ('LEASE', 'LOAN')
362 AND chr.authoring_org_id = p_achr_rec.org_id
363 -- BAKUCHIB 2748110 End
364 AND cim_ib.object1_id1 = csi.instance_id
365 AND cim_ib.object1_id2 = '#'
366 AND cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
367 -- BAKUCHIB 2781134 start
368 AND upper(nvl(csi.serial_number,'x')) LIKE upper(nvl(p_achr_rec.serial_number,nvl(csi.serial_number,'x'))))
369 -- BAKUCHIB 2781134 end
370 ORDER BY contract_number ASC;
371
372 -- Get the chr_id for asset number and serial number
373 CURSOR get_for_asset_sno_csr(p_achr_rec IN achr_rec_type)
374 IS
375 SELECT chr.id chr_id,
376 chr.contract_number contract_number,
377 chr.start_date from_start_date,
378 chr.end_date from_end_date,
379 stl.code sts_code,
380 stl.meaning sts_meaning,
381 chr.authoring_org_id org_id,
382 hp.party_name party_name
383 FROM okc_statuses_tl stl,
384 hz_parties hp,
385 okc_k_party_roles_b cpl,
386 okc_k_headers_b chr
387 -- BAKUCHIB 2781134 start
388 WHERE upper(chr.contract_number) LIKE upper(nvl(p_achr_rec.contract_number,chr.contract_number))
389 -- BAKUCHIB 2781134 end
390 -- BAKUCHIB 2748110 Start
391 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
392 AND chr.scs_code IN ('LEASE', 'LOAN')
393 AND chr.authoring_org_id = p_achr_rec.org_id
394 -- BAKUCHIB 2748110 End
395 -- BAKUCHIB 2781134 start
396 -- BAKUCHIB 2807201 start
397 AND nvl(chr.start_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_start_date,nvl(chr.start_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_start_date,nvl(chr.start_date,to_date('1111','yyyy')))
398 AND nvl(chr.end_date,to_date('1111','yyyy')) BETWEEN nvl(p_achr_rec.from_end_date,nvl(chr.end_date,to_date('1111','yyyy'))) AND nvl(p_achr_rec.to_end_date,nvl(chr.end_date,to_date('1111','yyyy')))
399 -- BAKUCHIB 2807201 end
400 -- BAKUCHIB 2781134 end
401 AND chr.id = cpl.dnz_chr_id
402 AND cpl.chr_id = cpl.dnz_chr_id
403 AND cpl.object1_id1 = hp.party_id
404 AND cpl.object1_id2 = '#'
405 AND cpl.jtot_object1_code = 'OKX_PARTY'
406 AND cpl.rle_code = 'LESSEE'
407 AND cpl.cle_id IS NULL
408 -- BAKUCHIB 2781134 start
409 AND upper(hp.party_name)LIKE upper(nvl(p_achr_rec.party_name,hp.party_name))
410 -- BAKUCHIB 2781134 end
411 AND hp.party_type IN ( 'PERSON','ORGANIZATION')
412 AND chr.sts_code = stl.code
413 AND stl.LANGUAGE = userenv('LANG')
414 AND chr.id IN (SELECT DISTINCT cle_fin.dnz_chr_id chr_id
415 FROM csi_item_instances csi,
416 okc_k_items cim_ib,
417 okc_line_styles_b lse_ib,
418 okc_k_lines_b cle_ib,
419 okc_line_styles_b lse_inst,
420 okc_k_lines_b cle_inst,
421 okc_line_styles_b lse_fin,
422 okc_k_lines_tl clet_fin,
423 okc_k_lines_b cle_fin,
424 okc_k_headers_b chr
425 WHERE cle_fin.cle_id IS NULL
426 AND cle_fin.chr_id = cle_fin.dnz_chr_id
427 AND cle_fin.dnz_chr_id = chr.id
428 -- BAKUCHIB 2748110 Start
429 AND chr.sts_code IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD')
430 AND chr.scs_code IN ('LEASE', 'LOAN')
431 AND chr.authoring_org_id = p_achr_rec.org_id
432 -- BAKUCHIB 2748110 End
433 AND cle_fin.id = clet_fin.id
434 AND clet_fin.LANGUAGE = userenv('LANG')
435 AND lse_fin.id = cle_fin.lse_id
436 AND lse_fin.lty_code = 'FREE_FORM1'
437 AND cle_inst.cle_id = cle_fin.id
438 AND cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
439 AND cle_inst.lse_id = lse_inst.id
440 AND lse_inst.lty_code = 'FREE_FORM2'
441 AND cle_ib.cle_id = cle_inst.id
442 AND cle_ib.dnz_chr_id = cle_fin.dnz_chr_id
443 AND cle_ib.lse_id = lse_ib.id
444 AND lse_ib.lty_code = 'INST_ITEM'
445 AND cim_ib.cle_id = cle_ib.id
446 AND cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
447 AND cim_ib.object1_id1 = csi.instance_id
448 AND cim_ib.object1_id2 = '#'
449 AND cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
450 -- BAKUCHIB 2781134 start
451 AND upper(nvl(csi.serial_number,'x')) LIKE upper(nvl(p_achr_rec.serial_number,nvl(csi.serial_number,'x')))
452 AND upper(nvl(clet_fin.name,'x')) LIKE upper(nvl(p_achr_rec.asset_number,nvl(clet_fin.name,'x'))))
453 -- BAKUCHIB 2781134 end
454 ORDER BY contract_number ASC;
455 -- For debug logging
456 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'advance_contract_search';
457 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
458 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
459 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
460 BEGIN
461
462 IF (is_debug_procedure_on) THEN
463 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
464 END IF;
465 IF (is_debug_statement_on) THEN
466 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.asset_number :'||p_achr_rec.asset_number);
467 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.serial_number :'||p_achr_rec.serial_number);
468 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.chr_id :'||p_achr_rec.chr_id);
469 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.contract_number:'||p_achr_rec.contract_number );
470 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.from_start_date:'||p_achr_rec.from_start_date );
471 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.to_start_date :'||p_achr_rec.to_start_date );
472 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.from_end_date :'||p_achr_rec.from_end_date );
473 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.to_end_date :'||p_achr_rec.to_end_date );
474 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.sts_code :'||p_achr_rec.sts_code );
475 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.sts_meaning :'||p_achr_rec.sts_meaning );
476 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.org_id :'||p_achr_rec.org_id );
477 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_achr_rec.party_name :'||p_achr_rec.party_name );
478
479 END IF;
480
481
482
483 x_return_status := OKL_API.G_RET_STS_SUCCESS;
484 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
485 G_PKG_NAME,
486 p_init_msg_list,
487 l_api_version,
488 p_api_version,
489 '_PVT',
490 x_return_status);
491 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
493 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
494 RAISE OKL_API.G_EXCEPTION_ERROR;
495 END IF;
496 -- BAKUCHIB 2748110 Start
497 -- Setting the org_id if the input org_id is null
498 IF (l_achr_rec.org_id IS NULL OR
499 l_achr_rec.org_id = OKL_API.G_MISS_NUM) THEN
500 l_achr_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
501 END IF;
502 -- BAKUCHIB 2748110 End
503 -- If the asset number and serial number when not given.
504 IF (l_achr_rec.asset_number = OKL_API.G_MISS_CHAR OR
505 l_achr_rec.asset_number IS NULL) AND
506 (l_achr_rec.serial_number = OKL_API.G_MISS_CHAR OR
507 l_achr_rec.serial_number IS NULL) THEN
508 -- Get the contract details
509 FOR r_get_chr_dtls_csr IN get_chr_dtls_csr(p_achr_rec => l_achr_rec) LOOP
510 IF get_chr_dtls_csr%NOTFOUND THEN
511 IF (l_achr_rec.contract_number IS NOT NULL OR
512 l_achr_rec.contract_number <> OKL_API.G_MISS_CHAR) THEN
513 OKL_API.set_message(p_app_name => G_APP_NAME,
514 p_msg_name => G_INVALID_VALUE1,
515 p_token1 => 'COL_NAME',
516 p_token1_value => 'Contract Number');
517 END IF;
518 IF (l_achr_rec.from_start_date IS NOT NULL OR
519 l_achr_rec.from_start_date <> OKL_API.G_MISS_DATE) OR
520 (l_achr_rec.to_start_date IS NOT NULL OR
521 l_achr_rec.to_start_date <> OKL_API.G_MISS_DATE) THEN
522 OKL_API.set_message(p_app_name => G_APP_NAME,
523 p_msg_name => G_INVALID_VALUE1,
524 p_token1 => 'COL_NAME',
525 p_token1_value => 'Start Date');
526 END IF;
527 IF (l_achr_rec.from_end_date IS NOT NULL OR
528 l_achr_rec.from_end_date <> OKL_API.G_MISS_DATE) OR
529 (l_achr_rec.to_end_date IS NOT NULL OR
530 l_achr_rec.to_end_date <> OKL_API.G_MISS_DATE) THEN
531 OKL_API.set_message(p_app_name => G_APP_NAME,
532 p_msg_name => G_INVALID_VALUE1,
533 p_token1 => 'COL_NAME',
534 p_token1_value => 'End Date');
535 END IF;
536 IF (l_achr_rec.party_name IS NOT NULL OR
537 l_achr_rec.party_name <> OKL_API.G_MISS_CHAR) THEN
538 OKL_API.set_message(p_app_name => G_APP_NAME,
539 p_msg_name => G_INVALID_VALUE1,
540 p_token1 => 'COL_NAME',
541 p_token1_value => 'Party Name');
542 END IF;
543 x_return_status := OKL_API.G_RET_STS_ERROR;
544 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
545 END IF;
546 x_achr_tbl(i).contract_number := r_get_chr_dtls_csr.contract_number;
547 x_achr_tbl(i).chr_id := r_get_chr_dtls_csr.chr_id;
548 x_achr_tbl(i).from_start_date := r_get_chr_dtls_csr.from_start_date;
549 x_achr_tbl(i).from_end_date := r_get_chr_dtls_csr.from_end_Date;
550 x_achr_tbl(i).sts_code := r_get_chr_dtls_csr.sts_code;
551 x_achr_tbl(i).sts_meaning := r_get_chr_dtls_csr.sts_meaning;
552 x_achr_tbl(i).org_id := r_get_chr_dtls_csr.org_id;
553 x_achr_tbl(i).party_name := r_get_chr_dtls_csr.party_name;
554 i := i + 1;
555 END LOOP;
556 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
557 RAISE OKL_API.G_EXCEPTION_ERROR;
558 END IF;
559 -- If the asset number given and serial number when not given.
560 ELSIF (l_achr_rec.asset_number <> OKL_API.G_MISS_CHAR OR
561 l_achr_rec.asset_number IS NOT NULL) AND
562 (l_achr_rec.serial_number = OKL_API.G_MISS_CHAR OR
563 l_achr_rec.serial_number IS NULL) THEN
564 -- Get the contract details for asset number
565 FOR r_get_for_asset_csr IN get_for_asset_csr(p_achr_rec => l_achr_rec) LOOP
566 IF get_for_asset_csr%NOTFOUND THEN
567 OKL_API.set_message(p_app_name => G_APP_NAME,
568 p_msg_name => G_INVALID_VALUE1,
569 p_token1 => 'COL_NAME',
570 p_token1_value => 'Asset Number');
571 x_return_status := OKL_API.G_RET_STS_ERROR;
572 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
573 END IF;
574 x_achr_tbl(i).contract_number := r_get_for_asset_csr.contract_number;
575 x_achr_tbl(i).chr_id := r_get_for_asset_csr.chr_id;
576 x_achr_tbl(i).from_start_date := r_get_for_asset_csr.from_start_date;
577 x_achr_tbl(i).from_end_date := r_get_for_asset_csr.from_end_Date;
578 x_achr_tbl(i).sts_code := r_get_for_asset_csr.sts_code;
579 x_achr_tbl(i).sts_meaning := r_get_for_asset_csr.sts_meaning;
580 x_achr_tbl(i).org_id := r_get_for_asset_csr.org_id;
581 x_achr_tbl(i).party_name := r_get_for_asset_csr.party_name;
582 i := i + 1;
583 END LOOP;
584 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
585 RAISE OKL_API.G_EXCEPTION_ERROR;
586 END IF;
587 -- If the asset number not given and serial number given.
588 ELSIF (l_achr_rec.asset_number = OKL_API.G_MISS_CHAR OR
589 l_achr_rec.asset_number IS NULL) AND
590 (l_achr_rec.serial_number <> OKL_API.G_MISS_CHAR OR
591 l_achr_rec.serial_number IS NOT NULL) THEN
592 -- Get the contract details for serial number
593 FOR r_get_for_sno_csr IN get_for_sno_csr(p_achr_rec => l_achr_rec) LOOP
594 IF get_for_sno_csr%NOTFOUND THEN
595 OKL_API.set_message(p_app_name => G_APP_NAME,
596 p_msg_name => G_INVALID_VALUE1,
597 p_token1 => 'COL_NAME',
598 p_token1_value => 'Serial Number');
599 x_return_status := OKL_API.G_RET_STS_ERROR;
600 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
601 END IF;
602 x_achr_tbl(i).contract_number := r_get_for_sno_csr.contract_number;
603 x_achr_tbl(i).chr_id := r_get_for_sno_csr.chr_id;
604 x_achr_tbl(i).from_start_date := r_get_for_sno_csr.from_start_date;
605 x_achr_tbl(i).from_end_date := r_get_for_sno_csr.from_end_Date;
606 x_achr_tbl(i).sts_code := r_get_for_sno_csr.sts_code;
607 x_achr_tbl(i).sts_meaning := r_get_for_sno_csr.sts_meaning;
608 x_achr_tbl(i).org_id := r_get_for_sno_csr.org_id;
609 x_achr_tbl(i).party_name := r_get_for_sno_csr.party_name;
610 i := i + 1;
611 END LOOP;
612 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
613 RAISE OKL_API.G_EXCEPTION_ERROR;
614 END IF;
615 -- If the asset number and serial number when given.
616 ELSIF (l_achr_rec.asset_number <> OKL_API.G_MISS_CHAR OR
617 l_achr_rec.asset_number IS NOT NULL) AND
618 (l_achr_rec.serial_number <> OKL_API.G_MISS_CHAR OR
619 l_achr_rec.serial_number IS NOT NULL) THEN
620 -- Get the contract details for asset number and serial number
621 FOR r_get_for_asset_sno_csr IN get_for_asset_sno_csr(p_achr_rec => l_achr_rec) LOOP
622 IF get_for_asset_sno_csr%NOTFOUND THEN
623 OKL_API.set_message(p_app_name => G_APP_NAME,
624 p_msg_name => G_INVALID_VALUE1,
625 p_token1 => 'COL_NAME',
626 p_token1_value => 'Asset Number or Serial Number');
627 x_return_status := OKL_API.G_RET_STS_ERROR;
628 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
629 END IF;
630 x_achr_tbl(i).contract_number := r_get_for_asset_sno_csr.contract_number;
631 x_achr_tbl(i).chr_id := r_get_for_asset_sno_csr.chr_id;
632 x_achr_tbl(i).from_start_date := r_get_for_asset_sno_csr.from_start_date;
633 x_achr_tbl(i).from_end_date := r_get_for_asset_sno_csr.from_end_Date;
634 x_achr_tbl(i).sts_code := r_get_for_asset_sno_csr.sts_code;
635 x_achr_tbl(i).sts_meaning := r_get_for_asset_sno_csr.sts_meaning;
636 x_achr_tbl(i).org_id := r_get_for_asset_sno_csr.org_id;
637 x_achr_tbl(i).party_name := r_get_for_asset_sno_csr.party_name;
638 i := i + 1;
639 END LOOP;
640 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
641 RAISE OKL_API.G_EXCEPTION_ERROR;
642 END IF;
643 END IF;
644 OKL_API.END_ACTIVITY (x_msg_count,
645 x_msg_data );
646
647 IF (is_debug_procedure_on) THEN
648 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,G_MODULE_NAME||'advance_contract_search','End(-)');
649 END IF;
650
651 EXCEPTION
652 WHEN OKL_API.G_EXCEPTION_ERROR THEN
653 IF get_chr_dtls_csr%ISOPEN THEN
654 CLOSE get_chr_dtls_csr;
655 END IF;
656 IF get_for_sno_csr%ISOPEN THEN
657 CLOSE get_for_sno_csr;
658 END IF;
659 IF get_for_asset_csr%ISOPEN THEN
660 CLOSE get_for_asset_csr;
661 END IF;
662 IF get_for_asset_sno_csr%ISOPEN THEN
663 CLOSE get_for_asset_sno_csr;
664 END IF;
665
666 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
667 l_api_name,
668 G_PKG_NAME,
669 'OKL_API.G_RET_STS_ERROR',
670 x_msg_count,
671 x_msg_data,
672 '_PVT');
673
674
675 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
676 IF get_chr_dtls_csr%ISOPEN THEN
677 CLOSE get_chr_dtls_csr;
678 END IF;
679 IF get_for_sno_csr%ISOPEN THEN
680 CLOSE get_for_sno_csr;
681 END IF;
682 IF get_for_asset_csr%ISOPEN THEN
683 CLOSE get_for_asset_csr;
684 END IF;
685 IF get_for_asset_sno_csr%ISOPEN THEN
686 CLOSE get_for_asset_sno_csr;
687 END IF;
688 IF (is_debug_exception_on) THEN
689 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
690 || sqlcode || ' , SQLERRM : ' || sqlerrm);
691 END IF;
692 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
693 l_api_name,
694 G_PKG_NAME,
695 'OKL_API.G_RET_STS_UNEXP_ERROR',
696 x_msg_count,
697 x_msg_data,
698 '_PVT');
699 WHEN OTHERS THEN
700 IF get_chr_dtls_csr%ISOPEN THEN
701 CLOSE get_chr_dtls_csr;
702 END IF;
703 IF get_for_sno_csr%ISOPEN THEN
704 CLOSE get_for_sno_csr;
705 END IF;
706 IF get_for_asset_csr%ISOPEN THEN
707 CLOSE get_for_asset_csr;
708 END IF;
709 IF get_for_asset_sno_csr%ISOPEN THEN
710 CLOSE get_for_asset_sno_csr;
711 END IF;
712 IF (is_debug_exception_on) THEN
713 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
714 || sqlcode || ' , SQLERRM : ' || sqlerrm);
715 END IF;
716 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
717 l_api_name,
718 G_PKG_NAME,
719 'OTHERS',
720 x_msg_count,
721 x_msg_data,
722 '_PVT');
723 END advance_contract_search;
724
725 ------------------------------------------------------------------------
726 -- PROCEDURE set_quote_defaults
727 -- Default the values of parameters if the values are not passed to this API
728 -- This assumption is necessary because this API can either be called from
729 -- a screen or from some other process api
730 -- rmunjulu EDT 3797384 made changes so that Quote Effective To Date is
731 -- properly defaulted
732 ------------------------------------------------------------------------
733 PROCEDURE set_quote_defaults(
734 px_quot_rec IN OUT NOCOPY quot_rec_type,
735 p_rule_chr_id IN NUMBER,
736 p_sys_date IN DATE,
737 x_return_status OUT NOCOPY VARCHAR2) IS
738
739 l_quote_eff_days NUMBER;
740 l_quote_eff_max_days NUMBER;
741 l_quote_status VARCHAR2(200) := 'DRAFTED';
742 l_quote_reason VARCHAR2(200) := 'EOT';
743 l_sys_date DATE;
744 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
745 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_quote_defaults';
746 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
747 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
748 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
749 BEGIN
750 IF (is_debug_procedure_on) THEN
751 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
752 END IF;
753
754 -- Get the sysdate
755 l_sys_date := p_sys_date;
756
757 -- Set the date_effective_from if null
758 IF ((px_quot_rec.date_effective_from IS NULL) OR
759 (px_quot_rec.date_effective_from = OKL_API.G_MISS_DATE)) THEN
760 px_quot_rec.date_effective_from := l_sys_date ;
761 END IF;
762
763 -- Set the date_effective_to if null
764 IF ((px_quot_rec.date_effective_to IS NULL) OR
765 (px_quot_rec.date_effective_to = OKL_API.G_MISS_DATE)) THEN
766
767 IF (is_debug_statement_on) THEN
768 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
769 'before call to quote_effectivity :'||l_return_status);
770 END IF;
771 -- set the date eff to using rules
772 quote_effectivity(
773 p_quot_rec => px_quot_rec,
774 p_rule_chr_id => p_rule_chr_id,
775 x_quote_eff_days => l_quote_eff_days,
776 x_quote_eff_max_days => l_quote_eff_max_days,
777 x_return_status => l_return_status);
778
779 IF (is_debug_statement_on) THEN
780 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
781 'after call to quote_effectivity :'||l_return_status);
782 END IF;
783
784 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
785 --rmunjulu EDT 3797384 removed and replaced with below logic
786 -- px_quot_rec.date_effective_to := px_quot_rec.date_effective_from + l_quote_eff_days;
787
788 -- rmunjulu EDT 3797384 logic for date_effective_to varies for pre and post
789 IF trunc(l_sys_date) > trunc(px_quot_rec.date_effective_from) THEN -- pre dated
790 -- PRE DATED QUOTE: effective_to = date_created + quote_eff_days
791 px_quot_rec.date_effective_to := l_sys_date + l_quote_eff_days;
792 ELSIF trunc(l_sys_date) < trunc(px_quot_rec.date_effective_from) THEN -- post dated
793 -- POST DATED QUOTE: effective_to = eff_from + quote_eff_days
794 px_quot_rec.date_effective_to := px_quot_rec.date_effective_from + l_quote_eff_days;
795 ELSE -- current
796 -- CURRENT DATED QUOTE: effective_to = eff_from + quote_eff_days
797 px_quot_rec.date_effective_to := px_quot_rec.date_effective_from + l_quote_eff_days;
798 END IF;
799
800 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
801
802 END IF;
803
804 -- Set the qst_code if null
805 IF ((px_quot_rec.qst_code IS NULL) OR
806 (px_quot_rec.qst_code = OKL_API.G_MISS_CHAR)) THEN
807 px_quot_rec.qst_code := l_quote_status;
808 END IF;
809
810 -- Set the qrs_code if null
811 IF ((px_quot_rec.qrs_code IS NULL) OR
812 (px_quot_rec.qrs_code = OKL_API.G_MISS_CHAR)) THEN
813 px_quot_rec.qrs_code := l_quote_reason;
814 END IF;
815
816 -- Set the preproceeds_yn if null
817 IF ((px_quot_rec.preproceeds_yn IS NULL) OR
818 (px_quot_rec.preproceeds_yn = OKL_API.G_MISS_CHAR)) THEN
819 px_quot_rec.preproceeds_yn := G_NO;
820 END IF;
821
822 -- Set the summary_format_yn if null
823 IF ((px_quot_rec.summary_format_yn IS NULL) OR
824 (px_quot_rec.summary_format_yn = OKL_API.G_MISS_CHAR)) THEN
825 px_quot_rec.summary_format_yn := G_NO;
826 END IF;
827
828 -- Set the consolidated_yn if null
829 IF ((px_quot_rec.consolidated_yn IS NULL) OR
830 (px_quot_rec.consolidated_yn = OKL_API.G_MISS_CHAR)) THEN
831 px_quot_rec.consolidated_yn := G_NO;
832 END IF;
833
834 -- Set the approved_yn if null
835 IF ((px_quot_rec.approved_yn IS NULL) OR
836 (px_quot_rec.approved_yn = OKL_API.G_MISS_CHAR)) THEN
837 px_quot_rec.approved_yn := G_NO;
838 END IF;
839
840 -- Set the payment_received_yn if null
841 IF ((px_quot_rec.payment_received_yn IS NULL) OR
842 (px_quot_rec.payment_received_yn = OKL_API.G_MISS_CHAR)) THEN
843 px_quot_rec.payment_received_yn := G_NO;
844 END IF;
845
846 -- Set the date_requested if null
847 IF ((px_quot_rec.date_requested IS NULL) OR
848 (px_quot_rec.date_requested = OKL_API.G_MISS_DATE)) THEN
849 px_quot_rec.date_requested := l_sys_date;
850 END IF;
851
852 -- Set the date_proposal if null
853 IF ((px_quot_rec.date_proposal IS NULL) OR
854 (px_quot_rec.date_proposal = OKL_API.G_MISS_DATE)) THEN
855 px_quot_rec.date_proposal := l_sys_date;
856 END IF;
857
858 -- Set the requested_by if null
859 IF ((px_quot_rec.requested_by IS NULL) OR
860 (px_quot_rec.requested_by = OKL_API.G_MISS_NUM)) THEN
861 px_quot_rec.requested_by := 1;
862 END IF;
863
864 -- Set the legal_entity_id if null ssiruvol(Nov 17th, 2006)
865 IF ((px_quot_rec.legal_entity_id IS NULL) OR
866 (px_quot_rec.legal_entity_id = OKL_API.G_MISS_NUM)) THEN
867 px_quot_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id (px_quot_rec.khr_id);
868 END IF;
869
870 -- Always NO during quote creation
871 px_quot_rec.accepted_yn := G_NO;
872
873 -- For now *** -- OKL_QTE_PVT.Validate_Trn_Code() expects a value for trn_code
874 px_quot_rec.trn_code := 'EXP';
875 x_return_status := l_return_status;
876
877 IF (is_debug_procedure_on) THEN
878 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
879 END IF;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883
884 IF (is_debug_exception_on) THEN
885 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
886 || sqlcode || ' , SQLERRM : ' || sqlerrm);
887 END IF;
888
889 OKL_API.set_message(p_app_name => g_app_name,
890 p_msg_name => g_unexpected_error,
891 p_token1 => g_sqlcode_token,
892 p_token1_value => sqlcode,
893 p_token2 => g_sqlerrm_token,
894 p_token2_value => sqlerrm);
895 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
896 END set_quote_defaults;
897
898
899
900
901 -- Start of comments
902 --
903 -- Procedure Name : early_termination_allowed
904 -- Description : Gets early termination of contract rule
905 -- Business Rules :
906 -- Parameters : quote rec, contract id, return status, rule found
907 -- Version : 1.0
908 -- History : RDRAGUIL 11-MAR-01 - changed from AMTQPR to AMTPAR rule group
909 -- RMUNJULU 11-DEC-02 - Bug # 2484327 Send FALSE to rule api
910 -- for p_message_yn
911 -- End of comments
912 PROCEDURE early_termination_allowed(
913 p_quot_rec IN quot_rec_type,
914 p_rule_chr_id IN NUMBER,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_rule_found OUT NOCOPY BOOLEAN) IS
917
918 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
919 l_rule_found BOOLEAN := FALSE;
920 l_rulv_rec rulv_rec_type;
921 l_rule_code CONSTANT VARCHAR2(30) := 'AMCTTA';
922 l_rgd_code VARCHAR2(30);
923
924 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'early_termination_allowed';
925 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
926 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
927 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
928 BEGIN
929
930 IF (is_debug_procedure_on) THEN
931 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
932 END IF;
933
934 IF (is_debug_statement_on) THEN
935 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_rule_chr_id : '||p_rule_chr_id );
936 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
937 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
938 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
939 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
940 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
941 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
942 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
943 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
944 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
945 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
946 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
947 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.early_termination_yn : '||p_quot_rec.early_termination_yn );
948 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
949 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
950 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
951 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
952 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.repo_quote_indicator_yn : '||p_quot_rec.repo_quote_indicator_yn );
953
954 END IF;
955
956 IF p_quot_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
957 l_rgd_code := 'AVTQPR';
958 ELSE
959 l_rgd_code := 'AMTQPR';
960 END IF;
961
962 IF (is_debug_statement_on) THEN
963 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
964 'before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
965 END IF;
966
967
968 OKL_AM_UTIL_PVT.get_rule_record(
969 p_rgd_code => l_rgd_code,
970 p_rdf_code => l_rule_code,
971 p_chr_id => p_rule_chr_id,
972 p_cle_id => NULL,
973 x_rulv_rec => l_rulv_rec,
974 x_return_status => l_return_status,
975 p_message_yn => FALSE); -- RMUNJULU 11-DEC-02 2484327 Send FALSE to rule api
976
977 IF (is_debug_statement_on) THEN
978 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
979 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
980 END IF;
981
982
983 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
984 IF NVL (l_rulv_rec.rule_information1, '*') = 'Y' THEN
985 l_rule_found := TRUE;
986 END IF;
987 END IF;
988
989 x_return_status := OKL_API.G_RET_STS_SUCCESS; -- Rule is optional
990 x_rule_found := l_rule_found;
991
992 IF (is_debug_procedure_on) THEN
993 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
994 END IF;
995
996 EXCEPTION
997 WHEN OKL_API.G_EXCEPTION_ERROR THEN
998 x_return_status := OKL_API.G_RET_STS_ERROR;
999 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1000 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1001 WHEN OTHERS THEN
1002 IF (is_debug_exception_on) THEN
1003 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1004 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1005 END IF;
1006
1007 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1008 OKL_API.set_message(p_app_name => g_app_name,
1009 p_msg_name => g_unexpected_error,
1010 p_token1 => g_sqlcode_token,
1011 p_token1_value => sqlcode,
1012 p_token2 => g_sqlerrm_token,
1013 p_token2_value => sqlerrm);
1014 END early_termination_allowed;
1015
1016
1017
1018
1019
1020 -- Start of comments
1021 --
1022 -- Procedure Name : partial_termination_allowed
1023 -- Description : Gets partial termination of contract rule
1024 -- Business Rules :
1025 -- Parameters : quote rec, contract id, return status, rule found
1026 -- Version : 1.0
1027 -- History : RDRAGUIL 11-MAR-01 - changed from AMTQPR to AMTPAR rule group
1028 -- RMUNJULU 11-DEC-02 - Bug # 2484327 Send FALSE to rule api
1029 -- for p_message_yn
1030 -- End of comments
1031 PROCEDURE partial_termination_allowed(
1032 p_quot_rec IN quot_rec_type,
1033 p_rule_chr_id IN NUMBER,
1034 x_return_status OUT NOCOPY VARCHAR2,
1035 x_rule_found OUT NOCOPY BOOLEAN) IS
1036
1037 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1038 l_rule_found BOOLEAN := FALSE;
1039 l_rulv_rec rulv_rec_type;
1040 l_rule_code CONSTANT VARCHAR2(30) := 'AMPTQA';
1041 l_rgd_code VARCHAR2(30);
1042 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'partial_termination_allowed';
1043 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1044 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1045 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1046 BEGIN
1047
1048 IF (is_debug_procedure_on) THEN
1049 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1050
1051 END IF;
1052
1053 IF (is_debug_statement_on) THEN
1054 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_rule_chr_id : '||p_rule_chr_id );
1055 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
1056 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
1057 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
1058 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
1059 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
1060 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
1061 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
1062 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
1063 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
1064 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
1065 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
1066 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.partial_yn : '||p_quot_rec.partial_yn );
1067 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
1068 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
1069 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
1070 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
1071 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.repo_quote_indicator_yn : '||p_quot_rec.repo_quote_indicator_yn );
1072
1073 END IF;
1074
1075 IF p_quot_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
1076 l_rgd_code := 'AVTPAR';
1077 ELSE
1078 l_rgd_code := 'AMTPAR';
1079 END IF;
1080
1081 IF (is_debug_statement_on) THEN
1082 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1083 'before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1084 END IF;
1085
1086 OKL_AM_UTIL_PVT.get_rule_record(
1087 p_rgd_code => l_rgd_code,
1088 p_rdf_code => l_rule_code,
1089 p_chr_id => p_rule_chr_id,
1090 p_cle_id => NULL,
1091 x_rulv_rec => l_rulv_rec,
1092 x_return_status => l_return_status,
1093 p_message_yn => FALSE); -- RMUNJULU 11-DEC-02 2484327 Send FALSE to rule api
1094
1095 IF (is_debug_statement_on) THEN
1096 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1097 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1098 END IF;
1099
1100 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1101 IF NVL (l_rulv_rec.rule_information1, '*') = 'Y' THEN
1102 l_rule_found := TRUE;
1103 END IF;
1104 END IF;
1105
1106 x_return_status := OKL_API.G_RET_STS_SUCCESS; -- Rule is optional
1107 x_rule_found := l_rule_found;
1108
1109 IF (is_debug_procedure_on) THEN
1110 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End (-)');
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1115 x_return_status := OKL_API.G_RET_STS_ERROR;
1116 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1117 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1118 WHEN OTHERS THEN
1119 IF (is_debug_exception_on) THEN
1120 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1121 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1122 END IF;
1123
1124 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1125 OKL_API.set_message(p_app_name => g_app_name,
1126 p_msg_name => g_unexpected_error,
1127 p_token1 => g_sqlcode_token,
1128 p_token1_value => sqlcode,
1129 p_token2 => g_sqlerrm_token,
1130 p_token2_value => sqlerrm);
1131 END partial_termination_allowed;
1132
1133 ------------------------------------------------------------------------
1134 -- PROCEDURE term_status
1135 -- gets the days before contract expiration
1136 ------------------------------------------------------------------------
1137 PROCEDURE term_status(
1138 p_quot_rec IN quot_rec_type,
1139 p_rule_chr_id IN NUMBER,
1140 x_days_before_k_exp OUT NOCOPY NUMBER,
1141 x_return_status OUT NOCOPY VARCHAR2) IS
1142
1143 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1144 l_rule_found BOOLEAN := FALSE;
1145 l_rulv_rec rulv_rec_type;
1146 l_rule_code CONSTANT VARCHAR2(30) := 'AMTSET';
1147 l_rgd_code VARCHAR2(30);
1148 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'term_status.';
1149 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1150 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1151 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1152 BEGIN
1153 IF (is_debug_procedure_on) THEN
1154 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1155 END IF;
1156
1157 IF p_quot_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
1158 l_rgd_code := 'AVTQPR';
1159 ELSE
1160 l_rgd_code := 'AMTQPR';
1161 END IF;
1162
1163 IF (is_debug_statement_on) THEN
1164 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1165 'before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1166 END IF;
1167
1168 OKL_AM_UTIL_PVT.get_rule_record(
1169 p_rgd_code => l_rgd_code,
1170 p_rdf_code => l_rule_code,
1171 p_chr_id => p_rule_chr_id,
1172 p_cle_id => NULL,
1173 x_rulv_rec => l_rulv_rec,
1174 x_return_status => l_return_status,
1175 p_message_yn => TRUE);
1176
1177 IF (is_debug_statement_on) THEN
1178 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1179 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1180 END IF;
1181
1182 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1183 IF NVL (l_rulv_rec.rule_information1, '-1') >= 0 THEN
1184 l_rule_found := TRUE;
1185 END IF;
1186 END IF;
1187
1188 IF l_rule_found THEN
1189 x_days_before_k_exp := l_rulv_rec.RULE_INFORMATION1;
1190 END IF;
1191
1192 x_return_status := l_return_status;
1193
1194 IF (is_debug_procedure_on) THEN
1195 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1196 END IF;
1197
1198 EXCEPTION
1199 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1200 x_return_status := OKL_API.G_RET_STS_ERROR;
1201 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1202 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1203 WHEN OTHERS THEN
1204
1205 IF (is_debug_exception_on) THEN
1206 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1207 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1208 END IF;
1209 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1210 OKL_API.set_message(p_app_name => g_app_name,
1211 p_msg_name => g_unexpected_error,
1212 p_token1 => g_sqlcode_token,
1213 p_token1_value => sqlcode,
1214 p_token2 => g_sqlerrm_token,
1215 p_token2_value => sqlerrm);
1216 END term_status;
1217
1218
1219
1220 -- Start of comments
1221 --
1222 -- Procedure Name : quote_effectivity
1223 -- Description : gets the quote effective dates
1224 -- Business Rules :
1225 -- Parameters : quote header, contract id
1226 -- Version : 1.0
1227 -- History : SECHAWLA 25-NOV-02 - Bug 2680542 : Removed DEFAULT
1228 -- from procedure parameters.
1229 -- End of comments
1230 PROCEDURE quote_effectivity(
1231 p_quot_rec IN quot_rec_type,
1232 p_rule_chr_id IN NUMBER,
1233 x_quote_eff_days OUT NOCOPY NUMBER,
1234 x_quote_eff_max_days OUT NOCOPY NUMBER,
1235 x_return_status OUT NOCOPY VARCHAR2) IS
1236
1237 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1238 l_rule_found BOOLEAN := FALSE;
1239 l_rulv_rec rulv_rec_type;
1240 l_rule_code CONSTANT VARCHAR2(30) := 'AMQTEF';
1241 l_rgd_code VARCHAR2(30);
1242 l_rule_chr_id NUMBER;
1243 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'quote_effectivity';
1244 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1245 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1246 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1247 BEGIN
1248 IF (is_debug_procedure_on) THEN
1249 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1250 END IF;
1251
1252 IF p_quot_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
1253 l_rgd_code := 'AVTQPR';
1254 ELSE
1255 l_rgd_code := 'AMTQPR';
1256 END IF;
1257
1258 IF p_rule_chr_id IS NOT NULL
1259 OR p_rule_chr_id <> OKL_API.G_MISS_NUM THEN
1260 l_rule_chr_id := p_rule_chr_id;
1261 ELSE
1262 l_rule_chr_id := okl_am_util_pvt.get_rule_chr_id (p_quot_rec);
1263 END IF;
1264
1265 IF (is_debug_statement_on) THEN
1266 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1267 'Before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1268 END IF;
1269 OKL_AM_UTIL_PVT.get_rule_record(
1270 p_rgd_code => l_rgd_code,
1271 p_rdf_code => l_rule_code,
1272 p_chr_id => l_rule_chr_id,
1273 p_cle_id => NULL,
1274 x_rulv_rec => l_rulv_rec,
1275 x_return_status => l_return_status,
1276 p_message_yn => TRUE);
1277
1278 IF (is_debug_statement_on) THEN
1279 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1280 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
1281 END IF;
1282
1283 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1284 IF NVL (l_rulv_rec.rule_information1, '-1') >= 0 THEN
1285 l_rule_found := TRUE;
1286 END IF;
1287 END IF;
1288
1289 IF l_rule_found THEN
1290 x_quote_eff_days := l_rulv_rec.RULE_INFORMATION1;
1291 x_quote_eff_max_days := l_rulv_rec.RULE_INFORMATION2;
1292 END IF;
1293
1294 x_return_status := l_return_status;
1295
1296 IF (is_debug_procedure_on) THEN
1297 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1298 END IF;
1299
1300 EXCEPTION
1301 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1302 x_return_status := OKL_API.G_RET_STS_ERROR;
1303 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1304 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1305 WHEN OTHERS THEN
1306
1307 IF (is_debug_exception_on) THEN
1308 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1309 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1310 END IF;
1311 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1312 OKL_API.set_message(p_app_name => g_app_name,
1313 p_msg_name => g_unexpected_error,
1314 p_token1 => g_sqlcode_token,
1315 p_token1_value => sqlcode,
1316 p_token2 => g_sqlerrm_token,
1317 p_token2_value => sqlerrm);
1318 END quote_effectivity;
1319
1320
1321
1322 -- Start of comments
1323 --
1324 -- Procedure Name : quote_type_check
1325 -- Description : checks the quote type
1326 -- Business Rules :
1327 -- Parameters : quote type code (input), flag indicating whether the
1328 -- quote type changed from auto to manual
1329 -- Version : 1.0
1330 -- History : SECHAWLA 06-DEC-02 - Bug 2699412
1331 -- Added logic to change the quote type from Auto to Manual,
1332 -- if the request was to create an Auto Quote,
1333 -- but Auto quotes are not allowed
1334 -- SECHAWLA 02-JAN-03 - Bug 2699412
1335 -- Added code to evaluate new rule to check if auto quotes are allowed
1336 -- End of comments
1337 PROCEDURE quote_type_check(
1338 p_qtp_code IN OUT NOCOPY VARCHAR2, --SECHAWLA 2699412 changed from IN to IN OUT
1339 p_khr_id IN NUMBER,
1340 x_auto_to_manual OUT NOCOPY BOOLEAN, -- SECHAWLA 2699412 added
1341 x_return_status OUT NOCOPY VARCHAR2) IS
1342
1343 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1344
1345 l_new_quote_type VARCHAR2(30); --SECHAWLA 2699412 added
1346 l_auto_to_manual BOOLEAN := FALSE; --SECHAWLA 2699412 added
1347
1348 -- SECHAWLA 02-JAN-03 2699412 new declarations
1349 l_auto_quotes_allowed VARCHAR2(1);
1350 l_rulv_rec okl_rule_pub.rulv_rec_type;
1351 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1352 l_msg_data VARCHAR2(2000);
1353 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'quote_type_check';
1354 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1355 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1356 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1357 BEGIN
1358 IF (is_debug_procedure_on) THEN
1359 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1360 END IF;
1361
1362 IF (p_qtp_code IS NOT NULL) AND (p_qtp_code LIKE 'TER%') THEN
1363
1364 IF p_qtp_code NOT LIKE 'TER_MAN%' THEN -- auto quotes
1365
1366 --SECHAWLA Bug # 2699412 : Added the following code to change the quote
1367 -- type from Auto to Manual, if Auto quotes are not allowed
1368
1369 --SECHAWLA Bug # 2699412 02-JAN-03 Added the following code to check if auto quoets are allowed
1370
1371 IF (is_debug_statement_on) THEN
1372 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1373 'before call to okl_am_util_pvt.get_rule_record :'||l_return_status);
1374 END IF;
1375 --Check if auto quotes are allowed
1376 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMTQPR'
1377 ,p_rdf_code => 'AMCMTQ'
1378 ,p_chr_id => p_khr_id
1379 ,p_cle_id => NULL
1380 ,p_message_yn => FALSE
1381 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
1382 ,x_return_status => l_return_status
1383 ,x_msg_count => l_msg_count
1384 ,x_msg_data => l_msg_data);
1385
1386 IF (is_debug_statement_on) THEN
1387 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1388 'after call to okl_am_util_pvt.get_rule_record :'||l_return_status);
1389 END IF;
1390
1391 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1392 -- Rule instance is found
1393 IF l_rulv_rec.rule_information1 IS NOT NULL AND l_rulv_rec.rule_information1 <> OKL_API.G_MISS_CHAR THEN
1394 IF l_rulv_rec.rule_information1 = 'Y' THEN
1395 l_auto_quotes_allowed := 'N';
1396 ELSE
1397 l_auto_quotes_allowed := 'Y';
1398 END IF;
1399 ELSE
1400 l_auto_quotes_allowed := 'Y';
1401 END IF;
1402 ELSE
1403 l_auto_quotes_allowed := 'Y';
1404 END IF;
1405
1406
1407 IF l_auto_quotes_allowed = 'N' THEN
1408 IF p_qtp_code IN ('TER_PURCHASE', 'TER_RECOURSE','TER_ROLL_PURCHASE') THEN
1409 l_new_quote_type := 'TER_MAN_PURCHASE';
1410 ELSE
1411 l_new_quote_type := 'TER_MAN_WO_PURCHASE';
1412 END IF;
1413 l_auto_to_manual := TRUE;
1414 p_qtp_code := l_new_quote_type;
1415 END IF;
1416
1417 END IF;
1418 l_return_status := OKL_API.G_RET_STS_SUCCESS;
1419 END IF;
1420 -- SECHAWLA 2699412 : Added a flag to indicate whether quote type changed
1421 -- from Auto to Manual
1422 x_auto_to_manual := l_auto_to_manual;
1423 x_return_status := l_return_status;
1424
1425 IF (is_debug_procedure_on) THEN
1426 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(+)');
1427 END IF;
1428
1429 END quote_type_check;
1430
1431
1432
1433
1434 -- PROCEDURE validate_quote
1435 -- checks the validity of the quote
1436 -- History: RDRAGUIL 11-MAR-01 - added No Assets validation
1437 -- RMUNJULU 11-APR-02 - Added call to Validate_Contract, removed
1438 -- check_contract_active_yn call
1439 -- SECHAWLA 25-NOV-02 - Bug # 2680542
1440 -- 1) Changed p_assn_tbl parameter type from IN to IN OUT
1441 -- 2) Added an out parameter x_partial_asset_line
1442 -- to the procedure to indicate to the calling
1443 -- procedure if the quote includes a partial asset line.
1444 -- 3) Moved all the line level validations towards the
1445 -- end of the procedure, after contarct level validations.
1446 -- 4) Added cursor l_clines_csr to validate the asset id
1447 -- and check if asset asset belongs to the passed contract.
1448 -- 5) Added cursor l_linesfull_csr to populate asset number
1449 -- if it is null
1450 -- 6) Added validations for asset quantity and quote quantity
1451
1452 -- SECHAWLA 06-DEC-02 - Bug # 2699412
1453 -- 1) Removed validation to check that already accepetd
1454 -- quote exists for contract
1455 -- 2) Added a new validation to check if an already
1456 -- accepted quote exists for contract line
1457 -- 3) Modified logic to check the quote type
1458 -- 4) Check for early and partial terminations only for
1459 -- Auto quotes
1460 -- 5) Added a new parameter p_days_before_k_exp,
1461 -- used to check if it is early termination
1462 -- RMUNJULU 11-DEC-02 - Bug # 2484327 --
1463 -- Added code to check for accepted quote based
1464 -- on asset level termination changes
1465 -- RMUNJULU 06-JAN-03 2736865 Date Eff From is now enterable changes
1466 -- SECHAWLA 17-FEB-03 Bug 2804703 : Added a validation to restrict the
1467 -- creation of quote with partial asset line(s)
1468 -- for an evergreen contract
1469 -- SECHAWLA 18-FEB-03 Bug # 2807201 : Moved the quote_type_check procedure call from this
1470 -- procedure to create_termination_quote procedure. Removed x_new_quote_type and
1471 -- x_auto_to_manual parameters
1472 -- SECHAWLA 28-FEB-03 Bug # 2757175 : Moved the validate contract validation from this procedure
1473 -- to create_termination_quote procedure
1474 -- RMUNJULU 14-MAR-03 2854796 Error if quote qty less than equal to 0
1475 -- RMUNJULU 09-APR-03 2897523 Added code to check if OKS line exists for the asset
1476 -- RMUNJULU 10-APR-03 2900178 Changed cursor l_oks_lines_csr
1477 -- RMUNJULU 02-OCT-03 2757312 Added code to check for New Asset Number uniqueness
1478 -- RMUNJULU 3241502 Changed token value
1479 -- rmunjulu EDT 3797384 changed the check for early termination, check using eff_from_date
1480 -- rmunjulu EDAT Added code to check for prior dated qte not before k start date and for
1481 -- evergreen contract not before k end date
1482 -- Removed check for only sysdated and future dated quotes, as prior are now allowed
1483 -- rmunjulu EDAT Added code to check for FA transactions and fiscal year for prior dated terms
1484 -- rmunjulu PPD Added code to check for PPD transaction after quote eff date for prior dated quotes
1485 -- rmunjulu EDAT 17-Jan-2005 Raise proper exception
1486 -- rmunjulu Bug 4143251 Modified Check for PPD -- check for all quotes
1487 -- Added check for BOOKED contract and Partial Quote with Quote Eff From Date after contract end date
1488 -- Modified check for FA checks to check for PIOR and CURRENT quotes
1489 -- PAGARG Bug 4299668 Move the cursor execution (to check whether
1490 -- there is any OKS line attached to asset) inside
1491 -- l_partial_asset_line check as OKS line needs to be checked
1492 -- only in case of partial line termination
1493 -- rmunjulu LOANS_ENHANCEMENTS Termination with purchase not allowed for loans
1494 -- Partial Line Termination not allowed for loans with actual/estimated actual
1495 -- rmunjulu LOANS_ENHANCEMENTS -- Check interest calculation done
1496 -- SECHAWLA 04-JAN-06 4915133 - partial quote (full and partial line) should not be allowed
1497 -- for a loan contract with rev rec method 'ESTIMATED_AND_BILLED' or 'ACTUAL'
1498 -- rpillay 01-JUN-12 Bug# 14137617: Partial Termination is not allowed for Catchup-Cleanup Loan contract
1499 PROCEDURE validate_quote(
1500 p_api_version IN NUMBER,
1501 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1502 x_return_status OUT NOCOPY VARCHAR2,
1503 x_msg_count OUT NOCOPY NUMBER,
1504 x_msg_data OUT NOCOPY VARCHAR2,
1505 p_quot_rec IN quot_rec_type,
1506 p_assn_tbl IN OUT NOCOPY assn_tbl_type, -- SECHAWLA 2680542 Changed from IN to IN OUT
1507 p_k_end_date IN DATE,
1508 p_no_of_assets IN NUMBER,
1509 p_sys_date IN DATE,
1510 p_rule_chr_id IN NUMBER,
1511 p_days_before_k_exp IN NUMBER, --SECHAWLA Bug # 2699412 -added
1512 x_partial_asset_line OUT NOCOPY BOOLEAN
1513
1514 ) IS
1515
1516 -- SECHAWLA 17-FEB-03 Bug 2804703 : replaced the usage of okl_k_headers_full_v
1517 -- with okc_k_keaders_b, added sts_code.
1518 -- Cursor to get the khr details for the k passed
1519 CURSOR k_details_for_qte_csr (p_khr_id IN NUMBER) IS
1520 SELECT K.contract_number, K.sts_code, K.start_date, K.end_date -- rmunjulu EDAT
1521 FROM OKC_K_HEADERS_B K
1522 WHERE K.id = p_khr_id;
1523
1524
1525 -- Cursor to get the quote details of a quote that is already accepted for
1526 -- the same contract for which this quote is being generated.
1527 CURSOR get_accepted_qte_details_csr( p_khr_id IN NUMBER) IS
1528 SELECT Q.quote_number, Q.qtp_code
1529 FROM OKL_TRX_QUOTES_B Q
1530 WHERE Q.khr_id = p_khr_id
1531 AND Q.accepted_yn = 'Y';
1532
1533 -- This cursor is used to check if a particular asset belongs to a particular contract.
1534 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Changed cursor to also check for
1535 -- sts_code of line match with sts_code of contract
1536 CURSOR l_clines_csr (p_kle_id NUMBER ) IS
1537 SELECT KLE.chr_id, KLE.start_date -- rmunjulu EDAT
1538 FROM OKC_K_LINES_B KLE,
1539 OKC_K_HEADERS_B KHR
1540 WHERE KLE.id = p_kle_id
1541 AND KLE.chr_id = KHR.id
1542 AND KLE.sts_code = KHR.sts_code;
1543
1544
1545 -- This cursor is used to get the asset number
1546 CURSOR l_linesfull_csr(p_id NUMBER) IS
1547 SELECT name
1548 FROM okl_k_lines_full_v
1549 WHERE id = p_id;
1550
1551
1552 -- RMUNJULU 09-APR-03 2897523 Get the OKS lines if any linked to this covered asset
1553 -- RMUNJULU 10-APR-03 2900178 Changed the query from SELECT 1 to SELECT '1',
1554 -- and removed TO_CHAR conversion to krel.object1_id1
1555 CURSOR l_oks_lines_csr ( p_kle_id IN NUMBER) IS
1556 SELECT '1'
1557 FROM dual WHERE EXISTS (
1558 SELECT '1'
1559 FROM okc_k_headers_b oks_chrb,
1560 okc_line_styles_b oks_cov_pd_lse,
1561 okc_k_lines_b oks_cov_pd_cleb,
1562 okc_k_rel_objs krel,
1563 okc_line_styles_b lnk_srv_lse,
1564 okc_statuses_b lnk_srv_sts,
1565 okc_k_lines_b lnk_srv_cleb,
1566 okc_k_items lnk_srv_cim
1567 WHERE oks_chrb.scs_code = 'SERVICE'
1568 AND oks_chrb.id = oks_cov_pd_cleb.dnz_chr_id
1569 AND oks_cov_pd_cleb.lse_id = oks_cov_pd_lse.id
1570 AND oks_cov_pd_lse.lty_code = 'COVER_PROD'
1571 AND '#' = krel.object1_id2
1572 AND oks_cov_pd_cleb.id = krel.object1_id1
1573 AND krel.rty_code = 'OKLSRV'
1574 AND krel.chr_id = lnk_srv_cleb.dnz_chr_id
1575 AND krel.cle_id = lnk_srv_cleb.id
1576 AND lnk_srv_cleb.lse_id = lnk_srv_lse.id
1577 AND lnk_srv_lse.lty_code = 'LINK_SERV_ASSET'
1578 AND lnk_srv_cleb.sts_code = lnk_srv_sts.code
1579 AND lnk_srv_sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
1580 AND lnk_srv_cleb.dnz_chr_id = lnk_srv_cim.dnz_chr_id
1581 AND lnk_srv_cleb.id = lnk_srv_cim.cle_id
1582 AND lnk_srv_cim.jtot_object1_code = 'OKX_COVASST'
1583 AND lnk_srv_cim.object1_id2 = '#'
1584 AND lnk_srv_cim.object1_id1 = TO_CHAR(p_kle_id));
1585
1586 -- Bug# 5998969 -- Start
1587 -- This cursor is used to get the asset auto range
1588 CURSOR l_asset_autorange_csr IS
1589 SELECT INITIAL_ASSET_ID
1590 FROM FA_SYSTEM_CONTROLS;
1591
1592 l_asset_init_number NUMBER :=0;
1593 l_temp_asset_number NUMBER :=0;
1594 is_number NUMBER :=1;
1595 -- Bug# 5998969 -- End
1596
1597 -- RMUNJULU 09-APR-03 2897523 Added variables
1598 l_oks_line_exists BOOLEAN := FALSE;
1599
1600 -- RMUNJULU 10-APR-03 2900178 changed to VARCHAR2
1601 l_number VARCHAR2(3);
1602
1603
1604 l_no_of_assets NUMBER := 0;
1605 l_k_end_date DATE;
1606 l_rule_found BOOLEAN := FALSE;
1607 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1608 l_contract_status OKC_STATUSES_V.MEANING%TYPE;
1609 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
1610 l_missing_lines BOOLEAN := FALSE;
1611 l_missing_asset_qty BOOLEAN := FALSE;
1612 l_partial_asset_line BOOLEAN := FALSE;
1613 l_invalid_lines BOOLEAN := FALSE;
1614 l_contract_mismatch BOOLEAN := FALSE;
1615 i NUMBER := 0;
1616
1617 l_accepted_quote_number NUMBER := -999;
1618 l_qtp_code VARCHAR2(30);
1619 l_quote_type VARCHAR2(200);
1620 l_asset_qty NUMBER;
1621 l_chr_id NUMBER;
1622 l_name VARCHAR2(150);
1623
1624 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Added parameters for checking
1625 -- related to asset level termination
1626 lx_quote_tbl OKL_AM_UTIL_PVT.quote_tbl_type;
1627
1628 -- SECHAWLA 17-FEB-03 Bug 2804703 : new declarations
1629 l_sts_code okc_k_headers_b.sts_code%TYPE;
1630
1631 -- RMUNJULU 2757312
1632 l_asset_exists VARCHAR2(1);
1633
1634 -- rmunjulu EDAT
1635 l_k_start_date DATE;
1636 l_l_start_date DATE;
1637
1638 -- rmunjulu PPD
1639 l_pdd_exists VARCHAR2(3);
1640
1641 -- LOAN_ENHANCEMENTS
1642 l_deal_type VARCHAR2(300);
1643 l_rev_rec_method VARCHAR2(300);
1644 l_int_cal_basis VARCHAR2(300);
1645 l_tax_owner VARCHAR2(300);
1646 l_int_calc_done VARCHAR2(3);
1647
1648 --Bug# 13447258
1649 l_icb_meaning FND_LOOKUPS.meaning%TYPE;
1650
1651 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'validate_quote';
1652 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1653 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1654 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1655 BEGIN
1656
1657 IF (is_debug_procedure_on) THEN
1658 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1659 END IF;
1660
1661 --Print Input Variables
1662 IF (is_debug_statement_on) THEN
1663 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1664 'p_api_version :'||p_api_version);
1665 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1666 'p_init_msg_list :'||p_init_msg_list);
1667 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1668 'p_k_end_date :'||p_k_end_date);
1669 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1670 'p_no_of_assets :'||p_no_of_assets);
1671 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1672 'p_sys_date :'||p_sys_date);
1673 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1674 'p_rule_chr_id :'||p_rule_chr_id);
1675 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1676 'p_days_before_k_exp :'||p_days_before_k_exp);
1677 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
1678 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
1679 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
1680 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
1681 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
1682 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
1683 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
1684 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
1685 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
1686 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
1687 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
1688 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.early_termination_yn : '||p_quot_rec.early_termination_yn );
1689 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.partial_yn : '||p_quot_rec.partial_yn );
1690 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.preproceeds_yn : '||p_quot_rec.preproceeds_yn );
1691 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.summary_format_yn : '||p_quot_rec.summary_format_yn );
1692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_yn : '||p_quot_rec.consolidated_yn );
1693 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_requested : '||p_quot_rec.date_requested );
1694 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_proposal : '||p_quot_rec.date_proposal );
1695 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_to : '||p_quot_rec.date_effective_to );
1696 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_accepted : '||p_quot_rec.date_accepted );
1697 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.payment_received_yn : '||p_quot_rec.payment_received_yn );
1698 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.requested_by : '||p_quot_rec.requested_by );
1699 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
1700 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
1701 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
1702 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_amount : '||p_quot_rec.purchase_amount );
1703 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_formula : '||p_quot_rec.purchase_formula );
1704 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.asset_value : '||p_quot_rec.asset_value );
1705 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.residual_value : '||p_quot_rec.residual_value );
1706 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.unbilled_receivables : '||p_quot_rec.unbilled_receivables );
1707 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.gain_loss : '||p_quot_rec.gain_loss );
1708 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.PERDIEM_AMOUNT : '||p_quot_rec.PERDIEM_AMOUNT );
1709 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_code : '||p_quot_rec.currency_code );
1710 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_conversion_code : '||p_quot_rec.currency_conversion_code );
1711 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
1712 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.repo_quote_indicator_yn : '||p_quot_rec.repo_quote_indicator_yn );
1713 END IF;
1714
1715 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1716
1717 -- SECHAWLA Bug #2680542 : moved all contract level validations
1718 -- to the beginning of the procedure, before line level validations.
1719
1720 OPEN k_details_for_qte_csr(p_quot_rec.khr_id);
1721 -- rmunjulu EDAT Added l_k_start_date, l_k_end_date
1722 FETCH k_details_for_qte_csr INTO l_contract_number, l_sts_code, l_k_start_date, l_k_end_date; -- SECHAWLA 17-FEB-03 Bug 2804703 : added l_sts_code
1723 CLOSE k_details_for_qte_csr;
1724
1725 --SECHAWLA 28-FEB-03 Bug # 2757175 : Moved the following validation to the beginning of create_termination_quote procedure
1726 /*
1727 -- Call the validate contract to check contract status
1728 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
1729 p_api_version => p_api_version,
1730 p_init_msg_list => p_init_msg_list,
1731 x_return_status => l_return_status,
1732 x_msg_count => x_msg_count,
1733 x_msg_data => x_msg_data,
1734 p_contract_id => p_quot_rec.khr_id,
1735 p_control_flag => 'TRMNT_QUOTE_CREATE',
1736 x_contract_status => lx_contract_status);
1737
1738 -- If error then above api will set the message, so exit now
1739 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1740 x_return_status := OKL_API.G_RET_STS_ERROR;
1741 RAISE G_EXCEPTION_HALT_VALIDATION;
1742 END IF;
1743 */ --SECHAWLA 28-FEB-03 Bug # 2757175 : end code move
1744
1745
1746 ---SECHAWLA Bug # 2699412--------Check the following for the contract line--
1747
1748 -- rmunjulu +++++++++ Effective Dated Terminations -- start +++++++++++++++
1749
1750 -- RMUNJULU EDAT Date Eff From can be future or past date so remove this check
1751 /*
1752 -- RMUNJULU 06-JAN-03 2736865 Date Eff From is now enterable
1753 -- Check date_eff_from should be >= sysdate
1754 IF p_quot_rec.date_effective_from IS NOT NULL
1755 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1756 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(p_sys_date) THEN
1757
1758 x_return_status := OKL_API.G_RET_STS_ERROR;
1759
1760 -- Please enter the current or future date for the Effective From date.
1761 OKL_API.SET_MESSAGE(
1762 p_app_name => 'OKL',
1763 p_msg_name => 'OKL_AM_DATE_EFF_FROM_PAST');
1764
1765 RAISE G_EXCEPTION_HALT_VALIDATION;
1766
1767 END IF;
1768 */
1769
1770 -- rmunjulu EDAT Add check so that quote effective date which is a prior date
1771 -- is not before the contract start date
1772 IF p_quot_rec.date_effective_from IS NOT NULL
1773 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1774 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(l_k_start_date) THEN
1775
1776 x_return_status := OKL_API.G_RET_STS_ERROR;
1777
1778 -- Quote Effectivity Date cannot be before contract start date.
1779 OKL_API.SET_MESSAGE(
1780 p_app_name => 'OKL',
1781 p_msg_name => 'OKL_AM_EDT_QTE_DATE_K');
1782
1783 RAISE G_EXCEPTION_HALT_VALIDATION;
1784
1785 END IF;
1786
1787 -- rmunjulu EDAT check if contract EVERGREEN then quote effective date cannot
1788 -- be before contract end date
1789 IF p_quot_rec.date_effective_from IS NOT NULL
1790 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1791 AND l_sts_code = 'EVERGREEN'
1792 AND TRUNC(p_quot_rec.date_effective_from) <= TRUNC(l_k_end_date) THEN -- rmunjulu bug 6978124 For Evergreen K quote should not be allowed as of K end Date.
1793
1794 x_return_status := OKL_API.G_RET_STS_ERROR;
1795
1796 -- Quote Effectivity Date for an Evergreen contract cannot be before contract end date.
1797 OKL_API.SET_MESSAGE(
1798 p_app_name => 'OKL',
1799 p_msg_name => 'OKL_AM_EDT_EVERGREEN_QTE_DATE');
1800
1801 RAISE G_EXCEPTION_HALT_VALIDATION;
1802
1803 END IF;
1804
1805 -- Code for PPD check will come here
1806
1807 -- rmunjulu +++++++++ Effective Dated Termination -- end ++++++++++++++++
1808
1809 -- Check date_eff_to >= date_eff_from
1810 IF (p_quot_rec.date_effective_from IS NOT NULL)
1811 AND (p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE)
1812 AND (p_quot_rec.date_effective_to IS NOT NULL)
1813 AND (p_quot_rec.date_effective_to <> OKL_API.G_MISS_DATE) THEN
1814 IF (TRUNC(p_quot_rec.date_effective_to) <= TRUNC(p_quot_rec.date_effective_from)) THEN
1815 x_return_status := OKL_API.G_RET_STS_ERROR;
1816 -- Message : Date Effective To DATE_EFFECTIVE_TO cannot be before
1817 -- Date Effective From DATE_EFFECTIVE_FROM.
1818 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
1819 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
1820 p_token1 => 'DATE_EFFECTIVE_TO',
1821 p_token1_value => p_quot_rec.date_effective_to,
1822 p_token2 => 'DATE_EFFECTIVE_FROM',
1823 p_token2_value => p_quot_rec.date_effective_from);
1824 RAISE G_EXCEPTION_HALT_VALIDATION;
1825 END IF;
1826 END IF;
1827
1828
1829 --SECHAWLA 18-FEB-03 Bug # 2807201 : Moved the quote_type_check procedure call from here to Create_termination quote procedure
1830
1831 --IF l_new_quote_type NOT LIKE 'TER_MAN%' THEN ---SECHAWLA 18-FEB-03 Bug # 2807201
1832 IF p_quot_rec.qtp_code NOT LIKE 'TER_MAN%' THEN ---SECHAWLA 18-FEB-03 Bug # 2807201 : quote type passed to validate_quote is now the new quote type from create_termination_quote
1833 -- check if early termination
1834 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
1835
1836 -- IF TRUNC(p_k_end_date) - p_days_before_k_exp > TRUNC(p_sys_date) THEN
1837 -- rmunjulu EDT 3797384 check with date effective instead of sysdate
1838 IF TRUNC(p_k_end_date) - p_days_before_k_exp > TRUNC(p_quot_rec.date_effective_from) THEN
1839
1840 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
1841
1842 IF (is_debug_statement_on) THEN
1843 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1844 'before call to early_termination_allowed :'||l_return_status);
1845 END IF;
1846
1847 -- check if early termination allowed
1848 early_termination_allowed(
1849 p_quot_rec => p_quot_rec,
1850 p_rule_chr_id => p_rule_chr_id,
1851 x_return_status => l_return_status,
1852 x_rule_found => l_rule_found);
1853
1854 IF (is_debug_statement_on) THEN
1855 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1856 'After call to early_termination_allowed :'||l_return_status);
1857 END IF;
1858
1859 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1860 x_return_status := OKL_API.G_RET_STS_ERROR;
1861 RAISE G_EXCEPTION_HALT_VALIDATION;
1862 ELSIF (l_rule_found = FALSE) THEN
1863 x_return_status := OKL_API.G_RET_STS_ERROR;
1864 -- Early Termination of Contract CONTRACT_NUMBER is not allowed.
1865 OKL_API.set_message( p_app_name => 'OKL',
1866 p_msg_name => 'OKL_AM_CONTRACT_EARLY_TERM_NA',
1867 p_token1 => 'CONTRACT_NUMBER',
1868 p_token1_value => l_contract_number);
1869 RAISE G_EXCEPTION_HALT_VALIDATION;
1870 END IF;
1871 END IF;
1872 END IF;
1873
1874 -- rmunjulu PPD
1875 -- do not allow creation of prior dated term quote before the principal paydown date.
1876 IF p_quot_rec.date_effective_from IS NOT NULL
1877 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE THEN
1878 --AND trunc(p_quot_rec.date_effective_from) < trunc(p_sys_date) THEN
1879 -- rmunjulu Bug 4143251 Removed above condition for PRIOR Dated Quotes, NOW check for all Quotes
1880
1881
1882 IF (is_debug_statement_on) THEN
1883 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1884 'before call to OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd :'||l_return_status);
1885 END IF;
1886
1887 -- Added code to check for PPD transaction after quote eff date
1888 l_pdd_exists := OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd(
1889 p_khr_id => p_quot_rec.khr_id,
1890 p_effective_date => p_quot_rec.date_effective_from);
1891
1892 IF (is_debug_statement_on) THEN
1893 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1894 'After call to OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd :'||l_return_status);
1895 END IF;
1896
1897 IF l_pdd_exists = 'Y' THEN
1898
1899 -- A principal paydown transaction exists for contract CONTRACT_NUMBER,
1900 -- can not create a quote with Effective From date before the principal
1901 -- paydown transaction date.
1902 OKL_API.set_message(
1903 p_app_name => 'OKL',
1904 p_msg_name => 'OKL_AM_PPD_ERR',
1905 p_token1 => 'CONTRACT_NUMBER',
1906 p_token1_value => l_contract_number);
1907
1908 RAISE G_EXCEPTION_HALT_VALIDATION;
1909 END IF;
1910 END IF;
1911
1912 -- SECHAWLA Bug #2680542 : moved all line level validations towards the end,
1913 -- after contract level validations.
1914
1915 -- Check that there are contract lines passed as parameters.
1916 IF (p_assn_tbl.COUNT > 0) THEN
1917 i := p_assn_tbl.FIRST;
1918 -- validate contract lines
1919 LOOP
1920
1921 IF (is_debug_statement_on) THEN
1922 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_id :'|| p_assn_tbl(i).p_asset_id );
1923 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_number :'|| p_assn_tbl(i).p_asset_number );
1924 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_qty :'|| p_assn_tbl(i).p_asset_qty );
1925 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_quote_qty :'|| p_assn_tbl(i).p_quote_qty );
1926 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_split_asset_number :'|| p_assn_tbl(i).p_split_asset_number);
1927 END IF;
1928 IF ((p_assn_tbl(i).p_asset_id IS NULL) OR
1929 (p_assn_tbl(i).p_asset_id = OKC_API.G_MISS_NUM)) THEN
1930 x_return_status := OKL_API.G_RET_STS_ERROR;
1931 --Quotes are not allowed for contracts without assets.
1932 OKC_API.SET_MESSAGE (
1933 p_app_name => 'OKL'
1934 ,p_msg_name => 'OKL_AM_NO_ASSETS_FOR_QUOTE');
1935 RAISE G_EXCEPTION_HALT_VALIDATION;
1936 END IF;
1937
1938 -- SECHAWLA Bug #2680542 : Added validations to check if asset id is
1939 -- valid and belongs to the passed contract.
1940 l_chr_id := 1;
1941
1942 OPEN l_clines_csr (p_assn_tbl(i).p_asset_id);
1943 FETCH l_clines_csr INTO l_chr_id, l_l_start_date; -- rmunjulu EDAT
1944 IF l_clines_csr%NOTFOUND THEN
1945 x_return_status := OKL_API.G_RET_STS_ERROR;
1946 -- invalid asset id
1947 OKC_API.SET_MESSAGE (
1948 p_app_name => 'OKC'
1949 ,p_msg_name => G_INVALID_VALUE
1950 ,p_token1 => G_COL_NAME_TOKEN
1951 ,p_token1_value => 'asset_id');
1952 RAISE G_EXCEPTION_HALT_VALIDATION;
1953 ELSIF (l_chr_id <> p_quot_rec.khr_id) OR (l_chr_id = 1) THEN
1954 x_return_status := OKL_API.G_RET_STS_ERROR;
1955 --Asset ASSET_NUMBER does not belong to the contract CONTRACT_NUMBER.
1956 OKC_API.SET_MESSAGE (
1957 p_app_name => 'OKL'
1958 ,p_msg_name => 'OKL_AM_CONTRACT_MISMATCH'
1959 ,p_token1 => 'ASSET_NUMBER'
1960 ,p_token1_value => p_assn_tbl(i).p_asset_number,
1961 p_token2 => 'CONTRACT_NUMBER',
1962 p_token2_value => l_contract_number);
1963 RAISE G_EXCEPTION_HALT_VALIDATION;
1964 END IF;
1965 CLOSE l_clines_csr;
1966
1967 -- SECHAWLA Bug #2680542 : Added code to populate asset number if it is null
1968 IF p_assn_tbl(i).p_asset_number IS NULL
1969 OR p_assn_tbl(i).p_asset_number = OKC_API.G_MISS_CHAR THEN
1970 OPEN l_linesfull_csr(p_assn_tbl(i).p_asset_id);
1971 FETCH l_linesfull_csr INTO l_name;
1972 CLOSE l_linesfull_csr;
1973
1974 p_assn_tbl(i).p_asset_number := l_name;
1975 END IF;
1976
1977 -- SECHAWLA Bug #2680542 : Added code to populate asset qty and quote qty, if null
1978 IF ((p_assn_tbl(i).p_asset_qty IS NULL) OR
1979 (p_assn_tbl(i).p_asset_qty = OKC_API.G_MISS_NUM)) THEN
1980 l_asset_qty := okl_am_util_pvt.get_asset_quantity(p_assn_tbl(i).p_asset_id);
1981 IF l_asset_qty IS NULL THEN
1982 x_return_status := OKL_API.G_RET_STS_ERROR;
1983 -- Can not create quote because the asset quantity is missing.
1984 OKC_API.SET_MESSAGE (
1985 p_app_name => 'OKL'
1986 ,p_msg_name => 'OKL_AM_NO_ASSET_QTY',
1987 p_token1 => 'ASSET_NUMBER',
1988 p_token1_value => p_assn_tbl(i).p_asset_number);
1989 RAISE G_EXCEPTION_HALT_VALIDATION;
1990 ELSE
1991 p_assn_tbl(i).p_asset_qty := l_asset_qty;
1992 END IF;
1993 END IF;
1994
1995
1996 IF ((p_assn_tbl(i).p_quote_qty IS NULL) OR
1997 (p_assn_tbl(i).p_quote_qty = OKC_API.G_MISS_NUM)) THEN
1998 p_assn_tbl(i).p_quote_qty := p_assn_tbl(i).p_asset_qty;
1999 END IF;
2000
2001
2002 -- RMUNJULU 14-MAR-03 2854796 Error if quote qty less than equal to 0
2003 IF p_assn_tbl(i).p_quote_qty <= 0 THEN
2004
2005 -- Please enter a value greater than zero for Units to Terminate of asset ASSET_NUMBER.
2006 OKL_API.SET_MESSAGE (
2007 p_app_name => 'OKL',
2008 p_msg_name => 'OKL_AM_QTE_QTY_LESS_THAN_ZERO',
2009 p_token1 => 'ASSET_NUMBER',
2010 p_token1_value => p_assn_tbl(i).p_asset_number);
2011
2012
2013 RAISE G_EXCEPTION_HALT_VALIDATION;
2014
2015 END IF;
2016
2017
2018 -- SECHAWLA Bug #2680542 : Added code to validate quote quantity
2019 IF p_assn_tbl(i).p_quote_qty > p_assn_tbl(i).p_asset_qty THEN
2020 x_return_status := OKL_API.G_RET_STS_ERROR;
2021 -- Asset ASSET_NUMBER quantity is less than the specified quote quantity.
2022 OKC_API.SET_MESSAGE (
2023 p_app_name => 'OKL'
2024 ,p_msg_name => 'OKL_AM_INVALID_QUOTE_QTY',
2025 p_token1 => 'ASSET_NUMBER',
2026 p_token1_value => p_assn_tbl(i).p_asset_number);
2027 RAISE G_EXCEPTION_HALT_VALIDATION;
2028 END IF;
2029
2030 -- SECHAWLA Bug #2680542 : Check if quote invloves a partial asset line.
2031 IF p_assn_tbl(i).p_quote_qty < p_assn_tbl(i).p_asset_qty THEN
2032 l_partial_asset_line := TRUE;
2033 END IF;
2034
2035
2036
2037 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Added code to check for accepted
2038 -- quote based on asset level termination changes
2039
2040 IF (is_debug_statement_on) THEN
2041 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2042 'before call to OKL_AM_UTIL_PVT.get_line_quotes :'||l_return_status);
2043 END IF;
2044
2045 -- Check if accepted quote exists for the asset
2046 OKL_AM_UTIL_PVT.get_line_quotes (
2047 p_kle_id => p_assn_tbl(i).p_asset_id,
2048 x_quote_tbl => lx_quote_tbl,
2049 x_return_status => x_return_status);
2050
2051 IF (is_debug_statement_on) THEN
2052 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2053 'After call to OKL_AM_UTIL_PVT.get_line_quotes :'||l_return_status);
2054 END IF;
2055
2056 -- Check the return status
2057 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2058
2059 -- Error occured in util proc, message set by util proc raise exp
2060 RAISE G_EXCEPTION_HALT_VALIDATION;
2061
2062 END IF;
2063
2064 -- Check if accepted quote exists for the asset
2065 IF lx_quote_tbl.COUNT > 0 THEN
2066
2067 IF (is_debug_statement_on) THEN
2068 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2069 'before call to OKL_AM_UTIL_PVT.get_lookup_meaning :'||l_return_status);
2070 END IF;
2071
2072 l_quote_type := OKL_AM_UTIL_PVT.get_lookup_meaning(
2073 'OKL_QUOTE_TYPE',
2074 lx_quote_tbl(lx_quote_tbl.FIRST).qtp_code,
2075 'Y');
2076
2077 IF (is_debug_statement_on) THEN
2078 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2079 'after call to OKL_AM_UTIL_PVT.get_lookup_meaning :'||l_return_status);
2080 END IF;
2081 -- Accepted quote QUOTE_NUMBER of quote type QUOTE_TYPE exists for
2082 -- asset ASSET_NUMBER. Cannot create another quote for the same asset.
2083 OKL_API.set_message (
2084 p_app_name => 'OKL',
2085 p_msg_name => 'OKL_AM_ASSET_QTE_EXISTS_ERR',
2086 p_token1 => 'QUOTE_NUMBER',
2087 p_token1_value => lx_quote_tbl(lx_quote_tbl.FIRST).quote_number,
2088 p_token2 => 'QUOTE_TYPE',
2089 p_token2_value => l_quote_type,
2090 p_token3 => 'ASSET_NUMBER',
2091 p_token3_value => p_assn_tbl(i).p_asset_number);
2092
2093 RAISE G_EXCEPTION_HALT_VALIDATION;
2094
2095 END IF;
2096
2097 -- AKP:REPO-QUOTE-START Get the contract product details 6599890
2098 OKL_AM_UTIL_PVT.get_contract_product_details(
2099 p_khr_id => p_quot_rec.khr_id,
2100 x_deal_type => l_deal_type,
2101 x_rev_rec_method => l_rev_rec_method,
2102 x_int_cal_basis => l_int_cal_basis,
2103 x_tax_owner => l_tax_owner,
2104 x_return_status => l_return_status);
2105
2106
2107 IF (is_debug_statement_on) THEN
2108 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2109 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2110 END IF;
2111
2112
2113 -- If error then throw exception
2114 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2115
2116 RAISE G_EXCEPTION_HALT_VALIDATION;
2117
2118 END IF;
2119 -- AKP:REPO-QUOTE-END Get the contract product details
2120
2121 -- rmunjulu LOANS_ENHANCEMENTS Termination with purchase not allowed for loans
2122 IF p_quot_rec.qtp_code IN ( 'TER_PURCHASE', -- Termination - With Purchase
2123 'TER_MAN_PURCHASE', -- Termination - Manual With Purchase
2124 'TER_RECOURSE', -- Termination - Recourse With Purchase
2125 'TER_ROLL_PURCHASE' -- Termination - Rollover To New Contract With Purchase
2126 ) THEN
2127
2128 IF (is_debug_statement_on) THEN
2129 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2130 'before call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2131 END IF;
2132
2133 -- AKP:REPO-QUOTE-START 6599890
2134 /* -- Get the contract product details
2135 OKL_AM_UTIL_PVT.get_contract_product_details(
2136 p_khr_id => p_quot_rec.khr_id,
2137 x_deal_type => l_deal_type,
2138 x_rev_rec_method => l_rev_rec_method,
2139 x_int_cal_basis => l_int_cal_basis,
2140 x_tax_owner => l_tax_owner,
2141 x_return_status => l_return_status);
2142
2143
2144 IF (is_debug_statement_on) THEN
2145 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2146 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2147 END IF;
2148
2149
2150 -- If error then throw exception
2151 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2152
2153 RAISE G_EXCEPTION_HALT_VALIDATION;
2154
2155 END IF; */
2156 -- AKP:REPO-QUOTE-END
2157
2158 IF l_deal_type LIKE 'LOAN%' THEN
2159
2160 -- Termination with purchase quote is not allowed for loan contract.
2161 OKL_API.SET_MESSAGE(
2162 p_app_name => 'OKL',
2163 p_msg_name => 'OKL_AM_LOAN_PAR_ERR');
2164
2165 RAISE G_EXCEPTION_HALT_VALIDATION;
2166
2167 END IF;
2168 END IF;
2169
2170 -- AKP:REPO-QUOTE-START 6599890
2171 -- asahoo Changed the message, no token will be passed.
2172 IF (p_quot_rec.repo_quote_indicator_yn IS NOT NULL AND
2173 p_quot_rec.repo_quote_indicator_yn <> OKL_API.G_MISS_CHAR) THEN
2174 IF p_quot_rec.repo_quote_indicator_yn ='Y' AND l_deal_type NOT LIKE 'LOAN%' THEN
2175 OKL_API.SET_MESSAGE(
2176 p_app_name => 'OKL',
2177 p_msg_name => 'OKL_AM_REPO_LOAN_VALID');
2178 RAISE G_EXCEPTION_HALT_VALIDATION;
2179 END IF;
2180 END IF;
2181 -- AKP:REPO-QUOTE-END
2182
2183 IF l_partial_asset_line = TRUE
2184 THEN
2185 -- Bug 4299668 PAGARG Moved the logic (to check whether there is any OKS
2186 -- line attached to asset) inside l_partial_asset_line condition as OKS
2187 -- line needs to be verified only in case of partial termination quote.
2188 -- RMUNJULU 09-APR-03 2897523 Added code to check if OKS line exists linked to covered
2189 -- asset and trying to create a partial line termination quote on that asset.
2190 OPEN l_oks_lines_csr ( p_assn_tbl(i).p_asset_id );
2191 FETCH l_oks_lines_csr INTO l_number;
2192 IF l_oks_lines_csr%FOUND THEN
2193 l_oks_line_exists := TRUE;
2194 END IF;
2195 CLOSE l_oks_lines_csr;
2196
2197 IF l_oks_line_exists = TRUE THEN
2198
2199 -- This asset is linked to a service contract. Assets linked to service contract can not be split.
2200 OKL_API.set_message (
2201 p_app_name => 'OKL',
2202 p_msg_name => 'OKL_LLA_SPA_SERVICE_LINKED');
2203 /*
2204 -- Service line LINE_NUMBER linked to asset ASSET_NUMBER exists.
2205 -- Can not create partial asset termination quote for this asset.
2206 OKL_API.set_message (
2207 p_app_name => 'OKL',
2208 p_msg_name => 'OKL_AM_SERVICE_LINE_EXISTS',
2209 p_token1 => 'LINE_NUMBER',
2210 p_token1_value => l_number,
2211 p_token2 => 'ASSET_NUMBER',
2212 p_token2_value => p_assn_tbl(i).p_asset_number);
2213 */
2214
2215 RAISE G_EXCEPTION_HALT_VALIDATION;
2216 END IF; -- Bug 4299668
2217
2218
2219 /* SECHAWLA 04-JAN-06 4915133 ; Moved this piece later in the code : move begin
2220 -- rmunjulu LOANS_ENHANCEMENTS 13-oct-05 moved this logic here.
2221 -- rmunjulu LOANS_ENHANCEMENTS Partial line termination for loans with Actual/Estimated Actual not allowed
2222 -- Get the contract product details
2223 OKL_AM_UTIL_PVT.get_contract_product_details(
2224 p_khr_id => p_quot_rec.khr_id,
2225 x_deal_type => l_deal_type,
2226 x_rev_rec_method => l_rev_rec_method,
2227 x_int_cal_basis => l_int_cal_basis,
2228 x_tax_owner => l_tax_owner,
2229 x_return_status => l_return_status);
2230
2231 -- If error then throw exception
2232 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2233
2234 RAISE G_EXCEPTION_HALT_VALIDATION;
2235
2236 END IF;
2237
2238 IF l_deal_type LIKE 'LOAN%'
2239 AND l_rev_rec_method IN ('ESTIMATED_AND_BILLED','ACTUAL') THEN
2240
2241 -- Termination of part of units of asset ASSET_NUMBER is not allowed for contract CONTRACT_NUMBER.
2242 OKL_API.SET_MESSAGE(
2243 p_app_name => 'OKL',
2244 p_msg_name => 'OKL_AM_LOAN_PAR_LN_TRMNT',
2245 p_token1 => 'ASSET_NUMBER',
2246 p_token1_value => p_assn_tbl(i).p_asset_number,
2247 p_token2 => 'CONTRACT_NUMBER',
2248 p_token2_value => l_contract_number);
2249
2250 RAISE G_EXCEPTION_HALT_VALIDATION;
2251
2252 END IF;
2253 */ -- SECHAWLA 04-JAN-06 4915133 : move end
2254
2255 END IF;
2256
2257 --SECHAWLA 04-JAN-06 4915133 : Partial termination quotes (full or partial line)
2258 --should not be permitted for contracts with revenue recognition method
2259 --'Estimated and Billed' or 'Actual'
2260 IF (p_assn_tbl.COUNT < p_no_of_assets) OR (l_partial_asset_line) THEN
2261
2262 IF (is_debug_statement_on) THEN
2263 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2264 'before call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2265 END IF;
2266
2267 -- Moved the above valdation here under this IF condition
2268 -- Get the contract product details
2269 OKL_AM_UTIL_PVT.get_contract_product_details(
2270 p_khr_id => p_quot_rec.khr_id,
2271 x_deal_type => l_deal_type,
2272 x_rev_rec_method => l_rev_rec_method,
2273 x_int_cal_basis => l_int_cal_basis,
2274 x_tax_owner => l_tax_owner,
2275 x_return_status => l_return_status);
2276
2277 IF (is_debug_statement_on) THEN
2278 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2279 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2280 END IF;
2281
2282 -- If error then throw exception
2283 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2284
2285 RAISE G_EXCEPTION_HALT_VALIDATION;
2286
2287 END IF;
2288
2289 --Bug# 13447258: Partial Termination allowed for Estimated and Billed Loan contracts
2290 IF l_deal_type LIKE 'LOAN%'
2291 AND l_rev_rec_method IN ('ACTUAL') THEN
2292
2293 -- Termination of part of units of asset ASSET_NUMBER is not allowed for contract CONTRACT_NUMBER.
2294 OKL_API.SET_MESSAGE(
2295 p_app_name => 'OKL',
2296 p_msg_name => 'OKL_AM_LOAN_PAR_LN_TRMNT');
2297
2298 RAISE G_EXCEPTION_HALT_VALIDATION;
2299
2300 END IF;
2301
2302 --Bug# 13447258: Partial Termination allowed for Catchup-Cleanup Loan contracts
2303 --Bug# 14137617: Partial Termination is not allowed for Catchup-Cleanup Loan contracts
2304 /*IF l_deal_type LIKE 'LOAN%'
2305 AND l_int_cal_basis = 'CATCHUP/CLEANUP' THEN
2306
2307 -- Termination of part of units of asset ASSET_NUMBER is not allowed for contract CONTRACT_NUMBER.
2308 OKL_API.SET_MESSAGE(
2309 p_app_name => 'OKL',
2310 p_msg_name => 'OKL_AM_LOAN_CC_PAR_LN_TRMNT');
2311
2312 RAISE G_EXCEPTION_HALT_VALIDATION;
2313
2314 END IF;
2315 */
2316
2317 --Bug# 13447258: Partial Termination is not allowed for Float and Catchup-Cleanup Loan contracts if
2318 -- contract streams have not been upgraded to asset level
2319 IF l_int_cal_basis IN ('FLOAT','CATCHUP/CLEANUP') THEN
2320 OKL_VARIABLE_INT_UTIL_PVT.check_vr_asset_level_upgrade(
2321 p_khr_id => p_quot_rec.khr_id,
2322 x_return_status => l_return_status);
2323
2324 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2325 RAISE G_EXCEPTION_HALT_VALIDATION;
2326 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2327 RAISE G_EXCEPTION_HALT_VALIDATION;
2328 END IF;
2329 END IF;
2330
2331 --Bug# 13447258: Partial Line Termination is not allowed for Float and Catchup-Cleanup Loan contracts
2332 IF l_partial_asset_line THEN
2333 IF l_int_cal_basis IN ('FLOAT','CATCHUP/CLEANUP') THEN
2334
2335 l_icb_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
2336 'OKL_INTEREST_CALCULATION_BASIS',
2337 l_int_cal_basis,
2338 'Y');
2339
2340 OKL_API.SET_MESSAGE(
2341 p_app_name => 'OKL',
2342 p_msg_name => 'OKL_AM_LOAN_PAR_LINE_TRMNT',
2343 p_token1 => 'INTEREST_CALC_METHOD',
2344 p_token1_value => l_icb_meaning);
2345
2346 RAISE G_EXCEPTION_HALT_VALIDATION;
2347
2348 END IF;
2349 END IF;
2350
2351 END IF;
2352 -- SECHAWLA 04-JAN-06 4915133 : end
2353
2354 -- RMUNJULU 2757312 Added code to validate the new asset number -- START
2355 IF p_assn_tbl(i).p_split_asset_number IS NOT NULL
2356 AND p_assn_tbl(i).p_split_asset_number <> OKL_API.G_MISS_CHAR THEN
2357
2358 -- If partial Line
2359 IF p_assn_tbl(i).p_asset_qty > p_assn_tbl(i).p_quote_qty THEN
2360
2361 IF (is_debug_statement_on) THEN
2362 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2363 'before call to asset_number_exists :'||l_return_status);
2364 END IF;
2365
2366 -- Check if Asset Number Unique --
2367 -- RMUNJULU 3241502 Added p_control
2368 l_return_status := asset_number_exists(
2369 p_asset_number => p_assn_tbl(i).p_split_asset_number,
2370 p_control => 'QUOTE',
2371 x_asset_exists => l_asset_exists);
2372
2373 IF (is_debug_statement_on) THEN
2374 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2375 'After call to asset_number_exists :'||l_return_status);
2376 END IF;
2377
2378 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2379 -- Message set in called proc
2380 RAISE G_EXCEPTION_HALT_VALIDATION;
2381 END IF;
2382
2383 -- If Asset Number Entered is not unique raise error
2384 IF l_asset_exists = 'Y' THEN
2385
2386 -- Asset number ASSET_NUMBER already exists.
2387 OKL_API.set_message (
2388 p_app_name => 'OKL',
2389 p_msg_name => 'OKL_AM_NEW_ASSET_EXISTS',
2390 p_token1 => 'ASSET_NUMBER',
2391 p_token1_value => p_assn_tbl(i).p_split_asset_number); -- RMUNJULU 3241502 Changed token value
2392 RAISE G_EXCEPTION_HALT_VALIDATION;
2393 END IF;
2394
2395 -- Bug# 5998969 -- start
2396 OPEN l_asset_autorange_csr;
2397 FETCH l_asset_autorange_csr INTO l_asset_init_number;
2398 CLOSE l_asset_autorange_csr;
2399
2400 BEGIN
2401 l_temp_asset_number := TO_NUMBER(p_assn_tbl(i).p_split_asset_number);
2402 is_number :=1;
2403 EXCEPTION
2404 WHEN OTHERS THEN
2405 is_number :=0;
2406 END;
2407 IF (is_number = 1) THEN
2408
2409 IF (p_assn_tbl(i).p_split_asset_number > l_asset_init_number) THEN
2410 -- The New Asset Number ASSET_NUMBER is reserved for automatic asset numbering.
2411 -- Asset number beyond AUTO_RANGE is reserved for automatic asset numbering.
2412 --Please modify the New Asset Number.
2413 OKL_API.set_message (
2414 p_app_name => 'OKL',
2415 p_msg_name => 'OKL_AM_NEW_ASSET_IN_AUOT_RANGE',
2416 p_token1 => 'ASSET_NUMBER',
2417 p_token1_value => p_assn_tbl(i).p_split_asset_number,
2418 p_token2 => 'AUTO_RANGE',
2419 p_token2_value => l_asset_init_number);
2420
2421 RAISE G_EXCEPTION_HALT_VALIDATION;
2422 END IF;
2423 END IF;
2424 -- Bug# 5998969 -- end
2425
2426 END IF;
2427 END IF;
2428 -- RMUNJULU 2757312 Added code to validate the new asset number -- END
2429
2430 -- rmunjulu +++++++++ Effective Dated Termination -- start ++++++++++++++++
2431
2432 -- rmunjulu EDAT Check if any asset transactions exists in FA for the asset after quote effective date
2433 IF p_quot_rec.date_effective_from IS NOT NULL
2434 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE THEN
2435
2436 -- ++++++++++++ Same as in Accept Quote API ++++++++++++++++++++++---
2437 -- rmunjulu EDAT Add code for FA checks, do this only for prior dated terminations
2438 -- and termination with purchase (which is when we do asset disposal)
2439 -- rmunjulu Bug 4143251 Changed condition to check for FA Checks for PRE and CURRENT dated quotes
2440 IF trunc(p_quot_rec.date_effective_from) <= trunc(p_sys_date)
2441 AND p_quot_rec.qtp_code IN ( 'TER_PURCHASE', -- Termination - With Purchase
2442 'TER_MAN_PURCHASE', -- Termination - Manual With Purchase
2443 'TER_RECOURSE', -- Termination - Recourse With Purchase
2444 'TER_ROLL_PURCHASE' -- Termination - Rollover To New Contract With Purchase
2445 ) THEN
2446
2447 IF (is_debug_statement_on) THEN
2448 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2449 'before call to OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa :'||l_return_status);
2450 END IF;
2451
2452 OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa(
2453 p_kle_id => p_assn_tbl(i).p_asset_id,
2454 p_trn_date => p_quot_rec.date_effective_from, -- quote eff from date will be passed
2455 p_check_fa_year => 'Y', -- do we need to check fiscal year
2456 p_check_fa_trn => 'Y', -- do we need to check fa transactions
2457 p_contract_number => l_contract_number,
2458 x_return_status => l_return_status);
2459
2460 IF (is_debug_statement_on) THEN
2461 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2462 'After call to OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa :'||l_return_status);
2463 END IF;
2464
2465 -- If error in FA checks the throw exception, message set in above routine
2466 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2467
2468 RAISE G_EXCEPTION_HALT_VALIDATION; -- rmunjulu EDAT 17-Jan-2005
2469 END IF;
2470 END IF;
2471 -- ++++++++++++ Same as in Accept Quote API ++++++++++++++++++++++---
2472 END IF;
2473
2474 -- rmunjulu EDAT Check if quote effectivity date before the asset start date for the quoted asset
2475 IF p_quot_rec.date_effective_from IS NOT NULL
2476 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
2477 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(l_l_start_date) THEN
2478
2479 x_return_status := OKL_API.G_RET_STS_ERROR;
2480
2481 -- Quote Effectivity Date cannot be before asset start date.
2482 OKL_API.SET_MESSAGE(
2483 p_app_name => 'OKL',
2484 p_msg_name => 'OKL_AM_EDT_QTE_DATE_ASSET');
2485
2486 RAISE G_EXCEPTION_HALT_VALIDATION;
2487
2488 END IF;
2489
2490 -- rmunjulu +++++++++ Effective Dated Termination -- end ++++++++++++++++
2491
2492
2493 EXIT WHEN (i = p_assn_tbl.LAST);
2494 i := p_assn_tbl.NEXT(i);
2495 END LOOP;
2496 ELSE
2497 x_return_status := OKL_API.G_RET_STS_ERROR;
2498 --Quotes are not allowed for contracts without assets.
2499 OKC_API.SET_MESSAGE (
2500 p_app_name => 'OKL'
2501 ,p_msg_name => 'OKL_AM_NO_ASSETS_FOR_QUOTE');
2502 RAISE G_EXCEPTION_HALT_VALIDATION;
2503 END IF;
2504
2505 --Bug# 5946411: Removed validation that prevents partial line
2506 -- termination quote creation for evergreen contracts
2507 /*
2508 --SECHAWLA 17-FEB-03 Bug 2804703 : Added the following validation
2509 IF l_sts_code = 'EVERGREEN' AND (l_partial_asset_line) THEN
2510 x_return_status := OKL_API.G_RET_STS_ERROR;
2511 -- Unable to create quote with partial asset quantities for Evergreen contract CONTRACT_NUMBER.
2512 OKC_API.SET_MESSAGE (
2513 p_app_name => 'OKL'
2514 ,p_msg_name => 'OKL_AM_PARTIAL_LINE_EVERGREEN',
2515 p_token1 => 'CONTRACT_NUMBER',
2516 p_token1_value => l_contract_number);
2517 RAISE G_EXCEPTION_HALT_VALIDATION;
2518 END IF;
2519 */
2520
2521 -- rmunjulu 4143251 Added condition, if contract BOOKED and Partial Quote with
2522 -- Quote Effective From Date after contract end date then error
2523 IF l_sts_code IN ('BOOKED')
2524 AND ((l_partial_asset_line) OR (p_assn_tbl.COUNT < p_no_of_assets))
2525 AND (p_quot_rec.date_effective_from IS NOT NULL
2526 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
2527 AND TRUNC(p_quot_rec.date_effective_from) > TRUNC(l_k_end_date)) THEN
2528
2529 x_return_status := OKL_API.G_RET_STS_ERROR;
2530
2531
2532 -- Unable to create partial quote for contract CONTRACT_NUMBER with quote Effective From Date QUOTE_EFF_DATE
2533 -- after contract End Date END_DATE.
2534 OKL_API.SET_MESSAGE (
2535 p_app_name => 'OKL',
2536 p_msg_name => 'OKL_AM_PARTIAL_BOOKED_K_ERR',
2537 p_token1 => 'CONTRACT_NUMBER',
2538 p_token1_value => l_contract_number,
2539 p_token2 => 'QUOTE_EFF_DATE',
2540 p_token2_value => TRUNC(p_quot_rec.date_effective_from),
2541 p_token3 => 'END_DATE',
2542 p_token3_value => TRUNC(l_k_end_date));
2543
2544 RAISE G_EXCEPTION_HALT_VALIDATION;
2545 END IF;
2546
2547
2548 IF (is_debug_statement_on) THEN
2549 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2550 'before call to OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done :'||l_return_status);
2551 END IF;
2552 -- rmunjulu LOANS_ENHANCEMENTS -- Check interest calculation done
2553
2554 --SECHAWLA 20-JAN-06 4970009 : The following interest calculation check
2555 --will also be done for lease contracts, with interest calculation basis 'FLOAT_FACTORS','REAMORT'
2556 --modifying OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done
2557 --no code changes done in this file for bug 4970009
2558 l_int_calc_done := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done(
2559 p_contract_id => p_quot_rec.khr_id,
2560 p_contract_number => l_contract_number,
2561 p_source => 'CREATE',
2562 p_trn_date => TRUNC(p_quot_rec.date_effective_from));
2563 IF (is_debug_statement_on) THEN
2564 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2565 'After call to OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done :'||l_return_status);
2566 END IF;
2567
2568 IF l_int_calc_done IS NULL OR l_int_calc_done = 'N' THEN
2569
2570 -- Message will be set in called procedure
2571 RAISE G_EXCEPTION_HALT_VALIDATION;
2572 END IF;
2573
2574 --IF l_new_quote_type NOT LIKE 'TER_MAN%' THEN -- SECHAWLA 18-FEB-03 Bug # 2807201
2575 IF p_quot_rec.qtp_code NOT LIKE 'TER_MAN%' THEN --SECHAWLA 18-FEB-03 Bug # 2807201 : quote_type passed to validate quote is now the new quote type from create_tyermination_quote
2576
2577 -- SECHAWLA Bug #2680542 : Added the second condition to the following
2578 -- IF statement, to check if the quote is partial. A quote is partial
2579 -- if it has less than the total number of assets on the contract or
2580 -- has units less than the total number of units for one or more assets
2581 -- on the contract
2582
2583 -- check if partial quote
2584 IF (p_assn_tbl.COUNT < p_no_of_assets)
2585 OR (l_partial_asset_line) THEN -- added second condition
2586
2587 IF (is_debug_statement_on) THEN
2588 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2589 'before call to partial_termination_allowed :'||l_return_status);
2590 END IF;
2591 -- check partial termination allowed
2592 partial_termination_allowed(
2593 p_quot_rec => p_quot_rec,
2594 p_rule_chr_id => p_rule_chr_id,
2595 x_return_status => l_return_status,
2596 x_rule_found => l_rule_found);
2597
2598 IF (is_debug_statement_on) THEN
2599 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2600 'After call to partial_termination_allowed :'||l_return_status);
2601 END IF;
2602
2603
2604 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2605 x_return_status := OKL_API.G_RET_STS_ERROR;
2606 RAISE G_EXCEPTION_HALT_VALIDATION;
2607 ELSIF (l_rule_found = FALSE) THEN
2608 x_return_status := OKL_API.G_RET_STS_ERROR;
2609 --Partial quote for contract CONTRACT_NUMBER is not allowed.
2610 OKL_API.set_message( p_app_name => 'OKL',
2611 p_msg_name => 'OKL_AM_PARTIAL_QUOTE_NA',
2612 p_token1 => 'CONTRACT_NUMBER',
2613 p_token1_value => l_contract_number);
2614 RAISE G_EXCEPTION_HALT_VALIDATION;
2615 END IF;
2616 END IF;
2617 END IF;
2618
2619 -- SECHAWLA Bug #2680542 : set the out parameter x_partial_asset_line to
2620 -- indicate if quote involves a partial
2621 -- asset line.
2622 x_partial_asset_line := l_partial_asset_line; -- added
2623
2624 IF (is_debug_statement_on) THEN
2625 -- OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2626 -- 'x_partial_asset_line..'||x_partial_asset_line);
2627
2628 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2629 'x_msg_data..'||x_msg_data);
2630
2631 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2632 'x_msg_count..'||x_msg_count);
2633
2634 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2635 'ret status at the end.. '||x_return_status);
2636
2637 END IF;
2638
2639 IF (is_debug_procedure_on) THEN
2640 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,G_MODULE_NAME||'validate_quote ','End(-)');
2641 END IF;
2642
2643
2644 EXCEPTION
2645 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2646 IF k_details_for_qte_csr%ISOPEN THEN
2647 CLOSE k_details_for_qte_csr;
2648 END IF;
2649
2650 IF get_accepted_qte_details_csr%ISOPEN THEN
2651 CLOSE get_accepted_qte_details_csr;
2652 END IF;
2653
2654 IF l_clines_csr%ISOPEN THEN
2655 CLOSE l_clines_csr;
2656 END IF;
2657
2658 IF l_linesfull_csr%ISOPEN THEN
2659 CLOSE l_linesfull_csr;
2660 END IF;
2661
2662 -- RMUNJULU 09-APR-03 2897523
2663 IF l_oks_lines_csr%ISOPEN THEN
2664 CLOSE l_oks_lines_csr;
2665 END IF;
2666
2667 x_return_status := OKL_API.G_RET_STS_ERROR;
2668
2669 WHEN OTHERS THEN
2670 IF k_details_for_qte_csr%ISOPEN THEN
2671 CLOSE k_details_for_qte_csr;
2672 END IF;
2673
2674 IF get_accepted_qte_details_csr%ISOPEN THEN
2675 CLOSE get_accepted_qte_details_csr;
2676 END IF;
2677
2678 IF l_clines_csr%ISOPEN THEN
2679 CLOSE l_clines_csr;
2680 END IF;
2681
2682 IF l_linesfull_csr%ISOPEN THEN
2683 CLOSE l_linesfull_csr;
2684 END IF;
2685
2686 -- RMUNJULU 09-APR-03 2897523
2687 IF l_oks_lines_csr%ISOPEN THEN
2688 CLOSE l_oks_lines_csr;
2689 END IF;
2690
2691 IF (is_debug_exception_on) THEN
2692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2693 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2694 END IF;
2695
2696 -- unexpected error
2697 OKL_API.set_message(p_app_name => g_app_name,
2698 p_msg_name => g_unexpected_error,
2699 p_token1 => g_sqlcode_token,
2700 p_token1_value => sqlcode,
2701 p_token2 => g_sqlerrm_token,
2702 p_token2_value => sqlerrm);
2703
2704 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2705 END validate_quote;
2706
2707
2708
2709 -- Start of comments
2710 --
2711 -- Function Name : set_currency_defaults
2712 -- Description : This procedure Defaults the Multi-Currency Columns
2713 -- Business Rules :
2714 -- Parameters : Input parameters : px_quot_rec, p_sys_date
2715 -- Version : 1.0
2716 -- History : 23-DEC-02 RMUNJULU 2726739 Created
2717 -- End of comments
2718 PROCEDURE set_currency_defaults(
2719 px_quot_rec IN OUT NOCOPY quot_rec_type,
2720 p_sys_date IN DATE,
2721 x_return_status OUT NOCOPY VARCHAR2) IS
2722
2723 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2724 l_functional_currency_code VARCHAR2(15);
2725 l_contract_currency_code VARCHAR2(15);
2726 l_currency_conversion_type VARCHAR2(30);
2727 l_currency_conversion_rate NUMBER;
2728 l_currency_conversion_date DATE;
2729
2730 l_org_id NUMBER;
2731 l_converted_amount NUMBER;
2732
2733 -- Since we do not use the amount or converted amount in TRX_Quotes table
2734 -- set a hardcoded value for the amount (and pass to to
2735 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
2736 -- conversion values )
2737 l_hard_coded_amount NUMBER := 100;
2738
2739
2740 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_currency_defaults';
2741 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2742 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2743 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2744 BEGIN
2745
2746 IF (is_debug_procedure_on) THEN
2747 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begins(+)');
2748 END IF;
2749
2750 -- Get the functional currency from AM_Util
2751 OKL_AM_UTIL_PVT.get_func_currency_org(
2752 x_org_id => l_org_id,
2753 x_currency_code => l_functional_currency_code);
2754
2755 IF (is_debug_statement_on) THEN
2756 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2757 'after call to OKL_AM_UTIL_PVT.get_func_currency_org :l_org_id :'||l_org_id);
2758 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2759 'after call to OKL_AM_UTIL_PVT.get_func_currency_org :l_functional_currency_code :'||l_functional_currency_code);
2760 END IF;
2761
2762
2763 -- Get the currency conversion details from ACCOUNTING_Util
2764 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2765 p_khr_id => px_quot_rec.khr_id,
2766 p_to_currency => l_functional_currency_code,
2767 p_transaction_date => p_sys_date,
2768 p_amount => l_hard_coded_amount,
2769 x_return_status => l_return_status,
2770 x_contract_currency => l_contract_currency_code,
2771 x_currency_conversion_type => l_currency_conversion_type,
2772 x_currency_conversion_rate => l_currency_conversion_rate,
2773 x_currency_conversion_date => l_currency_conversion_date,
2774 x_converted_amount => l_converted_amount);
2775
2776 IF (is_debug_statement_on) THEN
2777 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2778 'after call to OKL_ACCOUNTING_UTIL.convert_to_functional_currency :'||l_return_status);
2779 END IF;
2780
2781 -- raise exception if error
2782 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2783 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2784 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2785 RAISE OKL_API.G_EXCEPTION_ERROR;
2786 END IF;
2787
2788 px_quot_rec.currency_code := l_contract_currency_code;
2789 px_quot_rec.currency_conversion_code := l_functional_currency_code;
2790
2791 -- If the functional currency is different from contract currency then set
2792 -- currency conversion columns
2793 IF l_functional_currency_code <> l_contract_currency_code THEN
2794
2795 -- Set the currency conversion columns
2796 px_quot_rec.currency_conversion_type := l_currency_conversion_type;
2797 px_quot_rec.currency_conversion_rate := l_currency_conversion_rate;
2798 px_quot_rec.currency_conversion_date := l_currency_conversion_date;
2799
2800 END IF;
2801
2802 -- Set the return status
2803 x_return_status := l_return_status;
2804
2805 IF (is_debug_procedure_on) THEN
2806 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2807 END IF;
2808
2809 EXCEPTION
2810
2811 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2812
2813 x_return_status := OKL_API.G_RET_STS_ERROR;
2814
2815 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2816
2817 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2818
2819 WHEN OTHERS THEN
2820
2821 -- unexpected error
2822 OKL_API.set_message(
2823 p_app_name => 'OKC',
2824 p_msg_name => g_unexpected_error,
2825 p_token1 => g_sqlcode_token,
2826 p_token1_value => sqlcode,
2827 p_token2 => g_sqlerrm_token,
2828 p_token2_value => sqlerrm);
2829
2830 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2831
2832 END set_currency_defaults;
2833
2834
2835 -- Start of comments
2836 --
2837 -- Procedure Name : get_net_gain_loss
2838 -- Description : returns the net gain loss on a termination quote
2839 -- Business Rules :
2840 -- Parameters : IN parameters - quote header, contract id
2841 -- : OUT parameters - net gain loss, return status
2842 -- Version : 1.0
2843 -- History : rkuttiya created 15-SEP-2003 Bug: 2794685
2844 -- : RMUNJULU 2794685 Added comments
2845 -- : rmunjulu 3797384 Added code for passing quote_eff_from date
2846 -- and quote_id to formula engine
2847 -- rmunjulu EDAT 29-Dec-04 did to_char to convert to right format
2848
2849 PROCEDURE Get_Net_Gain_Loss(
2850 p_quote_rec IN quot_rec_type,
2851 p_chr_id IN NUMBER,
2852 x_return_status OUT NOCOPY VARCHAR2,
2853 x_net_gain_loss OUT NOCOPY NUMBER) IS
2854
2855 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
2856 l_rule_code CONSTANT VARCHAR2(30) := 'AMGALO';
2857 l_rgd_code VARCHAR2(30);
2858 l_qtev_rec okl_trx_quotes_pub.qtev_rec_type;
2859 l_rule_khr_id NUMBER;
2860
2861
2862 l_calc_option VARCHAR2(150);
2863 l_fixed_value NUMBER;
2864 l_formula_name VARCHAR2(150);
2865
2866 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2867 l_params OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
2868 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'Get_Net_Gain_Loss';
2869 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2870 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2871 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2872 BEGIN
2873
2874 IF (is_debug_procedure_on) THEN
2875 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
2876 END IF;
2877
2878 l_qtev_rec.khr_id := p_quote_rec.khr_id;
2879 l_qtev_rec.qtp_code := p_quote_rec.qtp_code;
2880
2881 IF l_qtev_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
2882 l_rgd_code := 'AVTGAL';
2883 ELSE
2884 l_rgd_code := 'AMTGAL';
2885 END IF;
2886
2887 --get the rule attributes
2888 l_rule_khr_id := okl_am_util_pvt.get_rule_chr_id (l_qtev_rec);
2889
2890 IF (is_debug_statement_on) THEN
2891 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2892 'Before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
2893 END IF;
2894
2895 OKL_AM_UTIL_PVT.get_rule_record(
2896 p_rgd_code => l_rgd_code,
2897 p_rdf_code => l_rule_code,
2898 p_chr_id => l_rule_khr_id,
2899 p_cle_id => NULL,
2900 x_rulv_rec => l_rulv_rec,
2901 x_return_status => l_return_status,
2902 p_message_yn => FALSE);
2903
2904 IF (is_debug_statement_on) THEN
2905 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2906 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
2907 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2908 'l_rgd_code :'||l_rgd_code);
2909 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2910 'l_rule_code :'||l_rule_code);
2911 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2912 'l_rule_khr_id :'||l_rule_khr_id);
2913 END IF;
2914
2915 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
2916 l_calc_option := l_rulv_rec.rule_information1;
2917 l_fixed_value := NVL (To_Number (l_rulv_rec.rule_information2), 0);
2918 l_formula_name := l_rulv_rec.rule_information3;
2919 END IF;
2920
2921 IF l_calc_option = 'NOT_APPLICABLE' THEN -- Net Gain/Loss Option is NOT APPLICABLE
2922
2923 x_net_gain_loss := 0;
2924
2925 ELSIF l_calc_option = 'USE_FIXED_AMOUNT' THEN -- Net Gain/Loss Option is FIXED AMOUNT
2926
2927 x_net_gain_loss := l_fixed_value;
2928
2929 ELSIF l_calc_option = 'USE_FORMULA' THEN -- Net Gain/Loss Option is FORMULA
2930
2931 l_params(1).name := 'QUOTE_ID';
2932 l_params(1).value := p_quote_rec.id;
2933
2934 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
2935
2936 -- set the operands for formula engine with quote_effective_from date
2937 l_params(2).name := 'quote_effective_from_date';
2938 l_params(2).value := to_char(p_quote_rec.date_effective_from, 'MM/DD/YYYY'); -- rmunjulu EDAT 29-Dec-04 did to_char to convert to right format
2939
2940 -- set the operands for formula engine with quote_id
2941 l_params(3).name := 'quote_id';
2942 l_params(3).value := to_char(p_quote_rec.id);
2943
2944 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
2945 IF (is_debug_statement_on) THEN
2946 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2947 'Before call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
2948 END IF;
2949 -- Get the formula value for the formula for Net Gain/Loss Formula
2950 OKL_AM_UTIL_PVT.get_formula_value (
2951 p_formula_name => l_formula_name,
2952 p_chr_id => l_rule_khr_id,
2953 p_cle_id => NULL,
2954 p_additional_parameters => l_params,
2955 x_formula_value => x_net_gain_loss,
2956 x_return_status => l_return_status);
2957 IF (is_debug_statement_on) THEN
2958 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2959 'after call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
2960 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2961 'l_formula_name :'||l_formula_name);
2962 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2963 'l_rule_khr_id :'||l_rule_khr_id);
2964 END IF;
2965
2966 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2967 x_net_gain_loss := 0;
2968 --RAISE OKL_API.G_EXCEPTION_ERROR;
2969 END IF;
2970 ELSE
2971 x_net_gain_loss := 0;
2972 END IF;
2973
2974 IF (is_debug_procedure_on) THEN
2975 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2976 END IF;
2977
2978 EXCEPTION
2979 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2980 x_return_status := OKL_API.G_RET_STS_ERROR;
2981 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2982 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2983 WHEN OTHERS THEN
2984 IF (is_debug_exception_on) THEN
2985 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2986 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2987 END IF;
2988
2989 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2990 OKL_API.set_message(p_app_name => g_app_name,
2991 p_msg_name => g_unexpected_error,
2992 p_token1 => g_sqlcode_token,
2993 p_token1_value => sqlcode,
2994 p_token2 => g_sqlerrm_token,
2995 p_token2_value => sqlerrm);
2996 END Get_Net_Gain_Loss;
2997
2998
2999 -- Start of comments
3000 --
3001 -- Procedure Name : create_terminate_quote
3002 -- Description : create the terminate quote
3003 -- Business Rules :
3004 -- Parameters : quote header, quote lines
3005 -- Version : 1.0
3006 -- History : SECHAWLA 25-NOV-02 - Bug #2680542 :
3007 -- 1) Added x_partial_asset_line out parameter to validate_quote
3008 -- procedure call
3009 -- 2) used x_partial_asset_line parameter later in the procedure
3010 -- to check if quote includes partial asset line.
3011 -- 3) removed the code to populate l_asset_tbl as it is no longer
3012 -- required to be passed to calculate quote api.
3013 -- 4) passed lp_assn_tbl instead of l_asset_tbl to calculate quote
3014 -- api as the calculate quote api now uses the same asset
3015 -- record structure as the create quote api.
3016 -- 5) Removed DEFAULT from procedure parameters.
3017 --
3018 -- : SECHAWLA 06-DEC-02 - Bug # 2699412 :
3019 -- 1) Change the quote type from Auto to Manual,
3020 -- if Auto Quotes are not allowed
3021 -- 2) Call send quote WF only for Auto Quotes
3022 -- 3) If quote type changed from Auto to Manual, then
3023 -- notify manual termination quotes REP
3024 -- : RMUNJULU 23-DEC-02 2726739 Multi-currency changes, default
3025 -- currency columns
3026 -- : SECHAWLA 02-JAN-03 2724951 : Changed the event name for
3027 -- Notify Manual Quote Rep WF
3028 -- : GKADARKA 06-JAN-03 2683876 Added code to check for non
3029 -- terminated assets in cursor
3030 -- : SECHAWLA 16-JAN-02 Bug # 2754280 : Changed the call to fn
3031 -- get_user_profile_option_name to refer it from am util
3032 -- : SECHAWLA 14-FEB-03 Bug 2749690 : Added code to update the
3033 -- quote header with total net investment,
3034 -- unbilled rec and residual value from all the quote lines
3035 -- : RMUNJULU 18-FEB-03 2804703 Chngd cursor to get active lines
3036 -- : SECHAWLA 18-FEB-03 2807201 Moved the check_quote_type procedure call from
3037 -- validate quote to the beginning if this procedure.
3038 -- : SECHAWLA 28-FEB-03 2757175 Modified the Manual Quote notification message to
3039 -- display the profile value and not the underlying ID.
3040 -- : SECHAWLA 14-APR-03 2902588 Changed the standard REQUIRED message to OKL_AM_NO_VENDOR_PROGRAM
3041 -- in the not null validation of l_rule_chr_id
3042 -- : SECHAWLA 15-APR-03 2902588 Changed the fetch order of columns in cur_k_end_date cursor.
3043 -- : SECHAWLA 03-OCT-03 ER 2777984 Calculate Quote Payments for a partial termination quote
3044 -- : RMUNJULU 3241502 Added code to set split_asset_number with UPPER CASE
3045 -- : rmunjulu 3842101 changed code so that gainloss is done after quote updated for net investment
3046 -- : RMUNJULU EDT 3797384 Added code to default eff_from_date and
3047 -- changed condition to check for early termination yn
3048 -- : RMUNJULU LOANS_ENHANCEMENTS Add code to evaluate and populate perdiem amount
3049 -- : SECHAWLA 15-Jun-09 7383445 Added new parameter p_term_from_intf
3050 -- End of comments
3051 PROCEDURE create_terminate_quote(
3052 p_api_version IN NUMBER,
3053 p_init_msg_list IN VARCHAR2 ,
3054 x_return_status OUT NOCOPY VARCHAR2,
3055 x_msg_count OUT NOCOPY NUMBER,
3056 x_msg_data OUT NOCOPY VARCHAR2,
3057 p_quot_rec IN quot_rec_type,
3058 p_assn_tbl IN assn_tbl_type,
3059 p_qpyv_tbl IN qpyv_tbl_type ,
3060 x_quot_rec OUT NOCOPY quot_rec_type,
3061 x_tqlv_tbl OUT NOCOPY tqlv_tbl_type,
3062 x_assn_tbl OUT NOCOPY assn_tbl_type,
3063 p_term_from_intf IN VARCHAR2 DEFAULT 'N') AS
3064
3065 -- SECHAWLA 14-APR-03 2902588 : Added contract_number in the SELECT clause. Changed FROM claue to use
3066 -- okc_k_headers_b instead of okc_k_headers_v
3067 -- Cursor to get the end date of the contract
3068 CURSOR cur_k_end_date ( p_chr_id NUMBER) IS
3069 SELECT contract_number, end_date
3070 FROM OKC_K_HEADERS_B
3071 WHERE id = p_chr_id;
3072
3073 -- Cursor to get the number of financial assets for the contract
3074 -- Outer join with line styles in UV... but gives error here in calculate quote
3075 -- GKADARKA 06-JAN-03 2683876 Added code to check for non terminated assets
3076 -- RMUNJULU 18-FEB-03 2804703 Changed cursor to check for active lines only
3077 CURSOR cur_k_assets ( p_chr_id NUMBER ) IS
3078 SELECT COUNT(OKLV.id )
3079 FROM OKC_K_LINES_V OKLV,
3080 OKC_LINE_STYLES_V OLSV,
3081 OKC_K_HEADERS_V KHR
3082 WHERE OKLV.lse_id = OLSV.id
3083 AND OLSV.lty_code = 'FREE_FORM1'
3084 AND OKLV.chr_id = p_chr_id
3085 AND OKLV.sts_code = KHR.sts_code
3086 AND OKLV.chr_id = KHR.id;
3087 --AND OKLV.date_terminated IS NULL; -- RMUNJULU 18-FEB-03 2804703 removed
3088
3089 --SECHAWLA 28-FEB-03 Bug # 2757175 : Added the following cursor
3090 -- This cursor isused to get the display name for a role
3091 CURSOR l_wfroles_csr(p_name wf_roles.name%TYPE) IS
3092 SELECT display_name
3093 FROM wf_roles
3094 WHERE name = p_name;
3095
3096 l_display_name wf_roles.display_name%TYPE;
3097 lx_contract_status VARCHAR2(200);
3098 --SECHAWLA 28-FEB-03 Bug # 2757175 : end new declarations
3099
3100 lp_quot_rec quot_rec_type := p_quot_rec;
3101 lx_quot_rec quot_rec_type;
3102 lp_assn_tbl assn_tbl_type := p_assn_tbl;
3103 lx_assn_tbl assn_tbl_type := p_assn_tbl;
3104 lx_tqlv_tbl tqlv_tbl_type;
3105 l_qpyv_tbl qpyv_tbl_type;
3106 l_quote_eff_days NUMBER;
3107 l_quote_eff_max_days NUMBER;
3108 l_days_before_k_exp NUMBER;
3109 l_api_version CONSTANT NUMBER := 1;
3110 l_api_name CONSTANT VARCHAR2(30) := 'create_terminate_quote';
3111 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3112 l_no_of_assets NUMBER := -1;
3113 l_k_end_date DATE;
3114 l_sys_date DATE;
3115 i NUMBER := 0;
3116 l_rule_chr_id NUMBER;
3117 l_event_name VARCHAR2(2000);
3118 lx_partial_asset_line BOOLEAN;
3119
3120 lx_auto_to_manual BOOLEAN := FALSE; --added
3121 lx_new_quote_type VARCHAR2(30); --added
3122 l_user_profile_name VARCHAR2(240); --added
3123 l_manual_quote_rep VARCHAR2(320); --added
3124
3125 --SECHAWLA 14-FEB-03 Bug 2749690 : new declarations
3126 l_total_net_investment NUMBER := 0;
3127 l_total_unbilled_rec NUMBER := 0;
3128 l_total_residual_value NUMBER := 0;
3129 lp_empty_quot_rec quot_rec_type;
3130
3131 -- SECHAWLA 14-APR-03 2902588 : New Declarations
3132 l_contract_number okc_k_headers_b.contract_number%TYPE;
3133
3134 --rkuttiya 15-SEP-2003 for bug: 2794685
3135 lx_net_gain_loss NUMBER;
3136
3137 -- rmunjulu LOANS_ENHANCEMENTS
3138 l_per_diem_amt NUMBER;
3139 l_params OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
3140
3141 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_terminate_quote';
3142 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
3143 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
3144 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
3145 BEGIN
3146
3147 IF (is_debug_procedure_on) THEN
3148 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
3149 END IF;
3150
3151 --Print Input Variables
3152 IF (is_debug_statement_on) THEN
3153 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3154 'p_api_version :'||p_api_version);
3155 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3156 'p_init_msg_list :'||p_init_msg_list);
3157 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
3158 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
3159 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
3160 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
3161 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
3162 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
3163 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
3164 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
3165 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
3166 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
3167 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
3168 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.early_termination_yn : '||p_quot_rec.early_termination_yn );
3169 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.partial_yn : '||p_quot_rec.partial_yn );
3170 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.preproceeds_yn : '||p_quot_rec.preproceeds_yn );
3171 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.summary_format_yn : '||p_quot_rec.summary_format_yn );
3172 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_yn : '||p_quot_rec.consolidated_yn );
3173 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_requested : '||p_quot_rec.date_requested );
3174 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_proposal : '||p_quot_rec.date_proposal );
3175 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_to : '||p_quot_rec.date_effective_to );
3176 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_accepted : '||p_quot_rec.date_accepted );
3177 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.payment_received_yn : '||p_quot_rec.payment_received_yn );
3178 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.requested_by : '||p_quot_rec.requested_by );
3179 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
3180 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
3181 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
3182 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_amount : '||p_quot_rec.purchase_amount );
3183 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_formula : '||p_quot_rec.purchase_formula );
3184 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.asset_value : '||p_quot_rec.asset_value );
3185 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.residual_value : '||p_quot_rec.residual_value );
3186 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.unbilled_receivables : '||p_quot_rec.unbilled_receivables );
3187 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.gain_loss : '||p_quot_rec.gain_loss );
3188 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.PERDIEM_AMOUNT : '||p_quot_rec.PERDIEM_AMOUNT );
3189 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_code : '||p_quot_rec.currency_code );
3190 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_conversion_code : '||p_quot_rec.currency_conversion_code );
3191 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
3192 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.repo_quote_indicator_yn : '||p_quot_rec.repo_quote_indicator_yn );
3193
3194 IF (p_assn_tbl.COUNT > 0) THEN
3195 FOR i IN p_assn_tbl.FIRST..p_assn_tbl.LAST LOOP
3196
3197 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_id :'|| p_assn_tbl(i).p_asset_id );
3198 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_number :'|| p_assn_tbl(i).p_asset_number );
3199 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_asset_qty :'|| p_assn_tbl(i).p_asset_qty );
3200 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_quote_qty :'|| p_assn_tbl(i).p_quote_qty );
3201 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_assn_tbl('||i||').'||'p_split_asset_number :'|| p_assn_tbl(i).p_split_asset_number);
3202 End loop;
3203 END IF;
3204
3205 END IF;
3206
3207
3208 IF (is_debug_statement_on) THEN
3209 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3210 'before call to OKL_API.START_ACTIVITY :'||l_return_status);
3211 END IF;
3212
3213
3214
3215
3216
3217 --Check API version, initialize message list and create savepoint.
3218 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3219 G_PKG_NAME,
3220 p_init_msg_list,
3221 l_api_version,
3222 p_api_version,
3223 '_PVT',
3224 x_return_status);
3225
3226 IF (is_debug_statement_on) THEN
3227 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3228 'after call to OKL_API.START_ACTIVITY :'||l_return_status);
3229 END IF;
3230
3231 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3232 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3233 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3234 RAISE OKL_API.G_EXCEPTION_ERROR;
3235 END IF;
3236
3237 SELECT SYSDATE INTO l_sys_date FROM DUAL;
3238
3239 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
3240 -- RMUNJULU EDT 3797384 default the date effective from in the beginning
3241 IF lp_quot_rec.date_effective_from IS NULL
3242 OR lp_quot_rec.date_effective_from = OKL_API.G_MISS_DATE THEN
3243
3244 lp_quot_rec.date_effective_from := l_sys_date;
3245
3246 END IF;
3247 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
3248
3249 -- SECHAWLA 28-FEB-03 Bug # 2757175 : Moved the following validation from validate_contract procedure
3250
3251 -- Call the validate contract to check contract status
3252
3253 IF (is_debug_statement_on) THEN
3254 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3255 'before call to OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract :'||l_return_status);
3256 END IF;
3257
3258
3259 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
3260 p_api_version => p_api_version,
3261 p_init_msg_list => OKL_API.G_FALSE,
3262 x_return_status => l_return_status,
3263 x_msg_count => x_msg_count,
3264 x_msg_data => x_msg_data,
3265 p_contract_id => lp_quot_rec.khr_id,
3266 p_control_flag => 'TRMNT_QUOTE_CREATE',
3267 x_contract_status => lx_contract_status);
3268
3269 IF (is_debug_statement_on) THEN
3270 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3271 'after call to OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract :'||l_return_status);
3272 END IF;
3273
3274 -- If error then above api will set the message, so exit now
3275 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3276 x_return_status := OKL_API.G_RET_STS_ERROR;
3277 RAISE OKL_API.G_EXCEPTION_ERROR;
3278 END IF;
3279 -- SECHAWLA 28-FEB-03 Bug # 2757175 :end moved code
3280
3281 OPEN cur_k_end_date(lp_quot_rec.khr_id);
3282 -- SECHAWLA 14-APR-03 2902588 : fetched the new column : contract_number
3283 FETCH cur_k_end_date INTO l_contract_number, l_k_end_date ; -- SECHAWLA 15-APR-03 2902588 : Changed the fetch order of columns
3284 IF cur_k_end_date%NOTFOUND THEN
3285 l_k_end_date := OKL_API.G_MISS_DATE;
3286 END IF;
3287 CLOSE cur_k_end_date;
3288
3289 OPEN cur_k_assets (lp_quot_rec.khr_id);
3290 FETCH cur_k_assets INTO l_no_of_assets;
3291 IF cur_k_assets%NOTFOUND THEN
3292 l_no_of_assets := -1;
3293 END IF;
3294 CLOSE cur_k_assets;
3295
3296 -- SECHAWLA 18-FEB-03 Bug # 2807201 : Moved the quote_type_check procedure call here from vaidate_quote
3297 -- as we want to validate and change the quote type (if required) in the beginning, before any other processing
3298 -- check if quote type is valid
3299 lx_new_quote_type := lp_quot_rec.qtp_code; ---SECHAWLA 2699412 added
3300
3301 -- rmunjulu 4923976 : added if check so that quote type check should not be done for TER_RELEASE_WO_PURCHASE quote
3302 IF lp_quot_rec.qtp_code <> 'TER_RELEASE_WO_PURCHASE' THEN
3303
3304 IF (is_debug_statement_on) THEN
3305 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3306 'before call to quote_type_check :'||l_return_status);
3307 END IF;
3308 quote_type_check(
3309 -- p_qtp_code => p_quot_rec.qtp_code, -- SECHAWLA 2699412
3310 p_qtp_code => lx_new_quote_type, -- SECHAWLA 2699412 changed
3311 p_khr_id => lp_quot_rec.khr_id, -- SECHAWLA 02-JAN-03 Added
3312 x_auto_to_manual => lx_auto_to_manual, -- -SECHAWLA 2699412 added
3313 x_return_status => l_return_status);
3314
3315 IF (is_debug_statement_on) THEN
3316 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3317 'after call to quote_type_check :'||l_return_status);
3318 END IF;
3319
3320 END IF; -- rmunjulu 4923976
3321
3322 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3323 x_return_status := OKL_API.G_RET_STS_ERROR;
3324 --Please select a valid Quote Type.
3325 OKL_API.set_message( p_app_name => 'OKL',
3326 p_msg_name =>'OKL_AM_QTP_CODE_INVALID');
3327 RAISE OKL_API.G_EXCEPTION_ERROR; -- SECHAWLA 28-FEB-03 Bug 2757175 : Changed the exception name as a result
3328 -- of moving this procedure from validate_quote on 18-FEB-03 Bug # 2807201
3329 END IF;
3330
3331 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- Added
3332 IF (lx_auto_to_manual) THEN
3333 lp_quot_rec.qtp_code := lx_new_quote_type;
3334 END IF;
3335
3336 -- SECHAWLA 18-FEB-03 Bug # 2807201 : end moved code
3337
3338 l_rule_chr_id := okl_am_util_pvt.get_rule_chr_id (lp_quot_rec);
3339
3340 -- SECHAWLA 28-FEB-03 Bug # 2757175 : Added a not null validation for l_rule_chr_id
3341 IF l_rule_chr_id IS NULL THEN
3342 x_return_status := OKL_API.G_RET_STS_ERROR;
3343 -- SECHAWLA 14-APR-03 2902588 : Use the following message instead of the standard REQUIRED message
3344
3345 --Unable to create quote because the contract CONTRACT_NUMBER does not have an associated vendor program.
3346 OKC_API.set_message( p_app_name => 'OKL',
3347 p_msg_name => 'OKL_AM_NO_VENDOR_PROGRAM',
3348 p_token1 => 'CONTRACT_NUMBER',
3349 p_token1_value => l_contract_number);
3350 RAISE OKL_API.G_EXCEPTION_ERROR;
3351 END IF;
3352
3353 -- SECHAWLA Bug # 2699412 : Moved the following code here so that
3354 -- l_days_before_k_exp can be passed to validate_quote
3355 -- to check for early terminations
3356
3357
3358 IF (is_debug_statement_on) THEN
3359 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3360 'before call to term_status :'||l_return_status);
3361 END IF;
3362 -- set term status from rules
3363 term_status(
3364 p_quot_rec => lp_quot_rec,
3365 p_rule_chr_id => l_rule_chr_id,
3366 x_days_before_k_exp => l_days_before_k_exp,
3367 x_return_status => l_return_status);
3368
3369 IF (is_debug_statement_on) THEN
3370 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3371 'after call to term_status :'||l_return_status);
3372 END IF;
3373
3374 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3375 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3376 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3377 RAISE OKL_API.G_EXCEPTION_ERROR;
3378 END IF;
3379 -- end moved code
3380
3381 -- SECHAWLA Bug #2680542 : Added x_partial_asset_line parameter to
3382 -- validate_quote procedure call
3383 -- check if quote valid
3384 IF (is_debug_statement_on) THEN
3385 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3386 'before call to validate_quote :'||l_return_status);
3387 END IF;
3388
3389 validate_quote(
3390 p_api_version => p_api_version,
3391 p_init_msg_list => OKL_API.G_FALSE,
3392 x_return_status => l_return_status,
3393 x_msg_count => x_msg_count,
3394 x_msg_data => x_msg_data,
3395 p_quot_rec => lp_quot_rec,
3396 p_assn_tbl => lp_assn_tbl,
3397 p_k_end_date => l_k_end_date,
3398 p_no_of_assets => l_no_of_assets,
3399 p_sys_date => l_sys_date,
3400 p_rule_chr_id => l_rule_chr_id,
3401 p_days_before_k_exp => l_days_before_k_exp, --SECHAWLA 06-DEC-02 2699412 added
3402 x_partial_asset_line => lx_partial_asset_line);
3403
3404 IF (is_debug_statement_on) THEN
3405 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3406 'after call to validate_quote :'||l_return_status);
3407 END IF;
3408
3409 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3411 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3412 RAISE OKL_API.G_EXCEPTION_ERROR;
3413 END IF;
3414
3415 -- RMUNJULU 3241502 Added code to set split_asset_number with UPPER CASE
3416 IF (lp_assn_tbl.COUNT > 0) THEN
3417 FOR i IN lp_assn_tbl.FIRST..lp_assn_tbl.LAST LOOP
3418
3419 IF lp_assn_tbl(i).p_split_asset_number IS NOT NULL
3420 AND lp_assn_tbl(i).p_split_asset_number <> OKL_API.G_MISS_CHAR THEN
3421 lp_assn_tbl(i).p_split_asset_number := UPPER(lp_assn_tbl(i).p_split_asset_number);
3422 END IF;
3423 END LOOP;
3424 END IF;
3425
3426 IF (is_debug_statement_on) THEN
3427 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3428 'before call to quote_effectivity :'||l_return_status);
3429 END IF;
3430 -- set the date eff to from rules
3431 quote_effectivity(
3432 p_quot_rec => lp_quot_rec,
3433 p_rule_chr_id => l_rule_chr_id,
3434 x_quote_eff_days => l_quote_eff_days,
3435 x_quote_eff_max_days => l_quote_eff_max_days,
3436 x_return_status => l_return_status);
3437
3438 IF (is_debug_statement_on) THEN
3439 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3440 'after call to quote_effectivity :'||l_return_status);
3441 END IF;
3442
3443 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3444 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3445 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3446 RAISE OKL_API.G_EXCEPTION_ERROR;
3447 END IF;
3448
3449 -- check if early termination
3450 IF (lp_quot_rec.early_termination_yn IS NULL)
3451 OR (lp_quot_rec.early_termination_yn = OKL_API.G_MISS_CHAR) THEN
3452 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
3453 -- IF (TRUNC(l_sys_date) < TRUNC(l_k_end_date) - l_days_before_k_exp) THEN
3454 -- rmunjulu EDT 3797384 changed check to check based on effective date
3455 IF (TRUNC(lp_quot_rec.date_effective_from) < TRUNC(l_k_end_date) - l_days_before_k_exp) THEN
3456 lp_quot_rec.early_termination_yn := 'Y';
3457 ELSE
3458 lp_quot_rec.early_termination_yn := 'N';
3459 END IF;
3460 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
3461 END IF;
3462
3463 -- check if partial quote
3464 IF (lp_quot_rec.partial_yn IS NULL)
3465 OR (lp_quot_rec.partial_yn = OKL_API.G_MISS_CHAR) THEN
3466 -- SECHAWLA Bug #2680542 : Added (lx_partial_asset_line) condition to
3467 -- the following IF to check if the
3468 -- quote is a partial quote
3469 IF (p_assn_tbl.COUNT < l_no_of_assets)
3470 OR (lx_partial_asset_line) THEN -- added second condition
3471 lp_quot_rec.partial_yn := 'Y';
3472 ELSE
3473 lp_quot_rec.partial_yn := 'N';
3474 END IF;
3475 END IF;
3476
3477
3478 IF (is_debug_statement_on) THEN
3479 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3480 'before call to set_quote_defaults :'||l_return_status);
3481 END IF;
3482
3483 -- Set the quote defaults
3484 set_quote_defaults(
3485 px_quot_rec => lp_quot_rec,
3486 p_rule_chr_id => l_rule_chr_id,
3487 p_sys_date => l_sys_date,
3488 x_return_status => l_return_status);
3489
3490 IF (is_debug_statement_on) THEN
3491 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3492 'after call to set_quote_defaults :'||l_return_status);
3493 END IF;
3494
3495 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3497 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3498 RAISE OKL_API.G_EXCEPTION_ERROR;
3499 END IF;
3500
3501
3502
3503 IF (is_debug_statement_on) THEN
3504 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3505 'before call to set_currency_defaults :'||l_return_status);
3506 END IF;
3507
3508 -- RMUNJULU 23-DEC-02 2726739 Multi-currency changes
3509 -- Default the Multi-Currency Columns
3510 set_currency_defaults(
3511 px_quot_rec => lp_quot_rec,
3512 p_sys_date => l_sys_date,
3513 x_return_status => l_return_status);
3514
3515 IF (is_debug_statement_on) THEN
3516 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3517 'after call to set_currency_defaults :'||l_return_status);
3518 END IF;
3519
3520 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3522 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3523 RAISE OKL_API.G_EXCEPTION_ERROR;
3524 END IF;
3525
3526 IF (is_debug_statement_on) THEN
3527 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3528 'before call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);
3529 END IF;
3530
3531 -- call the pub tapi insert
3532 OKL_TRX_QUOTES_PUB.insert_trx_quotes (
3533 p_api_version => p_api_version,
3534 p_init_msg_list => OKL_API.G_FALSE,
3535 x_msg_count => x_msg_count,
3536 x_msg_data => x_msg_data,
3537 p_qtev_rec => lp_quot_rec,
3538 x_qtev_rec => lx_quot_rec,
3539 x_return_status => l_return_status);
3540
3541 IF (is_debug_statement_on) THEN
3542 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3543 'after call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);
3544 END IF;
3545
3546 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3548 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3549 RAISE OKL_API.G_EXCEPTION_ERROR;
3550 END IF;
3551
3552
3553 IF (is_debug_statement_on) THEN
3554 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3555 'before call to OKL_AM_PARTIES_PVT.create_quote_parties :'||l_return_status);
3556 END IF;
3557
3558 -- Create quote parties
3559 OKL_AM_PARTIES_PVT.create_quote_parties (
3560 p_qtev_rec => lx_quot_rec,
3561 p_qpyv_tbl => p_qpyv_tbl,
3562 x_qpyv_tbl => l_qpyv_tbl,
3563 x_return_status => l_return_status);
3564
3565 IF (is_debug_statement_on) THEN
3566 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3567 'after call to OKL_AM_PARTIES_PVT.create_quote_parties :'||l_return_status);
3568 END IF;
3569
3570
3571 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3572 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3573 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3574 RAISE OKL_API.G_EXCEPTION_ERROR;
3575 END IF;
3576
3577 -- call quote calculation api (pass assets tbl)
3578 -- this will insert quote lines
3579
3580 IF (is_debug_statement_on) THEN
3581 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3582 'before call to OKL_AM_CALCULATE_QUOTE_PVT.generate :'||l_return_status);
3583 END IF;
3584
3585 OKL_AM_CALCULATE_QUOTE_PVT.generate(
3586 p_api_version => p_api_version,
3587 p_init_msg_list => OKL_API.G_FALSE,
3588 x_msg_count => x_msg_count,
3589 x_msg_data => x_msg_data,
3590 p_qtev_rec => lx_quot_rec,
3591 --p_asset_tbl => l_asset_tbl, -- SECHAWLA Bug #2680542 : calculate quote api now uses the same asset
3592 p_asset_tbl => lp_assn_tbl, -- record structure as the create quote api
3593 x_tqlv_tbl => lx_tqlv_tbl,
3594 x_return_status => l_return_status);
3595
3596 IF (is_debug_statement_on) THEN
3597 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3598 'After call to OKL_AM_CALCULATE_QUOTE_PVT.generate :'||l_return_status);
3599 END IF;
3600
3601 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3602 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3603 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3604 RAISE OKL_API.G_EXCEPTION_ERROR;
3605 END IF;
3606
3607 -- SECHAWLA 14-FEB-03 2749690 : sum up the net investment, unbilled receivable and residual value amounts for
3608 -- the quote lines and store the total amounts at the quote header level
3609 IF lx_tqlv_tbl.COUNT > 0 THEN
3610 i := lx_tqlv_tbl.FIRST;
3611 LOOP
3612 IF lx_tqlv_tbl(i).qlt_code = 'AMCFIA' THEN
3613 l_total_net_investment := l_total_net_investment + lx_tqlv_tbl(i).asset_value;
3614 l_total_unbilled_rec := l_total_unbilled_rec + lx_tqlv_tbl(i).unbilled_receivables;
3615 l_total_residual_value := l_total_residual_value + lx_tqlv_tbl(i).residual_value;
3616 END IF;
3617
3618 EXIT WHEN (i = lx_tqlv_tbl.LAST);
3619 i := lx_tqlv_tbl.NEXT(i);
3620 END LOOP;
3621 END IF;
3622
3623 -- call the pub tapi update to update the above totals at the header level
3624 lp_quot_rec := lp_empty_quot_rec;
3625 lp_quot_rec.id := lx_quot_rec.id ;
3626 lp_quot_rec.asset_value := l_total_net_investment;
3627 lp_quot_rec.unbilled_receivables := l_total_unbilled_rec;
3628 lp_quot_rec.residual_value := l_total_residual_value;
3629
3630 IF (is_debug_statement_on) THEN
3631 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3632 'before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3633 END IF;
3634
3635 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3636 p_api_version => p_api_version,
3637 p_init_msg_list => OKL_API.G_FALSE,
3638 x_msg_count => x_msg_count,
3639 x_msg_data => x_msg_data,
3640 p_qtev_rec => lp_quot_rec,
3641 x_qtev_rec => lx_quot_rec,
3642 x_return_status => l_return_status);
3643
3644 IF (is_debug_statement_on) THEN
3645 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3646 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3647 END IF;
3648
3649 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3650 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3651 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3652 RAISE OKL_API.G_EXCEPTION_ERROR;
3653 END IF;
3654 -- end new code
3655
3656
3657 IF (is_debug_statement_on) THEN
3658 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3659 'before call to get_net_gain_loss :'||l_return_status);
3660 END IF;
3661
3662 -- rmunjulu 3842101 moved net gain loss calculation here
3663 get_net_gain_loss(
3664 p_quote_rec =>lx_quot_rec,
3665 p_chr_id =>lx_quot_rec.khr_id,
3666 x_return_status =>l_return_status,
3667 x_net_gain_loss =>lx_net_gain_loss) ;
3668
3669
3670 IF (is_debug_statement_on) THEN
3671 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3672 'After call to get_net_gain_loss :'||l_return_status);
3673 END IF;
3674
3675 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3677 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3678 RAISE OKL_API.G_EXCEPTION_ERROR;
3679 END IF;
3680
3681 -- rmunjulu 3842101 added this code here so that formula uses calculation from quote
3682 lp_quot_rec := lp_empty_quot_rec;
3683 lp_quot_rec.id := lx_quot_rec.id ;
3684 lp_quot_rec.gain_loss := lx_net_gain_loss;
3685
3686 l_params(1).name := 'QUOTE_ID';
3687 l_params(1).value := lp_quot_rec.id;
3688
3689 IF (is_debug_statement_on) THEN
3690 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3691 'before call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
3692 END IF;
3693
3694 -- rmunjulu LOANS_ENHANCEMENTS Evaluate Quote perdiem amount formula and set quote perdiem value
3695 OKL_AM_UTIL_PVT.get_formula_value(
3696 p_formula_name => 'QUOTE_PERDIEM_AMOUNT',
3697 p_chr_id => lx_quot_rec.khr_id,
3698 p_cle_id => NULL,
3699 p_additional_parameters => l_params,
3700 x_formula_value => l_per_diem_amt,
3701 x_return_status => l_return_status);
3702
3703 IF (is_debug_statement_on) THEN
3704 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3705 'After call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
3706 END IF;
3707
3708 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3710 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3711 RAISE OKL_API.G_EXCEPTION_ERROR;
3712 END IF;
3713
3714 lp_quot_rec.perdiem_amount := l_per_diem_amt;
3715
3716
3717 IF (is_debug_statement_on) THEN
3718 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3719 'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3720 END IF;
3721
3722 -- rmunjulu 3842101 update the quote header again with GAIN LOSS + PERDIEM
3723 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3724 p_api_version => p_api_version,
3725 p_init_msg_list => OKL_API.G_FALSE,
3726 x_msg_count => x_msg_count,
3727 x_msg_data => x_msg_data,
3728 p_qtev_rec => lp_quot_rec,
3729 x_qtev_rec => lx_quot_rec,
3730 x_return_status => l_return_status);
3731
3732 IF (is_debug_statement_on) THEN
3733 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3734 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3735 END IF;
3736
3737 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3738 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3739 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3740 RAISE OKL_API.G_EXCEPTION_ERROR;
3741 END IF;
3742
3743 -- rmunjulu TNA 4059175 Brought this up above the workflow processing.
3744 -- SECHAWLA 03-OCT-2003 11i10 ER 2777984:Calculate Quote Payments for a partial termination quote
3745 IF lx_quot_rec.partial_yn = 'Y' THEN
3746
3747 IF (is_debug_statement_on) THEN
3748 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3749 'Before call to OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments :'||l_return_status);
3750 END IF;
3751
3752 OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments(
3753 p_api_version => p_api_version,
3754 p_init_msg_list => OKL_API.G_FALSE,
3755 x_return_status => l_return_status,
3756 x_msg_count => x_msg_count,
3757 x_msg_data => x_msg_data,
3758 p_quote_id => lx_quot_rec.id);
3759
3760 IF (is_debug_statement_on) THEN
3761 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3762 'After call to OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments :'||l_return_status);
3763 END IF;
3764
3765 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3766 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3767 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3768 RAISE OKL_API.G_EXCEPTION_ERROR;
3769 END IF;
3770
3771 END IF;
3772 -- SECHAWLA 03-OCT-2003 11i10 ER : 2777984 end
3773
3774 /* -- rmunjulu messages come twice so this fix
3775 -- rmunjulu TNA 4059175 Added process message here to get the messages
3776 -- Save messages in database
3777 OKL_AM_UTIL_PVT.process_messages (
3778 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3779 p_trx_id => lx_quot_rec.id,
3780 x_return_status => l_return_status);
3781
3782 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3783 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3784 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3785 RAISE OKL_API.G_EXCEPTION_ERROR;
3786 END IF;
3787 */
3788
3789 -- rmunjulu TNA 4059175 Added new IF to check for non Release quotes and DO send quote only for those
3790 -- sechawla bug 7383445 - Added p_term_from_intf check : raise sendquote evant for approval, only if
3791 -- auto_accept_yn is set to 'N' in termination interface table. If auto_accept_yn is set to 'Y', quote
3792 -- staus should get automatically changed to APPROVED, and approval is not needed
3793 IF (lx_new_quote_type <> 'TER_RELEASE_WO_PURCHASE' AND (p_term_from_intf = 'N')) THEN
3794
3795 -- rmunjulu TNA By this time the quote is already switched to manual if needed so will not go into this if
3796 IF lx_new_quote_type NOT LIKE 'TER_MAN%' THEN -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- added
3797 -- Request quote approval and notification
3798
3799 IF (is_debug_statement_on) THEN
3800 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3801 'Before call to OKL_AM_WF.raise_business_event :'||l_return_status);
3802 END IF;
3803
3804 OKL_AM_WF.raise_business_event (
3805 p_transaction_id => lx_quot_rec.id,
3806 p_event_name => 'oracle.apps.okl.am.sendquote');
3807
3808 IF (is_debug_statement_on) THEN
3809 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3810 'After call to OKL_AM_WF.raise_business_event :'||l_return_status);
3811 END IF;
3812
3813 END IF;
3814
3815 -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- Added the following program logic
3816 -- to send notification to the manual
3817 -- quote representative, if the quote type was changed from Auto to Manual
3818 IF (lx_auto_to_manual) THEN
3819
3820 -- rmunjulu messages come twice so this fix
3821 -- rmunjulu TNA 4059175 Added process message here to get the messages
3822 -- Save messages in database
3823
3824 IF (is_debug_statement_on) THEN
3825 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3826 'Before call to OKL_AM_UTIL_PVT.process_messages :'||l_return_status);
3827 END IF;
3828
3829 OKL_AM_UTIL_PVT.process_messages (
3830 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3831 p_trx_id => lx_quot_rec.id,
3832 x_return_status => l_return_status);
3833
3834 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3835 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3836 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3837 RAISE OKL_API.G_EXCEPTION_ERROR;
3838 END IF;
3839
3840 -- clear the stack
3841 okl_api.init_msg_list ( p_init_msg_list => OKL_API.G_TRUE);
3842
3843 l_manual_quote_rep := fnd_profile.value('OKL_MANUAL_TERMINATION_QUOTE_REP');
3844
3845 IF l_manual_quote_rep IS NULL THEN
3846
3847
3848 IF (is_debug_statement_on) THEN
3849 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3850 'Before call to okl_am_util_pvt.get_user_profile_option_name :'||l_return_status);
3851 END IF;
3852
3853 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
3854 p_profile_option_name => 'OKL_MANUAL_TERMINATION_QUOTE_REP',
3855 x_return_status => l_return_status);
3856
3857 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
3858 --Manual Quote Representative profile is missing.
3859 OKL_API.set_message( p_app_name => 'OKL',
3860 p_msg_name => 'OKL_AM_NO_MQ_REP_PROFILE');
3861
3862 RAISE okl_api.G_EXCEPTION_ERROR;
3863 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
3864 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3865 END IF;
3866
3867 x_return_status := OKL_API.G_RET_STS_ERROR;
3868
3869 --Profile value not defined
3870 OKL_API.set_message(
3871 p_app_name => 'OKL',
3872 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
3873 p_token1 => 'PROFILE',
3874 p_token1_value => l_user_profile_name);
3875
3876 RAISE OKL_API.G_EXCEPTION_ERROR;
3877 END IF;
3878
3879 --SECHAWLA 28-FEB-03 Bug # 2757175 : get the display name for a role
3880 OPEN l_wfroles_csr(l_manual_quote_rep);
3881 FETCH l_wfroles_csr INTO l_display_name;
3882 CLOSE l_wfroles_csr;
3883
3884 IF l_display_name IS NULL THEN
3885 l_display_name := l_manual_quote_rep;
3886 END IF;
3887
3888 -- Contract only allows for manual quotes. Manual quote request
3889 -- has been sent to MAN_QUOTE_REP.
3890 OKL_API.set_message(
3891 p_app_name => 'OKL',
3892 p_msg_name => 'OKL_AM_MAN_QUOTE_ALLOWED',
3893 p_token1 => 'MAN_QUOTE_REP',
3894 p_token1_value => l_display_name); --SECHAWLA 28-FEB-03 Bug # 2757175 : Changed to show display_name
3895 -- instead of name
3896
3897 IF (is_debug_statement_on) THEN
3898 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3899 'Before call to OKL_AM_WF.raise_business_event p_event_name oracle.apps.okl.am.manualquote :'||l_return_status);
3900 END IF;
3901
3902
3903 --SECHAWLA 28-FEB-03 Bug # 2757175 : end modifications
3904 -- notify manual quote representative
3905 OKL_AM_WF.raise_business_event (
3906 p_transaction_id => lx_quot_rec.id,
3907 p_event_name => 'oracle.apps.okl.am.manualquote'); -- SECHAWLA 02-JAN-03 2724951 Changed the event name
3908
3909 -- Save messages in database
3910 OKL_AM_UTIL_PVT.process_messages (
3911 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3912 p_trx_id => lx_quot_rec.id,
3913 x_return_status => l_return_status);
3914
3915 ELSE -- if not auto to manual -- rmunjulu TNA 4059175 Added this if
3916
3917 -- Save messages in database
3918 OKL_AM_UTIL_PVT.process_messages (
3919 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3920 p_trx_id => lx_quot_rec.id,
3921 x_return_status => l_return_status);
3922
3923 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- added the exception handling
3924 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3925 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3926 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3927 RAISE OKL_API.G_EXCEPTION_ERROR;
3928 END IF;
3929 END IF;
3930 -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- end new code
3931 ELSE -- Quote is Termination Release Without Purchase -- rmunjulu TNA 4059175
3932
3933 -- Update the quote to approved directly
3934 lp_quot_rec := lp_empty_quot_rec;
3935 lp_quot_rec.id := lx_quot_rec.id ;
3936 lp_quot_rec.qst_code := 'APPROVED';
3937 lp_quot_rec.date_approved := sysdate;
3938
3939 IF (is_debug_statement_on) THEN
3940 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3941 'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);
3942 END IF;
3943
3944 -- rmunjulu TNA 4059175 update the quote header with status as APPROVED
3945 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3946 p_api_version => p_api_version,
3947 p_init_msg_list => OKL_API.G_FALSE,
3948 x_msg_count => x_msg_count,
3949 x_msg_data => x_msg_data,
3950 p_qtev_rec => lp_quot_rec,
3951 x_qtev_rec => lx_quot_rec,
3952 x_return_status => l_return_status);
3953
3954 IF (is_debug_statement_on) THEN
3955 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3956 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);
3957 END IF;
3958
3959 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3960 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3961 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3962 RAISE OKL_API.G_EXCEPTION_ERROR;
3963 END IF;
3964
3965 -- Save messages in database
3966 OKL_AM_UTIL_PVT.process_messages (
3967 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3968 p_trx_id => lx_quot_rec.id,
3969 x_return_status => l_return_status);
3970
3971 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- added the exception handling
3972 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3973 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3974 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3975 RAISE OKL_API.G_EXCEPTION_ERROR;
3976 END IF;
3977 END IF;
3978
3979 -- set the return status and out variables
3980 x_return_status := l_return_status;
3981 x_quot_rec := lx_quot_rec;
3982 x_assn_tbl := lx_assn_tbl;
3983 x_tqlv_tbl := lx_tqlv_tbl;
3984
3985 -- end the transaction
3986 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3987
3988
3989 IF (is_debug_statement_on) THEN
3990 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3991 'x_return_status..'||x_return_status);
3992 END IF;
3993
3994 IF (is_debug_procedure_on) THEN
3995 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,G_MODULE_NAME||'create_terminate_quote. ','End(-)');
3996 END IF;
3997
3998 EXCEPTION
3999 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4000 IF cur_k_assets%ISOPEN THEN
4001 CLOSE cur_k_assets;
4002 END IF;
4003 IF cur_k_end_date%ISOPEN THEN
4004 CLOSE cur_k_end_date;
4005 END IF;
4006 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
4007 IF l_wfroles_csr%ISOPEN THEN
4008 CLOSE l_wfroles_csr;
4009 END IF;
4010 IF (is_debug_statement_on) THEN
4011 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
4012 'EXCEPTION ERROR');
4013 END IF;
4014 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4015 (
4016 l_api_name,
4017 G_PKG_NAME,
4018 'OKL_API.G_RET_STS_ERROR',
4019 x_msg_count,
4020 x_msg_data,
4021 '_PVT'
4022 );
4023 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4024 IF cur_k_assets%ISOPEN THEN
4025 CLOSE cur_k_assets;
4026 END IF;
4027 IF cur_k_end_date%ISOPEN THEN
4028 CLOSE cur_k_end_date;
4029 END IF;
4030 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
4031 IF l_wfroles_csr%ISOPEN THEN
4032 CLOSE l_wfroles_csr;
4033 END IF;
4034 IF (is_debug_statement_on) THEN
4035 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
4036 'UNEXPECTED EXCEPTION ERROR');
4037 END IF;
4038 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4039 (
4040 l_api_name,
4041 G_PKG_NAME,
4042 'OKL_API.G_RET_STS_UNEXP_ERROR',
4043 x_msg_count,
4044 x_msg_data,
4045 '_PVT'
4046 );
4047 WHEN OTHERS THEN
4048 IF cur_k_assets%ISOPEN THEN
4049 CLOSE cur_k_assets;
4050 END IF;
4051 IF cur_k_end_date%ISOPEN THEN
4052 CLOSE cur_k_end_date;
4053 END IF;
4054 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
4055 IF l_wfroles_csr%ISOPEN THEN
4056 CLOSE l_wfroles_csr;
4057 END IF;
4058 IF (is_debug_statement_on) THEN
4059 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
4060 'OTHER EXCEPTION ERROR');
4061 END IF;
4062 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4063 (
4064 l_api_name,
4065 G_PKG_NAME,
4066 'OTHERS',
4067 x_msg_count,
4068 x_msg_data,
4069 '_PVT'
4070 );
4071 END create_terminate_quote;
4072
4073 FUNCTION check_repo_quote(p_quote_id IN VARCHAR2,
4074 x_return_status OUT NOCOPY VARCHAR2)
4075 RETURN VARCHAR2 IS
4076 l_repo_yn VARCHAR2(1);
4077
4078 CURSOR check_repo_csr(p_quote_id IN NUMBER) IS
4079 SELECT NVL(repo_quote_indicator_yn,'N')
4080 FROM OKL_TRX_QUOTES_B
4081 WHERE id = p_quote_id;
4082
4083 BEGIN
4084 -- Check whether the quote is for Repossession
4085 OPEN check_repo_csr(p_quote_id);
4086 FETCH check_repo_csr INTO l_repo_yn;
4087 CLOSE check_repo_csr;
4088
4089 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4090 RETURN l_repo_yn;
4091 EXCEPTION
4092 WHEN OTHERS THEN
4093
4094 IF check_repo_csr%ISOPEN THEN
4095 CLOSE check_repo_csr;
4096 END IF;
4097 x_return_status := OKL_API.G_RET_STS_ERROR;
4098 END;
4099
4100
4101
4102 END OKL_AM_CREATE_QUOTE_PVT;
[Home] [Help]