1 PACKAGE BODY OKL_AM_CREATE_QUOTE_PVT AS
2 /* $Header: OKLRCQTB.pls 120.30 2008/05/08 20:50:17 rmunjulu noship $ */
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 PROCEDURE validate_quote(
1499 p_api_version IN NUMBER,
1500 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 x_msg_count OUT NOCOPY NUMBER,
1503 x_msg_data OUT NOCOPY VARCHAR2,
1504 p_quot_rec IN quot_rec_type,
1505 p_assn_tbl IN OUT NOCOPY assn_tbl_type, -- SECHAWLA 2680542 Changed from IN to IN OUT
1506 p_k_end_date IN DATE,
1507 p_no_of_assets IN NUMBER,
1508 p_sys_date IN DATE,
1509 p_rule_chr_id IN NUMBER,
1510 p_days_before_k_exp IN NUMBER, --SECHAWLA Bug # 2699412 -added
1511 x_partial_asset_line OUT NOCOPY BOOLEAN
1512
1513 ) IS
1514
1515 -- SECHAWLA 17-FEB-03 Bug 2804703 : replaced the usage of okl_k_headers_full_v
1516 -- with okc_k_keaders_b, added sts_code.
1517 -- Cursor to get the khr details for the k passed
1518 CURSOR k_details_for_qte_csr (p_khr_id IN NUMBER) IS
1519 SELECT K.contract_number, K.sts_code, K.start_date, K.end_date -- rmunjulu EDAT
1520 FROM OKC_K_HEADERS_B K
1521 WHERE K.id = p_khr_id;
1522
1523
1524 -- Cursor to get the quote details of a quote that is already accepted for
1525 -- the same contract for which this quote is being generated.
1526 CURSOR get_accepted_qte_details_csr( p_khr_id IN NUMBER) IS
1527 SELECT Q.quote_number, Q.qtp_code
1528 FROM OKL_TRX_QUOTES_B Q
1529 WHERE Q.khr_id = p_khr_id
1530 AND Q.accepted_yn = 'Y';
1531
1532 -- This cursor is used to check if a particular asset belongs to a particular contract.
1533 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Changed cursor to also check for
1534 -- sts_code of line match with sts_code of contract
1535 CURSOR l_clines_csr (p_kle_id NUMBER ) IS
1536 SELECT KLE.chr_id, KLE.start_date -- rmunjulu EDAT
1537 FROM OKC_K_LINES_B KLE,
1538 OKC_K_HEADERS_B KHR
1539 WHERE KLE.id = p_kle_id
1540 AND KLE.chr_id = KHR.id
1541 AND KLE.sts_code = KHR.sts_code;
1542
1543
1544 -- This cursor is used to get the asset number
1545 CURSOR l_linesfull_csr(p_id NUMBER) IS
1546 SELECT name
1547 FROM okl_k_lines_full_v
1548 WHERE id = p_id;
1549
1550
1551 -- RMUNJULU 09-APR-03 2897523 Get the OKS lines if any linked to this covered asset
1552 -- RMUNJULU 10-APR-03 2900178 Changed the query from SELECT 1 to SELECT '1',
1553 -- and removed TO_CHAR conversion to krel.object1_id1
1554 CURSOR l_oks_lines_csr ( p_kle_id IN NUMBER) IS
1555 SELECT '1'
1556 FROM dual WHERE EXISTS (
1557 SELECT '1'
1558 FROM okc_k_headers_b oks_chrb,
1559 okc_line_styles_b oks_cov_pd_lse,
1560 okc_k_lines_b oks_cov_pd_cleb,
1561 okc_k_rel_objs krel,
1562 okc_line_styles_b lnk_srv_lse,
1563 okc_statuses_b lnk_srv_sts,
1564 okc_k_lines_b lnk_srv_cleb,
1565 okc_k_items lnk_srv_cim
1566 WHERE oks_chrb.scs_code = 'SERVICE'
1567 AND oks_chrb.id = oks_cov_pd_cleb.dnz_chr_id
1568 AND oks_cov_pd_cleb.lse_id = oks_cov_pd_lse.id
1569 AND oks_cov_pd_lse.lty_code = 'COVER_PROD'
1570 AND '#' = krel.object1_id2
1571 AND oks_cov_pd_cleb.id = krel.object1_id1
1572 AND krel.rty_code = 'OKLSRV'
1573 AND krel.chr_id = lnk_srv_cleb.dnz_chr_id
1574 AND krel.cle_id = lnk_srv_cleb.id
1575 AND lnk_srv_cleb.lse_id = lnk_srv_lse.id
1576 AND lnk_srv_lse.lty_code = 'LINK_SERV_ASSET'
1577 AND lnk_srv_cleb.sts_code = lnk_srv_sts.code
1578 AND lnk_srv_sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
1579 AND lnk_srv_cleb.dnz_chr_id = lnk_srv_cim.dnz_chr_id
1580 AND lnk_srv_cleb.id = lnk_srv_cim.cle_id
1581 AND lnk_srv_cim.jtot_object1_code = 'OKX_COVASST'
1582 AND lnk_srv_cim.object1_id2 = '#'
1583 AND lnk_srv_cim.object1_id1 = TO_CHAR(p_kle_id));
1584
1585 -- Bug# 5998969 -- Start
1586 -- This cursor is used to get the asset auto range
1587 CURSOR l_asset_autorange_csr IS
1588 SELECT INITIAL_ASSET_ID
1589 FROM FA_SYSTEM_CONTROLS;
1590
1591 l_asset_init_number NUMBER :=0;
1592 l_temp_asset_number NUMBER :=0;
1593 is_number NUMBER :=1;
1594 -- Bug# 5998969 -- End
1595
1596 -- RMUNJULU 09-APR-03 2897523 Added variables
1597 l_oks_line_exists BOOLEAN := FALSE;
1598
1599 -- RMUNJULU 10-APR-03 2900178 changed to VARCHAR2
1600 l_number VARCHAR2(3);
1601
1602
1603 l_no_of_assets NUMBER := 0;
1604 l_k_end_date DATE;
1605 l_rule_found BOOLEAN := FALSE;
1606 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1607 l_contract_status OKC_STATUSES_V.MEANING%TYPE;
1608 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
1609 l_missing_lines BOOLEAN := FALSE;
1610 l_missing_asset_qty BOOLEAN := FALSE;
1611 l_partial_asset_line BOOLEAN := FALSE;
1612 l_invalid_lines BOOLEAN := FALSE;
1613 l_contract_mismatch BOOLEAN := FALSE;
1614 i NUMBER := 0;
1615
1616 l_accepted_quote_number NUMBER := -999;
1617 l_qtp_code VARCHAR2(30);
1618 l_quote_type VARCHAR2(200);
1619 l_asset_qty NUMBER;
1620 l_chr_id NUMBER;
1621 l_name VARCHAR2(150);
1622
1623 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Added parameters for checking
1624 -- related to asset level termination
1625 lx_quote_tbl OKL_AM_UTIL_PVT.quote_tbl_type;
1626
1627 -- SECHAWLA 17-FEB-03 Bug 2804703 : new declarations
1628 l_sts_code okc_k_headers_b.sts_code%TYPE;
1629
1630 -- RMUNJULU 2757312
1631 l_asset_exists VARCHAR2(1);
1632
1633 -- rmunjulu EDAT
1634 l_k_start_date DATE;
1635 l_l_start_date DATE;
1636
1637 -- rmunjulu PPD
1638 l_pdd_exists VARCHAR2(3);
1639
1640 -- LOAN_ENHANCEMENTS
1641 l_deal_type VARCHAR2(300);
1642 l_rev_rec_method VARCHAR2(300);
1643 l_int_cal_basis VARCHAR2(300);
1644 l_tax_owner VARCHAR2(300);
1645 l_int_calc_done VARCHAR2(3);
1646
1647 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'validate_quote';
1648 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1649 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1650 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1651 BEGIN
1652
1653 IF (is_debug_procedure_on) THEN
1654 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1655 END IF;
1656
1657 --Print Input Variables
1658 IF (is_debug_statement_on) THEN
1659 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1660 'p_api_version :'||p_api_version);
1661 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1662 'p_init_msg_list :'||p_init_msg_list);
1663 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1664 'p_k_end_date :'||p_k_end_date);
1665 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1666 'p_no_of_assets :'||p_no_of_assets);
1667 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1668 'p_sys_date :'||p_sys_date);
1669 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1670 'p_rule_chr_id :'||p_rule_chr_id);
1671 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1672 'p_days_before_k_exp :'||p_days_before_k_exp);
1673 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
1674 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
1675 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
1676 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
1677 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
1678 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
1679 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
1680 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
1681 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
1682 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
1683 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
1684 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.early_termination_yn : '||p_quot_rec.early_termination_yn );
1685 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.partial_yn : '||p_quot_rec.partial_yn );
1686 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.preproceeds_yn : '||p_quot_rec.preproceeds_yn );
1687 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.summary_format_yn : '||p_quot_rec.summary_format_yn );
1688 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_yn : '||p_quot_rec.consolidated_yn );
1689 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_requested : '||p_quot_rec.date_requested );
1690 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_proposal : '||p_quot_rec.date_proposal );
1691 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_to : '||p_quot_rec.date_effective_to );
1692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_accepted : '||p_quot_rec.date_accepted );
1693 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.payment_received_yn : '||p_quot_rec.payment_received_yn );
1694 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.requested_by : '||p_quot_rec.requested_by );
1695 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
1696 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
1697 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
1698 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_amount : '||p_quot_rec.purchase_amount );
1699 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_formula : '||p_quot_rec.purchase_formula );
1700 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.asset_value : '||p_quot_rec.asset_value );
1701 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.residual_value : '||p_quot_rec.residual_value );
1702 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.unbilled_receivables : '||p_quot_rec.unbilled_receivables );
1703 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.gain_loss : '||p_quot_rec.gain_loss );
1704 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.PERDIEM_AMOUNT : '||p_quot_rec.PERDIEM_AMOUNT );
1705 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_code : '||p_quot_rec.currency_code );
1706 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_conversion_code : '||p_quot_rec.currency_conversion_code );
1707 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
1708 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 );
1709 END IF;
1710
1711 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1712
1713 -- SECHAWLA Bug #2680542 : moved all contract level validations
1714 -- to the beginning of the procedure, before line level validations.
1715
1716 OPEN k_details_for_qte_csr(p_quot_rec.khr_id);
1717 -- rmunjulu EDAT Added l_k_start_date, l_k_end_date
1718 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
1719 CLOSE k_details_for_qte_csr;
1720
1721 --SECHAWLA 28-FEB-03 Bug # 2757175 : Moved the following validation to the beginning of create_termination_quote procedure
1722 /*
1723 -- Call the validate contract to check contract status
1724 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
1725 p_api_version => p_api_version,
1726 p_init_msg_list => p_init_msg_list,
1727 x_return_status => l_return_status,
1728 x_msg_count => x_msg_count,
1729 x_msg_data => x_msg_data,
1730 p_contract_id => p_quot_rec.khr_id,
1731 p_control_flag => 'TRMNT_QUOTE_CREATE',
1732 x_contract_status => lx_contract_status);
1733
1734 -- If error then above api will set the message, so exit now
1735 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1736 x_return_status := OKL_API.G_RET_STS_ERROR;
1737 RAISE G_EXCEPTION_HALT_VALIDATION;
1738 END IF;
1739 */ --SECHAWLA 28-FEB-03 Bug # 2757175 : end code move
1740
1741
1742 ---SECHAWLA Bug # 2699412--------Check the following for the contract line--
1743
1744 -- rmunjulu +++++++++ Effective Dated Terminations -- start +++++++++++++++
1745
1746 -- RMUNJULU EDAT Date Eff From can be future or past date so remove this check
1747 /*
1748 -- RMUNJULU 06-JAN-03 2736865 Date Eff From is now enterable
1749 -- Check date_eff_from should be >= sysdate
1750 IF p_quot_rec.date_effective_from IS NOT NULL
1751 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1752 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(p_sys_date) THEN
1753
1754 x_return_status := OKL_API.G_RET_STS_ERROR;
1755
1756 -- Please enter the current or future date for the Effective From date.
1757 OKL_API.SET_MESSAGE(
1758 p_app_name => 'OKL',
1759 p_msg_name => 'OKL_AM_DATE_EFF_FROM_PAST');
1760
1761 RAISE G_EXCEPTION_HALT_VALIDATION;
1762
1763 END IF;
1764 */
1765
1766 -- rmunjulu EDAT Add check so that quote effective date which is a prior date
1767 -- is not before the contract start date
1768 IF p_quot_rec.date_effective_from IS NOT NULL
1769 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1770 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(l_k_start_date) THEN
1771
1772 x_return_status := OKL_API.G_RET_STS_ERROR;
1773
1774 -- Quote Effectivity Date cannot be before contract start date.
1775 OKL_API.SET_MESSAGE(
1776 p_app_name => 'OKL',
1777 p_msg_name => 'OKL_AM_EDT_QTE_DATE_K');
1778
1779 RAISE G_EXCEPTION_HALT_VALIDATION;
1780
1781 END IF;
1782
1783 -- rmunjulu EDAT check if contract EVERGREEN then quote effective date cannot
1784 -- be before contract end date
1785 IF p_quot_rec.date_effective_from IS NOT NULL
1786 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
1787 AND l_sts_code = 'EVERGREEN'
1788 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.
1789
1790 x_return_status := OKL_API.G_RET_STS_ERROR;
1791
1792 -- Quote Effectivity Date for an Evergreen contract cannot be before contract end date.
1793 OKL_API.SET_MESSAGE(
1794 p_app_name => 'OKL',
1795 p_msg_name => 'OKL_AM_EDT_EVERGREEN_QTE_DATE');
1796
1797 RAISE G_EXCEPTION_HALT_VALIDATION;
1798
1799 END IF;
1800
1801 -- Code for PPD check will come here
1802
1803 -- rmunjulu +++++++++ Effective Dated Termination -- end ++++++++++++++++
1804
1805 -- Check date_eff_to >= date_eff_from
1806 IF (p_quot_rec.date_effective_from IS NOT NULL)
1807 AND (p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE)
1808 AND (p_quot_rec.date_effective_to IS NOT NULL)
1809 AND (p_quot_rec.date_effective_to <> OKL_API.G_MISS_DATE) THEN
1810 IF (TRUNC(p_quot_rec.date_effective_to) <= TRUNC(p_quot_rec.date_effective_from)) THEN
1811 x_return_status := OKL_API.G_RET_STS_ERROR;
1812 -- Message : Date Effective To DATE_EFFECTIVE_TO cannot be before
1813 -- Date Effective From DATE_EFFECTIVE_FROM.
1814 OKL_API.SET_MESSAGE(p_app_name => 'OKL',
1815 p_msg_name => 'OKL_AM_DATE_EFF_FROM_LESS_TO',
1816 p_token1 => 'DATE_EFFECTIVE_TO',
1817 p_token1_value => p_quot_rec.date_effective_to,
1818 p_token2 => 'DATE_EFFECTIVE_FROM',
1819 p_token2_value => p_quot_rec.date_effective_from);
1820 RAISE G_EXCEPTION_HALT_VALIDATION;
1821 END IF;
1822 END IF;
1823
1824
1825 --SECHAWLA 18-FEB-03 Bug # 2807201 : Moved the quote_type_check procedure call from here to Create_termination quote procedure
1826
1827 --IF l_new_quote_type NOT LIKE 'TER_MAN%' THEN ---SECHAWLA 18-FEB-03 Bug # 2807201
1828 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
1829 -- check if early termination
1830 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
1831
1832 -- IF TRUNC(p_k_end_date) - p_days_before_k_exp > TRUNC(p_sys_date) THEN
1833 -- rmunjulu EDT 3797384 check with date effective instead of sysdate
1834 IF TRUNC(p_k_end_date) - p_days_before_k_exp > TRUNC(p_quot_rec.date_effective_from) THEN
1835
1836 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
1837
1838 IF (is_debug_statement_on) THEN
1839 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1840 'before call to early_termination_allowed :'||l_return_status);
1841 END IF;
1842
1843 -- check if early termination allowed
1844 early_termination_allowed(
1845 p_quot_rec => p_quot_rec,
1846 p_rule_chr_id => p_rule_chr_id,
1847 x_return_status => l_return_status,
1848 x_rule_found => l_rule_found);
1849
1850 IF (is_debug_statement_on) THEN
1851 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1852 'After call to early_termination_allowed :'||l_return_status);
1853 END IF;
1854
1855 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1856 x_return_status := OKL_API.G_RET_STS_ERROR;
1857 RAISE G_EXCEPTION_HALT_VALIDATION;
1858 ELSIF (l_rule_found = FALSE) THEN
1859 x_return_status := OKL_API.G_RET_STS_ERROR;
1860 -- Early Termination of Contract CONTRACT_NUMBER is not allowed.
1861 OKL_API.set_message( p_app_name => 'OKL',
1862 p_msg_name => 'OKL_AM_CONTRACT_EARLY_TERM_NA',
1863 p_token1 => 'CONTRACT_NUMBER',
1864 p_token1_value => l_contract_number);
1865 RAISE G_EXCEPTION_HALT_VALIDATION;
1866 END IF;
1867 END IF;
1868 END IF;
1869
1870 -- rmunjulu PPD
1871 -- do not allow creation of prior dated term quote before the principal paydown date.
1872 IF p_quot_rec.date_effective_from IS NOT NULL
1873 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE THEN
1874 --AND trunc(p_quot_rec.date_effective_from) < trunc(p_sys_date) THEN
1875 -- rmunjulu Bug 4143251 Removed above condition for PRIOR Dated Quotes, NOW check for all Quotes
1876
1877
1878 IF (is_debug_statement_on) THEN
1879 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1880 'before call to OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd :'||l_return_status);
1881 END IF;
1882
1883 -- Added code to check for PPD transaction after quote eff date
1884 l_pdd_exists := OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd(
1885 p_khr_id => p_quot_rec.khr_id,
1886 p_effective_date => p_quot_rec.date_effective_from);
1887
1888 IF (is_debug_statement_on) THEN
1889 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1890 'After call to OKL_CS_PRINCIPAL_PAYDOWN_PVT.check_for_ppd :'||l_return_status);
1891 END IF;
1892
1893 IF l_pdd_exists = 'Y' THEN
1894
1895 -- A principal paydown transaction exists for contract CONTRACT_NUMBER,
1896 -- can not create a quote with Effective From date before the principal
1897 -- paydown transaction date.
1898 OKL_API.set_message(
1899 p_app_name => 'OKL',
1900 p_msg_name => 'OKL_AM_PPD_ERR',
1901 p_token1 => 'CONTRACT_NUMBER',
1902 p_token1_value => l_contract_number);
1903
1904 RAISE G_EXCEPTION_HALT_VALIDATION;
1905 END IF;
1906 END IF;
1907
1908 -- SECHAWLA Bug #2680542 : moved all line level validations towards the end,
1909 -- after contract level validations.
1910
1911 -- Check that there are contract lines passed as parameters.
1912 IF (p_assn_tbl.COUNT > 0) THEN
1913 i := p_assn_tbl.FIRST;
1914 -- validate contract lines
1915 LOOP
1916
1917 IF (is_debug_statement_on) THEN
1918 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 );
1919 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 );
1920 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 );
1921 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 );
1922 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);
1923 END IF;
1924 IF ((p_assn_tbl(i).p_asset_id IS NULL) OR
1925 (p_assn_tbl(i).p_asset_id = OKC_API.G_MISS_NUM)) THEN
1926 x_return_status := OKL_API.G_RET_STS_ERROR;
1927 --Quotes are not allowed for contracts without assets.
1928 OKC_API.SET_MESSAGE (
1929 p_app_name => 'OKL'
1930 ,p_msg_name => 'OKL_AM_NO_ASSETS_FOR_QUOTE');
1931 RAISE G_EXCEPTION_HALT_VALIDATION;
1932 END IF;
1933
1934 -- SECHAWLA Bug #2680542 : Added validations to check if asset id is
1935 -- valid and belongs to the passed contract.
1936 l_chr_id := 1;
1937
1938 OPEN l_clines_csr (p_assn_tbl(i).p_asset_id);
1939 FETCH l_clines_csr INTO l_chr_id, l_l_start_date; -- rmunjulu EDAT
1940 IF l_clines_csr%NOTFOUND THEN
1941 x_return_status := OKL_API.G_RET_STS_ERROR;
1942 -- invalid asset id
1943 OKC_API.SET_MESSAGE (
1944 p_app_name => 'OKC'
1945 ,p_msg_name => G_INVALID_VALUE
1946 ,p_token1 => G_COL_NAME_TOKEN
1947 ,p_token1_value => 'asset_id');
1948 RAISE G_EXCEPTION_HALT_VALIDATION;
1949 ELSIF (l_chr_id <> p_quot_rec.khr_id) OR (l_chr_id = 1) THEN
1950 x_return_status := OKL_API.G_RET_STS_ERROR;
1951 --Asset ASSET_NUMBER does not belong to the contract CONTRACT_NUMBER.
1952 OKC_API.SET_MESSAGE (
1953 p_app_name => 'OKL'
1954 ,p_msg_name => 'OKL_AM_CONTRACT_MISMATCH'
1955 ,p_token1 => 'ASSET_NUMBER'
1956 ,p_token1_value => p_assn_tbl(i).p_asset_number,
1957 p_token2 => 'CONTRACT_NUMBER',
1958 p_token2_value => l_contract_number);
1959 RAISE G_EXCEPTION_HALT_VALIDATION;
1960 END IF;
1961 CLOSE l_clines_csr;
1962
1963 -- SECHAWLA Bug #2680542 : Added code to populate asset number if it is null
1964 IF p_assn_tbl(i).p_asset_number IS NULL
1965 OR p_assn_tbl(i).p_asset_number = OKC_API.G_MISS_CHAR THEN
1966 OPEN l_linesfull_csr(p_assn_tbl(i).p_asset_id);
1967 FETCH l_linesfull_csr INTO l_name;
1968 CLOSE l_linesfull_csr;
1969
1970 p_assn_tbl(i).p_asset_number := l_name;
1971 END IF;
1972
1973 -- SECHAWLA Bug #2680542 : Added code to populate asset qty and quote qty, if null
1974 IF ((p_assn_tbl(i).p_asset_qty IS NULL) OR
1975 (p_assn_tbl(i).p_asset_qty = OKC_API.G_MISS_NUM)) THEN
1976 l_asset_qty := okl_am_util_pvt.get_asset_quantity(p_assn_tbl(i).p_asset_id);
1977 IF l_asset_qty IS NULL THEN
1978 x_return_status := OKL_API.G_RET_STS_ERROR;
1979 -- Can not create quote because the asset quantity is missing.
1980 OKC_API.SET_MESSAGE (
1981 p_app_name => 'OKL'
1982 ,p_msg_name => 'OKL_AM_NO_ASSET_QTY',
1983 p_token1 => 'ASSET_NUMBER',
1984 p_token1_value => p_assn_tbl(i).p_asset_number);
1985 RAISE G_EXCEPTION_HALT_VALIDATION;
1986 ELSE
1987 p_assn_tbl(i).p_asset_qty := l_asset_qty;
1988 END IF;
1989 END IF;
1990
1991
1992 IF ((p_assn_tbl(i).p_quote_qty IS NULL) OR
1993 (p_assn_tbl(i).p_quote_qty = OKC_API.G_MISS_NUM)) THEN
1994 p_assn_tbl(i).p_quote_qty := p_assn_tbl(i).p_asset_qty;
1995 END IF;
1996
1997
1998 -- RMUNJULU 14-MAR-03 2854796 Error if quote qty less than equal to 0
1999 IF p_assn_tbl(i).p_quote_qty <= 0 THEN
2000
2001 -- Please enter a value greater than zero for Units to Terminate of asset ASSET_NUMBER.
2002 OKL_API.SET_MESSAGE (
2003 p_app_name => 'OKL',
2004 p_msg_name => 'OKL_AM_QTE_QTY_LESS_THAN_ZERO',
2005 p_token1 => 'ASSET_NUMBER',
2006 p_token1_value => p_assn_tbl(i).p_asset_number);
2007
2008
2009 RAISE G_EXCEPTION_HALT_VALIDATION;
2010
2011 END IF;
2012
2013
2014 -- SECHAWLA Bug #2680542 : Added code to validate quote quantity
2015 IF p_assn_tbl(i).p_quote_qty > p_assn_tbl(i).p_asset_qty THEN
2016 x_return_status := OKL_API.G_RET_STS_ERROR;
2017 -- Asset ASSET_NUMBER quantity is less than the specified quote quantity.
2018 OKC_API.SET_MESSAGE (
2019 p_app_name => 'OKL'
2020 ,p_msg_name => 'OKL_AM_INVALID_QUOTE_QTY',
2021 p_token1 => 'ASSET_NUMBER',
2022 p_token1_value => p_assn_tbl(i).p_asset_number);
2023 RAISE G_EXCEPTION_HALT_VALIDATION;
2024 END IF;
2025
2026 -- SECHAWLA Bug #2680542 : Check if quote invloves a partial asset line.
2027 IF p_assn_tbl(i).p_quote_qty < p_assn_tbl(i).p_asset_qty THEN
2028 l_partial_asset_line := TRUE;
2029 END IF;
2030
2031
2032
2033 -- RMUNJULU -- 11-DEC-02 Bug # 2484327 -- Added code to check for accepted
2034 -- quote based on asset level termination changes
2035
2036 IF (is_debug_statement_on) THEN
2037 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2038 'before call to OKL_AM_UTIL_PVT.get_line_quotes :'||l_return_status);
2039 END IF;
2040
2041 -- Check if accepted quote exists for the asset
2042 OKL_AM_UTIL_PVT.get_line_quotes (
2043 p_kle_id => p_assn_tbl(i).p_asset_id,
2044 x_quote_tbl => lx_quote_tbl,
2045 x_return_status => x_return_status);
2046
2047 IF (is_debug_statement_on) THEN
2048 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2049 'After call to OKL_AM_UTIL_PVT.get_line_quotes :'||l_return_status);
2050 END IF;
2051
2052 -- Check the return status
2053 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2054
2055 -- Error occured in util proc, message set by util proc raise exp
2056 RAISE G_EXCEPTION_HALT_VALIDATION;
2057
2058 END IF;
2059
2060 -- Check if accepted quote exists for the asset
2061 IF lx_quote_tbl.COUNT > 0 THEN
2062
2063 IF (is_debug_statement_on) THEN
2064 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2065 'before call to OKL_AM_UTIL_PVT.get_lookup_meaning :'||l_return_status);
2066 END IF;
2067
2068 l_quote_type := OKL_AM_UTIL_PVT.get_lookup_meaning(
2069 'OKL_QUOTE_TYPE',
2070 lx_quote_tbl(lx_quote_tbl.FIRST).qtp_code,
2071 'Y');
2072
2073 IF (is_debug_statement_on) THEN
2074 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2075 'after call to OKL_AM_UTIL_PVT.get_lookup_meaning :'||l_return_status);
2076 END IF;
2077 -- Accepted quote QUOTE_NUMBER of quote type QUOTE_TYPE exists for
2078 -- asset ASSET_NUMBER. Cannot create another quote for the same asset.
2079 OKL_API.set_message (
2080 p_app_name => 'OKL',
2081 p_msg_name => 'OKL_AM_ASSET_QTE_EXISTS_ERR',
2082 p_token1 => 'QUOTE_NUMBER',
2083 p_token1_value => lx_quote_tbl(lx_quote_tbl.FIRST).quote_number,
2084 p_token2 => 'QUOTE_TYPE',
2085 p_token2_value => l_quote_type,
2086 p_token3 => 'ASSET_NUMBER',
2087 p_token3_value => p_assn_tbl(i).p_asset_number);
2088
2089 RAISE G_EXCEPTION_HALT_VALIDATION;
2090
2091 END IF;
2092
2093 -- AKP:REPO-QUOTE-START Get the contract product details 6599890
2094 OKL_AM_UTIL_PVT.get_contract_product_details(
2095 p_khr_id => p_quot_rec.khr_id,
2096 x_deal_type => l_deal_type,
2097 x_rev_rec_method => l_rev_rec_method,
2098 x_int_cal_basis => l_int_cal_basis,
2099 x_tax_owner => l_tax_owner,
2100 x_return_status => l_return_status);
2101
2102
2103 IF (is_debug_statement_on) THEN
2104 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2105 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2106 END IF;
2107
2108
2109 -- If error then throw exception
2110 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2111
2112 RAISE G_EXCEPTION_HALT_VALIDATION;
2113
2114 END IF;
2115 -- AKP:REPO-QUOTE-END Get the contract product details
2116
2117 -- rmunjulu LOANS_ENHANCEMENTS Termination with purchase not allowed for loans
2118 IF p_quot_rec.qtp_code IN ( 'TER_PURCHASE', -- Termination - With Purchase
2119 'TER_MAN_PURCHASE', -- Termination - Manual With Purchase
2120 'TER_RECOURSE', -- Termination - Recourse With Purchase
2121 'TER_ROLL_PURCHASE' -- Termination - Rollover To New Contract With Purchase
2122 ) THEN
2123
2124 IF (is_debug_statement_on) THEN
2125 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2126 'before call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2127 END IF;
2128
2129 -- AKP:REPO-QUOTE-START 6599890
2130 /* -- Get the contract product details
2131 OKL_AM_UTIL_PVT.get_contract_product_details(
2132 p_khr_id => p_quot_rec.khr_id,
2133 x_deal_type => l_deal_type,
2134 x_rev_rec_method => l_rev_rec_method,
2135 x_int_cal_basis => l_int_cal_basis,
2136 x_tax_owner => l_tax_owner,
2137 x_return_status => l_return_status);
2138
2139
2140 IF (is_debug_statement_on) THEN
2141 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2142 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2143 END IF;
2144
2145
2146 -- If error then throw exception
2147 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2148
2149 RAISE G_EXCEPTION_HALT_VALIDATION;
2150
2151 END IF; */
2152 -- AKP:REPO-QUOTE-END
2153
2154 IF l_deal_type LIKE 'LOAN%' THEN
2155
2156 -- Termination with purchase quote is not allowed for loan contract.
2157 OKL_API.SET_MESSAGE(
2158 p_app_name => 'OKL',
2159 p_msg_name => 'OKL_AM_LOAN_PAR_ERR');
2160
2161 RAISE G_EXCEPTION_HALT_VALIDATION;
2162
2163 END IF;
2164 END IF;
2165
2166 -- AKP:REPO-QUOTE-START 6599890
2167 -- asahoo Changed the message, no token will be passed.
2168 IF (p_quot_rec.repo_quote_indicator_yn IS NOT NULL AND
2169 p_quot_rec.repo_quote_indicator_yn <> OKL_API.G_MISS_CHAR) THEN
2170 IF p_quot_rec.repo_quote_indicator_yn ='Y' AND l_deal_type NOT LIKE 'LOAN%' THEN
2171 OKL_API.SET_MESSAGE(
2172 p_app_name => 'OKL',
2173 p_msg_name => 'OKL_AM_REPO_LOAN_VALID');
2174 RAISE G_EXCEPTION_HALT_VALIDATION;
2175 END IF;
2176 END IF;
2177 -- AKP:REPO-QUOTE-END
2178
2179 IF l_partial_asset_line = TRUE
2180 THEN
2181 -- Bug 4299668 PAGARG Moved the logic (to check whether there is any OKS
2182 -- line attached to asset) inside l_partial_asset_line condition as OKS
2183 -- line needs to be verified only in case of partial termination quote.
2184 -- RMUNJULU 09-APR-03 2897523 Added code to check if OKS line exists linked to covered
2185 -- asset and trying to create a partial line termination quote on that asset.
2186 OPEN l_oks_lines_csr ( p_assn_tbl(i).p_asset_id );
2187 FETCH l_oks_lines_csr INTO l_number;
2188 IF l_oks_lines_csr%FOUND THEN
2189 l_oks_line_exists := TRUE;
2190 END IF;
2191 CLOSE l_oks_lines_csr;
2192
2193 IF l_oks_line_exists = TRUE THEN
2194
2195 -- This asset is linked to a service contract. Assets linked to service contract can not be split.
2196 OKL_API.set_message (
2197 p_app_name => 'OKL',
2198 p_msg_name => 'OKL_LLA_SPA_SERVICE_LINKED');
2199 /*
2200 -- Service line LINE_NUMBER linked to asset ASSET_NUMBER exists.
2201 -- Can not create partial asset termination quote for this asset.
2202 OKL_API.set_message (
2203 p_app_name => 'OKL',
2204 p_msg_name => 'OKL_AM_SERVICE_LINE_EXISTS',
2205 p_token1 => 'LINE_NUMBER',
2206 p_token1_value => l_number,
2207 p_token2 => 'ASSET_NUMBER',
2208 p_token2_value => p_assn_tbl(i).p_asset_number);
2209 */
2210
2211 RAISE G_EXCEPTION_HALT_VALIDATION;
2212 END IF; -- Bug 4299668
2213
2214
2215 /* SECHAWLA 04-JAN-06 4915133 ; Moved this piece later in the code : move begin
2216 -- rmunjulu LOANS_ENHANCEMENTS 13-oct-05 moved this logic here.
2217 -- rmunjulu LOANS_ENHANCEMENTS Partial line termination for loans with Actual/Estimated Actual not allowed
2218 -- Get the contract product details
2219 OKL_AM_UTIL_PVT.get_contract_product_details(
2220 p_khr_id => p_quot_rec.khr_id,
2221 x_deal_type => l_deal_type,
2222 x_rev_rec_method => l_rev_rec_method,
2223 x_int_cal_basis => l_int_cal_basis,
2224 x_tax_owner => l_tax_owner,
2225 x_return_status => l_return_status);
2226
2227 -- If error then throw exception
2228 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2229
2230 RAISE G_EXCEPTION_HALT_VALIDATION;
2231
2232 END IF;
2233
2234 IF l_deal_type LIKE 'LOAN%'
2235 AND l_rev_rec_method IN ('ESTIMATED_AND_BILLED','ACTUAL') THEN
2236
2237 -- Termination of part of units of asset ASSET_NUMBER is not allowed for contract CONTRACT_NUMBER.
2238 OKL_API.SET_MESSAGE(
2239 p_app_name => 'OKL',
2240 p_msg_name => 'OKL_AM_LOAN_PAR_LN_TRMNT',
2241 p_token1 => 'ASSET_NUMBER',
2242 p_token1_value => p_assn_tbl(i).p_asset_number,
2243 p_token2 => 'CONTRACT_NUMBER',
2244 p_token2_value => l_contract_number);
2245
2246 RAISE G_EXCEPTION_HALT_VALIDATION;
2247
2248 END IF;
2249 */ -- SECHAWLA 04-JAN-06 4915133 : move end
2250
2251 END IF;
2252
2253 --SECHAWLA 04-JAN-06 4915133 : Partial termination quotes (full or partial line)
2254 --should not be permitted for contracts with revenue recognition method
2255 --'Estimated and Billed' or 'Actual'
2256 IF (p_assn_tbl.COUNT < p_no_of_assets) OR (l_partial_asset_line) THEN
2257
2258 IF (is_debug_statement_on) THEN
2259 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2260 'before call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2261 END IF;
2262
2263 -- Moved the above valdation here under this IF condition
2264 -- Get the contract product details
2265 OKL_AM_UTIL_PVT.get_contract_product_details(
2266 p_khr_id => p_quot_rec.khr_id,
2267 x_deal_type => l_deal_type,
2268 x_rev_rec_method => l_rev_rec_method,
2269 x_int_cal_basis => l_int_cal_basis,
2270 x_tax_owner => l_tax_owner,
2271 x_return_status => l_return_status);
2272
2273 IF (is_debug_statement_on) THEN
2274 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2275 'After call to OKL_AM_UTIL_PVT.get_contract_product_details :'||l_return_status);
2276 END IF;
2277
2278 -- If error then throw exception
2279 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2280
2281 RAISE G_EXCEPTION_HALT_VALIDATION;
2282
2283 END IF;
2284
2285 IF l_deal_type LIKE 'LOAN%'
2286 AND l_rev_rec_method IN ('ESTIMATED_AND_BILLED','ACTUAL') THEN
2287
2288 -- Termination of part of units of asset ASSET_NUMBER is not allowed for contract CONTRACT_NUMBER.
2289 OKL_API.SET_MESSAGE(
2290 p_app_name => 'OKL',
2291 p_msg_name => 'OKL_AM_LOAN_PAR_LN_TRMNT');
2292
2293 RAISE G_EXCEPTION_HALT_VALIDATION;
2294
2295 END IF;
2296
2297 END IF;
2298 -- SECHAWLA 04-JAN-06 4915133 : end
2299
2300 -- RMUNJULU 2757312 Added code to validate the new asset number -- START
2301 IF p_assn_tbl(i).p_split_asset_number IS NOT NULL
2302 AND p_assn_tbl(i).p_split_asset_number <> OKL_API.G_MISS_CHAR THEN
2303
2304 -- If partial Line
2305 IF p_assn_tbl(i).p_asset_qty > p_assn_tbl(i).p_quote_qty THEN
2306
2307 IF (is_debug_statement_on) THEN
2308 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2309 'before call to asset_number_exists :'||l_return_status);
2310 END IF;
2311
2312 -- Check if Asset Number Unique --
2313 -- RMUNJULU 3241502 Added p_control
2314 l_return_status := asset_number_exists(
2315 p_asset_number => p_assn_tbl(i).p_split_asset_number,
2316 p_control => 'QUOTE',
2317 x_asset_exists => l_asset_exists);
2318
2319 IF (is_debug_statement_on) THEN
2320 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2321 'After call to asset_number_exists :'||l_return_status);
2322 END IF;
2323
2324 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2325 -- Message set in called proc
2326 RAISE G_EXCEPTION_HALT_VALIDATION;
2327 END IF;
2328
2329 -- If Asset Number Entered is not unique raise error
2330 IF l_asset_exists = 'Y' THEN
2331
2332 -- Asset number ASSET_NUMBER already exists.
2333 OKL_API.set_message (
2334 p_app_name => 'OKL',
2335 p_msg_name => 'OKL_AM_NEW_ASSET_EXISTS',
2336 p_token1 => 'ASSET_NUMBER',
2337 p_token1_value => p_assn_tbl(i).p_split_asset_number); -- RMUNJULU 3241502 Changed token value
2338 RAISE G_EXCEPTION_HALT_VALIDATION;
2339 END IF;
2340
2341 -- Bug# 5998969 -- start
2342 OPEN l_asset_autorange_csr;
2343 FETCH l_asset_autorange_csr INTO l_asset_init_number;
2344 CLOSE l_asset_autorange_csr;
2345
2346 BEGIN
2347 l_temp_asset_number := TO_NUMBER(p_assn_tbl(i).p_split_asset_number);
2348 is_number :=1;
2349 EXCEPTION
2350 WHEN OTHERS THEN
2351 is_number :=0;
2352 END;
2353 IF (is_number = 1) THEN
2354
2355 IF (p_assn_tbl(i).p_split_asset_number > l_asset_init_number) THEN
2356 -- The New Asset Number ASSET_NUMBER is reserved for automatic asset numbering.
2357 -- Asset number beyond AUTO_RANGE is reserved for automatic asset numbering.
2358 --Please modify the New Asset Number.
2359 OKL_API.set_message (
2360 p_app_name => 'OKL',
2361 p_msg_name => 'OKL_AM_NEW_ASSET_IN_AUOT_RANGE',
2362 p_token1 => 'ASSET_NUMBER',
2363 p_token1_value => p_assn_tbl(i).p_split_asset_number,
2364 p_token2 => 'AUTO_RANGE',
2365 p_token2_value => l_asset_init_number);
2366
2367 RAISE G_EXCEPTION_HALT_VALIDATION;
2368 END IF;
2369 END IF;
2370 -- Bug# 5998969 -- end
2371
2372 END IF;
2373 END IF;
2374 -- RMUNJULU 2757312 Added code to validate the new asset number -- END
2375
2376 -- rmunjulu +++++++++ Effective Dated Termination -- start ++++++++++++++++
2377
2378 -- rmunjulu EDAT Check if any asset transactions exists in FA for the asset after quote effective date
2379 IF p_quot_rec.date_effective_from IS NOT NULL
2380 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE THEN
2381
2382 -- ++++++++++++ Same as in Accept Quote API ++++++++++++++++++++++---
2383 -- rmunjulu EDAT Add code for FA checks, do this only for prior dated terminations
2384 -- and termination with purchase (which is when we do asset disposal)
2385 -- rmunjulu Bug 4143251 Changed condition to check for FA Checks for PRE and CURRENT dated quotes
2386 IF trunc(p_quot_rec.date_effective_from) <= trunc(p_sys_date)
2387 AND p_quot_rec.qtp_code IN ( 'TER_PURCHASE', -- Termination - With Purchase
2388 'TER_MAN_PURCHASE', -- Termination - Manual With Purchase
2389 'TER_RECOURSE', -- Termination - Recourse With Purchase
2390 'TER_ROLL_PURCHASE' -- Termination - Rollover To New Contract With Purchase
2391 ) THEN
2392
2393 IF (is_debug_statement_on) THEN
2394 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2395 'before call to OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa :'||l_return_status);
2396 END IF;
2397
2398 OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa(
2399 p_kle_id => p_assn_tbl(i).p_asset_id,
2400 p_trn_date => p_quot_rec.date_effective_from, -- quote eff from date will be passed
2401 p_check_fa_year => 'Y', -- do we need to check fiscal year
2402 p_check_fa_trn => 'Y', -- do we need to check fa transactions
2403 p_contract_number => l_contract_number,
2404 x_return_status => l_return_status);
2405
2406 IF (is_debug_statement_on) THEN
2407 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2408 'After call to OKL_AM_TERMNT_QUOTE_PVT.check_asset_validity_in_fa :'||l_return_status);
2409 END IF;
2410
2411 -- If error in FA checks the throw exception, message set in above routine
2412 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2413
2414 RAISE G_EXCEPTION_HALT_VALIDATION; -- rmunjulu EDAT 17-Jan-2005
2415 END IF;
2416 END IF;
2417 -- ++++++++++++ Same as in Accept Quote API ++++++++++++++++++++++---
2418 END IF;
2419
2420 -- rmunjulu EDAT Check if quote effectivity date before the asset start date for the quoted asset
2421 IF p_quot_rec.date_effective_from IS NOT NULL
2422 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
2423 AND TRUNC(p_quot_rec.date_effective_from) < TRUNC(l_l_start_date) THEN
2424
2425 x_return_status := OKL_API.G_RET_STS_ERROR;
2426
2427 -- Quote Effectivity Date cannot be before asset start date.
2428 OKL_API.SET_MESSAGE(
2429 p_app_name => 'OKL',
2430 p_msg_name => 'OKL_AM_EDT_QTE_DATE_ASSET');
2431
2432 RAISE G_EXCEPTION_HALT_VALIDATION;
2433
2434 END IF;
2435
2436 -- rmunjulu +++++++++ Effective Dated Termination -- end ++++++++++++++++
2437
2438
2439 EXIT WHEN (i = p_assn_tbl.LAST);
2440 i := p_assn_tbl.NEXT(i);
2441 END LOOP;
2442 ELSE
2443 x_return_status := OKL_API.G_RET_STS_ERROR;
2444 --Quotes are not allowed for contracts without assets.
2445 OKC_API.SET_MESSAGE (
2446 p_app_name => 'OKL'
2447 ,p_msg_name => 'OKL_AM_NO_ASSETS_FOR_QUOTE');
2448 RAISE G_EXCEPTION_HALT_VALIDATION;
2449 END IF;
2450
2451 --Bug# 5946411: Removed validation that prevents partial line
2452 -- termination quote creation for evergreen contracts
2453 /*
2454 --SECHAWLA 17-FEB-03 Bug 2804703 : Added the following validation
2455 IF l_sts_code = 'EVERGREEN' AND (l_partial_asset_line) THEN
2456 x_return_status := OKL_API.G_RET_STS_ERROR;
2457 -- Unable to create quote with partial asset quantities for Evergreen contract CONTRACT_NUMBER.
2458 OKC_API.SET_MESSAGE (
2459 p_app_name => 'OKL'
2460 ,p_msg_name => 'OKL_AM_PARTIAL_LINE_EVERGREEN',
2461 p_token1 => 'CONTRACT_NUMBER',
2462 p_token1_value => l_contract_number);
2463 RAISE G_EXCEPTION_HALT_VALIDATION;
2464 END IF;
2465 */
2466
2467 -- rmunjulu 4143251 Added condition, if contract BOOKED and Partial Quote with
2468 -- Quote Effective From Date after contract end date then error
2469 IF l_sts_code IN ('BOOKED')
2470 AND ((l_partial_asset_line) OR (p_assn_tbl.COUNT < p_no_of_assets))
2471 AND (p_quot_rec.date_effective_from IS NOT NULL
2472 AND p_quot_rec.date_effective_from <> OKL_API.G_MISS_DATE
2473 AND TRUNC(p_quot_rec.date_effective_from) > TRUNC(l_k_end_date)) THEN
2474
2475 x_return_status := OKL_API.G_RET_STS_ERROR;
2476
2477
2478 -- Unable to create partial quote for contract CONTRACT_NUMBER with quote Effective From Date QUOTE_EFF_DATE
2479 -- after contract End Date END_DATE.
2480 OKL_API.SET_MESSAGE (
2481 p_app_name => 'OKL',
2482 p_msg_name => 'OKL_AM_PARTIAL_BOOKED_K_ERR',
2483 p_token1 => 'CONTRACT_NUMBER',
2484 p_token1_value => l_contract_number,
2485 p_token2 => 'QUOTE_EFF_DATE',
2486 p_token2_value => TRUNC(p_quot_rec.date_effective_from),
2487 p_token3 => 'END_DATE',
2488 p_token3_value => TRUNC(l_k_end_date));
2489
2490 RAISE G_EXCEPTION_HALT_VALIDATION;
2491 END IF;
2492
2493
2494 IF (is_debug_statement_on) THEN
2495 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2496 'before call to OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done :'||l_return_status);
2497 END IF;
2498 -- rmunjulu LOANS_ENHANCEMENTS -- Check interest calculation done
2499
2500 --SECHAWLA 20-JAN-06 4970009 : The following interest calculation check
2501 --will also be done for lease contracts, with interest calculation basis 'FLOAT_FACTORS','REAMORT'
2502 --modifying OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done
2503 --no code changes done in this file for bug 4970009
2504 l_int_calc_done := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done(
2505 p_contract_id => p_quot_rec.khr_id,
2506 p_contract_number => l_contract_number,
2507 p_source => 'CREATE',
2508 p_trn_date => TRUNC(p_quot_rec.date_effective_from));
2509 IF (is_debug_statement_on) THEN
2510 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2511 'After call to OKL_AM_LEASE_LOAN_TRMNT_PVT.check_int_calc_done :'||l_return_status);
2512 END IF;
2513
2514 IF l_int_calc_done IS NULL OR l_int_calc_done = 'N' THEN
2515
2516 -- Message will be set in called procedure
2517 RAISE G_EXCEPTION_HALT_VALIDATION;
2518 END IF;
2519
2520 --IF l_new_quote_type NOT LIKE 'TER_MAN%' THEN -- SECHAWLA 18-FEB-03 Bug # 2807201
2521 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
2522
2523 -- SECHAWLA Bug #2680542 : Added the second condition to the following
2524 -- IF statement, to check if the quote is partial. A quote is partial
2525 -- if it has less than the total number of assets on the contract or
2526 -- has units less than the total number of units for one or more assets
2527 -- on the contract
2528
2529 -- check if partial quote
2530 IF (p_assn_tbl.COUNT < p_no_of_assets)
2531 OR (l_partial_asset_line) THEN -- added second condition
2532
2533 IF (is_debug_statement_on) THEN
2534 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2535 'before call to partial_termination_allowed :'||l_return_status);
2536 END IF;
2537 -- check partial termination allowed
2538 partial_termination_allowed(
2539 p_quot_rec => p_quot_rec,
2540 p_rule_chr_id => p_rule_chr_id,
2541 x_return_status => l_return_status,
2542 x_rule_found => l_rule_found);
2543
2544 IF (is_debug_statement_on) THEN
2545 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2546 'After call to partial_termination_allowed :'||l_return_status);
2547 END IF;
2548
2549
2550 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2551 x_return_status := OKL_API.G_RET_STS_ERROR;
2552 RAISE G_EXCEPTION_HALT_VALIDATION;
2553 ELSIF (l_rule_found = FALSE) THEN
2554 x_return_status := OKL_API.G_RET_STS_ERROR;
2555 --Partial quote for contract CONTRACT_NUMBER is not allowed.
2556 OKL_API.set_message( p_app_name => 'OKL',
2557 p_msg_name => 'OKL_AM_PARTIAL_QUOTE_NA',
2558 p_token1 => 'CONTRACT_NUMBER',
2559 p_token1_value => l_contract_number);
2560 RAISE G_EXCEPTION_HALT_VALIDATION;
2561 END IF;
2562 END IF;
2563 END IF;
2564
2565 -- SECHAWLA Bug #2680542 : set the out parameter x_partial_asset_line to
2566 -- indicate if quote involves a partial
2567 -- asset line.
2568 x_partial_asset_line := l_partial_asset_line; -- added
2569
2570 IF (is_debug_statement_on) THEN
2571 -- OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2572 -- 'x_partial_asset_line..'||x_partial_asset_line);
2573
2574 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2575 'x_msg_data..'||x_msg_data);
2576
2577 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2578 'x_msg_count..'||x_msg_count);
2579
2580 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2581 'ret status at the end.. '||x_return_status);
2582
2583 END IF;
2584
2585 IF (is_debug_procedure_on) THEN
2586 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,G_MODULE_NAME||'validate_quote ','End(-)');
2587 END IF;
2588
2589
2590 EXCEPTION
2591 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2592 IF k_details_for_qte_csr%ISOPEN THEN
2593 CLOSE k_details_for_qte_csr;
2594 END IF;
2595
2596 IF get_accepted_qte_details_csr%ISOPEN THEN
2597 CLOSE get_accepted_qte_details_csr;
2598 END IF;
2599
2600 IF l_clines_csr%ISOPEN THEN
2601 CLOSE l_clines_csr;
2602 END IF;
2603
2604 IF l_linesfull_csr%ISOPEN THEN
2605 CLOSE l_linesfull_csr;
2606 END IF;
2607
2608 -- RMUNJULU 09-APR-03 2897523
2609 IF l_oks_lines_csr%ISOPEN THEN
2610 CLOSE l_oks_lines_csr;
2611 END IF;
2612
2613 x_return_status := OKL_API.G_RET_STS_ERROR;
2614
2615 WHEN OTHERS THEN
2616 IF k_details_for_qte_csr%ISOPEN THEN
2617 CLOSE k_details_for_qte_csr;
2618 END IF;
2619
2620 IF get_accepted_qte_details_csr%ISOPEN THEN
2621 CLOSE get_accepted_qte_details_csr;
2622 END IF;
2623
2624 IF l_clines_csr%ISOPEN THEN
2625 CLOSE l_clines_csr;
2626 END IF;
2627
2628 IF l_linesfull_csr%ISOPEN THEN
2629 CLOSE l_linesfull_csr;
2630 END IF;
2631
2632 -- RMUNJULU 09-APR-03 2897523
2633 IF l_oks_lines_csr%ISOPEN THEN
2634 CLOSE l_oks_lines_csr;
2635 END IF;
2636
2637 IF (is_debug_exception_on) THEN
2638 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2639 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2640 END IF;
2641
2642 -- unexpected error
2643 OKL_API.set_message(p_app_name => g_app_name,
2644 p_msg_name => g_unexpected_error,
2645 p_token1 => g_sqlcode_token,
2646 p_token1_value => sqlcode,
2647 p_token2 => g_sqlerrm_token,
2648 p_token2_value => sqlerrm);
2649
2650 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2651 END validate_quote;
2652
2653
2654
2655 -- Start of comments
2656 --
2657 -- Function Name : set_currency_defaults
2658 -- Description : This procedure Defaults the Multi-Currency Columns
2659 -- Business Rules :
2660 -- Parameters : Input parameters : px_quot_rec, p_sys_date
2661 -- Version : 1.0
2662 -- History : 23-DEC-02 RMUNJULU 2726739 Created
2663 -- End of comments
2664 PROCEDURE set_currency_defaults(
2665 px_quot_rec IN OUT NOCOPY quot_rec_type,
2666 p_sys_date IN DATE,
2667 x_return_status OUT NOCOPY VARCHAR2) IS
2668
2669 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2670 l_functional_currency_code VARCHAR2(15);
2671 l_contract_currency_code VARCHAR2(15);
2672 l_currency_conversion_type VARCHAR2(30);
2673 l_currency_conversion_rate NUMBER;
2674 l_currency_conversion_date DATE;
2675
2676 l_org_id NUMBER;
2677 l_converted_amount NUMBER;
2678
2679 -- Since we do not use the amount or converted amount in TRX_Quotes table
2680 -- set a hardcoded value for the amount (and pass to to
2681 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
2682 -- conversion values )
2683 l_hard_coded_amount NUMBER := 100;
2684
2685
2686 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_currency_defaults';
2687 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2688 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2689 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2690 BEGIN
2691
2692 IF (is_debug_procedure_on) THEN
2693 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begins(+)');
2694 END IF;
2695
2696 -- Get the functional currency from AM_Util
2697 OKL_AM_UTIL_PVT.get_func_currency_org(
2698 x_org_id => l_org_id,
2699 x_currency_code => l_functional_currency_code);
2700
2701 IF (is_debug_statement_on) THEN
2702 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2703 'after call to OKL_AM_UTIL_PVT.get_func_currency_org :l_org_id :'||l_org_id);
2704 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2705 'after call to OKL_AM_UTIL_PVT.get_func_currency_org :l_functional_currency_code :'||l_functional_currency_code);
2706 END IF;
2707
2708
2709 -- Get the currency conversion details from ACCOUNTING_Util
2710 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2711 p_khr_id => px_quot_rec.khr_id,
2712 p_to_currency => l_functional_currency_code,
2713 p_transaction_date => p_sys_date,
2714 p_amount => l_hard_coded_amount,
2715 x_return_status => l_return_status,
2716 x_contract_currency => l_contract_currency_code,
2717 x_currency_conversion_type => l_currency_conversion_type,
2718 x_currency_conversion_rate => l_currency_conversion_rate,
2719 x_currency_conversion_date => l_currency_conversion_date,
2720 x_converted_amount => l_converted_amount);
2721
2722 IF (is_debug_statement_on) THEN
2723 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2724 'after call to OKL_ACCOUNTING_UTIL.convert_to_functional_currency :'||l_return_status);
2725 END IF;
2726
2727 -- raise exception if error
2728 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2729 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2730 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2731 RAISE OKL_API.G_EXCEPTION_ERROR;
2732 END IF;
2733
2734 px_quot_rec.currency_code := l_contract_currency_code;
2735 px_quot_rec.currency_conversion_code := l_functional_currency_code;
2736
2737 -- If the functional currency is different from contract currency then set
2738 -- currency conversion columns
2739 IF l_functional_currency_code <> l_contract_currency_code THEN
2740
2741 -- Set the currency conversion columns
2742 px_quot_rec.currency_conversion_type := l_currency_conversion_type;
2743 px_quot_rec.currency_conversion_rate := l_currency_conversion_rate;
2744 px_quot_rec.currency_conversion_date := l_currency_conversion_date;
2745
2746 END IF;
2747
2748 -- Set the return status
2749 x_return_status := l_return_status;
2750
2751 IF (is_debug_procedure_on) THEN
2752 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2753 END IF;
2754
2755 EXCEPTION
2756
2757 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2758
2759 x_return_status := OKL_API.G_RET_STS_ERROR;
2760
2761 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2762
2763 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2764
2765 WHEN OTHERS THEN
2766
2767 -- unexpected error
2768 OKL_API.set_message(
2769 p_app_name => 'OKC',
2770 p_msg_name => g_unexpected_error,
2771 p_token1 => g_sqlcode_token,
2772 p_token1_value => sqlcode,
2773 p_token2 => g_sqlerrm_token,
2774 p_token2_value => sqlerrm);
2775
2776 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2777
2778 END set_currency_defaults;
2779
2780
2781 -- Start of comments
2782 --
2783 -- Procedure Name : get_net_gain_loss
2784 -- Description : returns the net gain loss on a termination quote
2785 -- Business Rules :
2786 -- Parameters : IN parameters - quote header, contract id
2787 -- : OUT parameters - net gain loss, return status
2788 -- Version : 1.0
2789 -- History : rkuttiya created 15-SEP-2003 Bug: 2794685
2790 -- : RMUNJULU 2794685 Added comments
2791 -- : rmunjulu 3797384 Added code for passing quote_eff_from date
2792 -- and quote_id to formula engine
2793 -- rmunjulu EDAT 29-Dec-04 did to_char to convert to right format
2794
2795 PROCEDURE Get_Net_Gain_Loss(
2796 p_quote_rec IN quot_rec_type,
2797 p_chr_id IN NUMBER,
2798 x_return_status OUT NOCOPY VARCHAR2,
2799 x_net_gain_loss OUT NOCOPY NUMBER) IS
2800
2801 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
2802 l_rule_code CONSTANT VARCHAR2(30) := 'AMGALO';
2803 l_rgd_code VARCHAR2(30);
2804 l_qtev_rec okl_trx_quotes_pub.qtev_rec_type;
2805 l_rule_khr_id NUMBER;
2806
2807
2808 l_calc_option VARCHAR2(150);
2809 l_fixed_value NUMBER;
2810 l_formula_name VARCHAR2(150);
2811
2812 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2813 l_params OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
2814 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'Get_Net_Gain_Loss';
2815 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2816 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2817 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2818 BEGIN
2819
2820 IF (is_debug_procedure_on) THEN
2821 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
2822 END IF;
2823
2824 l_qtev_rec.khr_id := p_quote_rec.khr_id;
2825 l_qtev_rec.qtp_code := p_quote_rec.qtp_code;
2826
2827 IF l_qtev_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
2828 l_rgd_code := 'AVTGAL';
2829 ELSE
2830 l_rgd_code := 'AMTGAL';
2831 END IF;
2832
2833 --get the rule attributes
2834 l_rule_khr_id := okl_am_util_pvt.get_rule_chr_id (l_qtev_rec);
2835
2836 IF (is_debug_statement_on) THEN
2837 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2838 'Before call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
2839 END IF;
2840
2841 OKL_AM_UTIL_PVT.get_rule_record(
2842 p_rgd_code => l_rgd_code,
2843 p_rdf_code => l_rule_code,
2844 p_chr_id => l_rule_khr_id,
2845 p_cle_id => NULL,
2846 x_rulv_rec => l_rulv_rec,
2847 x_return_status => l_return_status,
2848 p_message_yn => FALSE);
2849
2850 IF (is_debug_statement_on) THEN
2851 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2852 'after call to OKL_AM_UTIL_PVT.get_rule_record :'||l_return_status);
2853 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2854 'l_rgd_code :'||l_rgd_code);
2855 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2856 'l_rule_code :'||l_rule_code);
2857 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2858 'l_rule_khr_id :'||l_rule_khr_id);
2859 END IF;
2860
2861 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
2862 l_calc_option := l_rulv_rec.rule_information1;
2863 l_fixed_value := NVL (To_Number (l_rulv_rec.rule_information2), 0);
2864 l_formula_name := l_rulv_rec.rule_information3;
2865 END IF;
2866
2867 IF l_calc_option = 'NOT_APPLICABLE' THEN -- Net Gain/Loss Option is NOT APPLICABLE
2868
2869 x_net_gain_loss := 0;
2870
2871 ELSIF l_calc_option = 'USE_FIXED_AMOUNT' THEN -- Net Gain/Loss Option is FIXED AMOUNT
2872
2873 x_net_gain_loss := l_fixed_value;
2874
2875 ELSIF l_calc_option = 'USE_FORMULA' THEN -- Net Gain/Loss Option is FORMULA
2876
2877 l_params(1).name := 'QUOTE_ID';
2878 l_params(1).value := p_quote_rec.id;
2879
2880 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
2881
2882 -- set the operands for formula engine with quote_effective_from date
2883 l_params(2).name := 'quote_effective_from_date';
2884 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
2885
2886 -- set the operands for formula engine with quote_id
2887 l_params(3).name := 'quote_id';
2888 l_params(3).value := to_char(p_quote_rec.id);
2889
2890 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
2891 IF (is_debug_statement_on) THEN
2892 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2893 'Before call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
2894 END IF;
2895 -- Get the formula value for the formula for Net Gain/Loss Formula
2896 OKL_AM_UTIL_PVT.get_formula_value (
2897 p_formula_name => l_formula_name,
2898 p_chr_id => l_rule_khr_id,
2899 p_cle_id => NULL,
2900 p_additional_parameters => l_params,
2901 x_formula_value => x_net_gain_loss,
2902 x_return_status => l_return_status);
2903 IF (is_debug_statement_on) THEN
2904 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2905 'after call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
2906 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2907 'l_formula_name :'||l_formula_name);
2908 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2909 'l_rule_khr_id :'||l_rule_khr_id);
2910 END IF;
2911
2912 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2913 x_net_gain_loss := 0;
2914 --RAISE OKL_API.G_EXCEPTION_ERROR;
2915 END IF;
2916 ELSE
2917 x_net_gain_loss := 0;
2918 END IF;
2919
2920 IF (is_debug_procedure_on) THEN
2921 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2922 END IF;
2923
2924 EXCEPTION
2925 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2926 x_return_status := OKL_API.G_RET_STS_ERROR;
2927 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2928 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2929 WHEN OTHERS THEN
2930 IF (is_debug_exception_on) THEN
2931 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2932 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2933 END IF;
2934
2935 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2936 OKL_API.set_message(p_app_name => g_app_name,
2937 p_msg_name => g_unexpected_error,
2938 p_token1 => g_sqlcode_token,
2939 p_token1_value => sqlcode,
2940 p_token2 => g_sqlerrm_token,
2941 p_token2_value => sqlerrm);
2942 END Get_Net_Gain_Loss;
2943
2944
2945 -- Start of comments
2946 --
2947 -- Procedure Name : create_terminate_quote
2948 -- Description : create the terminate quote
2949 -- Business Rules :
2950 -- Parameters : quote header, quote lines
2951 -- Version : 1.0
2952 -- History : SECHAWLA 25-NOV-02 - Bug #2680542 :
2953 -- 1) Added x_partial_asset_line out parameter to validate_quote
2954 -- procedure call
2955 -- 2) used x_partial_asset_line parameter later in the procedure
2956 -- to check if quote includes partial asset line.
2957 -- 3) removed the code to populate l_asset_tbl as it is no longer
2958 -- required to be passed to calculate quote api.
2959 -- 4) passed lp_assn_tbl instead of l_asset_tbl to calculate quote
2960 -- api as the calculate quote api now uses the same asset
2961 -- record structure as the create quote api.
2962 -- 5) Removed DEFAULT from procedure parameters.
2963 --
2964 -- : SECHAWLA 06-DEC-02 - Bug # 2699412 :
2965 -- 1) Change the quote type from Auto to Manual,
2966 -- if Auto Quotes are not allowed
2967 -- 2) Call send quote WF only for Auto Quotes
2968 -- 3) If quote type changed from Auto to Manual, then
2969 -- notify manual termination quotes REP
2970 -- : RMUNJULU 23-DEC-02 2726739 Multi-currency changes, default
2971 -- currency columns
2972 -- : SECHAWLA 02-JAN-03 2724951 : Changed the event name for
2973 -- Notify Manual Quote Rep WF
2974 -- : GKADARKA 06-JAN-03 2683876 Added code to check for non
2975 -- terminated assets in cursor
2976 -- : SECHAWLA 16-JAN-02 Bug # 2754280 : Changed the call to fn
2977 -- get_user_profile_option_name to refer it from am util
2978 -- : SECHAWLA 14-FEB-03 Bug 2749690 : Added code to update the
2979 -- quote header with total net investment,
2980 -- unbilled rec and residual value from all the quote lines
2981 -- : RMUNJULU 18-FEB-03 2804703 Chngd cursor to get active lines
2982 -- : SECHAWLA 18-FEB-03 2807201 Moved the check_quote_type procedure call from
2983 -- validate quote to the beginning if this procedure.
2984 -- : SECHAWLA 28-FEB-03 2757175 Modified the Manual Quote notification message to
2985 -- display the profile value and not the underlying ID.
2986 -- : SECHAWLA 14-APR-03 2902588 Changed the standard REQUIRED message to OKL_AM_NO_VENDOR_PROGRAM
2987 -- in the not null validation of l_rule_chr_id
2988 -- : SECHAWLA 15-APR-03 2902588 Changed the fetch order of columns in cur_k_end_date cursor.
2989 -- : SECHAWLA 03-OCT-03 ER 2777984 Calculate Quote Payments for a partial termination quote
2990 -- : RMUNJULU 3241502 Added code to set split_asset_number with UPPER CASE
2991 -- : rmunjulu 3842101 changed code so that gainloss is done after quote updated for net investment
2992 -- : RMUNJULU EDT 3797384 Added code to default eff_from_date and
2993 -- changed condition to check for early termination yn
2994 -- : RMUNJULU LOANS_ENHANCEMENTS Add code to evaluate and populate perdiem amount
2995 -- End of comments
2996 PROCEDURE create_terminate_quote(
2997 p_api_version IN NUMBER,
2998 p_init_msg_list IN VARCHAR2 ,
2999 x_return_status OUT NOCOPY VARCHAR2,
3000 x_msg_count OUT NOCOPY NUMBER,
3001 x_msg_data OUT NOCOPY VARCHAR2,
3002 p_quot_rec IN quot_rec_type,
3003 p_assn_tbl IN assn_tbl_type,
3004 p_qpyv_tbl IN qpyv_tbl_type ,
3005 x_quot_rec OUT NOCOPY quot_rec_type,
3006 x_tqlv_tbl OUT NOCOPY tqlv_tbl_type,
3007 x_assn_tbl OUT NOCOPY assn_tbl_type) AS
3008
3009 -- SECHAWLA 14-APR-03 2902588 : Added contract_number in the SELECT clause. Changed FROM claue to use
3010 -- okc_k_headers_b instead of okc_k_headers_v
3011 -- Cursor to get the end date of the contract
3012 CURSOR cur_k_end_date ( p_chr_id NUMBER) IS
3013 SELECT contract_number, end_date
3014 FROM OKC_K_HEADERS_B
3015 WHERE id = p_chr_id;
3016
3017 -- Cursor to get the number of financial assets for the contract
3018 -- Outer join with line styles in UV... but gives error here in calculate quote
3019 -- GKADARKA 06-JAN-03 2683876 Added code to check for non terminated assets
3020 -- RMUNJULU 18-FEB-03 2804703 Changed cursor to check for active lines only
3021 CURSOR cur_k_assets ( p_chr_id NUMBER ) IS
3022 SELECT COUNT(OKLV.id )
3023 FROM OKC_K_LINES_V OKLV,
3024 OKC_LINE_STYLES_V OLSV,
3025 OKC_K_HEADERS_V KHR
3026 WHERE OKLV.lse_id = OLSV.id
3027 AND OLSV.lty_code = 'FREE_FORM1'
3028 AND OKLV.chr_id = p_chr_id
3029 AND OKLV.sts_code = KHR.sts_code
3030 AND OKLV.chr_id = KHR.id;
3031 --AND OKLV.date_terminated IS NULL; -- RMUNJULU 18-FEB-03 2804703 removed
3032
3033 --SECHAWLA 28-FEB-03 Bug # 2757175 : Added the following cursor
3034 -- This cursor isused to get the display name for a role
3035 CURSOR l_wfroles_csr(p_name wf_roles.name%TYPE) IS
3036 SELECT display_name
3037 FROM wf_roles
3038 WHERE name = p_name;
3039
3040 l_display_name wf_roles.display_name%TYPE;
3041 lx_contract_status VARCHAR2(200);
3042 --SECHAWLA 28-FEB-03 Bug # 2757175 : end new declarations
3043
3044 lp_quot_rec quot_rec_type := p_quot_rec;
3045 lx_quot_rec quot_rec_type;
3046 lp_assn_tbl assn_tbl_type := p_assn_tbl;
3047 lx_assn_tbl assn_tbl_type := p_assn_tbl;
3048 lx_tqlv_tbl tqlv_tbl_type;
3049 l_qpyv_tbl qpyv_tbl_type;
3050 l_quote_eff_days NUMBER;
3051 l_quote_eff_max_days NUMBER;
3052 l_days_before_k_exp NUMBER;
3053 l_api_version CONSTANT NUMBER := 1;
3054 l_api_name CONSTANT VARCHAR2(30) := 'create_terminate_quote';
3055 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3056 l_no_of_assets NUMBER := -1;
3057 l_k_end_date DATE;
3058 l_sys_date DATE;
3059 i NUMBER := 0;
3060 l_rule_chr_id NUMBER;
3061 l_event_name VARCHAR2(2000);
3062 lx_partial_asset_line BOOLEAN;
3063
3064 lx_auto_to_manual BOOLEAN := FALSE; --added
3065 lx_new_quote_type VARCHAR2(30); --added
3066 l_user_profile_name VARCHAR2(240); --added
3067 l_manual_quote_rep VARCHAR2(320); --added
3068
3069 --SECHAWLA 14-FEB-03 Bug 2749690 : new declarations
3070 l_total_net_investment NUMBER := 0;
3071 l_total_unbilled_rec NUMBER := 0;
3072 l_total_residual_value NUMBER := 0;
3073 lp_empty_quot_rec quot_rec_type;
3074
3075 -- SECHAWLA 14-APR-03 2902588 : New Declarations
3076 l_contract_number okc_k_headers_b.contract_number%TYPE;
3077
3078 --rkuttiya 15-SEP-2003 for bug: 2794685
3079 lx_net_gain_loss NUMBER;
3080
3081 -- rmunjulu LOANS_ENHANCEMENTS
3082 l_per_diem_amt NUMBER;
3083 l_params OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
3084
3085 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_terminate_quote';
3086 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
3087 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
3088 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
3089 BEGIN
3090
3091 IF (is_debug_procedure_on) THEN
3092 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
3093 END IF;
3094
3095 --Print Input Variables
3096 IF (is_debug_statement_on) THEN
3097 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3098 'p_api_version :'||p_api_version);
3099 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3100 'p_init_msg_list :'||p_init_msg_list);
3101 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.id : '||p_quot_rec.id );
3102 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qrs_code : '||p_quot_rec.qrs_code );
3103 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qst_code : '||p_quot_rec.qst_code );
3104 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_qte_id : '||p_quot_rec.consolidated_qte_id );
3105 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.khr_id : '||p_quot_rec.khr_id );
3106 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.art_id : '||p_quot_rec.art_id );
3107 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.qtp_code : '||p_quot_rec.qtp_code );
3108 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.trn_code : '||p_quot_rec.trn_code );
3109 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.pdt_id : '||p_quot_rec.pdt_id );
3110 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_from : '||p_quot_rec.date_effective_from );
3111 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.quote_number : '||p_quot_rec.quote_number );
3112 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.early_termination_yn : '||p_quot_rec.early_termination_yn );
3113 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.partial_yn : '||p_quot_rec.partial_yn );
3114 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.preproceeds_yn : '||p_quot_rec.preproceeds_yn );
3115 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.summary_format_yn : '||p_quot_rec.summary_format_yn );
3116 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.consolidated_yn : '||p_quot_rec.consolidated_yn );
3117 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_requested : '||p_quot_rec.date_requested );
3118 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_proposal : '||p_quot_rec.date_proposal );
3119 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_effective_to : '||p_quot_rec.date_effective_to );
3120 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.date_accepted : '||p_quot_rec.date_accepted );
3121 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.payment_received_yn : '||p_quot_rec.payment_received_yn );
3122 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.requested_by : '||p_quot_rec.requested_by );
3123 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.approved_yn : '||p_quot_rec.approved_yn );
3124 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.accepted_yn : '||p_quot_rec.accepted_yn );
3125 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.org_id : '||p_quot_rec.org_id );
3126 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_amount : '||p_quot_rec.purchase_amount );
3127 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.purchase_formula : '||p_quot_rec.purchase_formula );
3128 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.asset_value : '||p_quot_rec.asset_value );
3129 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.residual_value : '||p_quot_rec.residual_value );
3130 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.unbilled_receivables : '||p_quot_rec.unbilled_receivables );
3131 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.gain_loss : '||p_quot_rec.gain_loss );
3132 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.PERDIEM_AMOUNT : '||p_quot_rec.PERDIEM_AMOUNT );
3133 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_code : '||p_quot_rec.currency_code );
3134 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.currency_conversion_code : '||p_quot_rec.currency_conversion_code );
3135 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_quot_rec.legal_entity_id : '||p_quot_rec.legal_entity_id );
3136 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 );
3137
3138 IF (p_assn_tbl.COUNT > 0) THEN
3139 FOR i IN p_assn_tbl.FIRST..p_assn_tbl.LAST LOOP
3140
3141 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 );
3142 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 );
3143 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 );
3144 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 );
3145 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);
3146 End loop;
3147 END IF;
3148
3149 END IF;
3150
3151
3152 IF (is_debug_statement_on) THEN
3153 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3154 'before call to OKL_API.START_ACTIVITY :'||l_return_status);
3155 END IF;
3156
3157
3158
3159
3160
3161 --Check API version, initialize message list and create savepoint.
3162 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3163 G_PKG_NAME,
3164 p_init_msg_list,
3165 l_api_version,
3166 p_api_version,
3167 '_PVT',
3168 x_return_status);
3169
3170 IF (is_debug_statement_on) THEN
3171 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3172 'after call to OKL_API.START_ACTIVITY :'||l_return_status);
3173 END IF;
3174
3175 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3176 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3177 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3178 RAISE OKL_API.G_EXCEPTION_ERROR;
3179 END IF;
3180
3181 SELECT SYSDATE INTO l_sys_date FROM DUAL;
3182
3183 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
3184 -- RMUNJULU EDT 3797384 default the date effective from in the beginning
3185 IF lp_quot_rec.date_effective_from IS NULL
3186 OR lp_quot_rec.date_effective_from = OKL_API.G_MISS_DATE THEN
3187
3188 lp_quot_rec.date_effective_from := l_sys_date;
3189
3190 END IF;
3191 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
3192
3193 -- SECHAWLA 28-FEB-03 Bug # 2757175 : Moved the following validation from validate_contract procedure
3194
3195 -- Call the validate contract to check contract status
3196
3197 IF (is_debug_statement_on) THEN
3198 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3199 'before call to OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract :'||l_return_status);
3200 END IF;
3201
3202
3203 OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract(
3204 p_api_version => p_api_version,
3205 p_init_msg_list => OKL_API.G_FALSE,
3206 x_return_status => l_return_status,
3207 x_msg_count => x_msg_count,
3208 x_msg_data => x_msg_data,
3209 p_contract_id => lp_quot_rec.khr_id,
3210 p_control_flag => 'TRMNT_QUOTE_CREATE',
3211 x_contract_status => lx_contract_status);
3212
3213 IF (is_debug_statement_on) THEN
3214 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3215 'after call to OKL_AM_LEASE_LOAN_TRMNT_PUB.validate_contract :'||l_return_status);
3216 END IF;
3217
3218 -- If error then above api will set the message, so exit now
3219 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3220 x_return_status := OKL_API.G_RET_STS_ERROR;
3221 RAISE OKL_API.G_EXCEPTION_ERROR;
3222 END IF;
3223 -- SECHAWLA 28-FEB-03 Bug # 2757175 :end moved code
3224
3225 OPEN cur_k_end_date(lp_quot_rec.khr_id);
3226 -- SECHAWLA 14-APR-03 2902588 : fetched the new column : contract_number
3227 FETCH cur_k_end_date INTO l_contract_number, l_k_end_date ; -- SECHAWLA 15-APR-03 2902588 : Changed the fetch order of columns
3228 IF cur_k_end_date%NOTFOUND THEN
3229 l_k_end_date := OKL_API.G_MISS_DATE;
3230 END IF;
3231 CLOSE cur_k_end_date;
3232
3233 OPEN cur_k_assets (lp_quot_rec.khr_id);
3234 FETCH cur_k_assets INTO l_no_of_assets;
3235 IF cur_k_assets%NOTFOUND THEN
3236 l_no_of_assets := -1;
3237 END IF;
3238 CLOSE cur_k_assets;
3239
3240 -- SECHAWLA 18-FEB-03 Bug # 2807201 : Moved the quote_type_check procedure call here from vaidate_quote
3241 -- as we want to validate and change the quote type (if required) in the beginning, before any other processing
3242 -- check if quote type is valid
3243 lx_new_quote_type := lp_quot_rec.qtp_code; ---SECHAWLA 2699412 added
3244
3245 -- rmunjulu 4923976 : added if check so that quote type check should not be done for TER_RELEASE_WO_PURCHASE quote
3246 IF lp_quot_rec.qtp_code <> 'TER_RELEASE_WO_PURCHASE' THEN
3247
3248 IF (is_debug_statement_on) THEN
3249 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3250 'before call to quote_type_check :'||l_return_status);
3251 END IF;
3252 quote_type_check(
3253 -- p_qtp_code => p_quot_rec.qtp_code, -- SECHAWLA 2699412
3254 p_qtp_code => lx_new_quote_type, -- SECHAWLA 2699412 changed
3255 p_khr_id => lp_quot_rec.khr_id, -- SECHAWLA 02-JAN-03 Added
3256 x_auto_to_manual => lx_auto_to_manual, -- -SECHAWLA 2699412 added
3257 x_return_status => l_return_status);
3258
3259 IF (is_debug_statement_on) THEN
3260 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3261 'after call to quote_type_check :'||l_return_status);
3262 END IF;
3263
3264 END IF; -- rmunjulu 4923976
3265
3266 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3267 x_return_status := OKL_API.G_RET_STS_ERROR;
3268 --Please select a valid Quote Type.
3269 OKL_API.set_message( p_app_name => 'OKL',
3270 p_msg_name =>'OKL_AM_QTP_CODE_INVALID');
3271 RAISE OKL_API.G_EXCEPTION_ERROR; -- SECHAWLA 28-FEB-03 Bug 2757175 : Changed the exception name as a result
3272 -- of moving this procedure from validate_quote on 18-FEB-03 Bug # 2807201
3273 END IF;
3274
3275 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- Added
3276 IF (lx_auto_to_manual) THEN
3277 lp_quot_rec.qtp_code := lx_new_quote_type;
3278 END IF;
3279
3280 -- SECHAWLA 18-FEB-03 Bug # 2807201 : end moved code
3281
3282 l_rule_chr_id := okl_am_util_pvt.get_rule_chr_id (lp_quot_rec);
3283
3284 -- SECHAWLA 28-FEB-03 Bug # 2757175 : Added a not null validation for l_rule_chr_id
3285 IF l_rule_chr_id IS NULL THEN
3286 x_return_status := OKL_API.G_RET_STS_ERROR;
3287 -- SECHAWLA 14-APR-03 2902588 : Use the following message instead of the standard REQUIRED message
3288
3289 --Unable to create quote because the contract CONTRACT_NUMBER does not have an associated vendor program.
3290 OKC_API.set_message( p_app_name => 'OKL',
3291 p_msg_name => 'OKL_AM_NO_VENDOR_PROGRAM',
3292 p_token1 => 'CONTRACT_NUMBER',
3293 p_token1_value => l_contract_number);
3294 RAISE OKL_API.G_EXCEPTION_ERROR;
3295 END IF;
3296
3297 -- SECHAWLA Bug # 2699412 : Moved the following code here so that
3298 -- l_days_before_k_exp can be passed to validate_quote
3299 -- to check for early terminations
3300
3301
3302 IF (is_debug_statement_on) THEN
3303 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3304 'before call to term_status :'||l_return_status);
3305 END IF;
3306 -- set term status from rules
3307 term_status(
3308 p_quot_rec => lp_quot_rec,
3309 p_rule_chr_id => l_rule_chr_id,
3310 x_days_before_k_exp => l_days_before_k_exp,
3311 x_return_status => l_return_status);
3312
3313 IF (is_debug_statement_on) THEN
3314 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3315 'after call to term_status :'||l_return_status);
3316 END IF;
3317
3318 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3319 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3320 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3321 RAISE OKL_API.G_EXCEPTION_ERROR;
3322 END IF;
3323 -- end moved code
3324
3325 -- SECHAWLA Bug #2680542 : Added x_partial_asset_line parameter to
3326 -- validate_quote procedure call
3327 -- check if quote valid
3328 IF (is_debug_statement_on) THEN
3329 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3330 'before call to validate_quote :'||l_return_status);
3331 END IF;
3332
3333 validate_quote(
3334 p_api_version => p_api_version,
3335 p_init_msg_list => OKL_API.G_FALSE,
3336 x_return_status => l_return_status,
3337 x_msg_count => x_msg_count,
3338 x_msg_data => x_msg_data,
3339 p_quot_rec => lp_quot_rec,
3340 p_assn_tbl => lp_assn_tbl,
3341 p_k_end_date => l_k_end_date,
3342 p_no_of_assets => l_no_of_assets,
3343 p_sys_date => l_sys_date,
3344 p_rule_chr_id => l_rule_chr_id,
3345 p_days_before_k_exp => l_days_before_k_exp, --SECHAWLA 06-DEC-02 2699412 added
3346 x_partial_asset_line => lx_partial_asset_line);
3347
3348 IF (is_debug_statement_on) THEN
3349 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3350 'after call to validate_quote :'||l_return_status);
3351 END IF;
3352
3353 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3354 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3355 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3356 RAISE OKL_API.G_EXCEPTION_ERROR;
3357 END IF;
3358
3359 -- RMUNJULU 3241502 Added code to set split_asset_number with UPPER CASE
3360 IF (lp_assn_tbl.COUNT > 0) THEN
3361 FOR i IN lp_assn_tbl.FIRST..lp_assn_tbl.LAST LOOP
3362
3363 IF lp_assn_tbl(i).p_split_asset_number IS NOT NULL
3364 AND lp_assn_tbl(i).p_split_asset_number <> OKL_API.G_MISS_CHAR THEN
3365 lp_assn_tbl(i).p_split_asset_number := UPPER(lp_assn_tbl(i).p_split_asset_number);
3366 END IF;
3367 END LOOP;
3368 END IF;
3369
3370 IF (is_debug_statement_on) THEN
3371 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3372 'before call to quote_effectivity :'||l_return_status);
3373 END IF;
3374 -- set the date eff to from rules
3375 quote_effectivity(
3376 p_quot_rec => lp_quot_rec,
3377 p_rule_chr_id => l_rule_chr_id,
3378 x_quote_eff_days => l_quote_eff_days,
3379 x_quote_eff_max_days => l_quote_eff_max_days,
3380 x_return_status => l_return_status);
3381
3382 IF (is_debug_statement_on) THEN
3383 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3384 'after call to quote_effectivity :'||l_return_status);
3385 END IF;
3386
3387 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3388 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3389 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3390 RAISE OKL_API.G_EXCEPTION_ERROR;
3391 END IF;
3392
3393 -- check if early termination
3394 IF (lp_quot_rec.early_termination_yn IS NULL)
3395 OR (lp_quot_rec.early_termination_yn = OKL_API.G_MISS_CHAR) THEN
3396 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
3397 -- IF (TRUNC(l_sys_date) < TRUNC(l_k_end_date) - l_days_before_k_exp) THEN
3398 -- rmunjulu EDT 3797384 changed check to check based on effective date
3399 IF (TRUNC(lp_quot_rec.date_effective_from) < TRUNC(l_k_end_date) - l_days_before_k_exp) THEN
3400 lp_quot_rec.early_termination_yn := 'Y';
3401 ELSE
3402 lp_quot_rec.early_termination_yn := 'N';
3403 END IF;
3404 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
3405 END IF;
3406
3407 -- check if partial quote
3408 IF (lp_quot_rec.partial_yn IS NULL)
3409 OR (lp_quot_rec.partial_yn = OKL_API.G_MISS_CHAR) THEN
3410 -- SECHAWLA Bug #2680542 : Added (lx_partial_asset_line) condition to
3411 -- the following IF to check if the
3412 -- quote is a partial quote
3413 IF (p_assn_tbl.COUNT < l_no_of_assets)
3414 OR (lx_partial_asset_line) THEN -- added second condition
3415 lp_quot_rec.partial_yn := 'Y';
3416 ELSE
3417 lp_quot_rec.partial_yn := 'N';
3418 END IF;
3419 END IF;
3420
3421
3422 IF (is_debug_statement_on) THEN
3423 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3424 'before call to set_quote_defaults :'||l_return_status);
3425 END IF;
3426
3427 -- Set the quote defaults
3428 set_quote_defaults(
3429 px_quot_rec => lp_quot_rec,
3430 p_rule_chr_id => l_rule_chr_id,
3431 p_sys_date => l_sys_date,
3432 x_return_status => l_return_status);
3433
3434 IF (is_debug_statement_on) THEN
3435 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3436 'after call to set_quote_defaults :'||l_return_status);
3437 END IF;
3438
3439 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3440 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3441 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3442 RAISE OKL_API.G_EXCEPTION_ERROR;
3443 END IF;
3444
3445
3446
3447 IF (is_debug_statement_on) THEN
3448 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3449 'before call to set_currency_defaults :'||l_return_status);
3450 END IF;
3451
3452 -- RMUNJULU 23-DEC-02 2726739 Multi-currency changes
3453 -- Default the Multi-Currency Columns
3454 set_currency_defaults(
3455 px_quot_rec => lp_quot_rec,
3456 p_sys_date => l_sys_date,
3457 x_return_status => l_return_status);
3458
3459 IF (is_debug_statement_on) THEN
3460 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3461 'after call to set_currency_defaults :'||l_return_status);
3462 END IF;
3463
3464 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3465 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3466 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3467 RAISE OKL_API.G_EXCEPTION_ERROR;
3468 END IF;
3469
3470 IF (is_debug_statement_on) THEN
3471 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3472 'before call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);
3473 END IF;
3474
3475 -- call the pub tapi insert
3476 OKL_TRX_QUOTES_PUB.insert_trx_quotes (
3477 p_api_version => p_api_version,
3478 p_init_msg_list => OKL_API.G_FALSE,
3479 x_msg_count => x_msg_count,
3480 x_msg_data => x_msg_data,
3481 p_qtev_rec => lp_quot_rec,
3482 x_qtev_rec => lx_quot_rec,
3483 x_return_status => l_return_status);
3484
3485 IF (is_debug_statement_on) THEN
3486 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3487 'after call to OKL_TRX_QUOTES_PUB.insert_trx_quotes :'||l_return_status);
3488 END IF;
3489
3490 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3491 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3492 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3493 RAISE OKL_API.G_EXCEPTION_ERROR;
3494 END IF;
3495
3496
3497 IF (is_debug_statement_on) THEN
3498 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3499 'before call to OKL_AM_PARTIES_PVT.create_quote_parties :'||l_return_status);
3500 END IF;
3501
3502 -- Create quote parties
3503 OKL_AM_PARTIES_PVT.create_quote_parties (
3504 p_qtev_rec => lx_quot_rec,
3505 p_qpyv_tbl => p_qpyv_tbl,
3506 x_qpyv_tbl => l_qpyv_tbl,
3507 x_return_status => l_return_status);
3508
3509 IF (is_debug_statement_on) THEN
3510 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3511 'after call to OKL_AM_PARTIES_PVT.create_quote_parties :'||l_return_status);
3512 END IF;
3513
3514
3515 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3516 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3517 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3518 RAISE OKL_API.G_EXCEPTION_ERROR;
3519 END IF;
3520
3521 -- call quote calculation api (pass assets tbl)
3522 -- this will insert quote lines
3523
3524 IF (is_debug_statement_on) THEN
3525 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3526 'before call to OKL_AM_CALCULATE_QUOTE_PVT.generate :'||l_return_status);
3527 END IF;
3528
3529 OKL_AM_CALCULATE_QUOTE_PVT.generate(
3530 p_api_version => p_api_version,
3531 p_init_msg_list => OKL_API.G_FALSE,
3532 x_msg_count => x_msg_count,
3533 x_msg_data => x_msg_data,
3534 p_qtev_rec => lx_quot_rec,
3535 --p_asset_tbl => l_asset_tbl, -- SECHAWLA Bug #2680542 : calculate quote api now uses the same asset
3536 p_asset_tbl => lp_assn_tbl, -- record structure as the create quote api
3537 x_tqlv_tbl => lx_tqlv_tbl,
3538 x_return_status => l_return_status);
3539
3540 IF (is_debug_statement_on) THEN
3541 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3542 'After call to OKL_AM_CALCULATE_QUOTE_PVT.generate :'||l_return_status);
3543 END IF;
3544
3545 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3546 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3547 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3548 RAISE OKL_API.G_EXCEPTION_ERROR;
3549 END IF;
3550
3551 -- SECHAWLA 14-FEB-03 2749690 : sum up the net investment, unbilled receivable and residual value amounts for
3552 -- the quote lines and store the total amounts at the quote header level
3553 IF lx_tqlv_tbl.COUNT > 0 THEN
3554 i := lx_tqlv_tbl.FIRST;
3555 LOOP
3556 IF lx_tqlv_tbl(i).qlt_code = 'AMCFIA' THEN
3557 l_total_net_investment := l_total_net_investment + lx_tqlv_tbl(i).asset_value;
3558 l_total_unbilled_rec := l_total_unbilled_rec + lx_tqlv_tbl(i).unbilled_receivables;
3559 l_total_residual_value := l_total_residual_value + lx_tqlv_tbl(i).residual_value;
3560 END IF;
3561
3562 EXIT WHEN (i = lx_tqlv_tbl.LAST);
3563 i := lx_tqlv_tbl.NEXT(i);
3564 END LOOP;
3565 END IF;
3566
3567 -- call the pub tapi update to update the above totals at the header level
3568 lp_quot_rec := lp_empty_quot_rec;
3569 lp_quot_rec.id := lx_quot_rec.id ;
3570 lp_quot_rec.asset_value := l_total_net_investment;
3571 lp_quot_rec.unbilled_receivables := l_total_unbilled_rec;
3572 lp_quot_rec.residual_value := l_total_residual_value;
3573
3574 IF (is_debug_statement_on) THEN
3575 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3576 'before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3577 END IF;
3578
3579 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3580 p_api_version => p_api_version,
3581 p_init_msg_list => OKL_API.G_FALSE,
3582 x_msg_count => x_msg_count,
3583 x_msg_data => x_msg_data,
3584 p_qtev_rec => lp_quot_rec,
3585 x_qtev_rec => lx_quot_rec,
3586 x_return_status => l_return_status);
3587
3588 IF (is_debug_statement_on) THEN
3589 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3590 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3591 END IF;
3592
3593 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3594 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3595 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3596 RAISE OKL_API.G_EXCEPTION_ERROR;
3597 END IF;
3598 -- end new code
3599
3600
3601 IF (is_debug_statement_on) THEN
3602 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3603 'before call to get_net_gain_loss :'||l_return_status);
3604 END IF;
3605
3606 -- rmunjulu 3842101 moved net gain loss calculation here
3607 get_net_gain_loss(
3608 p_quote_rec =>lx_quot_rec,
3609 p_chr_id =>lx_quot_rec.khr_id,
3610 x_return_status =>l_return_status,
3611 x_net_gain_loss =>lx_net_gain_loss) ;
3612
3613
3614 IF (is_debug_statement_on) THEN
3615 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3616 'After call to get_net_gain_loss :'||l_return_status);
3617 END IF;
3618
3619 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3620 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3621 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3622 RAISE OKL_API.G_EXCEPTION_ERROR;
3623 END IF;
3624
3625 -- rmunjulu 3842101 added this code here so that formula uses calculation from quote
3626 lp_quot_rec := lp_empty_quot_rec;
3627 lp_quot_rec.id := lx_quot_rec.id ;
3628 lp_quot_rec.gain_loss := lx_net_gain_loss;
3629
3630 l_params(1).name := 'QUOTE_ID';
3631 l_params(1).value := lp_quot_rec.id;
3632
3633 IF (is_debug_statement_on) THEN
3634 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3635 'before call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
3636 END IF;
3637
3638 -- rmunjulu LOANS_ENHANCEMENTS Evaluate Quote perdiem amount formula and set quote perdiem value
3639 OKL_AM_UTIL_PVT.get_formula_value(
3640 p_formula_name => 'QUOTE_PERDIEM_AMOUNT',
3641 p_chr_id => lx_quot_rec.khr_id,
3642 p_cle_id => NULL,
3643 p_additional_parameters => l_params,
3644 x_formula_value => l_per_diem_amt,
3645 x_return_status => l_return_status);
3646
3647 IF (is_debug_statement_on) THEN
3648 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3649 'After call to OKL_AM_UTIL_PVT.get_formula_value :'||l_return_status);
3650 END IF;
3651
3652 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3653 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3654 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3655 RAISE OKL_API.G_EXCEPTION_ERROR;
3656 END IF;
3657
3658 lp_quot_rec.perdiem_amount := l_per_diem_amt;
3659
3660
3661 IF (is_debug_statement_on) THEN
3662 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3663 'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3664 END IF;
3665
3666 -- rmunjulu 3842101 update the quote header again with GAIN LOSS + PERDIEM
3667 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3668 p_api_version => p_api_version,
3669 p_init_msg_list => OKL_API.G_FALSE,
3670 x_msg_count => x_msg_count,
3671 x_msg_data => x_msg_data,
3672 p_qtev_rec => lp_quot_rec,
3673 x_qtev_rec => lx_quot_rec,
3674 x_return_status => l_return_status);
3675
3676 IF (is_debug_statement_on) THEN
3677 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3678 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
3679 END IF;
3680
3681 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3682 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3683 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3684 RAISE OKL_API.G_EXCEPTION_ERROR;
3685 END IF;
3686
3687 -- rmunjulu TNA 4059175 Brought this up above the workflow processing.
3688 -- SECHAWLA 03-OCT-2003 11i10 ER 2777984:Calculate Quote Payments for a partial termination quote
3689 IF lx_quot_rec.partial_yn = 'Y' THEN
3690
3691 IF (is_debug_statement_on) THEN
3692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3693 'Before call to OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments :'||l_return_status);
3694 END IF;
3695
3696 OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments(
3697 p_api_version => p_api_version,
3698 p_init_msg_list => OKL_API.G_FALSE,
3699 x_return_status => l_return_status,
3700 x_msg_count => x_msg_count,
3701 x_msg_data => x_msg_data,
3702 p_quote_id => lx_quot_rec.id);
3703
3704 IF (is_debug_statement_on) THEN
3705 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3706 'After call to OKL_AM_CALC_QUOTE_PYMNT_PVT.calc_quote_payments :'||l_return_status);
3707 END IF;
3708
3709 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3710 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3711 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3712 RAISE OKL_API.G_EXCEPTION_ERROR;
3713 END IF;
3714
3715 END IF;
3716 -- SECHAWLA 03-OCT-2003 11i10 ER : 2777984 end
3717
3718 /* -- rmunjulu messages come twice so this fix
3719 -- rmunjulu TNA 4059175 Added process message here to get the messages
3720 -- Save messages in database
3721 OKL_AM_UTIL_PVT.process_messages (
3722 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3723 p_trx_id => lx_quot_rec.id,
3724 x_return_status => l_return_status);
3725
3726 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3727 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3728 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3729 RAISE OKL_API.G_EXCEPTION_ERROR;
3730 END IF;
3731 */
3732
3733 -- rmunjulu TNA 4059175 Added new IF to check for non Release quotes and DO send quote only for those
3734 IF lx_new_quote_type <> 'TER_RELEASE_WO_PURCHASE' THEN
3735
3736 -- rmunjulu TNA By this time the quote is already switched to manual if needed so will not go into this if
3737 IF lx_new_quote_type NOT LIKE 'TER_MAN%' THEN -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- added
3738 -- Request quote approval and notification
3739
3740 IF (is_debug_statement_on) THEN
3741 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3742 'Before call to OKL_AM_WF.raise_business_event :'||l_return_status);
3743 END IF;
3744
3745 OKL_AM_WF.raise_business_event (
3746 p_transaction_id => lx_quot_rec.id,
3747 p_event_name => 'oracle.apps.okl.am.sendquote');
3748
3749 IF (is_debug_statement_on) THEN
3750 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3751 'After call to OKL_AM_WF.raise_business_event :'||l_return_status);
3752 END IF;
3753
3754 END IF;
3755
3756 -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- Added the following program logic
3757 -- to send notification to the manual
3758 -- quote representative, if the quote type was changed from Auto to Manual
3759 IF (lx_auto_to_manual) THEN
3760
3761 -- rmunjulu messages come twice so this fix
3762 -- rmunjulu TNA 4059175 Added process message here to get the messages
3763 -- Save messages in database
3764
3765 IF (is_debug_statement_on) THEN
3766 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3767 'Before call to OKL_AM_UTIL_PVT.process_messages :'||l_return_status);
3768 END IF;
3769
3770 OKL_AM_UTIL_PVT.process_messages (
3771 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3772 p_trx_id => lx_quot_rec.id,
3773 x_return_status => l_return_status);
3774
3775 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3776 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3777 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3778 RAISE OKL_API.G_EXCEPTION_ERROR;
3779 END IF;
3780
3781 -- clear the stack
3782 okl_api.init_msg_list ( p_init_msg_list => OKL_API.G_TRUE);
3783
3784 l_manual_quote_rep := fnd_profile.value('OKL_MANUAL_TERMINATION_QUOTE_REP');
3785
3786 IF l_manual_quote_rep IS NULL THEN
3787
3788
3789 IF (is_debug_statement_on) THEN
3790 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3791 'Before call to okl_am_util_pvt.get_user_profile_option_name :'||l_return_status);
3792 END IF;
3793
3794 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
3795 p_profile_option_name => 'OKL_MANUAL_TERMINATION_QUOTE_REP',
3796 x_return_status => l_return_status);
3797
3798 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
3799 --Manual Quote Representative profile is missing.
3800 OKL_API.set_message( p_app_name => 'OKL',
3801 p_msg_name => 'OKL_AM_NO_MQ_REP_PROFILE');
3802
3803 RAISE okl_api.G_EXCEPTION_ERROR;
3804 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
3805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3806 END IF;
3807
3808 x_return_status := OKL_API.G_RET_STS_ERROR;
3809
3810 --Profile value not defined
3811 OKL_API.set_message(
3812 p_app_name => 'OKL',
3813 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
3814 p_token1 => 'PROFILE',
3815 p_token1_value => l_user_profile_name);
3816
3817 RAISE OKL_API.G_EXCEPTION_ERROR;
3818 END IF;
3819
3820 --SECHAWLA 28-FEB-03 Bug # 2757175 : get the display name for a role
3821 OPEN l_wfroles_csr(l_manual_quote_rep);
3822 FETCH l_wfroles_csr INTO l_display_name;
3823 CLOSE l_wfroles_csr;
3824
3825 IF l_display_name IS NULL THEN
3826 l_display_name := l_manual_quote_rep;
3827 END IF;
3828
3829 -- Contract only allows for manual quotes. Manual quote request
3830 -- has been sent to MAN_QUOTE_REP.
3831 OKL_API.set_message(
3832 p_app_name => 'OKL',
3833 p_msg_name => 'OKL_AM_MAN_QUOTE_ALLOWED',
3834 p_token1 => 'MAN_QUOTE_REP',
3835 p_token1_value => l_display_name); --SECHAWLA 28-FEB-03 Bug # 2757175 : Changed to show display_name
3836 -- instead of name
3837
3838 IF (is_debug_statement_on) THEN
3839 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3840 'Before call to OKL_AM_WF.raise_business_event p_event_name oracle.apps.okl.am.manualquote :'||l_return_status);
3841 END IF;
3842
3843
3844 --SECHAWLA 28-FEB-03 Bug # 2757175 : end modifications
3845 -- notify manual quote representative
3846 OKL_AM_WF.raise_business_event (
3847 p_transaction_id => lx_quot_rec.id,
3848 p_event_name => 'oracle.apps.okl.am.manualquote'); -- SECHAWLA 02-JAN-03 2724951 Changed the event name
3849
3850 -- Save messages in database
3851 OKL_AM_UTIL_PVT.process_messages (
3852 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3853 p_trx_id => lx_quot_rec.id,
3854 x_return_status => l_return_status);
3855
3856 ELSE -- if not auto to manual -- rmunjulu TNA 4059175 Added this if
3857
3858 -- Save messages in database
3859 OKL_AM_UTIL_PVT.process_messages (
3860 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3861 p_trx_id => lx_quot_rec.id,
3862 x_return_status => l_return_status);
3863
3864 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- added the exception handling
3865 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3866 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3867 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3868 RAISE OKL_API.G_EXCEPTION_ERROR;
3869 END IF;
3870 END IF;
3871 -- SECHAWLA 06-DEC-02 - Bug # 2699412 -- end new code
3872 ELSE -- Quote is Termination Release Without Purchase -- rmunjulu TNA 4059175
3873
3874 -- Update the quote to approved directly
3875 lp_quot_rec := lp_empty_quot_rec;
3876 lp_quot_rec.id := lx_quot_rec.id ;
3877 lp_quot_rec.qst_code := 'APPROVED';
3878 lp_quot_rec.date_approved := sysdate;
3879
3880 IF (is_debug_statement_on) THEN
3881 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3882 'Before call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);
3883 END IF;
3884
3885 -- rmunjulu TNA 4059175 update the quote header with status as APPROVED
3886 OKL_TRX_QUOTES_PUB.update_trx_quotes (
3887 p_api_version => p_api_version,
3888 p_init_msg_list => OKL_API.G_FALSE,
3889 x_msg_count => x_msg_count,
3890 x_msg_data => x_msg_data,
3891 p_qtev_rec => lp_quot_rec,
3892 x_qtev_rec => lx_quot_rec,
3893 x_return_status => l_return_status);
3894
3895 IF (is_debug_statement_on) THEN
3896 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3897 'After call to OKL_TRX_QUOTES_PUB.update_trx_quotes'||l_return_status);
3898 END IF;
3899
3900 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3901 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3902 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3903 RAISE OKL_API.G_EXCEPTION_ERROR;
3904 END IF;
3905
3906 -- Save messages in database
3907 OKL_AM_UTIL_PVT.process_messages (
3908 p_trx_source_table => 'OKL_TRX_QUOTES_V',
3909 p_trx_id => lx_quot_rec.id,
3910 x_return_status => l_return_status);
3911
3912 --SECHAWLA 06-DEC-02 - Bug # 2699412 -- added the exception handling
3913 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3914 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3915 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3916 RAISE OKL_API.G_EXCEPTION_ERROR;
3917 END IF;
3918 END IF;
3919
3920 -- set the return status and out variables
3921 x_return_status := l_return_status;
3922 x_quot_rec := lx_quot_rec;
3923 x_assn_tbl := lx_assn_tbl;
3924 x_tqlv_tbl := lx_tqlv_tbl;
3925
3926 -- end the transaction
3927 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3928
3929
3930 IF (is_debug_statement_on) THEN
3931 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3932 'x_return_status..'||x_return_status);
3933 END IF;
3934
3935 IF (is_debug_procedure_on) THEN
3936 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,G_MODULE_NAME||'create_terminate_quote. ','End(-)');
3937 END IF;
3938
3939 EXCEPTION
3940 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3941 IF cur_k_assets%ISOPEN THEN
3942 CLOSE cur_k_assets;
3943 END IF;
3944 IF cur_k_end_date%ISOPEN THEN
3945 CLOSE cur_k_end_date;
3946 END IF;
3947 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
3948 IF l_wfroles_csr%ISOPEN THEN
3949 CLOSE l_wfroles_csr;
3950 END IF;
3951 IF (is_debug_statement_on) THEN
3952 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3953 'EXCEPTION ERROR');
3954 END IF;
3955 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3956 (
3957 l_api_name,
3958 G_PKG_NAME,
3959 'OKL_API.G_RET_STS_ERROR',
3960 x_msg_count,
3961 x_msg_data,
3962 '_PVT'
3963 );
3964 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3965 IF cur_k_assets%ISOPEN THEN
3966 CLOSE cur_k_assets;
3967 END IF;
3968 IF cur_k_end_date%ISOPEN THEN
3969 CLOSE cur_k_end_date;
3970 END IF;
3971 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
3972 IF l_wfroles_csr%ISOPEN THEN
3973 CLOSE l_wfroles_csr;
3974 END IF;
3975 IF (is_debug_statement_on) THEN
3976 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3977 'UNEXPECTED EXCEPTION ERROR');
3978 END IF;
3979 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3980 (
3981 l_api_name,
3982 G_PKG_NAME,
3983 'OKL_API.G_RET_STS_UNEXP_ERROR',
3984 x_msg_count,
3985 x_msg_data,
3986 '_PVT'
3987 );
3988 WHEN OTHERS THEN
3989 IF cur_k_assets%ISOPEN THEN
3990 CLOSE cur_k_assets;
3991 END IF;
3992 IF cur_k_end_date%ISOPEN THEN
3993 CLOSE cur_k_end_date;
3994 END IF;
3995 --SECHAWLA 28-FEB-03 Bug # 2757175 : Close the new cursor
3996 IF l_wfroles_csr%ISOPEN THEN
3997 CLOSE l_wfroles_csr;
3998 END IF;
3999 IF (is_debug_statement_on) THEN
4000 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
4001 'OTHER EXCEPTION ERROR');
4002 END IF;
4003 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4004 (
4005 l_api_name,
4006 G_PKG_NAME,
4007 'OTHERS',
4008 x_msg_count,
4009 x_msg_data,
4010 '_PVT'
4011 );
4012 END create_terminate_quote;
4013
4014 FUNCTION check_repo_quote(p_quote_id IN VARCHAR2,
4015 x_return_status OUT NOCOPY VARCHAR2)
4016 RETURN VARCHAR2 IS
4017 l_repo_yn VARCHAR2(1);
4018
4019 CURSOR check_repo_csr(p_quote_id IN NUMBER) IS
4020 SELECT NVL(repo_quote_indicator_yn,'N')
4021 FROM OKL_TRX_QUOTES_B
4022 WHERE id = p_quote_id;
4023
4024 BEGIN
4025 -- Check whether the quote is for Repossession
4026 OPEN check_repo_csr(p_quote_id);
4027 FETCH check_repo_csr INTO l_repo_yn;
4028 CLOSE check_repo_csr;
4029
4030 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4031 RETURN l_repo_yn;
4032 EXCEPTION
4033 WHEN OTHERS THEN
4034
4035 IF check_repo_csr%ISOPEN THEN
4036 CLOSE check_repo_csr;
4037 END IF;
4038 x_return_status := OKL_API.G_RET_STS_ERROR;
4039 END;
4040
4041
4042
4043 END OKL_AM_CREATE_QUOTE_PVT;
[Home] [Help]