[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_INVOICES_PVT
Source
1 PACKAGE BODY OKL_AM_INVOICES_PVT AS
2 /* $Header: OKLRAMIB.pls 120.47 2008/05/16 04:53:46 akrangan noship $ */
3
4 -- GLOBAL VARIABLES
5 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
7 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_invoices_pvt.';
9 ---------------------------------------------------------------------------
10 -- GLOBAL DATASTRUCTURES
11 ---------------------------------------------------------------------------
12
13 SUBTYPE tilv_rec_type IS okl_txl_ar_inv_lns_pub.tilv_rec_type;
14 SUBTYPE tilv_tbl_type IS okl_txl_ar_inv_lns_pub.tilv_tbl_type;
15 SUBTYPE bpd_acc_rec_type IS okl_acc_call_pub.bpd_acc_rec_type;
16 SUBTYPE bpd_acc_tbl_type IS okl_acc_call_pub.bpd_acc_tbl_type;
17 SUBTYPE rulv_rec_type IS okl_rule_pub.rulv_rec_type;
18
19 -- Start of comments
20 --
21 -- Procedure Name : Get_Quote_Line_Stream
22 -- Description : Returns stream type id for a quote line code
23 -- Business Rules :
24 -- Parameters : billing record
25 -- Version : 1.0
26 -- End of comments
27
28 FUNCTION Get_Quote_Line_Stream (
29 p_qlt_code IN VARCHAR2)
30 RETURN NUMBER IS
31
32 -- Get stream_type_id
33 CURSOR l_sty_csr (cp_qlt_code VARCHAR2) IS
34 SELECT s.sty_id
35 FROM okl_quote_line_strm s
36 WHERE s.quote_line_type_code = cp_qlt_code;
37
38 l_stream_type_id NUMBER;
39 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_quote_line_stream';
40 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
41 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
42 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
43
44 BEGIN
45 IF (is_debug_procedure_on) THEN
46 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
47 END IF;
48 IF (is_debug_statement_on) THEN
49 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qlt_code: '||p_qlt_code);
50 END IF;
51
52 OPEN l_sty_csr (p_qlt_code);
53 FETCH l_sty_csr INTO l_stream_type_id;
54 CLOSE l_sty_csr;
55 IF (is_debug_statement_on) THEN
56 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'Returning l_stream_type_id: '||l_stream_type_id);
57 END IF;
58 IF (is_debug_procedure_on) THEN
59 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
60 END IF;
61
62 RETURN l_stream_type_id;
63
64 EXCEPTION
65
66 WHEN OTHERS THEN
67 IF (is_debug_exception_on) THEN
68 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
69 || sqlcode || ' , SQLERRM : ' || sqlerrm);
70 END IF;
71 IF (l_sty_csr%ISOPEN) THEN
72 CLOSE l_sty_csr;
73 END IF;
74 RETURN NULL;
75
76 END Get_Quote_Line_Stream;
77
78
79 -- Start of comments
80 --
81 -- Procedure Name : Get_Vendor_Billing_Info
82 -- Description : Extract Vendor Billing Information
83 -- Business Rules :
84 -- Parameters : Contract Party Id or Contract Id
85 -- History : RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
86 -- : RMUNJULU 26-MAR-04 3300594 Changed Cursor l_partner_rg_csr to get
87 -- vendor billing info from vendor programs party role table
88 -- : PAGARG 14-Feb-2005 Bug 3559535, correct the message used
89 -- for no vendor program being there for the given contract
90 -- Version : 1.0
91 -- End of comments
92
93 PROCEDURE Get_Vendor_Billing_Info (
94 p_cpl_id IN NUMBER DEFAULT NULL,
95 px_taiv_rec IN OUT NOCOPY taiv_rec_type,
96 x_return_status OUT NOCOPY VARCHAR2) IS
97
98 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
99 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
100
101 l_contract_id NUMBER := px_taiv_rec.khr_id;
102 l_khr_id NUMBER;
103 l_par_id NUMBER;
104 l_rgd_id NUMBER;
105 l_party_name VARCHAR2(1000);
106
107 l_bill_to_site_use_id OKC_K_HEADERS_B.bill_to_site_use_id%TYPE; -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
108 l_party_role FND_LOOKUPS.meaning%TYPE; -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
109
110 --l_bto_rulv_rec rulv_rec_type; -- Bill To Address Rule
111 l_mth_rulv_rec rulv_rec_type; -- Payment Method Rule
112
113
114 -- This cursor called from
115 -- okl_am_parties_pvt.create_quote_parties when termination quote recipient is OKL_VENDOR(Lease Vendor)
116 -- OR
117 -- Okl_Am_Invoices_Pvt.validate_populate_quote when termination quote recipient is OKL_VENDOR(Lease Vendor)
118 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
119 -- Get bill to site of OKL_VENDOR party
120 CURSOR l_k_party_rg_csr (cp_cpl_id IN NUMBER) IS
121 SELECT cpl.id cpl_id,
122 cpl.jtot_object1_code object1_code,
123 cpl.object1_id1 object1_id1,
124 cpl.object1_id2 object1_id2,
125 rgd.id rgd_id,
126 cpl.bill_to_site_use_id bill_to_site_use_id, -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
127 cpl.role party_role -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
128 FROM okc_k_party_roles_v cpl,
129 okc_rg_party_roles rgpr,
130 okc_rule_groups_v rgd
131 WHERE cpl.id = cp_cpl_id
132 AND cpl.rle_code = 'OKL_VENDOR'
133 AND rgpr.cpl_id (+) = cpl.id
134 AND rgd.id (+) = rgpr.rgp_id
135 AND rgd.rgd_code (+) = 'LAVENB';
136
137 -- This cursor called from REPURCHASE QUOTE
138 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
139 -- Get bill to site of vendor PROGRAM
140 -- RMUNJULU 3300594 Changed way to get bill_to_site_use_id, get from CPL not PAR
141 -- PAGARG Bug 3559535 Make join of okc_k_headers_b par with okc_k_party_roles_v
142 -- as outer join so that it returns te record of contract even if vendor program
143 -- is not there for that contract
144 CURSOR l_partner_rg_csr (cp_khr_id IN NUMBER) IS
145 SELECT khr.id khr_id,
146 par.id par_id,
147 rgd.id rgd_id,
148 CPL.bill_to_site_use_id bill_to_site_use_id, -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
149 cpl.role party_role -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
150 FROM okl_k_headers khr,
151 okc_k_headers_all_b par,
152 okc_rule_groups_b rgd,
153 okc_k_party_roles_v cpl -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
154 WHERE khr.id = cp_khr_id
155 AND par.id (+) = khr.khr_id
156 AND par.scs_code (+) = 'PROGRAM'
157 AND rgd.chr_id (+) = par.id
158 AND rgd.dnz_chr_id (+) = par.id
159 AND rgd.cle_id IS NULL
160 AND rgd.rgd_code (+) = 'LAVENB'
161 AND par.id = cpl.dnz_chr_id (+) -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
162 AND cpl.rle_code (+) = 'OKL_VENDOR'; -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
163
164 CURSOR l_rcpt_mthd_csr (cp_cust_rct_mthd IN NUMBER) IS
165 SELECT c.receipt_method_id
166 FROM ra_cust_receipt_methods c
167 WHERE c.cust_receipt_method_id = cp_cust_rct_mthd;
168
169 CURSOR l_site_use_csr (
170 cp_site_use_id IN NUMBER,
171 cp_site_use_code IN VARCHAR2) IS
172 SELECT a.cust_account_id cust_account_id,
173 a.cust_acct_site_id cust_acct_site_id,
174 a.payment_term_id payment_term_id
175 FROM okx_cust_site_uses_v a,
176 okx_customer_accounts_v c
177 WHERE a.id1 = cp_site_use_id
178 AND a.site_use_code = cp_site_use_code
179 AND c.id1 = a.cust_account_id;
180
181 CURSOR l_std_terms_csr (
182 cp_cust_id IN NUMBER,
183 cp_site_use_id IN NUMBER) IS
184 SELECT c.standard_terms standard_terms
185 FROM hz_customer_profiles c
186 WHERE c.cust_account_id = cp_cust_id
187 AND c.site_use_id = cp_site_use_id
188 UNION
189 SELECT c1.standard_terms standard_terms
190 FROM hz_customer_profiles c1
191 WHERE c1.cust_account_id = cp_cust_id
192 AND c1.site_use_id IS NULL
193 AND NOT EXISTS (
194 SELECT '1'
195 FROM hz_customer_profiles c2
196 WHERE c2.cust_account_id = cp_cust_id
197 AND c2.site_use_id = cp_site_use_id);
198
199 l_site_use_rec l_site_use_csr%ROWTYPE;
200 l_k_party_rg_rec l_k_party_rg_csr%ROWTYPE;
201 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_vendor_billing_info';
202 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
203 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
204 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
205
206
207 BEGIN
208 IF (is_debug_procedure_on) THEN
209 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
210 END IF;
211 IF (is_debug_statement_on) THEN
212 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_cpl_id: '||p_cpl_id);
213 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.khr_id: ' || px_taiv_rec.khr_id);
214 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.ibt_id: ' || px_taiv_rec.ibt_id);
215 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.ixx_id: ' || px_taiv_rec.ixx_id);
216 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.irt_id: ' || px_taiv_rec.irt_id);
217 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.irm_id: ' || px_taiv_rec.irm_id);
218 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.currency_code: ' || px_taiv_rec.currency_code);
219 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.description: ' || px_taiv_rec.description);
220 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.date_entered: ' || px_taiv_rec.date_entered);
221 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.date_invoiced: ' || px_taiv_rec.date_invoiced);
222 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.try_id: ' || px_taiv_rec.try_id);
223 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.amount: ' || px_taiv_rec.amount);
224 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.OKL_SOURCE_BILLING_TRX: ' || px_taiv_rec.OKL_SOURCE_BILLING_TRX);
225 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.currency_conversion_type: ' || px_taiv_rec.currency_conversion_type);
226 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.currency_conversion_rate: ' || px_taiv_rec.currency_conversion_rate);
227 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.currency_conversion_date: ' || px_taiv_rec.currency_conversion_date);
228 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.legal_entity_id: ' || px_taiv_rec.legal_entity_id);
229 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.set_of_books_id: ' || px_taiv_rec.set_of_books_id);
230 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.org_id: ' || px_taiv_rec.org_id);
231 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.qte_id: ' || px_taiv_rec.qte_id);
232 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, px_taiv_rec.id: ' || px_taiv_rec.id);
233 END IF;
234
235 -- *******************
236 -- Validate parameters
237 -- *******************
238
239 IF ( p_cpl_id IS NULL
240 OR p_cpl_id = G_MISS_NUM)
241 AND ( l_contract_id IS NULL
242 OR l_contract_id = G_MISS_NUM) THEN
243
244 l_return_status := OKL_API.G_RET_STS_ERROR;
245
246 OKC_API.SET_MESSAGE (
247 p_app_name => G_OKC_APP_NAME,
248 p_msg_name => 'OKC_NO_PARAMS',
249 p_token1 => 'PARAM',
250 p_token1_value => 'Contract Party Id or Contract Id',
251 p_token2 => 'PROCESS',
252 p_token2_value => 'Get_Vendor_Billing_Info');
253
254 END IF;
255
256 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
257 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
258 l_overall_status := l_return_status;
259 END IF;
260 END IF;
261
262 -- ***************
263 -- Find Rule Group
264 -- ***************
265
266 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
267
268 IF p_cpl_id IS NOT NULL
269 AND p_cpl_id <> G_MISS_NUM THEN
270
271 OPEN l_k_party_rg_csr (p_cpl_id);
272 FETCH l_k_party_rg_csr INTO l_k_party_rg_rec;
273 CLOSE l_k_party_rg_csr;
274 l_rgd_id := l_k_party_rg_rec.rgd_id;
275 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
276 l_bill_to_site_use_id := l_k_party_rg_rec.bill_to_site_use_id ;
277
278 IF l_k_party_rg_rec.cpl_id IS NULL THEN
279 l_return_status := OKL_API.G_RET_STS_ERROR;
280 OKC_API.SET_MESSAGE (
281 p_app_name => G_OKC_APP_NAME,
282 p_msg_name => G_INVALID_VALUE,
283 p_token1 => G_COL_NAME_TOKEN,
284 p_token1_value => 'Contract Party Id');
285 /* -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
286 ELSIF l_rgd_id IS NULL THEN
287 l_return_status := OKL_API.G_RET_STS_ERROR;
288 l_party_name := okl_am_util_pvt.get_jtf_object_name (
289 l_k_party_rg_rec.object1_code,
290 l_k_party_rg_rec.object1_id1,
291 l_k_party_rg_rec.object1_id2);
292 OKC_API.SET_MESSAGE (
293 p_app_name => G_APP_NAME,
294 p_msg_name => 'OKL_AM_NO_BILLING_INFO',
295 p_token1 => 'PARTY',
296 p_token1_value => l_party_name);
297 */
298 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
299 ELSIF l_k_party_rg_rec.bill_to_site_use_id IS NULL THEN
300 l_return_status := OKL_API.G_RET_STS_ERROR;
301 l_party_name := okl_am_util_pvt.get_jtf_object_name (
302 l_k_party_rg_rec.object1_code,
303 l_k_party_rg_rec.object1_id1,
304 l_k_party_rg_rec.object1_id2);
305 -- Billing information is not found for party PARTY having role PARTY_ROLE.
306 OKC_API.SET_MESSAGE (
307 p_app_name => G_APP_NAME,
308 p_msg_name => 'OKL_AM_NO_BILLING_INFO_NEW',
309 p_token1 => 'PARTY',
310 p_token1_value => l_party_name,
311 p_token2 => 'PARTY_ROLE',
312 p_token2_value => l_k_party_rg_rec.party_role);
313 END IF;
314
315
316 ELSIF l_contract_id IS NOT NULL
317 AND l_contract_id <> G_MISS_NUM THEN
318
319 OPEN l_partner_rg_csr (l_contract_id);
320 FETCH l_partner_rg_csr INTO l_khr_id, l_par_id, l_rgd_id, l_bill_to_site_use_id, l_party_role; -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
321 CLOSE l_partner_rg_csr;
322
323 IF l_khr_id IS NULL THEN
324 l_return_status := OKL_API.G_RET_STS_ERROR;
325 OKC_API.SET_MESSAGE (
326 p_app_name => G_OKC_APP_NAME,
327 p_msg_name => G_INVALID_VALUE,
328 p_token1 => G_COL_NAME_TOKEN,
329 p_token1_value => 'Contract Id');
330 ELSIF l_par_id IS NULL THEN
331 l_return_status := OKL_API.G_RET_STS_ERROR;
332 okl_am_util_pvt.set_message(
333 p_app_name => G_APP_NAME
334 -- PAGARG Bug 3559535 Use proper message for no vendor program found
335 ,p_msg_name => 'OKL_AM_NO_VENDOR_PROG');
336
337 /* -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
338 ELSIF l_rgd_id IS NULL THEN
339 l_return_status := OKL_API.G_RET_STS_ERROR;
340 l_party_name := okl_am_util_pvt.get_program_partner (l_khr_id);
341 OKC_API.SET_MESSAGE (
342 p_app_name => G_APP_NAME,
343 p_msg_name => 'OKL_AM_NO_BILLING_INFO',
344 p_token1 => 'PARTY',
345 p_token1_value => l_party_name);
346 */
347
348 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
349 ELSIF l_bill_to_site_use_id IS NULL THEN
350 l_return_status := OKL_API.G_RET_STS_ERROR;
351 l_party_name := okl_am_util_pvt.get_program_partner (l_khr_id);
352 IF (is_debug_statement_on) THEN
353 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_program_partner and got l_party_name : ' || l_party_name);
354 END IF;
355 -- Billing information is not found for party PARTY having role PARTY_ROLE.
356 OKC_API.SET_MESSAGE (
357 p_app_name => G_APP_NAME,
358 p_msg_name => 'OKL_AM_NO_BILLING_INFO_NEW',
359 p_token1 => 'PARTY',
360 p_token1_value => l_party_name,
361 p_token2 => 'PARTY_ROLE',
362 p_token2_value => l_party_role);
363 END IF;
364
365 ELSE
366 l_return_status := OKL_API.G_RET_STS_ERROR;
367 END IF;
368
369 END IF;
370
371 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
372 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
373 l_overall_status := l_return_status;
374 END IF;
375 END IF;
376
377 -- ***********************************
378 -- Get Rules to set billing attributes
379 -- ***********************************
380
381 /* -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
382
383 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
384
385
386 okl_am_util_pvt.get_rule_record (
387 p_rgd_id => l_rgd_id,
388 p_rgd_code => 'LAVENB',
389 p_rdf_code => 'BTO',
390 p_chr_id => l_contract_id,
391 p_cle_id => NULL,
392 p_message_yn => TRUE,
393 x_rulv_rec => l_bto_rulv_rec,
394 x_return_status => l_return_status);
395
396
397 END IF;
398
399 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
400 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
401 l_overall_status := l_return_status;
402 END IF;
403 END IF;
404 */
405
406 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
407 IF (is_debug_statement_on) THEN
408 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_rule_record');
409 END IF;
410
411 okl_am_util_pvt.get_rule_record (
412 p_rgd_id => l_rgd_id,
413 p_rgd_code => 'LAVENB',
414 p_rdf_code => 'LAPMTH',
415 p_chr_id => l_contract_id,
416 p_cle_id => NULL,
417 p_message_yn => FALSE, -- Rule is optional - bug 2533080
418 x_rulv_rec => l_mth_rulv_rec,
419 x_return_status => l_return_status);
420 IF (is_debug_statement_on) THEN
421 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_rule_record , return status: ' || l_return_status);
422 END IF;
423
424 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
425 -- ADDED SINCE 2533080 not fixed properly
426 -- If the above rule is optional then should not check the return status
427 l_return_status := OKL_API.G_RET_STS_SUCCESS;
428
429
430 END IF;
431
432 -- Rule is optional - bug 2533080
433 -- IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
434 -- IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
435 -- l_overall_status := l_return_status;
436 -- END IF;
437 -- END IF;
438
439 -- *****************************************************
440 -- Extract Customer, Bill To and Payment Term from rules
441 -- *****************************************************
442
443 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
444
445 -- RMUNJULU 27-AUG-03 OKC RULE MIGRATION changes
446 OPEN l_site_use_csr (l_bill_to_site_use_id, 'BILL_TO');
447 FETCH l_site_use_csr INTO l_site_use_rec;
448 CLOSE l_site_use_csr;
449
450 px_taiv_rec.ibt_id := l_site_use_rec.cust_acct_site_id;
451 px_taiv_rec.ixx_id := l_site_use_rec.cust_account_id;
452 px_taiv_rec.irt_id := l_site_use_rec.payment_term_id;
453
454 IF px_taiv_rec.irt_id IS NULL
455 OR px_taiv_rec.irt_id = G_MISS_NUM THEN
456 OPEN l_std_terms_csr (
457 l_site_use_rec.cust_account_id,
458 l_bill_to_site_use_id); -- RMUNJULU 27-AUG-03 OKC RULE MIGRATION changes
459 FETCH l_std_terms_csr INTO px_taiv_rec.irt_id;
460 CLOSE l_std_terms_csr;
461 END IF;
462
463 END IF;
464
465 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
466 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
467 l_overall_status := l_return_status;
468 END IF;
469 END IF;
470
471 -- *******************************
472 -- Extract Payment Term from rules
473 -- *******************************
474
475 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS
476 AND l_mth_rulv_rec.id IS NOT NULL -- Rule is optional - bug 2533080
477 AND l_mth_rulv_rec.id <> G_MISS_NUM THEN
478
479 IF l_mth_rulv_rec.object1_id2 <> '#' THEN
480 px_taiv_rec.irm_id := l_mth_rulv_rec.object1_id2;
481 ELSE
482 -- This cursor needs to be removed when
483 -- the view changes to include id2
484 OPEN l_rcpt_mthd_csr (l_mth_rulv_rec.object1_id1);
485 FETCH l_rcpt_mthd_csr INTO px_taiv_rec.irm_id;
486 CLOSE l_rcpt_mthd_csr;
487 END IF;
488
489 END IF;
490
491 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
492 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
493 l_overall_status := l_return_status;
494 END IF;
495 END IF;
496
497 -- ****************
498 -- Validate Results
499 -- ****************
500
501 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
502
503 IF px_taiv_rec.ixx_id IS NULL
504 OR px_taiv_rec.ixx_id = G_MISS_NUM THEN
505 l_return_status := OKL_API.G_RET_STS_ERROR;
506 OKC_API.SET_MESSAGE (
507 p_app_name => G_OKC_APP_NAME,
508 p_msg_name => G_REQUIRED_VALUE,
509 p_token1 => G_COL_NAME_TOKEN,
510 p_token1_value => 'Customer Account Id');
511 END IF;
512
513 IF px_taiv_rec.ibt_id IS NULL
514 OR px_taiv_rec.ibt_id = G_MISS_NUM THEN
515 l_return_status := OKL_API.G_RET_STS_ERROR;
516 OKC_API.SET_MESSAGE (
517 p_app_name => G_OKC_APP_NAME,
518 p_msg_name => G_REQUIRED_VALUE,
519 p_token1 => G_COL_NAME_TOKEN,
520 p_token1_value => 'Bill To Address Id');
521 END IF;
522
523 IF px_taiv_rec.irt_id IS NULL
524 OR px_taiv_rec.irt_id = G_MISS_NUM THEN
525 l_return_status := OKL_API.G_RET_STS_ERROR;
526 OKC_API.SET_MESSAGE (
527 p_app_name => G_OKC_APP_NAME,
528 p_msg_name => G_REQUIRED_VALUE,
529 p_token1 => G_COL_NAME_TOKEN,
530 p_token1_value => 'Payment Term Id');
531 END IF;
532
533 -- Rule is optional - bug 2533080
534 -- IF px_taiv_rec.irm_id IS NULL
535 -- OR px_taiv_rec.irm_id = G_MISS_NUM THEN
536 -- l_return_status := OKL_API.G_RET_STS_ERROR;
537 -- OKC_API.SET_MESSAGE (
538 -- p_app_name => G_OKC_APP_NAME,
539 -- p_msg_name => G_REQUIRED_VALUE,
540 -- p_token1 => G_COL_NAME_TOKEN,
541 -- p_token1_value => 'Payment Method Id');
542 -- END IF;
543
544 END IF;
545
546 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
547 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
548 l_overall_status := l_return_status;
549 END IF;
550 END IF;
551
552
553 x_return_status := l_overall_status;
554 IF (is_debug_procedure_on) THEN
555 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
556 END IF;
557
558 EXCEPTION
559
560 WHEN OTHERS THEN
561 IF (is_debug_exception_on) THEN
562 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
563 || sqlcode || ' , SQLERRM : ' || sqlerrm);
564 END IF;
565
566 -- close open cursors
567 IF l_k_party_rg_csr%ISOPEN THEN
568 CLOSE l_k_party_rg_csr;
569 END IF;
570
571 IF l_partner_rg_csr%ISOPEN THEN
572 CLOSE l_partner_rg_csr;
573 END IF;
574
575 IF l_rcpt_mthd_csr%ISOPEN THEN
576 CLOSE l_rcpt_mthd_csr;
577 END IF;
578
579 IF l_site_use_csr%ISOPEN THEN
580 CLOSE l_site_use_csr;
581 END IF;
582
583 IF l_std_terms_csr%ISOPEN THEN
584 CLOSE l_std_terms_csr;
585 END IF;
586
587 -- store SQL error message on message stack for caller
588 OKL_API.SET_MESSAGE (
589 p_app_name => G_APP_NAME
590 ,p_msg_name => G_UNEXPECTED_ERROR
591 ,p_token1 => G_SQLCODE_TOKEN
592 ,p_token1_value => sqlcode
593 ,p_token2 => G_SQLERRM_TOKEN
594 ,p_token2_value => sqlerrm);
595
596 -- notify caller of an UNEXPECTED error
597 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
598
599 END Get_Vendor_Billing_Info;
600
601 -- Start of comments
602 --
603 -- Procedure Name : Create_billing_invoices
604 -- Description : Creates OKL Billing Transaction for AR Invoicing
605 -- Business Rules :
606 -- Parameters : Transaction Record for AR Invoice
607 -- Version : 1.0
608 -- History : ANSETHUR 03/02/2007 Created For R12B Billing Enhancement project
609 -- To Replace Create_AR_Invoice_Header and Create_AR_Invoice_Lines procedures
610 -- with the Enhanced Billing API
611 -- End of comments
612
613 PROCEDURE Create_billing_invoices (
614 p_taiv_rec IN taiv_rec_type,
615 p_pos_amount IN NUMBER DEFAULT 0,
616 p_neg_amount IN NUMBER DEFAULT 0,
617 p_quote_type IN VARCHAR2 DEFAULT NULL,
618 p_trans_type IN VARCHAR2 DEFAULT NULL,
619 p_tilv_tbl IN tilv_tbl_type,
620 x_tilv_tbl OUT NOCOPY tilv_tbl_type,
621 x_pos_taiv_rec OUT NOCOPY taiv_rec_type,
622 x_neg_taiv_rec OUT NOCOPY taiv_rec_type,
623 x_return_status OUT NOCOPY VARCHAR2) IS
624
625 l_pos_try_id NUMBER := NULL;
626 l_neg_try_id NUMBER := NULL;
627 l_sysdate DATE := SYSDATE;
628 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
629 l_taiv_rec taiv_rec_type;
630
631 l_api_version CONSTANT NUMBER := G_API_VERSION;
632 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
633 l_msg_data VARCHAR2(2000);
634
635
636 l_roll_bill_try_id NUMBER DEFAULT NULL;
637 l_roll_cm_try_id NUMBER DEFAULT NULL;
638
639 l_release_bill_try_id NUMBER DEFAULT NULL;
640 l_release_cm_try_id NUMBER DEFAULT NULL;
641
642 --from lines
643 l_tilv_rec tilv_rec_type;
644 l_bpd_acc_rec bpd_acc_rec_type;
645
646 -- Added For Enhanced Billing PVT
647 l_tldv_tbl okl_tld_pvt.tldv_tbl_type;
648 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
649
650 l_tilv_tbl okl_txl_ar_inv_lns_pub.tilv_tbl_type;
651 lx_tilv_tbl okl_txl_ar_inv_lns_pub.tilv_tbl_type;
652
653 l_pos_tilv_tbl okl_txl_ar_inv_lns_pub.tilv_tbl_type;
654 l_neg_tilv_tbl okl_txl_ar_inv_lns_pub.tilv_tbl_type;
655
656 i number :=0;
657 j number :=0;
658 k number :=0;
659 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_billing_invoices';
660 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
661 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
662 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
663
664 BEGIN
665 IF (is_debug_procedure_on) THEN
666 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
667 END IF;
668 IF (is_debug_statement_on) THEN
669 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_pos_amount: '||p_pos_amount);
670 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_neg_amount: '||p_neg_amount);
671 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_quote_type: '||p_quote_type);
672 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_trans_type: '||p_trans_type);
673 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.khr_id: ' || p_taiv_rec.khr_id);
674 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.ibt_id: ' || p_taiv_rec.ibt_id);
675 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.ixx_id: ' || p_taiv_rec.ixx_id);
676 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.irt_id: ' || p_taiv_rec.irt_id);
677 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.irm_id: ' || p_taiv_rec.irm_id);
678 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.currency_code: ' || p_taiv_rec.currency_code);
679 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.description: ' || p_taiv_rec.description);
680 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.date_entered: ' || p_taiv_rec.date_entered);
681 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.date_invoiced: ' || p_taiv_rec.date_invoiced);
682 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.try_id: ' || p_taiv_rec.try_id);
683 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.amount: ' || p_taiv_rec.amount);
684 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.OKL_SOURCE_BILLING_TRX: ' || p_taiv_rec.OKL_SOURCE_BILLING_TRX);
685 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.currency_conversion_type: ' || p_taiv_rec.currency_conversion_type);
686 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.currency_conversion_rate: ' || p_taiv_rec.currency_conversion_rate);
687 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.currency_conversion_date: ' || p_taiv_rec.currency_conversion_date);
688 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.legal_entity_id: ' || p_taiv_rec.legal_entity_id);
689 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.set_of_books_id: ' || p_taiv_rec.set_of_books_id);
690 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.org_id: ' || p_taiv_rec.org_id);
691 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.qte_id: ' || p_taiv_rec.qte_id);
692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_taiv_rec.id: ' || p_taiv_rec.id);
693 FOR i IN p_tilv_tbl.FIRST..p_tilv_tbl.LAST LOOP
694 IF (p_tilv_tbl.exists(i)) THEN
695 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_tilv_tbl(' || i || ').amount : ' || p_tilv_tbl(i).amount);
696 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_tilv_tbl(' || i || ').sty_id : ' || p_tilv_tbl(i).sty_id);
697 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_tilv_tbl(' || i || ').description : ' || p_tilv_tbl(i).description);
698 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_tilv_tbl(' || i || ').inv_receiv_line_code : ' || p_tilv_tbl(i).inv_receiv_line_code);
699 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_tilv_tbl(' || i || ').line_number : ' || p_tilv_tbl(i).line_number);
700 END IF;
701 END LOOP;
702 END IF;
703
704 -- *******************
705 -- Validate parameters
706 -- *******************
707
708 IF (is_debug_statement_on) THEN
709 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
710 END IF;
711 okl_am_util_pvt.get_transaction_id (
712 p_try_name => G_AR_INV_TRX_TYPE,
713 x_return_status => l_return_status,
714 x_try_id => l_pos_try_id);
715 IF (is_debug_statement_on) THEN
716 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
717 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_pos_try_id : ' || l_pos_try_id);
718 END IF;
719
720 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
721 OR NVL (l_pos_try_id, G_MISS_NUM) = G_MISS_NUM THEN
722 l_return_status := OKL_API.G_RET_STS_ERROR;
723 OKC_API.SET_MESSAGE (
724 P_APP_NAME => G_OKC_APP_NAME,
725 P_MSG_NAME => G_INVALID_VALUE,
726 P_TOKEN1 => G_COL_NAME_TOKEN,
727 P_TOKEN1_VALUE => 'TRANSACTION TYPE');
728 END IF;
729
730 IF (is_debug_statement_on) THEN
731 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
732 END IF;
733 okl_am_util_pvt.get_transaction_id (
734 p_try_name => G_AR_CM_TRX_TYPE,
735 x_return_status => l_return_status,
736 x_try_id => l_neg_try_id);
737 IF (is_debug_statement_on) THEN
738 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
739 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_neg_try_id : ' || l_neg_try_id);
740 END IF;
741
742 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
743 OR NVL (l_neg_try_id, G_MISS_NUM) = G_MISS_NUM THEN
744 l_return_status := OKL_API.G_RET_STS_ERROR;
745 OKC_API.SET_MESSAGE (
746 p_app_name => G_OKC_APP_NAME,
747 p_msg_name => G_INVALID_VALUE,
748 p_token1 => G_COL_NAME_TOKEN,
749 p_token1_value => 'Transaction Type');
750 END IF;
751
752 ----------------------
753 -- Obtain transaction id for Rollover transactions
754 ----------------------
755 --09-Nov-04 PAGARG Bug #4002033 moved the logic to obtain transaction type
756 --id inside appropriate condition
757 IF p_quote_type LIKE 'TER_ROLL%'
758 AND p_trans_type = 'REVERSE' THEN
759 IF (is_debug_statement_on) THEN
760 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
761 END IF;
762 okl_am_util_pvt.get_transaction_id (
763 p_try_name => 'ROLLOVER BILLING',
764 x_return_status => l_return_status,
765 x_try_id => l_roll_bill_try_id);
766 IF (is_debug_statement_on) THEN
767 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
768 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_roll_bill_try_id : ' || l_roll_bill_try_id);
769 END IF;
770
771 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
772 OR NVL (l_roll_bill_try_id, G_MISS_NUM) = G_MISS_NUM THEN
773 l_return_status := OKL_API.G_RET_STS_ERROR;
774 OKC_API.SET_MESSAGE (
775 p_app_name => G_OKC_APP_NAME,
776 p_msg_name => G_INVALID_VALUE,
777 p_token1 => G_COL_NAME_TOKEN,
778 p_token1_value => 'Transaction Type');
779 END IF;
780
781 IF (is_debug_statement_on) THEN
782 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
783 END IF;
784 okl_am_util_pvt.get_transaction_id (
785 p_try_name => 'ROLLOVER CREDIT MEMO',
786 x_return_status => l_return_status,
787 x_try_id => l_roll_cm_try_id);
788 IF (is_debug_statement_on) THEN
789 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
790 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_roll_cm_try_id : ' || l_roll_cm_try_id);
791 END IF;
792
793 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
794 OR NVL (l_roll_cm_try_id, G_MISS_NUM) = G_MISS_NUM THEN
795 l_return_status := OKL_API.G_RET_STS_ERROR;
796 OKC_API.SET_MESSAGE (
797 p_app_name => G_OKC_APP_NAME,
798 p_msg_name => G_INVALID_VALUE,
799 p_token1 => G_COL_NAME_TOKEN,
800 p_token1_value => 'Transaction Type');
801 END IF;
802 END IF;
803
804 ----------------------
805 -- Obtain transaction id for Release transactions
806 ----------------------
807 --09-Nov-04 PAGARG Bug #4002033 moved the logic to obtain transaction type
808 --id inside appropriate condition
809 IF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
810 AND p_trans_type = 'REVERSE' THEN
811 IF (is_debug_statement_on) THEN
812 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
813 END IF;
814 okl_am_util_pvt.get_transaction_id (
815 p_try_name => 'RELEASE BILLING',
816 x_return_status => l_return_status,
817 x_try_id => l_release_bill_try_id);
818 IF (is_debug_statement_on) THEN
819 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
820 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_release_bill_try_id : ' || l_release_bill_try_id);
821 END IF;
822
823 -- 02-Dec-2004 PAGARG Bug# 4043464, check correct variable for G_MISS value
824 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
825 OR NVL (l_release_bill_try_id, G_MISS_NUM) = G_MISS_NUM THEN
826 l_return_status := OKL_API.G_RET_STS_ERROR;
827 OKC_API.SET_MESSAGE (
828 p_app_name => G_OKC_APP_NAME,
829 p_msg_name => G_INVALID_VALUE,
830 p_token1 => G_COL_NAME_TOKEN,
831 p_token1_value => 'Transaction Type');
832 END IF;
833 IF (is_debug_statement_on) THEN
834 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_transaction_id');
835 END IF;
836
837 okl_am_util_pvt.get_transaction_id (
838 p_try_name => 'RELEASE CREDIT MEMO',
839 x_return_status => l_return_status,
840 x_try_id => l_release_cm_try_id);
841 IF (is_debug_statement_on) THEN
842 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_transaction_id , return status: ' || l_return_status);
843 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_release_cm_try_id : ' || l_release_cm_try_id);
844 END IF;
845
846 -- 02-Dec-2004 PAGARG Bug# 4043464, check correct variable for G_MISS value
847 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
848 OR NVL (l_release_cm_try_id, G_MISS_NUM) = G_MISS_NUM THEN
849 l_return_status := okl_api.g_ret_sts_error;
850 okc_api.set_message (
851 p_app_name => g_okc_app_name,
852 p_msg_name => g_invalid_value,
853 p_token1 => g_col_name_token,
854 p_token1_value => 'transaction type');
855 END IF;
856 END IF;
857
858
859 IF NVL (p_pos_amount, 0) IN (G_MISS_NUM, 0)
860 AND NVL (p_neg_amount, 0) IN (G_MISS_NUM, 0)THEN
861 l_return_status := OKL_API.G_RET_STS_ERROR;
862 OKC_API.SET_MESSAGE (
863 p_app_name => G_OKC_APP_NAME,
864 p_msg_name => G_REQUIRED_VALUE,
865 p_token1 => G_COL_NAME_TOKEN,
866 p_token1_value => 'Amount');
867 END IF;
868
869 IF NVL (p_taiv_rec.khr_id, G_MISS_NUM) = G_MISS_NUM THEN
870 l_return_status := OKL_API.G_RET_STS_ERROR;
871 OKC_API.SET_MESSAGE (
872 p_app_name => G_OKC_APP_NAME,
873 p_msg_name => G_REQUIRED_VALUE,
874 p_token1 => G_COL_NAME_TOKEN,
875 p_token1_value => 'Contract_Id');
876 END IF;
877
878 IF NVL (p_taiv_rec.currency_code, G_MISS_CHAR) = G_MISS_CHAR THEN
879 l_return_status := OKL_API.G_RET_STS_ERROR;
880 OKC_API.SET_MESSAGE (
881 p_app_name => G_OKC_APP_NAME,
882 p_msg_name => G_REQUIRED_VALUE,
883 p_token1 => G_COL_NAME_TOKEN,
884 p_token1_value => 'Currency_Code');
885 END IF;
886
887 IF NVL (p_taiv_rec.description, G_MISS_CHAR) = G_MISS_CHAR THEN
888 l_return_status := OKL_API.G_RET_STS_ERROR;
889 OKC_API.SET_MESSAGE (
890 p_app_name => G_OKC_APP_NAME,
891 p_msg_name => G_REQUIRED_VALUE,
892 p_token1 => G_COL_NAME_TOKEN,
893 p_token1_value => 'Description');
894 END IF;
895
896
897 l_taiv_rec := p_taiv_rec;
898 l_taiv_rec.trx_status_code := G_SUBMIT_STATUS;
899
900 IF NVL (l_taiv_rec.date_entered, G_MISS_DATE) = G_MISS_DATE THEN
901 l_taiv_rec.date_entered := l_sysdate;
902 END IF;
903
904 IF NVL (l_taiv_rec.date_invoiced, G_MISS_DATE) = G_MISS_DATE THEN
905 l_taiv_rec.date_invoiced := l_sysdate;
906 END IF;
907
908 -- from lines
909
910 IF p_tilv_tbl.COUNT > 0 THEN
911 --akrangan bug 6494341 fix start
912 --changed the looping structure from for loop to normal loop
913 --this approach gives error while indices are not sequential
914 i := p_tilv_tbl.FIRST;
915 LOOP
916 --akrangan bug 6494341 fix end
917
918 IF NVL (p_tilv_tbl(i).amount, G_MISS_NUM) = G_MISS_NUM THEN
919 l_return_status := OKL_API.G_RET_STS_ERROR;
920 OKC_API.SET_MESSAGE (
921 p_app_name => G_OKC_APP_NAME,
922 p_msg_name => G_REQUIRED_VALUE,
923 p_token1 => G_COL_NAME_TOKEN,
924 p_token1_value => 'Amount');
925 END IF;
926
927 /* Begin - Bug#5874824 - Asset Remarketing Fix
928 -- Removed the mandatory check for Invoice Header Id in invoice lines table (p_tilv_tbl(i).tai_id)
929 -- Since the invoice header and lines will be created from the common billing API call
930 -- Invoice header id will be assigned before calling Line creation call in 'okl_internal_billing_pvt'
931 -- End - Bug#5874824 - Asset Remarketing Fix */
932
933 IF NVL (p_tilv_tbl(i).sty_id, G_MISS_NUM) = G_MISS_NUM THEN
934 l_return_status := OKL_API.G_RET_STS_ERROR;
935 OKC_API.SET_MESSAGE (
936 p_app_name => G_OKC_APP_NAME,
937 p_msg_name => G_REQUIRED_VALUE,
938 p_token1 => G_COL_NAME_TOKEN,
939 p_token1_value => 'Stream_Type_Id');
940 END IF;
941
942 IF NVL (p_tilv_tbl(i).description, G_MISS_CHAR) = G_MISS_CHAR THEN
943 l_return_status := OKL_API.G_RET_STS_ERROR;
944 OKC_API.SET_MESSAGE (
945 p_app_name => G_OKC_APP_NAME,
946 p_msg_name => G_REQUIRED_VALUE,
947 p_token1 => G_COL_NAME_TOKEN,
948 p_token1_value => 'Description');
949 END IF;
950
951
952 IF p_tilv_tbl(i).amount > 0 then
953 l_pos_tilv_tbl(j):=p_tilv_tbl(i);
954 l_pos_tilv_tbl(j).inv_receiv_line_code := G_AR_INV_LINE_CODE;
955 j:=j+1;
956 elsif p_tilv_tbl(i).amount < 0 then
957 l_neg_tilv_tbl(k):=p_tilv_tbl(i);
958 l_neg_tilv_tbl(k).inv_receiv_line_code := G_AR_INV_LINE_CODE;
959 k:=k+1;
960 end if;
961 --akrangan bug 6494341 fix start
962 exit when (i = p_tilv_tbl.last);
963 i := p_tilv_tbl.next(i);
964 --akrangan bug 6494341 fix end
965 END LOOP;
966 END IF;
967
968 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
969 AND p_pos_amount > 0 THEN
970
971 -- If quote type is rollover and transaction type is rollover then set
972 -- set rollover credit memo transaction id for positive amounts
973 IF p_quote_type LIKE 'TER_ROLL%'
974 AND p_trans_type = 'REVERSE' THEN
975 l_taiv_rec.try_id := l_roll_cm_try_id;
976 -- pagarg +++ T and A +++
977 ELSIF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
978 AND p_trans_type = 'REVERSE' THEN
979 l_taiv_rec.try_id := l_release_cm_try_id;
980 ELSE
981 l_taiv_rec.try_id := l_pos_try_id;
982 END IF;
983
984 l_taiv_rec.amount := p_pos_amount;
985 IF (is_debug_statement_on) THEN
986 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_internal_billing_pvt.create_billing_trx');
987 END IF;
988
989 okl_internal_billing_pvt.create_billing_trx(p_api_version => l_api_version,
990 p_init_msg_list => OKL_API.G_FALSE,
991 x_return_status => l_return_status, -- 6140786
992 x_msg_count => l_msg_count,
993 x_msg_data => l_msg_data,
994 p_taiv_rec => l_taiv_rec,
995 p_tilv_tbl => l_pos_tilv_tbl,
996 p_tldv_tbl => l_tldv_tbl,
997 x_taiv_rec => x_pos_taiv_rec,
998 x_tilv_tbl => lx_tilv_tbl,
999 x_tldv_tbl => lx_tldv_tbl);
1000 IF (is_debug_statement_on) THEN
1001 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_internal_billing_pvt.create_billing_trx , return status: ' || l_return_status);
1002 END IF;
1003
1004 END IF;
1005
1006 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1007 AND p_neg_amount < 0 THEN
1008
1009
1010 -- If quote type is rollover and transaction type is rollover then set
1011 -- set rollover billing transaction id for negative amounts
1012 IF p_quote_type LIKE 'TER_ROLL%'
1013 AND p_trans_type = 'REVERSE' THEN
1014 l_taiv_rec.try_id := l_roll_bill_try_id;
1015 -- pagarg +++ T and A +++
1016 ELSIF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
1017 AND p_trans_type = 'REVERSE' THEN
1018 l_taiv_rec.try_id := l_release_bill_try_id;
1019 ELSE
1020 l_taiv_rec.try_id := l_neg_try_id;
1021 END IF;
1022
1023 l_taiv_rec.amount := p_neg_amount;
1024 IF (is_debug_statement_on) THEN
1025 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_internal_billing_pvt.create_billing_trx');
1026 END IF;
1027
1028 okl_internal_billing_pvt.create_billing_trx(p_api_version => l_api_version,
1029 p_init_msg_list => OKL_API.G_FALSE,
1030 x_return_status => l_return_status, -- 6140786
1031 x_msg_count => l_msg_count,
1032 x_msg_data => l_msg_data,
1033 p_taiv_rec => l_taiv_rec,
1034 p_tilv_tbl => l_neg_tilv_tbl,
1035 p_tldv_tbl => l_tldv_tbl,
1036 x_taiv_rec => x_neg_taiv_rec,
1037 x_tilv_tbl => lx_tilv_tbl,
1038 x_tldv_tbl => lx_tldv_tbl);
1039 IF (is_debug_statement_on) THEN
1040 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_internal_billing_pvt.create_billing_trx , return status: ' || l_return_status);
1041 END IF;
1042 END IF;
1043 x_return_status := l_return_status;
1044 IF (is_debug_procedure_on) THEN
1045 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1046 END IF;
1047
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 IF (is_debug_exception_on) THEN
1051 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1052 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1053 END IF;
1054 -- store SQL error message on message stack for caller
1055 OKL_API.SET_MESSAGE (
1056 p_app_name => G_APP_NAME
1057 ,p_msg_name => G_UNEXPECTED_ERROR
1058 ,p_token1 => G_SQLCODE_TOKEN
1059 ,p_token1_value => sqlcode
1060 ,p_token2 => G_SQLERRM_TOKEN
1061 ,p_token2_value => sqlerrm);
1062
1063 -- notify caller of an UNEXPECTED error
1064 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1065
1066 END create_billing_invoices ;
1067
1068 -- Start of comments
1069 --
1070 -- Procedure Name : Create_AR_Invoice_Header
1071 -- Description : Create OKL Transaction for AR Invoicing
1072 -- Business Rules :
1073 -- Parameters : Transaction Record for AR Invoice
1074 -- Version : 1.0
1075 -- History : 18-Aug-04 PAGARG Set different transaction types for
1076 -- : Rollover transactions
1077 -- : 21-Oct-04 PAGARG Bug# 3925453 Set different transaction types for
1078 -- : Release transactions
1079 -- End of comments
1080 /* --ansethur 09-MAR-2007 Commented For Billing Architecture Starts
1081 PROCEDURE Create_AR_Invoice_Header (
1082 p_taiv_rec IN taiv_rec_type,
1083 p_pos_amount IN NUMBER,
1084 p_neg_amount IN NUMBER,
1085 p_quote_type IN VARCHAR2 DEFAULT NULL,
1086 p_trans_type IN VARCHAR2 DEFAULT NULL,
1087 x_pos_taiv_rec OUT NOCOPY taiv_rec_type,
1088 x_neg_taiv_rec OUT NOCOPY taiv_rec_type,
1089 x_return_status OUT NOCOPY VARCHAR2) IS
1090
1091 l_pos_try_id NUMBER := NULL;
1092 l_neg_try_id NUMBER := NULL;
1093 l_sysdate DATE := SYSDATE;
1094 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1095 l_taiv_rec taiv_rec_type;
1096
1097 l_api_version CONSTANT NUMBER := G_API_VERSION;
1098 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
1099 l_msg_data VARCHAR2(2000);
1100
1101 -- pagarg +++ Rollover +++
1102 -- Variables to store transaction id for rollover biling and rollover credit memo
1103 l_roll_bill_try_id NUMBER DEFAULT NULL;
1104 l_roll_cm_try_id NUMBER DEFAULT NULL;
1105 -- Bug# 3925453: pagarg +++ T and A +++
1106 -- Variables to store transaction id for release biling and release credit memo
1107 l_release_bill_try_id NUMBER DEFAULT NULL;
1108 l_release_cm_try_id NUMBER DEFAULT NULL;
1109 BEGIN
1110
1111 -- *******************
1112 -- Validate parameters
1113 -- *******************
1114
1115 okl_am_util_pvt.get_transaction_id (
1116 p_try_name => G_AR_INV_TRX_TYPE,
1117 x_return_status => l_return_status,
1118 x_try_id => l_pos_try_id);
1119
1120 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1121 OR NVL (l_pos_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1122 l_return_status := OKL_API.G_RET_STS_ERROR;
1123 OKC_API.SET_MESSAGE (
1124 p_app_name => G_OKC_APP_NAME,
1125 p_msg_name => G_INVALID_VALUE,
1126 p_token1 => G_COL_NAME_TOKEN,
1127 p_token1_value => 'Transaction Type');
1128 END IF;
1129
1130 okl_am_util_pvt.get_transaction_id (
1131 p_try_name => G_AR_CM_TRX_TYPE,
1132 x_return_status => l_return_status,
1133 x_try_id => l_neg_try_id);
1134
1135 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1136 OR NVL (l_neg_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1137 l_return_status := OKL_API.G_RET_STS_ERROR;
1138 OKC_API.SET_MESSAGE (
1139 p_app_name => G_OKC_APP_NAME,
1140 p_msg_name => G_INVALID_VALUE,
1141 p_token1 => G_COL_NAME_TOKEN,
1142 p_token1_value => 'Transaction Type');
1143 END IF;
1144
1145 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
1146 ----------------------
1147 -- Obtain transaction id for Rollover transactions
1148 ----------------------
1149 --09-Nov-04 PAGARG Bug #4002033 moved the logic to obtain transaction type
1150 --id inside appropriate condition
1151 IF p_quote_type LIKE 'TER_ROLL%'
1152 AND p_trans_type = 'REVERSE' THEN
1153 okl_am_util_pvt.get_transaction_id (
1154 p_try_name => 'ROLLOVER BILLING',
1155 x_return_status => l_return_status,
1156 x_try_id => l_roll_bill_try_id);
1157
1158 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1159 OR NVL (l_roll_bill_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1160 l_return_status := OKL_API.G_RET_STS_ERROR;
1161 OKC_API.SET_MESSAGE (
1162 p_app_name => G_OKC_APP_NAME,
1163 p_msg_name => G_INVALID_VALUE,
1164 p_token1 => G_COL_NAME_TOKEN,
1165 p_token1_value => 'Transaction Type');
1166 END IF;
1167
1168 okl_am_util_pvt.get_transaction_id (
1169 p_try_name => 'ROLLOVER CREDIT MEMO',
1170 x_return_status => l_return_status,
1171 x_try_id => l_roll_cm_try_id);
1172
1173 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1174 OR NVL (l_roll_cm_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1175 l_return_status := OKL_API.G_RET_STS_ERROR;
1176 OKC_API.SET_MESSAGE (
1177 p_app_name => G_OKC_APP_NAME,
1178 p_msg_name => G_INVALID_VALUE,
1179 p_token1 => G_COL_NAME_TOKEN,
1180 p_token1_value => 'Transaction Type');
1181 END IF;
1182 END IF;
1183 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
1184
1185 --+++++++++++ Bug# 3925453: pagarg +++ T and A +++++++ Start ++++++++++
1186 ----------------------
1187 -- Obtain transaction id for Release transactions
1188 ----------------------
1189 --09-Nov-04 PAGARG Bug #4002033 moved the logic to obtain transaction type
1190 --id inside appropriate condition
1191 IF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
1192 AND p_trans_type = 'REVERSE' THEN
1193 okl_am_util_pvt.get_transaction_id (
1194 p_try_name => 'RELEASE BILLING',
1195 x_return_status => l_return_status,
1196 x_try_id => l_release_bill_try_id);
1197
1198 -- 02-Dec-2004 PAGARG Bug# 4043464, check correct variable for G_MISS value
1199 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1200 OR NVL (l_release_bill_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1201 l_return_status := OKL_API.G_RET_STS_ERROR;
1202 OKC_API.SET_MESSAGE (
1203 p_app_name => G_OKC_APP_NAME,
1204 p_msg_name => G_INVALID_VALUE,
1205 p_token1 => G_COL_NAME_TOKEN,
1206 p_token1_value => 'Transaction Type');
1207 END IF;
1208
1209 okl_am_util_pvt.get_transaction_id (
1210 p_try_name => 'RELEASE CREDIT MEMO',
1211 x_return_status => l_return_status,
1212 x_try_id => l_release_cm_try_id);
1213
1214 -- 02-Dec-2004 PAGARG Bug# 4043464, check correct variable for G_MISS value
1215 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
1216 OR NVL (l_release_cm_try_id, G_MISS_NUM) = G_MISS_NUM THEN
1217 l_return_status := OKL_API.G_RET_STS_ERROR;
1218 OKC_API.SET_MESSAGE (
1219 p_app_name => G_OKC_APP_NAME,
1220 p_msg_name => G_INVALID_VALUE,
1221 p_token1 => G_COL_NAME_TOKEN,
1222 p_token1_value => 'Transaction Type');
1223 END IF;
1224 END IF;
1225 --+++++++++++ pagarg +++ T and A +++++++ End ++++++++++
1226
1227 IF NVL (p_pos_amount, 0) IN (G_MISS_NUM, 0)
1228 AND NVL (p_neg_amount, 0) IN (G_MISS_NUM, 0)THEN
1229 l_return_status := OKL_API.G_RET_STS_ERROR;
1230 OKC_API.SET_MESSAGE (
1231 p_app_name => G_OKC_APP_NAME,
1232 p_msg_name => G_REQUIRED_VALUE,
1233 p_token1 => G_COL_NAME_TOKEN,
1234 p_token1_value => 'Amount');
1235 END IF;
1236
1237 IF NVL (p_taiv_rec.khr_id, G_MISS_NUM) = G_MISS_NUM THEN
1238 l_return_status := OKL_API.G_RET_STS_ERROR;
1239 OKC_API.SET_MESSAGE (
1240 p_app_name => G_OKC_APP_NAME,
1241 p_msg_name => G_REQUIRED_VALUE,
1242 p_token1 => G_COL_NAME_TOKEN,
1243 p_token1_value => 'Contract_Id');
1244 END IF;
1245
1246 IF NVL (p_taiv_rec.currency_code, G_MISS_CHAR) = G_MISS_CHAR THEN
1247 l_return_status := OKL_API.G_RET_STS_ERROR;
1248 OKC_API.SET_MESSAGE (
1249 p_app_name => G_OKC_APP_NAME,
1250 p_msg_name => G_REQUIRED_VALUE,
1251 p_token1 => G_COL_NAME_TOKEN,
1252 p_token1_value => 'Currency_Code');
1253 END IF;
1254
1255 IF NVL (p_taiv_rec.description, G_MISS_CHAR) = G_MISS_CHAR THEN
1256 l_return_status := OKL_API.G_RET_STS_ERROR;
1257 OKC_API.SET_MESSAGE (
1258 p_app_name => G_OKC_APP_NAME,
1259 p_msg_name => G_REQUIRED_VALUE,
1260 p_token1 => G_COL_NAME_TOKEN,
1261 p_token1_value => 'Description');
1262 END IF;
1263
1264 -- *********************
1265 -- Create Invoice Header
1266 -- *********************
1267
1268 l_taiv_rec := p_taiv_rec;
1269 l_taiv_rec.trx_status_code := G_SUBMIT_STATUS;
1270
1271 IF NVL (l_taiv_rec.date_entered, G_MISS_DATE) = G_MISS_DATE THEN
1272 l_taiv_rec.date_entered := l_sysdate;
1273 END IF;
1274
1275 IF NVL (l_taiv_rec.date_invoiced, G_MISS_DATE) = G_MISS_DATE THEN
1276 l_taiv_rec.date_invoiced := l_sysdate;
1277 END IF;
1278
1279 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1280 AND p_pos_amount > 0 THEN
1281
1282 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
1283
1284 -- If quote type is rollover and transaction type is rollover then set
1285 -- set rollover credit memo transaction id for positive amounts
1286 IF p_quote_type LIKE 'TER_ROLL%'
1287 AND p_trans_type = 'REVERSE' THEN
1288 l_taiv_rec.try_id := l_roll_cm_try_id;
1289 -- pagarg +++ T and A +++
1290 ELSIF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
1291 AND p_trans_type = 'REVERSE' THEN
1292 l_taiv_rec.try_id := l_release_cm_try_id;
1293 ELSE
1294 l_taiv_rec.try_id := l_pos_try_id;
1295 END IF;
1296
1297 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
1298
1299 l_taiv_rec.amount := p_pos_amount;
1300
1301 okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
1302 p_api_version => l_api_version,
1303 p_init_msg_list => OKL_API.G_FALSE,
1304 x_return_status => l_return_status,
1305 x_msg_count => l_msg_count,
1306 x_msg_data => l_msg_data,
1307 p_taiv_rec => l_taiv_rec,
1308 x_taiv_rec => x_pos_taiv_rec);
1309
1310 END IF;
1311
1312 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1313 AND p_neg_amount < 0 THEN
1314
1315 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
1316
1317 -- If quote type is rollover and transaction type is rollover then set
1318 -- set rollover billing transaction id for negative amounts
1319 IF p_quote_type LIKE 'TER_ROLL%'
1320 AND p_trans_type = 'REVERSE' THEN
1321 l_taiv_rec.try_id := l_roll_bill_try_id;
1322 -- pagarg +++ T and A +++
1323 ELSIF p_quote_type = 'TER_RELEASE_WO_PURCHASE'
1324 AND p_trans_type = 'REVERSE' THEN
1325 l_taiv_rec.try_id := l_release_bill_try_id;
1326 ELSE
1327 l_taiv_rec.try_id := l_neg_try_id;
1328 END IF;
1329
1330 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
1331
1332 l_taiv_rec.amount := p_neg_amount;
1333
1334 okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
1335 p_api_version => l_api_version,
1336 p_init_msg_list => OKL_API.G_FALSE,
1337 x_return_status => l_return_status,
1338 x_msg_count => l_msg_count,
1339 x_msg_data => l_msg_data,
1340 p_taiv_rec => l_taiv_rec,
1341 x_taiv_rec => x_neg_taiv_rec);
1342
1343 END IF;
1344
1345 x_return_status := l_return_status;
1346
1347 EXCEPTION
1348
1349 WHEN OTHERS THEN
1350 -- store SQL error message on message stack for caller
1351 OKL_API.SET_MESSAGE (
1352 p_app_name => G_APP_NAME
1353 ,p_msg_name => G_UNEXPECTED_ERROR
1354 ,p_token1 => G_SQLCODE_TOKEN
1355 ,p_token1_value => sqlcode
1356 ,p_token2 => G_SQLERRM_TOKEN
1357 ,p_token2_value => sqlerrm);
1358
1359 -- notify caller of an UNEXPECTED error
1360 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1361
1362 END Create_AR_Invoice_Header;
1363
1364
1365 -- Start of comments
1366 --
1367 -- Procedure Name : Create_AR_Invoice_Lines
1368 -- Description : Create OKL Transaction for AR Invoice Lines
1369 -- Business Rules :
1370 -- Parameters : Transaction Record for AR Invoice Lines
1371 -- Version : 1.0
1372 -- End of comments
1373
1374 PROCEDURE Create_AR_Invoice_Lines (
1375 p_tilv_rec IN tilv_rec_type,
1376 x_tilv_rec OUT NOCOPY tilv_rec_type,
1377 x_return_status OUT NOCOPY VARCHAR2) IS
1378
1379 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1380 l_tilv_rec tilv_rec_type;
1381 l_bpd_acc_rec bpd_acc_rec_type;
1382
1383 l_api_version CONSTANT NUMBER := G_API_VERSION;
1384 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
1385 l_msg_data VARCHAR2(2000);
1386
1387 BEGIN
1388
1389 -- *******************
1390 -- Validate parameters
1391 -- *******************
1392
1393 IF NVL (p_tilv_rec.amount, G_MISS_NUM) = G_MISS_NUM THEN
1394 l_return_status := OKL_API.G_RET_STS_ERROR;
1395 OKC_API.SET_MESSAGE (
1396 p_app_name => G_OKC_APP_NAME,
1397 p_msg_name => G_REQUIRED_VALUE,
1398 p_token1 => G_COL_NAME_TOKEN,
1399 p_token1_value => 'Amount');
1400 END IF;
1401
1402 IF NVL (p_tilv_rec.tai_id, G_MISS_NUM) = G_MISS_NUM THEN
1403 l_return_status := OKL_API.G_RET_STS_ERROR;
1404 OKC_API.SET_MESSAGE (
1405 p_app_name => G_OKC_APP_NAME,
1406 p_msg_name => G_REQUIRED_VALUE,
1407 p_token1 => G_COL_NAME_TOKEN,
1408 p_token1_value => 'Invoice_Header_Id');
1409 END IF;
1410
1411 IF NVL (p_tilv_rec.sty_id, G_MISS_NUM) = G_MISS_NUM THEN
1412 l_return_status := OKL_API.G_RET_STS_ERROR;
1413 OKC_API.SET_MESSAGE (
1414 p_app_name => G_OKC_APP_NAME,
1415 p_msg_name => G_REQUIRED_VALUE,
1416 p_token1 => G_COL_NAME_TOKEN,
1417 p_token1_value => 'Stream_Type_Id');
1418 END IF;
1419
1420 IF NVL (p_tilv_rec.description, G_MISS_CHAR) = G_MISS_CHAR THEN
1421 l_return_status := OKL_API.G_RET_STS_ERROR;
1422 OKC_API.SET_MESSAGE (
1423 p_app_name => G_OKC_APP_NAME,
1424 p_msg_name => G_REQUIRED_VALUE,
1425 p_token1 => G_COL_NAME_TOKEN,
1426 p_token1_value => 'Description');
1427 END IF;
1428
1429 -- *******************
1430 -- Create Invoice Line
1431 -- *******************
1432
1433 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1434
1435 l_tilv_rec := p_tilv_rec;
1436 l_tilv_rec.inv_receiv_line_code := G_AR_INV_LINE_CODE;
1437
1438 -- Create Invoice Line
1439 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
1440 p_api_version => l_api_version,
1441 p_init_msg_list => OKL_API.G_FALSE,
1442 x_return_status => l_return_status,
1443 x_msg_count => l_msg_count,
1444 x_msg_data => l_msg_data,
1445 p_tilv_rec => l_tilv_rec,
1446 x_tilv_rec => x_tilv_rec);
1447
1448 END IF;
1449
1450 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1451
1452 l_bpd_acc_rec.id := x_tilv_rec.id;
1453 l_bpd_acc_rec.source_table := G_AR_LINES_SOURCE;
1454
1455 -- Create Accounting Distribution
1456 okl_acc_call_pub.create_acc_trans (
1457 p_api_version => l_api_version,
1458 p_init_msg_list => OKL_API.G_FALSE,
1459 x_return_status => l_return_status,
1460 x_msg_count => l_msg_count,
1461 x_msg_data => l_msg_data,
1462 p_bpd_acc_rec => l_bpd_acc_rec);
1463
1464 END IF;
1465
1466 x_return_status := l_return_status;
1467
1468 EXCEPTION
1469
1470 WHEN OTHERS THEN
1471 -- store SQL error message on message stack for caller
1472 OKL_API.SET_MESSAGE (
1473 p_app_name => G_APP_NAME
1474 ,p_msg_name => G_UNEXPECTED_ERROR
1475 ,p_token1 => G_SQLCODE_TOKEN
1476 ,p_token1_value => sqlcode
1477 ,p_token2 => G_SQLERRM_TOKEN
1478 ,p_token2_value => sqlerrm);
1479
1480 -- notify caller of an UNEXPECTED error
1481 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1482
1483 END Create_AR_Invoice_Lines;
1484 */ --ansethur 09-MAR-2007 Commented For Billing Architecture Ends
1485
1486 -- Start of comments
1487 --
1488 -- Procedure Name : Validate_Populate_Repair
1489 -- Description : Ensure that invoice is approved, billed first
1490 -- time, has Bill To Flag and unique Contract Line
1491 -- Populates Header Fields: Khr Id, Description, Amount,
1492 -- Currency; populates common line fields: Kle_Id, Sty_Id
1493 -- Business Rules :
1494 -- Parameters : asset return fields
1495 -- Version : 1.0
1496 -- History : RMUNJULU 30-DEC-02 2726739 Changed cursor and taiv_rec
1497 -- to set currency columns
1498 -- : PAGARG 4044659 25-Jan-2005 Obtain inventory org id from
1499 -- contract and assign it to tilv_rec if LEASE_INV_ORG_YN
1500 -- is Y in OKL_SYSTEM_PARAMS_ALL
1501 -- : PAGARG 14-Feb-2005 Bug 3559535, pass l_vendor_status in call
1502 -- to get_vendor_billing_info and based on the value of l_vendor_status
1503 -- set the value for l_return_status
1504 -- End of comments
1505
1506 PROCEDURE Validate_Populate_Repair (
1507 p_ariv_tbl IN ariv_tbl_type,
1508 x_pos_amount OUT NOCOPY NUMBER,
1509 x_neg_amount OUT NOCOPY NUMBER,
1510 x_taiv_rec OUT NOCOPY taiv_rec_type,
1511 x_tilv_rec OUT NOCOPY tilv_rec_type,
1512 x_return_status OUT NOCOPY VARCHAR2) IS
1513
1514 -- Returns Contract Line IDs For Asset Condition Line
1515 -- RMUNJULU 30-DEC-02 2726739 -- Added columns for multi-currency get values
1516 -- from asset_cndtn_lns
1517 --PAGARG Bug 4044659 Query Contract inventory org id
1518 CURSOR l_cle_csr (cp_acn_id IN NUMBER) IS
1519 SELECT kle.chr_id chr_id,
1520 kle.id cle_id,
1521 kle.name asset_number,
1522 acn.currency_code currency_code,
1523 acn.currency_conversion_type currency_conversion_type,
1524 acn.currency_conversion_rate currency_conversion_rate,
1525 acn.currency_conversion_date currency_conversion_date
1526 ,chr.inv_organization_id inv_organization_id
1527 FROM okl_asset_cndtn_lns_b acn,
1528 okl_asset_cndtns acd,
1529 okc_k_lines_v kle
1530 ,okc_k_headers_b chr
1531 WHERE acn.id = cp_acn_id
1532 AND acd.id = acn.acd_id
1533 AND kle.id = acd.kle_id
1534 AND kle.dnz_chr_id = chr.id;
1535
1536 -- Get the part name for the asset condition line
1537 CURSOR l_part_csr (cp_acn_id IN NUMBER) IS
1538 SELECT acn.part_name
1539 FROM okl_asset_cndtn_lns_v acn
1540 WHERE acn.id = cp_acn_id;
1541
1542 l_cle_rec l_cle_csr%ROWTYPE;
1543 l_taiv_rec taiv_rec_type;
1544 l_tilv_rec tilv_rec_type;
1545
1546 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1547 l_bill_to_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1548 l_cle_mismatch VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1549 l_acn_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1550 l_stream_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1551 l_amount_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1552 -- PAGARG Bug 3559535 variable to store status of vendor billing info call
1553 l_vendor_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1554
1555 l_approval_counter NUMBER := 0;
1556 l_invoiced_counter NUMBER := 0;
1557 l_sty_id NUMBER := NULL;
1558
1559 l_pos_amount NUMBER := 0;
1560 l_neg_amount NUMBER := 0;
1561
1562 l_cnt NUMBER;
1563 l_bill_to_flag NUMBER;
1564 l_part_name VARCHAR2(200);
1565 --PAGARG Bug 4044659 Cursor to obtain operational options values
1566 CURSOR l_sys_prms_csr IS
1567 SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
1568 FROM OKL_SYSTEM_PARAMS;
1569 l_sys_prms_rec l_sys_prms_csr%ROWTYPE;
1570
1571 -- RRAVIKIR Legal Entity Changes
1572 CURSOR l_assetreturns_csr(cp_kle_id IN NUMBER) IS
1573 SELECT legal_entity_id
1574 FROM okl_asset_returns_all_b
1575 WHERE kle_id = cp_kle_id;
1576
1577 l_legal_entity_id NUMBER;
1578 -- Legal Entity Changes End
1579 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'validate_populate_repair';
1580 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1581 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1582 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1583
1584 BEGIN
1585 IF (is_debug_procedure_on) THEN
1586 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1587 END IF;
1588 IF (is_debug_statement_on) THEN
1589 FOR i IN p_ariv_tbl.FIRST..p_ariv_tbl.LAST LOOP
1590 IF (p_ariv_tbl.exists(i)) THEN
1591 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_bill_to : ' || p_ariv_tbl(i).p_bill_to);
1592 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_approved_yn : ' || p_ariv_tbl(i).p_approved_yn);
1593 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_acd_id_cost : ' || p_ariv_tbl(i).p_acd_id_cost);
1594 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_acn_id : ' || p_ariv_tbl(i).p_acn_id);
1595 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_actual_repair_cost : ' || p_ariv_tbl(i).p_actual_repair_cost);
1596 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_condition_type : ' || p_ariv_tbl(i).p_condition_type);
1597 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_damage_type : ' || p_ariv_tbl(i).p_damage_type);
1598 END IF;
1599 END LOOP;
1600 END IF;
1601
1602 -- *****************
1603 -- Check all records
1604 -- *****************
1605
1606 -- Initialize procedure variables
1607 l_cnt := p_ariv_tbl.FIRST;
1608 l_bill_to_flag := p_ariv_tbl(l_cnt).p_bill_to;
1609 --PAGARG Bug 4044659 Open and fetch values from l_sys_prms_csr
1610 OPEN l_sys_prms_csr;
1611 FETCH l_sys_prms_csr INTO l_sys_prms_rec;
1612 -- IF no row fetched from cursor then set the value as N for LEASE_INV_ORG_YN
1613 IF l_sys_prms_csr%NOTFOUND
1614 THEN
1615 l_sys_prms_rec.LEASE_INV_ORG_YN := 'N';
1616 END IF;
1617
1618 CLOSE l_sys_prms_csr;
1619
1620 LOOP
1621
1622 -- Check that all records have been approved
1623 IF NVL (p_ariv_tbl(l_cnt).p_approved_yn, G_MISS_CHAR) <> 'Y' THEN
1624 l_approval_counter := l_approval_counter + 1;
1625 END IF;
1626
1627 -- Check that no records have been previously invoiced
1628 IF NVL (p_ariv_tbl(l_cnt).p_acd_id_cost, G_MISS_NUM) <> G_MISS_NUM THEN
1629 l_invoiced_counter := l_invoiced_counter + 1;
1630 -- Get the part no for the asset condition line
1631 OPEN l_part_csr(p_ariv_tbl(l_cnt).p_acn_id );
1632 FETCH l_part_csr INTO l_part_name;
1633 CLOSE l_part_csr;
1634
1635 l_return_status := OKL_API.G_RET_STS_ERROR;
1636
1637 IF l_part_name IS NULL THEN
1638 --Rkuttiya added for bug:3528618
1639 --Message Text: Unable to process request for
1640 -- a Invoice creation. Invoice(s) already exists for part PART_NUMBER.
1641 OKL_API.SET_MESSAGE (
1642 p_app_name => G_APP_NAME,
1643 p_msg_name => 'OKL_AM_INV_EXIST');
1644
1645 ELSE
1646
1647 -- Message Text: Unable to process request for
1648 -- a Invoice creation. Invoice(s) already exists for part PART_NUMBER.
1649 OKL_API.SET_MESSAGE (
1650 p_app_name => G_APP_NAME,
1651 p_msg_name => 'OKL_AM_INVOICES_EXIST',
1652 p_token1 => 'PART_NUMBER',
1653 p_token1_value => l_part_name);
1654 END IF;
1655
1656 END IF;
1657
1658 -- Check that amount is passed and calculate invoice total
1659 IF p_ariv_tbl(l_cnt).p_actual_repair_cost IS NULL
1660 OR p_ariv_tbl(l_cnt).p_actual_repair_cost = G_MISS_NUM
1661 OR p_ariv_tbl(l_cnt).p_actual_repair_cost = 0 THEN
1662 l_amount_status := OKL_API.G_RET_STS_ERROR;
1663 ELSIF p_ariv_tbl(l_cnt).p_actual_repair_cost > 0 THEN
1664 l_pos_amount := l_pos_amount +
1665 p_ariv_tbl(l_cnt).p_actual_repair_cost;
1666 ELSIF p_ariv_tbl(l_cnt).p_actual_repair_cost < 0 THEN
1667 l_neg_amount := l_neg_amount +
1668 p_ariv_tbl(l_cnt).p_actual_repair_cost;
1669 END IF;
1670
1671 -- Check that Bill To has been indicated
1672 IF NVL (p_ariv_tbl(l_cnt).p_bill_to, G_MISS_NUM) NOT IN (1,2)
1673 OR p_ariv_tbl(l_cnt).p_bill_to <> l_bill_to_flag THEN
1674 l_bill_to_status := OKL_API.G_RET_STS_ERROR;
1675 ELSE
1676
1677 -- Get Contract IDs
1678 OPEN l_cle_csr (p_ariv_tbl(l_cnt).p_acn_id);
1679 FETCH l_cle_csr INTO l_cle_rec;
1680
1681 IF l_cle_csr%NOTFOUND THEN
1682 l_acn_status := OKL_API.G_RET_STS_ERROR;
1683
1684 ELSE
1685 IF NVL (l_tilv_rec.kle_id, G_MISS_NUM) = G_MISS_NUM THEN
1686 -- Save Contract information
1687 l_tilv_rec.kle_id := l_cle_rec.cle_id;
1688
1689 -- RRAVIKIR Legal Entity Changes
1690 OPEN l_assetreturns_csr(cp_kle_id => l_cle_rec.cle_id);
1691 FETCH l_assetreturns_csr INTO l_legal_entity_id;
1692 CLOSE l_assetreturns_csr;
1693
1694 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
1695 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1696 p_msg_name => g_required_value,
1697 p_token1 => g_col_name_token,
1698 p_token1_value => 'legal_entity_id');
1699 RAISE OKC_API.G_EXCEPTION_ERROR;
1700 END IF;
1701
1702 -- Legal Entity Changes End
1703
1704 --PAGARG Bug 4044659 If LEASE_INV_ORG_YN is Y then set the value of
1705 --INVENTORY_ORG_ID in invoice line with contract inv_organization_id
1706 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y'
1707 THEN
1708 l_tilv_rec.inventory_org_id := l_cle_rec.inv_organization_id;
1709 END IF;
1710 l_taiv_rec.khr_id := l_cle_rec.chr_id;
1711 l_taiv_rec.description := l_cle_rec.asset_number;
1712 l_taiv_rec.currency_code := l_cle_rec.currency_code;
1713 -- ansethur 05-jun-07 R12B Billing Architecture
1714 l_taiv_rec.OKL_SOURCE_BILLING_TRX :='ASSET_REPAIR';
1715
1716 -- RMUNJULU 30-DEC-02 2726739 -- Added for multi-currency
1717 l_taiv_rec.currency_conversion_type := l_cle_rec.currency_conversion_type;
1718 l_taiv_rec.currency_conversion_rate := l_cle_rec.currency_conversion_rate;
1719 l_taiv_rec.currency_conversion_date := l_cle_rec.currency_conversion_date;
1720
1721 -- RRAVIKIR Legal Entity Changes
1722 l_taiv_rec.legal_entity_id := l_legal_entity_id;
1723 -- Legal Entity Changes End
1724
1725 ELSE
1726 IF l_tilv_rec.kle_id <> l_cle_rec.cle_id THEN
1727 l_cle_mismatch := OKL_API.G_RET_STS_ERROR;
1728 END IF;
1729 END IF;
1730 END IF;
1731
1732 CLOSE l_cle_csr;
1733
1734 END IF;
1735
1736 EXIT WHEN (l_cnt = p_ariv_tbl.LAST);
1737 l_cnt := p_ariv_tbl.NEXT(l_cnt);
1738
1739 END LOOP;
1740
1741 -- ***************
1742 -- Get stream type
1743 -- ***************
1744 /* bug 4631541
1745 okl_am_util_pvt.get_stream_type_id (
1746 p_sty_code => G_REPAIR_STREAM,
1747 x_return_status => l_stream_status,
1748 x_sty_id => l_sty_id);
1749 */
1750 IF (is_debug_statement_on) THEN
1751 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_STREAMS_UTIL.get_primary_stream_type');
1752 END IF;
1753 OKL_STREAMS_UTIL.get_primary_stream_type(l_cle_rec.chr_id,
1754 G_REPAIR_STREAM,
1755 l_stream_status,
1756 l_sty_id);
1757 IF (is_debug_statement_on) THEN
1758 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_STREAMS_UTIL.get_primary_stream_type , l_stream_status: ' || l_stream_status);
1759 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_sty_id: ' || l_sty_id);
1760 END IF;
1761
1762 IF l_stream_status <> OKL_API.G_RET_STS_SUCCESS
1763 OR NVL (l_sty_id, G_MISS_NUM) = G_MISS_NUM THEN
1764 l_return_status := OKL_API.G_RET_STS_ERROR;
1765 OKC_API.SET_MESSAGE (
1766 p_app_name => G_OKC_APP_NAME,
1767 p_msg_name => G_INVALID_VALUE,
1768 p_token1 => G_COL_NAME_TOKEN,
1769 p_token1_value => 'Stream_Type');
1770 ELSE
1771 l_tilv_rec.sty_id := l_sty_id;
1772 END IF;
1773
1774 -- ****************
1775 -- Get Bill_To info
1776 -- ****************
1777
1778 -- Bill Vendor
1779 IF l_bill_to_flag = 1 THEN
1780 -- PAGARG Bug 3559535 Pass l_vendor_status instead of l_return_status and if
1781 -- l_vendor_status is not success then set l_return_status as error
1782 -- Get Customer and Bill_To linked to a Vendor
1783 Get_Vendor_Billing_Info (
1784 px_taiv_rec => l_taiv_rec,
1785 x_return_status => l_vendor_status);
1786
1787 IF l_vendor_status <> OKL_API.G_RET_STS_SUCCESS
1788 THEN
1789 l_return_status := OKL_API.G_RET_STS_ERROR;
1790 END IF;
1791
1792 -- Bill Lessee
1793 ELSIF l_bill_to_flag = 2
1794 AND NVL (l_taiv_rec.khr_id, G_MISS_NUM) <> G_MISS_NUM THEN
1795 NULL; -- BPD derives billing info using KHR_ID
1796
1797 ELSE
1798 l_bill_to_status := OKL_API.G_RET_STS_ERROR;
1799 END IF;
1800
1801 -- **************
1802 -- Display errors
1803 -- **************
1804
1805 IF l_approval_counter > 0 THEN
1806 l_return_status := OKL_API.G_RET_STS_ERROR;
1807 -- Message Text: Unable to process the request for a Repair Invoice.
1808 -- Approval for OUTSTANDING_APPROVALS repair line(s) outstanding.
1809 OKL_API.SET_MESSAGE (
1810 p_app_name => G_APP_NAME,
1811 p_msg_name => 'OKL_AM_APPROVALS_OUTSTANDING',
1812 p_token1 => 'OUTSTANDING_APPROVALS',
1813 p_token1_value => l_approval_counter);
1814 END IF;
1815
1816 /*
1817 IF l_invoiced_counter > 0 THEN
1818 l_return_status := OKL_API.G_RET_STS_ERROR;
1819 -- Message Text: Unable to process request for
1820 -- a Invoice creation. Invoice(s) already exists for part PART_NUMBER.
1821 OKL_API.SET_MESSAGE (
1822 p_app_name => G_APP_NAME,
1823 p_msg_name => 'OKL_AM_INVOICES_EXIST');
1824 END IF;
1825 */
1826 IF l_bill_to_status <> OKL_API.G_RET_STS_SUCCESS THEN
1827 l_return_status := OKL_API.G_RET_STS_ERROR;
1828 -- Message Text: Invalid value for the column P_BILL_ID
1829 OKL_API.SET_MESSAGE (
1830 p_app_name => G_OKC_APP_NAME,
1831 p_msg_name => G_INVALID_VALUE,
1832 p_token1 => G_COL_NAME_TOKEN,
1833 p_token1_value => 'p_bill_to');
1834 END IF;
1835
1836 IF l_cle_mismatch <> OKL_API.G_RET_STS_SUCCESS THEN
1837 l_return_status := OKL_API.G_RET_STS_ERROR;
1838 -- Message Text: Invalid value for the column CLE_ID
1839 OKL_API.SET_MESSAGE (
1840 p_app_name => G_OKC_APP_NAME,
1841 p_msg_name => G_INVALID_VALUE,
1842 p_token1 => G_COL_NAME_TOKEN,
1843 p_token1_value => 'cle_id');
1844 END IF;
1845
1846 IF l_acn_status <> OKL_API.G_RET_STS_SUCCESS THEN
1847 l_return_status := OKL_API.G_RET_STS_ERROR;
1848 -- Message Text: Invalid value for the column P_ACN_ID
1849 OKL_API.SET_MESSAGE (
1850 p_app_name => G_OKC_APP_NAME,
1851 p_msg_name => G_INVALID_VALUE,
1852 p_token1 => G_COL_NAME_TOKEN,
1853 p_token1_value => 'p_acn_id');
1854 END IF;
1855
1856 IF l_amount_status <> OKL_API.G_RET_STS_SUCCESS THEN
1857 l_return_status := OKL_API.G_RET_STS_ERROR;
1858 /*
1859 -- Message Text: Invalid value for the column P_ACTUAL_REPAIR_COST
1860 OKL_API.SET_MESSAGE (
1861 p_app_name => G_OKC_APP_NAME,
1862 p_msg_name => G_INVALID_VALUE,
1863 p_token1 => G_COL_NAME_TOKEN,
1864 p_token1_value => 'p_actual_repair_cost');
1865 */
1866 -- You must enter a value for PROMPT
1867 OKL_API.set_message (
1868 p_app_name => G_APP_NAME,
1869 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
1870 p_token1 => 'PROMPT',
1871 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_ACTUAL_REPAIR_COST'));
1872
1873 END IF;
1874
1875 x_pos_amount := l_pos_amount;
1876 x_neg_amount := l_neg_amount;
1877 x_taiv_rec := l_taiv_rec;
1878 x_tilv_rec := l_tilv_rec;
1879 x_return_status := l_return_status;
1880 IF (is_debug_procedure_on) THEN
1881 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
1882 END IF;
1883
1884 EXCEPTION
1885
1886 WHEN OTHERS THEN
1887 IF (is_debug_exception_on) THEN
1888 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1889 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1890 END IF;
1891
1892 IF l_cle_csr%ISOPEN THEN
1893 CLOSE l_cle_csr;
1894 END IF;
1895 --PAGARG Bug 4044659 Close the cusrsor if open
1896 IF l_sys_prms_csr%ISOPEN THEN
1897 CLOSE l_sys_prms_csr;
1898 END IF;
1899
1900 IF l_assetreturns_csr%ISOPEN THEN
1901 CLOSE l_assetreturns_csr;
1902 END IF;
1903
1904 -- store SQL error message on message stack for caller
1905 OKL_API.SET_MESSAGE (
1906 p_app_name => G_APP_NAME
1907 ,p_msg_name => G_UNEXPECTED_ERROR
1908 ,p_token1 => G_SQLCODE_TOKEN
1909 ,p_token1_value => sqlcode
1910 ,p_token2 => G_SQLERRM_TOKEN
1911 ,p_token2_value => sqlerrm);
1912
1913 -- notify caller of an UNEXPECTED error
1914 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1915
1916 END Validate_Populate_Repair;
1917
1918
1919 -- Start of comments
1920 --
1921 -- Procedure Name : Create_Repair_Invoice
1922 -- Description : Create Invoice for Asset Repair
1923 -- Business Rules :
1924 -- Parameters : asset return fields
1925 -- Version : 1.0
1926 -- End of comments
1927
1928 PROCEDURE Create_Repair_Invoice (
1929 p_api_version IN NUMBER,
1930 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1931 x_msg_count OUT NOCOPY NUMBER,
1932 x_msg_data OUT NOCOPY VARCHAR2,
1933 x_return_status OUT NOCOPY VARCHAR2,
1934 p_ariv_tbl IN ariv_tbl_type,
1935 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
1936
1937 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1938 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1939
1940 l_api_name CONSTANT VARCHAR2(30) :=
1941 'Create_Repair_Invoice';
1942 l_api_version CONSTANT NUMBER := G_API_VERSION;
1943 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
1944 l_msg_data VARCHAR2(2000);
1945 l_cnt NUMBER;
1946
1947 l_pos_amount NUMBER := 0;
1948 l_neg_amount NUMBER := 0;
1949
1950 l_taiv_rec taiv_rec_type;
1951 lx_pos_taiv_rec taiv_rec_type;
1952 lx_neg_taiv_rec taiv_rec_type;
1953 l_tilv_rec tilv_rec_type;
1954 lx_tilv_rec tilv_rec_type;
1955
1956 -- ANSETHUR 08-MAR-2007 R12B Added For Billing Architecture
1957 l_tilv_tbl tilv_tbl_type;
1958 lx_tilv_tbl tilv_tbl_type;
1959 -- ANSETHUR 08-MAR-2007 R12B Added For Billing Architecture
1960 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_repair_invoice';
1961 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1962 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1963 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1964
1965 BEGIN
1966 IF (is_debug_procedure_on) THEN
1967 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
1968 END IF;
1969 IF (is_debug_statement_on) THEN
1970 FOR i IN p_ariv_tbl.FIRST..p_ariv_tbl.LAST LOOP
1971 IF (p_ariv_tbl.exists(i)) THEN
1972 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_bill_to : ' || p_ariv_tbl(i).p_bill_to);
1973 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_approved_yn : ' || p_ariv_tbl(i).p_approved_yn);
1974 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_acd_id_cost : ' || p_ariv_tbl(i).p_acd_id_cost);
1975 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_acn_id : ' || p_ariv_tbl(i).p_acn_id);
1976 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_actual_repair_cost : ' || p_ariv_tbl(i).p_actual_repair_cost);
1977 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_condition_type : ' || p_ariv_tbl(i).p_condition_type);
1978 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_ariv_tbl(' || i || ').p_damage_type : ' || p_ariv_tbl(i).p_damage_type);
1979 END IF;
1980 END LOOP;
1981 END IF;
1982
1983 -- ***************************************************************
1984 -- Check API version, initialize message list and create savepoint
1985 -- ***************************************************************
1986
1987 l_return_status := OKL_API.START_ACTIVITY (l_api_name,
1988 G_PKG_NAME,
1989 p_init_msg_list,
1990 l_api_version,
1991 p_api_version,
1992 '_PVT',
1993 x_return_status);
1994
1995 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1997 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1998 RAISE OKL_API.G_EXCEPTION_ERROR;
1999 END IF;
2000
2001 -- **********************************************
2002 -- Validate parameters and populate common fields
2003 -- **********************************************
2004
2005 IF p_ariv_tbl.COUNT = 0 THEN
2006
2007 OKC_API.SET_MESSAGE (
2008 p_app_name => G_OKC_APP_NAME,
2009 p_msg_name => 'OKC_NO_PARAMS',
2010 p_token1 => 'PARAM',
2011 p_token1_value => 'ARIV_TBL',
2012 p_token2 => 'PROCESS',
2013 p_token2_value => l_api_name);
2014
2015 RAISE OKL_API.G_EXCEPTION_ERROR;
2016
2017 END IF;
2018
2019 -- Validate all in-records
2020 -- Populate header: Amount, Description, Currency and Contract_Id
2021 -- Populate common line fields: Contract_Line_Id and Stream_Type_Id
2022 Validate_Populate_Repair (
2023 p_ariv_tbl => p_ariv_tbl,
2024 x_pos_amount => l_pos_amount,
2025 x_neg_amount => l_neg_amount,
2026 x_taiv_rec => l_taiv_rec,
2027 x_tilv_rec => l_tilv_rec,
2028 x_return_status => l_return_status);
2029 IF (is_debug_statement_on) THEN
2030 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Validate_Populate_Repair , return status: ' || l_return_status);
2031 END IF;
2032
2033 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2034 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2035 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2036 RAISE OKL_API.G_EXCEPTION_ERROR;
2037 END IF;
2038
2039 -- ANSETHUR 08-MAR-2007 R12B Billing Architecture Start Changes
2040 -- Replaced the call of Create_AR_Invoice_Header and Create_AR_Invoice_Lines
2041 -- with the Create_billing_invoices Procedure which is created as
2042 -- a part of new billing architecture.
2043 -- Added loop to populate l_tilv_tbl which has to be passed to the new procedure
2044
2045 l_cnt := p_ariv_tbl.FIRST;
2046 LOOP
2047
2048 l_tilv_rec.line_number := l_cnt;
2049 l_tilv_rec.acn_id_cost := p_ariv_tbl(l_cnt).p_acn_id;
2050 l_tilv_rec.amount := p_ariv_tbl(l_cnt).p_actual_repair_cost;
2051
2052 IF NVL (p_ariv_tbl(l_cnt).p_condition_type, G_MISS_CHAR) <> G_MISS_CHAR
2053 AND NVL (p_ariv_tbl(l_cnt).p_damage_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2054 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_condition_type || ' - ' ||
2055 p_ariv_tbl(l_cnt).p_damage_type;
2056 ELSIF NVL (p_ariv_tbl(l_cnt).p_condition_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2057 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_condition_type;
2058 ELSIF NVL (p_ariv_tbl(l_cnt).p_damage_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2059 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_damage_type;
2060 ELSE
2061 l_tilv_rec.description := NULL;
2062 END IF;
2063
2064 l_tilv_tbl(l_cnt):= l_tilv_rec ;
2065
2066 EXIT WHEN ( l_cnt = p_ariv_tbl.LAST OR l_return_status <> OKL_API.G_RET_STS_SUCCESS);
2067 l_cnt := p_ariv_tbl.NEXT(l_cnt);
2068 END LOOP;
2069
2070 Create_billing_invoices ( p_taiv_rec =>l_taiv_rec,
2071 p_pos_amount =>l_pos_amount,
2072 p_neg_amount =>l_neg_amount,
2073 p_tilv_tbl =>l_tilv_tbl,
2074 x_tilv_tbl =>lx_tilv_tbl,
2075 x_pos_taiv_rec =>lx_pos_taiv_rec ,
2076 x_neg_taiv_rec =>lx_neg_taiv_rec,
2077 x_return_status=>l_return_status);
2078 IF (is_debug_statement_on) THEN
2079 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_billing_invoices , return status: ' || l_return_status);
2080 END IF;
2081
2082
2083 /* ansethur 08-MAR-2007 R12B Commented for Billing Architecture
2084 -- *********************
2085 -- Create Invoice Header
2086 -- *********************
2087
2088 Create_AR_Invoice_Header (
2089 p_taiv_rec => l_taiv_rec,
2090 p_pos_amount => l_pos_amount,
2091 p_neg_amount => l_neg_amount,
2092 x_pos_taiv_rec => lx_pos_taiv_rec,
2093 x_neg_taiv_rec => lx_neg_taiv_rec,
2094 x_return_status => l_return_status);
2095
2096 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2097 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2098 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2099 RAISE OKL_API.G_EXCEPTION_ERROR;
2100 END IF;
2101
2102 -- *********************************************
2103 -- Create Invoice Lines for each record in table
2104 -- *********************************************
2105
2106 l_cnt := p_ariv_tbl.FIRST;
2107
2108 LOOP
2109
2110 l_tilv_rec.line_number := l_cnt;
2111 l_tilv_rec.acn_id_cost := p_ariv_tbl(l_cnt).p_acn_id;
2112 l_tilv_rec.amount := p_ariv_tbl(l_cnt).p_actual_repair_cost;
2113
2114 IF l_tilv_rec.amount > 0 THEN
2115 l_tilv_rec.tai_id := lx_pos_taiv_rec.id;
2116 ELSIF l_tilv_rec.amount < 0 THEN
2117 l_tilv_rec.tai_id := lx_neg_taiv_rec.id;
2118 ELSE
2119 l_tilv_rec.tai_id := NULL;
2120 END IF;
2121
2122 IF NVL (p_ariv_tbl(l_cnt).p_condition_type, G_MISS_CHAR) <> G_MISS_CHAR
2123 AND NVL (p_ariv_tbl(l_cnt).p_damage_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2124 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_condition_type || ' - ' ||
2125 p_ariv_tbl(l_cnt).p_damage_type;
2126 ELSIF NVL (p_ariv_tbl(l_cnt).p_condition_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2127 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_condition_type;
2128 ELSIF NVL (p_ariv_tbl(l_cnt).p_damage_type, G_MISS_CHAR) <> G_MISS_CHAR THEN
2129 l_tilv_rec.description := p_ariv_tbl(l_cnt).p_damage_type;
2130 ELSE
2131 l_tilv_rec.description := NULL;
2132 END IF;
2133
2134 Create_AR_Invoice_Lines (
2135 p_tilv_rec => l_tilv_rec,
2136 x_tilv_rec => lx_tilv_rec,
2137 x_return_status => l_return_status);
2138
2139 EXIT WHEN ( l_cnt = p_ariv_tbl.LAST
2140 OR l_return_status <> OKL_API.G_RET_STS_SUCCESS);
2141 l_cnt := p_ariv_tbl.NEXT(l_cnt);
2142
2143 END LOOP;
2144 */
2145 -- ansethur 08-MAR-2007 R12B Billing Architecture End Changes
2146
2147 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2148 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2149 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2150 RAISE OKL_API.G_EXCEPTION_ERROR;
2151 END IF;
2152
2153 -- **************
2154 -- Return results
2155 -- **************
2156
2157 l_cnt := 0;
2158
2159 IF l_pos_amount > 0 THEN
2160 l_cnt := l_cnt + 1;
2161 x_taiv_tbl (l_cnt) := lx_pos_taiv_rec;
2162 END IF;
2163
2164 IF l_neg_amount < 0 THEN
2165 l_cnt := l_cnt + 1;
2166 x_taiv_tbl (l_cnt) := lx_neg_taiv_rec;
2167 END IF;
2168
2169 x_return_status := l_overall_status;
2170
2171 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
2172 IF (is_debug_procedure_on) THEN
2173 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
2174 END IF;
2175
2176 EXCEPTION
2177
2178 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2179 IF (is_debug_exception_on) THEN
2180 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
2181 END IF;
2182 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2183 (
2184 l_api_name,
2185 G_PKG_NAME,
2186 'OKL_API.G_RET_STS_ERROR',
2187 x_msg_count,
2188 x_msg_data,
2189 '_PVT'
2190 );
2191
2192 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2193 IF (is_debug_exception_on) THEN
2194 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
2195 END IF;
2196 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2197 (
2198 l_api_name,
2199 G_PKG_NAME,
2200 'OKL_API.G_RET_STS_UNEXP_ERROR',
2201 x_msg_count,
2202 x_msg_data,
2203 '_PVT'
2204 );
2205
2206 WHEN OTHERS THEN
2207 IF (is_debug_exception_on) THEN
2208 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2209 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2210 END IF;
2211
2212 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2213 (
2214 l_api_name,
2215 G_PKG_NAME,
2216 'OTHERS',
2217 x_msg_count,
2218 x_msg_data,
2219 '_PVT'
2220 );
2221
2222 END Create_Repair_Invoice;
2223
2224
2225 -- Start of comments
2226 --
2227 -- Procedure Name : Validate_Populate_Remarket
2228 -- Description : Ensure order line is coneected to asset return
2229 -- and has not been billed before
2230 -- Populates all header fields
2231 -- Populates all line fields
2232 -- Business Rules :
2233 -- Parameters : asset return fields
2234 -- Version : 1.0
2235 -- History : RMUNJULU 30-DEC-02 2726739 Changed cursor and taiv_rec
2236 -- to set currency columns
2237 -- : SECHAWLA 01-NOV-04 3967398 : create remarket invoice for remarket item
2238 -- : rmunjulu bug 4056364 Removed Quote line alloc message
2239 -- : PAGARG 4044659 25-Jan-2005 Assign order lines' or header's
2240 -- sold_from_org_id and assign it to tilv_rec.inventory_org_id
2241 -- if LEASE_INV_ORG_YN is Y in OKL_SYSTEM_PARAMS_ALL
2242 -- : rmunjulu 3985369 Added code to get Ship_From Org Id from the order lines and
2243 -- pass as inventory_org_id when creating invoice transaction lines
2244 -- End of comments
2245
2246 PROCEDURE Validate_Populate_Remarket (
2247 p_order_line_id IN NUMBER,
2248 x_pos_amount OUT NOCOPY NUMBER,
2249 x_neg_amount OUT NOCOPY NUMBER,
2250 x_taiv_rec OUT NOCOPY taiv_rec_type,
2251 x_tilv_rec OUT NOCOPY tilv_rec_type,
2252 x_return_status OUT NOCOPY VARCHAR2) IS
2253
2254 -- Returns Contract Line and Asset Return records
2255 -- RMUNJULU 30-DEC-02 2726739 -- Added columns for multi-currency get values
2256 -- from asset_returns
2257 CURSOR l_art_csr (cp_order_line_id IN NUMBER) IS
2258 SELECT oli.unit_selling_price price,
2259 oli.ordered_quantity quantity,
2260 ohe.ordered_date ordered_date,
2261 kle.chr_id chr_id,
2262 kle.id cle_id,
2263 kle.name asset_number,
2264 kle.item_description asset_description,
2265 csu.cust_acct_site_id ibt_id,
2266 csu.cust_account_id ixx_id,
2267 art.currency_code currency_code,
2268 art.currency_conversion_type currency_conversion_type,
2269 art.currency_conversion_rate currency_conversion_rate,
2270 art.currency_conversion_date currency_conversion_date,
2271 NVL (oli.payment_term_id, ohe.payment_term_id) irt_id,
2272 NVL (oli.sold_from_org_id, ohe.sold_from_org_id) org_id,
2273 NVL(oli.ship_from_org_id, ohe.ship_from_org_id) ship_from_org_id, -- rmunjulu Bug 3985369 Get Ship_From_Org_Id which will be passed to inventory_org_id when creating a invoice transaction.
2274 NULL set_of_books_id, -- derived from org_id
2275 NULL irm_id, -- defaulted in AR
2276 art.legal_entity_id
2277 FROM oe_order_lines_all oli,
2278 oe_order_headers_all ohe,
2279 okl_asset_returns_b art,
2280 okc_k_lines_v kle,
2281 okx_cust_site_uses_v csu
2282 WHERE oli.line_id = cp_order_line_id
2283 AND ohe.header_id = oli.header_id
2284 AND art.imr_id = oli.inventory_item_id
2285 AND kle.id = art.kle_id
2286 AND csu.id1 = nvl (oli.invoice_to_org_id, ohe.invoice_to_org_id);
2287
2288 -- Returns previously billed records
2289 CURSOR l_til_csr (cp_order_line_id IN NUMBER) IS
2290 SELECT til.id
2291 FROM okl_txl_ar_inv_lns_v til
2292 WHERE til.isl_id = cp_order_line_id;
2293
2294 -- SECHAWLA 01-NOV-04 3967398 : get the remarketing inventory item id
2295 CURSOR l_orderlines_csr(cp_order_line_id IN NUMBER) IS
2296 SELECT inventory_item_id
2297 FROM oe_order_lines_all
2298 WHERE line_id = cp_order_line_id;
2299
2300 -- SECHAWLA 01-NOV-04 3967398 : check the item invoiced option from the setup
2301 -- PAGARG 26-Jan-2005 Bug 4044659 Querying LEASE_INV_ORG_YN also.
2302 CURSOR l_systemparamsall_csr IS
2303 SELECT REMK_ITEM_INVOICED_CODE
2304 ,NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
2305 FROM OKL_SYSTEM_PARAMS ;
2306
2307 l_remk_item_invoiced VARCHAR2(15);
2308 l_inventory_item_id NUMBER;
2309 -- PAGARG 26-Jan-2005 Bug 4044659 variable to store the value of LEASE_INV_ORG_YN
2310 l_lease_inv_org_yn OKL_SYSTEM_PARAMS_ALL.lease_inv_org_yn%TYPE;
2311
2312
2313 l_art_rec l_art_csr%ROWTYPE;
2314 l_til_rec l_til_csr%ROWTYPE;
2315 l_taiv_rec taiv_rec_type;
2316 l_tilv_rec tilv_rec_type;
2317
2318 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2319 lx_remrkt_sty_id NUMBER; --User Defined Streams
2320 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'validate_populate_remarket';
2321 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2322 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2323 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2324
2325 BEGIN
2326 IF (is_debug_procedure_on) THEN
2327 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
2328 END IF;
2329 IF (is_debug_statement_on) THEN
2330 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_order_line_id: '||p_order_line_id);
2331 END IF;
2332
2333 -- ***************************************************
2334 -- Check that no records have been previously invoiced
2335 -- ***************************************************
2336
2337 OPEN l_til_csr (p_order_line_id);
2338 FETCH l_til_csr INTO l_til_rec;
2339
2340 IF l_til_csr%FOUND THEN
2341 l_return_status := OKL_API.G_RET_STS_ERROR;
2342 -- Message Text: Unable to process request for
2343 -- a Invoice creation. Invoice(s) already exist
2344 OKL_API.SET_MESSAGE (
2345 p_app_name => G_APP_NAME,
2346 p_msg_name => 'OKL_AM_INVOICES_EXIST');
2347 END IF;
2348
2349 CLOSE l_til_csr;
2350
2351 -- ******************************************
2352 -- Get Contract Line and Asset Return Records
2353 -- ******************************************
2354
2355 OPEN l_art_csr (p_order_line_id);
2356 FETCH l_art_csr INTO l_art_rec;
2357
2358 IF l_art_csr%NOTFOUND THEN
2359 l_return_status := OKL_API.G_RET_STS_ERROR;
2360 OKC_API.SET_MESSAGE (
2361 p_app_name => G_OKC_APP_NAME,
2362 p_msg_name => G_INVALID_VALUE,
2363 p_token1 => G_COL_NAME_TOKEN,
2364 p_token1_value => 'Order_Line_Id');
2365
2366 ELSE
2367
2368 -- RRAVIKIR Legal Entity Changes
2369 IF (l_art_rec.legal_entity_id is null or l_art_rec.legal_entity_id = OKC_API.G_MISS_NUM) THEN
2370 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2371 p_msg_name => g_required_value,
2372 p_token1 => g_col_name_token,
2373 p_token1_value => 'legal_entity_id');
2374 RAISE OKC_API.G_EXCEPTION_ERROR;
2375 END IF;
2376 -- Legal Entity Changes End
2377
2378 l_taiv_rec.khr_id := l_art_rec.chr_id;
2379 l_taiv_rec.description := l_art_rec.asset_number || ' - ' ||l_art_rec.asset_description;
2380 l_taiv_rec.date_invoiced := l_art_rec.ordered_date;
2381 l_taiv_rec.currency_code := l_art_rec.currency_code;
2382 l_taiv_rec.set_of_books_id := l_art_rec.set_of_books_id;
2383 l_taiv_rec.ibt_id := l_art_rec.ibt_id; -- bill_to
2384 l_taiv_rec.ixx_id := l_art_rec.ixx_id; -- customer
2385 l_taiv_rec.irm_id := l_art_rec.irm_id; -- payment method
2386 l_taiv_rec.irt_id := l_art_rec.irt_id; -- payment term
2387 l_taiv_rec.org_id := l_art_rec.org_id;
2388
2389 -- RMUNJULU 30-DEC-02 2726739 -- Added for multi-currency
2390 l_taiv_rec.currency_conversion_type := l_art_rec.currency_conversion_type;
2391 l_taiv_rec.currency_conversion_rate := l_art_rec.currency_conversion_rate;
2392 l_taiv_rec.currency_conversion_date := l_art_rec.currency_conversion_date;
2393
2394 -- RRAVIKIR Legal Entity Changes
2395 l_taiv_rec.legal_entity_id := l_art_rec.legal_entity_id;
2396 -- Legal Entity Changes End
2397 -- Begin - varangan- Bug#5874824 - Asset Remarketing Fix
2398
2399 l_taiv_rec.OKL_SOURCE_BILLING_TRX :='REMARKETING';
2400
2401 -- End - varangan- Bug#5874824 - Asset Remarketing Fix
2402
2403 l_tilv_rec.line_number := 1;
2404 l_tilv_rec.kle_id := l_art_rec.cle_id;
2405 l_tilv_rec.isl_id := p_order_line_id;
2406 l_tilv_rec.description := l_art_rec.asset_number || ' - ' ||
2407 l_art_rec.asset_description;
2408 l_tilv_rec.amount := l_art_rec.price * l_art_rec.quantity;
2409
2410 -- SECHAWLA 01-NOV-04 3967398 : Added the following piece of code
2411 -- PAGARG 26-Jan-2005 Bug 4044659 obtain the value of LEASE_INV_ORG_YN
2412 OPEN l_systemparamsall_csr;
2413 FETCH l_systemparamsall_csr INTO l_remk_item_invoiced, l_lease_inv_org_yn;
2414 CLOSE l_systemparamsall_csr;
2415
2416 IF l_remk_item_invoiced = 'REMARKET_ITEM' THEN
2417 OPEN l_orderlines_csr(p_order_line_id);
2418 FETCH l_orderlines_csr INTO l_inventory_item_id;
2419 CLOSE l_orderlines_csr;
2420
2421 l_tilv_rec.inventory_item_id := l_inventory_item_id;
2422 END IF;
2423 --PAGARG Bug 4044659 If LEASE_INV_ORG_YN is Y then set the value of
2424 --INVENTORY_ORG_ID in invoice line with org_id of order lines or header
2425 IF l_lease_inv_org_yn = 'Y'
2426 THEN
2427 l_tilv_rec.inventory_org_id := l_art_rec.ship_from_org_id; -- rmunjulu Bug 3985369 Changed to pass ship_from_org_id
2428 END IF;
2429 -- SECHAWLA 01-NOV-04 3967398 : Added the following piece of code : end
2430
2431 END IF;
2432
2433 CLOSE l_art_csr;
2434
2435 -- ***************
2436 -- Get stream type
2437 -- ***************
2438
2439 -- ++++ User Defined Streams Change ++++-----
2440 IF (is_debug_statement_on) THEN
2441 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_STREAMS_UTIL.get_primary_stream_type');
2442 END IF;
2443 OKL_STREAMS_UTIL.get_primary_stream_type(l_art_rec.chr_id,
2444 G_REMARKET_QUOTE_LINE,
2445 l_return_status,
2446 lx_remrkt_sty_id);
2447 IF (is_debug_statement_on) THEN
2448 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_STREAMS_UTIL.get_primary_stream_type , return status: ' || l_return_status);
2449 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'lx_remrkt_sty_id: ' || lx_remrkt_sty_id);
2450 END IF;
2451
2452 l_tilv_rec.sty_id := lx_remrkt_sty_id;
2453 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
2454
2455 IF NVL (l_tilv_rec.sty_id, G_MISS_NUM) = G_MISS_NUM THEN
2456 l_return_status := OKL_API.G_RET_STS_ERROR;
2457
2458 /* -- rmunjulu bug 4056364 No need to set this message
2459 -- Stream Purpose is not setup
2460 okl_am_util_pvt.set_message (
2461 p_app_name => G_APP_NAME
2462 ,p_msg_name => 'OKL_AM_NO_STREAM_TO_QUOTE'
2463 ,p_token1 => 'QLT_CODE'
2464 ,p_token1_value => G_REMARKET_QUOTE_LINE);
2465 */
2466 END IF;
2467
2468 -- ***********
2469 -- Save amount
2470 -- ***********
2471
2472 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
2473 -- if order line is found
2474
2475 IF l_tilv_rec.amount IS NULL
2476 OR l_tilv_rec.amount = G_MISS_NUM
2477 OR l_tilv_rec.amount = 0 THEN
2478 l_return_status := OKL_API.G_RET_STS_ERROR;
2479 -- Message Text: Invalid value for the column Amount
2480 OKC_API.SET_MESSAGE (
2481 p_app_name => G_OKC_APP_NAME,
2482 p_msg_name => G_INVALID_VALUE,
2483 p_token1 => G_COL_NAME_TOKEN,
2484 p_token1_value => 'Amount');
2485 ELSIF l_tilv_rec.amount > 0 THEN
2486 x_pos_amount := l_tilv_rec.amount;
2487 x_neg_amount := 0;
2488 ELSIF l_tilv_rec.amount < 0 THEN
2489 x_neg_amount := l_tilv_rec.amount;
2490 x_pos_amount := 0;
2491 END IF;
2492
2493 END IF;
2494
2495 x_taiv_rec := l_taiv_rec;
2496 x_tilv_rec := l_tilv_rec;
2497 x_return_status := l_return_status;
2498 IF (is_debug_procedure_on) THEN
2499 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
2500 END IF;
2501
2502 EXCEPTION
2503
2504 WHEN OTHERS THEN
2505 IF (is_debug_exception_on) THEN
2506 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2507 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2508 END IF;
2509
2510 IF l_art_csr%ISOPEN THEN
2511 CLOSE l_art_csr;
2512 END IF;
2513
2514 IF l_til_csr%ISOPEN THEN
2515 CLOSE l_til_csr;
2516 END IF;
2517
2518 -- SECHAWLA 01-NOV-04 3967398
2519 IF l_systemparamsall_csr%ISOPEN THEN
2520 CLOSE l_systemparamsall_csr;
2521 END IF;
2522
2523 IF l_orderlines_csr%ISOPEN THEN
2524 CLOSE l_orderlines_csr;
2525 END IF;
2526
2527
2528 -- store SQL error message on message stack for caller
2529 OKL_API.SET_MESSAGE (
2530 p_app_name => G_APP_NAME
2531 ,p_msg_name => G_UNEXPECTED_ERROR
2532 ,p_token1 => G_SQLCODE_TOKEN
2533 ,p_token1_value => sqlcode
2534 ,p_token2 => G_SQLERRM_TOKEN
2535 ,p_token2_value => sqlerrm);
2536
2537 -- notify caller of an UNEXPECTED error
2538 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2539
2540 END Validate_Populate_Remarket;
2541
2542
2543 -- Start of comments
2544 --
2545 -- Procedure Name : Create_Remarket_Invoice
2546 -- Description : Create Invoice for Remarket Sale
2547 -- Business Rules :
2548 -- Parameters : order line id
2549 -- Version : 1.0
2550 -- End of comments
2551
2552 PROCEDURE Create_Remarket_Invoice (
2553 p_api_version IN NUMBER,
2554 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2555 x_msg_count OUT NOCOPY NUMBER,
2556 x_msg_data OUT NOCOPY VARCHAR2,
2557 x_return_status OUT NOCOPY VARCHAR2,
2558 p_order_line_id IN NUMBER,
2559 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
2560
2561 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2562 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2563
2564 l_api_name CONSTANT VARCHAR2(30) :='Create_Remarket_Invoice';
2565 l_api_version CONSTANT NUMBER := G_API_VERSION;
2566 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
2567 l_msg_data VARCHAR2(2000);
2568 l_cnt NUMBER;
2569
2570 l_pos_amount NUMBER := 0;
2571 l_neg_amount NUMBER := 0;
2572
2573 l_taiv_rec taiv_rec_type;
2574 lx_pos_taiv_rec taiv_rec_type;
2575 lx_neg_taiv_rec taiv_rec_type;
2576 l_tilv_rec tilv_rec_type;
2577 lx_tilv_rec tilv_rec_type;
2578
2579 --ANSETHUR 08-MAR-2007 R12B Billling Architecture Start Changes
2580 l_tilv_tbl tilv_tbl_type;
2581 lx_tilv_tbl tilv_tbl_type;
2582 --ANSETHUR 08-MAR-2007 R12B Billling Architecture End Changes
2583 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_remarket_invoice';
2584 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2585 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2586 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2587 BEGIN
2588 IF (is_debug_procedure_on) THEN
2589 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
2590 END IF;
2591 IF (is_debug_statement_on) THEN
2592 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_order_line_id: '||p_order_line_id);
2593 END IF;
2594
2595 -- ***************************************************************
2596 -- Check API version, initialize message list and create savepoint
2597 -- ***************************************************************
2598
2599 l_return_status := OKL_API.START_ACTIVITY (
2600 l_api_name,
2601 G_PKG_NAME,
2602 p_init_msg_list,
2603 l_api_version,
2604 p_api_version,
2605 '_PVT',
2606 x_return_status);
2607
2608 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2609 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2610 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2611 RAISE OKL_API.G_EXCEPTION_ERROR;
2612 END IF;
2613
2614 -- *******************
2615 -- Validate parameters
2616 -- *******************
2617
2618 IF p_order_line_id IS NULL
2619 OR p_order_line_id = G_MISS_NUM THEN
2620
2621 OKC_API.SET_MESSAGE (
2622 p_app_name => G_OKC_APP_NAME,
2623 p_msg_name => 'OKC_NO_PARAMS',
2624 p_token1 => 'PARAM',
2625 p_token1_value => 'ORDER_LINE_ID',
2626 p_token2 => 'PROCESS',
2627 p_token2_value => l_api_name);
2628
2629 RAISE OKL_API.G_EXCEPTION_ERROR;
2630
2631 END IF;
2632
2633 -- Validate order_line_id
2634 -- Populate all header fields
2635 -- Populate all line fields for a single line
2636 Validate_Populate_Remarket (p_order_line_id => p_order_line_id,
2637 x_pos_amount => l_pos_amount,
2638 x_neg_amount => l_neg_amount,
2639 x_taiv_rec => l_taiv_rec,
2640 x_tilv_rec => l_tilv_rec,
2641 x_return_status => l_return_status);
2642 IF (is_debug_statement_on) THEN
2643 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Validate_Populate_Remarket , return status: ' || l_return_status);
2644 END IF;
2645
2646 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2647 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2648 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2649 RAISE OKL_API.G_EXCEPTION_ERROR;
2650 END IF;
2651
2652
2653 -- ANSETHUR 08-MAR-2007 R12B Billling Architecture Start Changes
2654 -- Replaced the call of Create_AR_Invoice_Header and Create_AR_Invoice_Lines
2655 -- with the Create_billing_invoices Procedure which is created as
2656 -- a part of new billing architecture.
2657 l_tilv_tbl(0):= l_tilv_rec;
2658 Create_billing_invoices ( p_taiv_rec =>l_taiv_rec,
2659 p_pos_amount =>l_pos_amount,
2660 p_neg_amount =>l_neg_amount,
2661 p_tilv_tbl =>l_tilv_tbl,
2662 x_tilv_tbl =>lx_tilv_tbl,
2663 x_pos_taiv_rec =>lx_pos_taiv_rec ,
2664 x_neg_taiv_rec =>lx_neg_taiv_rec,
2665 x_return_status=>l_return_status);
2666 IF (is_debug_statement_on) THEN
2667 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_billing_invoices , return status: ' || l_return_status);
2668 END IF;
2669 /*
2670 -- *********************
2671 -- Create Invoice Header
2672 -- *********************
2673
2674 Create_AR_Invoice_Header (p_taiv_rec => l_taiv_rec,
2675 p_pos_amount => l_pos_amount,
2676 p_neg_amount => l_neg_amount,
2677 x_pos_taiv_rec => lx_pos_taiv_rec,
2678 x_neg_taiv_rec => lx_neg_taiv_rec,
2679 x_return_status => l_return_status);
2680
2681 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2682 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2683 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2684 RAISE OKL_API.G_EXCEPTION_ERROR;
2685 END IF;
2686
2687 -- *********************************************
2688 -- Create Invoice Lines for each record in table
2689 -- *********************************************
2690
2691 IF l_tilv_rec.amount > 0 THEN
2692 l_tilv_rec.tai_id := lx_pos_taiv_rec.id;
2693 ELSIF l_tilv_rec.amount < 0 THEN
2694 l_tilv_rec.tai_id := lx_neg_taiv_rec.id;
2695 ELSE
2696 l_tilv_rec.tai_id := NULL;
2697 END IF;
2698
2699 Create_AR_Invoice_Lines (
2700 p_tilv_rec => l_tilv_rec,
2701 x_tilv_rec => lx_tilv_rec,
2702 x_return_status => l_return_status);
2703 */
2704 --ANSETHUR 08-MAR-2007 R12B Billling Architecture End Changes
2705 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2707 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2708 RAISE OKL_API.G_EXCEPTION_ERROR;
2709 END IF;
2710
2711 -- **************
2712 -- Return results
2713 -- **************
2714
2715 l_cnt := 0;
2716
2717 IF l_pos_amount > 0 THEN
2718 l_cnt := l_cnt + 1;
2719 x_taiv_tbl (l_cnt) := lx_pos_taiv_rec;
2720 END IF;
2721
2722 IF l_neg_amount < 0 THEN
2723 l_cnt := l_cnt + 1;
2724 x_taiv_tbl (l_cnt) := lx_neg_taiv_rec;
2725 END IF;
2726
2727 x_return_status := l_overall_status;
2728
2729 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
2730 IF (is_debug_procedure_on) THEN
2731 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
2732 END IF;
2733
2734 EXCEPTION
2735
2736 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2737 IF (is_debug_exception_on) THEN
2738 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
2739 END IF;
2740 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2741 (
2742 l_api_name,
2743 G_PKG_NAME,
2744 'OKL_API.G_RET_STS_ERROR',
2745 x_msg_count,
2746 x_msg_data,
2747 '_PVT'
2748 );
2749
2750 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2751 IF (is_debug_exception_on) THEN
2752 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
2753 END IF;
2754 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2755 (
2756 l_api_name,
2757 G_PKG_NAME,
2758 'OKL_API.G_RET_STS_UNEXP_ERROR',
2759 x_msg_count,
2760 x_msg_data,
2761 '_PVT'
2762 );
2763
2764 WHEN OTHERS THEN
2765 IF (is_debug_exception_on) THEN
2766 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2767 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2768 END IF;
2769
2770 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2771 (
2772 l_api_name,
2773 G_PKG_NAME,
2774 'OTHERS',
2775 x_msg_count,
2776 x_msg_data,
2777 '_PVT'
2778 );
2779
2780 END Create_Remarket_Invoice;
2781
2782
2783 -- Start of comments
2784 --
2785 -- Procedure Name : Contract_Remaining_Sec_Dep
2786 -- Description : Calculate Security Deposit Disposition
2787 -- Business Rules :
2788 -- Parameters : quote id
2789 -- Version : 1.0
2790 -- End of comments
2791
2792 PROCEDURE Contract_Remaining_Sec_Dep (
2793 p_contract_id IN NUMBER,
2794 p_contract_line_id IN NUMBER,
2795 x_sdd_tbl OUT NOCOPY sdd_tbl_type,
2796 x_tld_tbl OUT NOCOPY tld_tbl_type,
2797 x_total_amount OUT NOCOPY NUMBER) IS
2798
2799 -- Get default date format
2800 CURSOR l_date_format_csr IS
2801 SELECT SYS_CONTEXT ('USERENV','NLS_DATE_FORMAT')
2802 FROM dual;
2803
2804 -- Get contract end date
2805 CURSOR l_contract_csr (cp_contract_id NUMBER) IS
2806 SELECT end_date
2807 FROM okc_k_headers_b
2808 WHERE id = cp_contract_id;
2809
2810 -- ansethur 03/02/2007 For R12B Billing Architecture project Start Changes
2811 -- Modified the cursor to exclude the reference of okl_xtl_sell_invs_b table.
2812
2813 -- SMODUGA 11-Oct-04 Bug 3925469
2814 -- Modified cursor by passing sty_id based on the purspose and
2815 -- removed reference to stream type view.
2816 -- Get original security deposit stream
2817 CURSOR l_sdd_stream_csr (cp_contract_id NUMBER,cp_sty_id NUMBER) IS
2818 SELECT sel.amount amount,
2819 tld.id tld_id,
2820 NULL lsm_id --ansethur 03/02/2007 Added For R12B Billing Architecture project
2821 -- xls.lsm_id lsm_id --ansethur 03/02/2007 commented for R12B Billing Architecture project
2822 FROM okc_k_lines_b kle,
2823 okc_line_styles_b lse,
2824 okc_k_items ite,
2825 okl_streams stm,
2826 okl_strm_elements sel,
2827 okl_txd_ar_ln_dtls_b tld
2828 -- ,okl_xtl_sell_invs_b xls --ansethur 03/02/2007 commented for R12B Billing Architecture project
2829 WHERE kle.chr_id = cp_contract_id
2830 AND lse.id = kle.lse_id
2831 AND lse.lty_code = 'FEE'
2832 AND ite.cle_id = kle.id
2833 AND ite.jtot_object1_code = 'OKL_STRMTYP'
2834 AND cp_sty_id = ite.object1_id1
2835 AND stm.kle_id = kle.id
2836 AND stm.khr_id = cp_contract_id
2837 AND stm.active_yn = 'Y'
2838 AND stm.say_code = 'CURR'
2839 AND cp_sty_id = stm.sty_id
2840 AND sel.stm_id = stm.id
2841 AND sel.date_billed IS NOT NULL
2842 AND NVL (sel.amount, 0) <> 0
2843 AND tld.sel_id = sel.id
2844 AND tld.tld_id_reverses IS NULL
2845 -- AND xls.tld_id = tld.id --ansethur 03/02/2007 commented for R12B Billing Architecture project
2846 ORDER BY sel.date_billed;
2847 -- ansethur 03/02/2007 For R12B Billing Architecture project End Changes
2848
2849 -- Get credit memos against security deposit
2850 CURSOR l_credit_memo_csr (cp_inv_tld_id NUMBER) IS
2851 SELECT tld.id, tld.amount
2852 FROM okl_txd_ar_ln_dtls_b tld
2853 WHERE tld.tld_id_reverses = cp_inv_tld_id;
2854
2855 l_rulv_rec okl_rule_pub.rulv_rec_type;
2856 l_sdd_tbl sdd_tbl_type;
2857 l_tld_tbl tld_tbl_type;
2858 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2859
2860 -- Calculation results
2861 l_old_cm_amount NUMBER;
2862 l_cnt NUMBER := 0;
2863 l_cnt2 NUMBER := 0;
2864 l_total_amount NUMBER := 0;
2865
2866 -- Values stored in Security Deposit Rule
2867 l_held_until_maturity VARCHAR2(1);
2868 l_held_until_date DATE;
2869
2870 l_date_format VARCHAR2(100);
2871 l_contract_end_date DATE;
2872 l_sysdate DATE := SYSDATE;
2873 l_calculate_sdd BOOLEAN := TRUE;
2874 l_non_null_line_id EXCEPTION;
2875 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'contract_remaining_sec_dep';
2876 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2877 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2878 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2879
2880 --smoduga added variables for userdefined streams 3925469
2881 lx_sty_id NUMBER;
2882
2883 BEGIN
2884 IF (is_debug_procedure_on) THEN
2885 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
2886 END IF;
2887 IF (is_debug_statement_on) THEN
2888 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_contract_id: '||p_contract_id);
2889 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_contract_line_id: '||p_contract_line_id);
2890 END IF;
2891
2892 IF p_contract_line_id IS NOT NULL THEN
2893 -- Security Deposit is calculated on Header Level
2894 RAISE l_non_null_line_id;
2895 END IF;
2896
2897 -- *************************
2898 -- Get Security Deposit Rule
2899 -- *************************
2900
2901 IF (is_debug_statement_on) THEN
2902 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_rule_record');
2903 END IF;
2904 okl_am_util_pvt.get_rule_record (
2905 p_rgd_code => 'LASDEP',
2906 p_rdf_code => 'LASDEP',
2907 p_chr_id => p_contract_id,
2908 p_cle_id => NULL,
2909 x_rulv_rec => l_rulv_rec,
2910 x_return_status => l_return_status,
2911 p_message_yn => FALSE);
2912 IF (is_debug_statement_on) THEN
2913 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_rule_record , return status: ' || l_return_status);
2914 END IF;
2915
2916 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
2917
2918 OPEN l_contract_csr (p_contract_id);
2919 FETCH l_contract_csr INTO l_contract_end_date;
2920 CLOSE l_contract_csr;
2921
2922 l_held_until_maturity := l_rulv_rec.rule_information2;
2923
2924 OPEN l_date_format_csr;
2925 FETCH l_date_format_csr INTO l_date_format;
2926 CLOSE l_date_format_csr;
2927
2928 /* -- rmunjulu bug 4341480
2929 -- Security Deposit is hold till pre-defined date
2930 l_held_until_date := to_date (
2931 l_rulv_rec.rule_information5, l_date_format);
2932 */
2933
2934 l_held_until_date := FND_DATE.CANONICAL_TO_DATE(l_rulv_rec.rule_information5); -- rmunjulu bug 4341480
2935
2936 IF l_held_until_date IS NULL
2937 OR l_held_until_date = G_MISS_DATE THEN
2938 -- Security Deposit is hold until maturity
2939 IF l_held_until_maturity = 'Y' THEN
2940 l_held_until_date := l_contract_end_date;
2941 END IF;
2942 END IF;
2943
2944 IF l_held_until_date IS NOT NULL
2945 AND l_held_until_date <> G_MISS_DATE THEN
2946 -- Can not release Security Deposit
2947 -- BEGIN rmunjulu bug 4341480 --
2948
2949
2950 IF trunc(l_held_until_date) < trunc(l_sysdate) THEN
2951 -- END rmunjulu bug 4341480 --
2952 --IF l_held_until_date < l_sysdate THEN
2953 l_calculate_sdd := FALSE;
2954 END IF;
2955
2956 END IF;
2957
2958 END IF;
2959
2960 -- ************************************************
2961 -- Get original security deposit minus credit memos
2962 -- ************************************************
2963
2964 IF l_calculate_sdd THEN
2965
2966 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
2967 IF (is_debug_statement_on) THEN
2968 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_STREAMS_UTIL.get_primary_stream_type');
2969 END IF;
2970 OKL_STREAMS_UTIL.get_primary_stream_type(p_contract_id,
2971 'SECURITY_DEPOSIT',
2972 l_return_status,
2973 lx_sty_id);
2974 IF (is_debug_statement_on) THEN
2975 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_STREAMS_UTIL.get_primary_stream_type , return status: ' || l_return_status);
2976 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'lx_sty_id : ' || lx_sty_id);
2977 END IF;
2978 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
2979
2980 FOR l_sdd_stream_rec IN l_sdd_stream_csr (p_contract_id,lx_sty_id)
2981 LOOP
2982
2983 l_old_cm_amount := 0;
2984 l_cnt := l_cnt + 1;
2985
2986 FOR l_cm_rec IN l_credit_memo_csr (l_sdd_stream_rec.tld_id) LOOP
2987 l_cnt2 := l_cnt2 + 1;
2988 l_old_cm_amount := l_old_cm_amount +
2989 NVL (l_cm_rec.amount, 0);
2990 l_tld_tbl(l_cnt2).inv_tld_id := l_sdd_stream_rec.tld_id;
2991 l_tld_tbl(l_cnt2).cm_tld_id := l_cm_rec.id;
2992 END LOOP;
2993
2994 l_sdd_tbl(l_cnt).lsm_id := l_sdd_stream_rec.lsm_id;
2995 l_sdd_tbl(l_cnt).tld_id := l_sdd_stream_rec.tld_id;
2996 -- Add total Sec Dep to negative CMs
2997 l_sdd_tbl(l_cnt).amount := NVL (l_sdd_stream_rec.amount, 0) +
2998 NVL (l_old_cm_amount, 0);
2999 l_total_amount := l_total_amount +
3000 l_sdd_tbl(l_cnt).amount;
3001
3002 END LOOP;
3003
3004 END IF;
3005
3006 x_sdd_tbl := l_sdd_tbl;
3007 x_tld_tbl := l_tld_tbl;
3008 x_total_amount := l_total_amount;
3009 IF (is_debug_procedure_on) THEN
3010 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
3011 END IF;
3012
3013 EXCEPTION
3014
3015 WHEN OTHERS THEN
3016 IF (is_debug_exception_on) THEN
3017 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
3018 || sqlcode || ' , SQLERRM : ' || sqlerrm);
3019 END IF;
3020
3021 -- Close open cursors
3022
3023 IF l_date_format_csr%ISOPEN THEN
3024 CLOSE l_date_format_csr;
3025 END IF;
3026
3027 IF l_contract_csr%ISOPEN THEN
3028 CLOSE l_contract_csr;
3029 END IF;
3030
3031 IF l_sdd_stream_csr%ISOPEN THEN
3032 CLOSE l_sdd_stream_csr;
3033 END IF;
3034
3035 IF l_credit_memo_csr%ISOPEN THEN
3036 CLOSE l_credit_memo_csr;
3037 END IF;
3038
3039 -- store SQL error message on message stack for caller
3040
3041 OKL_API.SET_MESSAGE (
3042 p_app_name => OKL_API.G_APP_NAME,
3043 p_msg_name => 'OKL_CONTRACTS_UNEXPECTED_ERROR',
3044 p_token1 => 'SQLCODE',
3045 p_token1_value => SQLCODE,
3046 p_token2 => 'SQLERRM',
3047 p_token2_value => SQLERRM);
3048
3049 END contract_remaining_sec_dep;
3050
3051
3052 -- Start of comments
3053 --
3054 -- Procedure Name : Create_Scrt_Dpst_Dsps_Inv
3055 -- Description : Create Credit Memo for Security Deposit Disposition
3056 -- Business Rules :
3057 -- Parameters : quote id
3058 -- Version : 1.0
3059 -- History : RMUNJULU 11-FEB-03 2793710 changed code to raise excpt when
3060 -- sec dep disp amt not found or less than disp amt
3061 -- End of comments
3062
3063 PROCEDURE Create_Scrt_Dpst_Dsps_Inv (
3064 p_api_version IN NUMBER,
3065 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3066 x_msg_count OUT NOCOPY NUMBER,
3067 x_msg_data OUT NOCOPY VARCHAR2,
3068 x_return_status OUT NOCOPY VARCHAR2,
3069 p_contract_id IN NUMBER,
3070 p_contract_line_id IN NUMBER DEFAULT NULL,
3071 p_dispose_amount IN NUMBER DEFAULT NULL,
3072 p_quote_id IN NUMBER DEFAULT NULL, --akrangan added for bug 7036873
3073 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
3074
3075 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3076 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3077
3078 l_api_name CONSTANT VARCHAR2(30) :=
3079 'Create_Scrt_Dpst_Dsps_Inv';
3080 l_api_version CONSTANT NUMBER := G_API_VERSION;
3081 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
3082 l_msg_data VARCHAR2(2000);
3083
3084 l_taiv_rec taiv_rec_type;
3085 l_taiv_tbl taiv_tbl_type;
3086 l_cnt NUMBER := 0;
3087 l_dispose_amount NUMBER;
3088 l_sdd_tbl sdd_tbl_type;
3089 l_tld_tbl tld_tbl_type;
3090 l_total_amount NUMBER;
3091 l_tai_id NUMBER;
3092 l_description VARCHAR2(80);
3093 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_scrt_dpst_dsps_inv';
3094 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
3095 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
3096 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
3097
3098 -- RMUNJULU 11-FEB-03 2793710 Added exception variable
3099 l_exception_halt_validation EXCEPTION;
3100 --added by akrangan for bug
3101 l_transaction_source VARCHAR2(80);
3102 CURSOR c_set_trn_src
3103 IS
3104 SELECT 'TERMINATION_QUOTE' transaction_source
3105 FROM OKL_TRX_QUOTES_B qte
3106 WHERE qte.id = p_quote_id; --akrangan modified for bug 7036873
3107
3108 BEGIN
3109 IF (is_debug_procedure_on) THEN
3110 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
3111 END IF;
3112 IF (is_debug_statement_on) THEN
3113 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_contract_id: '||p_contract_id);
3114 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_contract_line_id: '||p_contract_line_id);
3115 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_dispose_amount: '||p_dispose_amount);
3116 END IF;
3117
3118 -- ***************************************************************
3119 -- Check API version, initialize message list and create savepoint
3120 -- ***************************************************************
3121
3122 l_return_status := OKL_API.START_ACTIVITY (
3123 l_api_name,
3124 G_PKG_NAME,
3125 p_init_msg_list,
3126 l_api_version,
3127 p_api_version,
3128 '_PVT',
3129 x_return_status);
3130
3131 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
3132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3133 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
3134 RAISE OKL_API.G_EXCEPTION_ERROR;
3135 END IF;
3136
3137 -- *******************
3138 -- Validate parameters
3139 -- *******************
3140
3141 IF p_contract_id IS NULL
3142 OR p_contract_id = G_MISS_NUM THEN
3143
3144 OKC_API.SET_MESSAGE (
3145 p_app_name => G_OKC_APP_NAME,
3146 p_msg_name => 'OKC_NO_PARAMS',
3147 p_token1 => 'PARAM',
3148 p_token1_value => 'CONTRACT_ID',
3149 p_token2 => 'PROCESS',
3150 p_token2_value => l_api_name);
3151
3152 RAISE OKL_API.G_EXCEPTION_ERROR;
3153
3154 END IF;
3155
3156 -- ***************************
3157 -- Calculate amount to dispose
3158 -- ***************************
3159
3160 contract_remaining_sec_dep (
3161 p_contract_id => p_contract_id,
3162 p_contract_line_id => p_contract_line_id,
3163 x_sdd_tbl => l_sdd_tbl,
3164 x_tld_tbl => l_tld_tbl,
3165 x_total_amount => l_total_amount);
3166 IF (is_debug_statement_on) THEN
3167 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called contract_remaining_sec_dep , l_total_amount : ' || l_total_amount);
3168 END IF;
3169
3170 -- RMUNJULU 11-FEB-03 2793710 Raised halt validation if remaining sec dep = 0
3171 IF l_total_amount IS NULL
3172 OR l_total_amount = 0
3173 OR l_total_amount = G_MISS_NUM THEN
3174
3175
3176 -- There is no security deposit disposition amount remaining for the contract.
3177 OKL_API.set_message (
3178 p_app_name => 'OKL',
3179 p_msg_name => 'OKL_AM_INVALID_DEP_AMT');
3180
3181 -- Raise halt validation so as not to set return status to E
3182 RAISE l_exception_halt_validation;
3183
3184 END IF;
3185
3186 -- RMUNJULU 11-FEB-03 2793710 Raised halt validation if remaining sec dep < line amt
3187 IF ABS (p_dispose_amount) > ABS (l_total_amount) THEN
3188
3189
3190 -- The remaining security deposit disposition amount for the contract is less than
3191 -- the disposition amount specified.
3192 OKL_API.set_message (
3193 p_app_name => 'OKL',
3194 p_msg_name => 'OKL_AM_INVALID_INV_AMT');
3195
3196 -- Raise halt validation so as not to set return status to E
3197 RAISE l_exception_halt_validation;
3198
3199 END IF;
3200
3201 IF p_dispose_amount IS NULL
3202 OR p_dispose_amount = G_MISS_NUM
3203 OR p_dispose_amount = 0 THEN
3204 l_total_amount := abs (l_total_amount);
3205 ELSE
3206 l_total_amount := abs (p_dispose_amount);
3207 END IF;
3208
3209 -- ******************
3210 -- Create Credit Memo
3211 -- ******************
3212
3213 l_description := okl_am_util_pvt.get_lookup_meaning
3214 ('OKL_QUOTE_LINE_TYPE','AMCSDD');
3215
3216 FOR i IN l_sdd_tbl.FIRST..l_sdd_tbl.LAST LOOP
3217
3218 IF abs (l_sdd_tbl(i).amount) > l_total_amount THEN
3219 l_dispose_amount := - l_total_amount;
3220 ELSE
3221 l_dispose_amount := - abs (l_sdd_tbl(i).amount);
3222 END IF;
3223
3224 -- Add negative dispose amount
3225 l_total_amount := l_total_amount + l_dispose_amount;
3226
3227 IF NVL (l_dispose_amount, 0) < 0 THEN
3228 --akrangan added for bug begin
3229 OPEN c_set_trn_src;
3230 FETCH c_set_trn_src INTO l_transaction_source;
3231 CLOSE c_set_trn_src;
3232 --akrangan added for bug end
3233 -- rmunjuluu bug 4341480 okl_credit_memo_pub.insert_request (
3234 IF (is_debug_statement_on) THEN
3235 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_credit_memo_pub.insert_on_acc_cm_request');
3236 END IF;
3237 okl_credit_memo_pub.insert_on_acc_cm_request (
3238 p_api_version => l_api_version,
3239 p_init_msg_list => OKL_API.G_FALSE,
3240 x_return_status => l_return_status,
3241 x_msg_count => l_msg_count,
3242 x_msg_data => l_msg_data,
3243 p_tld_id => l_sdd_tbl(i).tld_id, -- ansethur 03/02/2007 Added For Billing Architecture Project
3244 -- p_tld_id => l_sdd_tbl(i).tld_id, -- ansethur 03/02/2007 Commmented For Billing Architecture Project
3245 p_credit_amount => l_dispose_amount,
3246 p_credit_desc => l_description,
3247 x_tai_id => l_tai_id,
3248 x_taiv_rec => l_taiv_rec
3249 ,p_transaction_source => l_transaction_source);
3250 IF (is_debug_statement_on) THEN
3251 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_credit_memo_pub.insert_on_acc_cm_request , return status: ' || l_return_status);
3252 END IF;
3253
3254 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3255 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3256 l_overall_status := l_return_status;
3257 END IF;
3258 ELSE
3259 l_cnt := l_cnt + 1;
3260 l_taiv_tbl(l_cnt) := l_taiv_rec;
3261 END IF;
3262
3263 END IF;
3264
3265 END LOOP;
3266
3267 -- **************
3268 -- Return results
3269 -- **************
3270
3271 x_taiv_tbl := l_taiv_tbl;
3272 x_return_status := l_overall_status;
3273
3274 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
3275 IF (is_debug_procedure_on) THEN
3276 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
3277 END IF;
3278
3279 EXCEPTION
3280
3281 -- RMUNJULU 11-FEB-03 2793710 Raised halt validation so as not to set to E or U
3282 WHEN l_exception_halt_validation THEN
3283 IF (is_debug_exception_on) THEN
3284 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'l_exception_halt_validation');
3285 END IF;
3286
3287 NULL;
3288
3289 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3290 IF (is_debug_exception_on) THEN
3291 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
3292 END IF;
3293 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3294 (
3295 l_api_name,
3296 G_PKG_NAME,
3297 'OKL_API.G_RET_STS_ERROR',
3298 x_msg_count,
3299 x_msg_data,
3300 '_PVT'
3301 );
3302
3303 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3304 IF (is_debug_exception_on) THEN
3305 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
3306 END IF;
3307 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3308 (
3309 l_api_name,
3310 G_PKG_NAME,
3311 'OKL_API.G_RET_STS_UNEXP_ERROR',
3312 x_msg_count,
3313 x_msg_data,
3314 '_PVT'
3315 );
3316
3317 WHEN OTHERS THEN
3318 IF (is_debug_exception_on) THEN
3319 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
3320 || sqlcode || ' , SQLERRM : ' || sqlerrm);
3321 END IF;
3322
3323 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3324 (
3325 l_api_name,
3326 G_PKG_NAME,
3327 'OTHERS',
3328 x_msg_count,
3329 x_msg_data,
3330 '_PVT'
3331 );
3332
3333 END Create_Scrt_Dpst_Dsps_Inv;
3334
3335
3336 -- Start of comments
3337 --
3338 -- Procedure Name : Validate_Populate_Quote
3339 -- Description : Ensure quote exists
3340 -- Populates all header fields
3341 -- Populates all line fields
3342 -- Business Rules :
3343 -- Parameters : asset return fields
3344 -- Version : 1.0
3345 -- History : RMUNJULU 30-DEC-02 2726739 Changed cursor and taiv_rec
3346 -- to set currency columns
3347 -- : RMUNJULU 30-DEC-02 2726739 Added code to set currency columns
3348 -- when updating credit memos for security deposit dispositions
3349 -- : RMUNJULU 11-FEB-03 2793710 Added code to check if tbl has recs
3350 -- : rmunjulu EDAT Added code to not do processing of billing adjustment
3351 -- quote lines if partial terminations and
3352 -- When full term and billing adjustment then use original stream type id
3353 -- : rmunjulu EDAT Ignore above comments
3354 -- Do billing adjustment processing for full termination and then
3355 -- CALL BPD API to do disbursements
3356 -- : rmunjulu EDAT 09-Nov-04 Modified to get proper total neg amount
3357 -- : rmunjulu EDAT 07-DEC-04 Set pos and neg amts properly
3358 -- : rmunjulu 4056364 09-DEC-04 Modified to not set Quote Line Allocation message
3359 -- : PAGARG 4044659 25-Jan-2005 Obtain inventory org id from
3360 -- contract and assign it to tilv_rec if LEASE_INV_ORG_YN
3361 -- is Y in OKL_SYSTEM_PARAMS_ALL
3362 -- : rmunjulu 27-Jan-05 3985369 Modified to set inv_org_id
3363 -- when creating transaction for billing adjustment line
3364 -- : rmunjulu 3985369 Modified get_qte_dtls_csr cursor
3365 -- : rmunjulu sales tax enhancement, set quote line id in tilv_rec/tilv_tbl
3366 -- NOTE ::: Cannot set quote_line_id for billing adjustment quote lines
3367 -- as they are recalculated during invoicing
3368 -- : rmunjulu 4547765 Added code to handle Future dated billing adjustments
3369 -- : RMUNJULU LOANS_ENHANCEMENTS Bill PERDIEM_AMOUNT
3370 -- : SECHAWLA 30-DEC-05 4917391 Create Invoice for quote per-diem if the amount is positive
3371 -- Create Credit Memo for quote per-diem if the amount is negative
3372 -- : SECHAWLA 05-JAN-06 4926740 For prior dated termination quotes, no. of days for per-diem
3373 -- calculation should be (acceptance date - creation date) and not
3374 -- (acceptance date - quote effective date)
3375 -- End of comments
3376
3377 PROCEDURE Validate_Populate_Quote (
3378 p_quote_id IN NUMBER,
3379 x_pos_amount OUT NOCOPY NUMBER,
3380 x_neg_amount OUT NOCOPY NUMBER,
3381 x_taiv_tbl OUT NOCOPY taiv_tbl_type,
3382 x_tilv_tbl OUT NOCOPY tilv_tbl_type,
3383 x_sdd_taiv_tbl OUT NOCOPY taiv_tbl_type,
3384 x_return_status OUT NOCOPY VARCHAR2) IS
3385
3386 -- Returns Quote Header
3387 -- RMUNJULU 30-DEC-02 2726739 -- Added columns for multi-currency get values
3388 -- from trx_quotes
3389 CURSOR l_qte_csr (cp_quote_id IN NUMBER) IS
3390 SELECT qte.khr_id khr_id,
3391 qte.qtp_code qtp_code,
3392 flo.meaning description,
3393 qte.date_accepted date_invoiced,
3394 qte.currency_code currency_code,
3395 qte.currency_conversion_type currency_conversion_type,
3396 qte.currency_conversion_rate currency_conversion_rate,
3397 qte.currency_conversion_date currency_conversion_date
3398 --PAGARG Bug 4044659 Query inventory org id
3399 ,khr.inv_organization_id inv_organization_id,
3400 qte.art_id -- RRAVIKIR Legal Entity Changes
3401 FROM okl_trx_quotes_b qte,
3402 fnd_lookups flo,
3403 okc_k_headers_b khr
3404 WHERE qte.id = cp_quote_id
3405 AND flo.lookup_type = 'OKL_QUOTE_TYPE'
3406 AND flo.lookup_code = qte.qtp_code
3407 AND khr.id = qte.khr_id;
3408
3409 -- Returns Quote Recipients
3410 CURSOR l_qpt_csr (cp_quote_id IN NUMBER) IS
3411 SELECT kpr.rle_code rle_code,
3412 qpt.cpl_id cpl_id,
3413 qpt.qpt_code qpt_code,
3414 qpt.party_jtot_object1_code party_code,
3415 qpt.party_object1_id1 party_id1,
3416 qpt.party_object1_id2 party_id2,
3417 qpt.allocation_percentage allc_perc
3418 FROM okl_quote_parties qpt,
3419 okc_k_party_roles_b kpr
3420 WHERE qpt.qte_id = cp_quote_id
3421 AND qpt.qpt_code IN ('RECIPIENT','RECIPIENT_ADDITIONAL')
3422 AND kpr.id (+) = qpt.cpl_id
3423 AND qpt.allocation_percentage <> 0; -- rmunjulu bug 4341480
3424
3425
3426 -- Returns Quote Lines
3427 CURSOR l_qlt_csr (cp_quote_id IN NUMBER) IS
3428 SELECT qlt.kle_id kle_id,
3429 qlt.amount amount,
3430 qlt.line_number line_number,
3431 qlt.sty_id sty_id,
3432 qlt.qlt_code qlt_code,
3433 flo.meaning description,
3434 qlt.id quote_line_id -- rmunjulu sales_tax_enhancement
3435 FROM okl_txl_quote_lines_b qlt,
3436 fnd_lookups flo
3437 WHERE qlt.qte_id = cp_quote_id
3438 AND qlt.amount NOT IN (G_MISS_NUM, 0)
3439 AND flo.lookup_type = 'OKL_QUOTE_LINE_TYPE'
3440 AND flo.lookup_code = qlt.qlt_code
3441 AND qlt.qlt_code NOT IN (
3442 'BILL_ADJST', -- rmunjulu EDAT Added since billing adjustments will be handled separately
3443 'AMCFIA', -- Used to save quote assets, not amounts
3444 'AMCTAX', -- Estimated tax, AR will recalculate tax
3445 'AMYOUB'); -- Outstanding balances are already billed
3446
3447 -- Returns previously billed records
3448 CURSOR l_tai_csr (cp_quote_id IN NUMBER) IS
3449 SELECT tai.id
3450 FROM okl_trx_ar_invoices_v tai
3451 WHERE tai.qte_id = cp_quote_id;
3452
3453 -- rmunjulu EDAT -- get if quote partial
3454 -- rmunjulu 3985369 -- Modified to get contract inv org
3455 CURSOR get_qte_dtls_csr (p_quote_id IN NUMBER) IS
3456 SELECT upper(nvl(qte.partial_yn,'N')) partial_yn,
3457 qte.khr_id khr_id,
3458 qte.date_effective_from date_eff_from,
3459 QTE.DATE_ACCEPTED DATE_ACCEPTED, -- RMUNJULU 4547765 FUTURE_BILLS_BUG
3460 qte.creation_date creation_date, --SECHAWLA 05-JAN-05 4926740 : added creation_date
3461 chr.inv_organization_id inv_organization_id,
3462 QTE.perdiem_amount -- rmunjulu LOANS_ENHACEMENTS
3463 FROM OKL_TRX_QUOTES_B qte,
3464 OKC_K_HEADERS_B chr
3465 WHERE qte.id = p_quote_id
3466 AND qte.khr_id = chr.id;-- rmunjulu 3985369
3467
3468 -- rmunjulu EDAT -- Get the quote line meaning for BILL_ADJST
3469 CURSOR get_qte_ln_meaning_csr IS
3470 SELECT fnd.meaning meaning
3471 FROM FND_LOOKUPS fnd
3472 WHERE fnd.lookup_type = 'OKL_QUOTE_LINE_TYPE'
3473 AND fnd.lookup_code = 'BILL_ADJST';
3474
3475 l_api_version CONSTANT NUMBER := G_API_VERSION;
3476 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
3477 l_msg_data VARCHAR2(2000);
3478 l_cnt NUMBER;
3479 l_seq NUMBER;
3480
3481 l_qte_rec l_qte_csr%ROWTYPE;
3482 l_qlt_rec l_qlt_csr%ROWTYPE;
3483 l_tai_rec l_tai_csr%ROWTYPE;
3484 l_taiv_rec taiv_rec_type;
3485 l_r_taiv_tbl taiv_tbl_type;
3486 l_tilv_rec tilv_rec_type;
3487 l_tilv_tbl tilv_tbl_type;
3488
3489 -- SDD related variables
3490 l_taiv_tbl taiv_tbl_type;
3491 lu_taiv_rec taiv_rec_type;
3492 lx_taiv_rec taiv_rec_type;
3493
3494 l_pos_amount NUMBER := 0;
3495 l_neg_amount NUMBER := 0;
3496
3497 l_allc_total NUMBER := 0; -- Total allocated
3498 l_no_allc NUMBER := 0; -- Recipients without allocation
3499
3500 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3501 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3502 l_amount_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3503 l_stream_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3504 l_alloc_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3505 l_sdd_invoice BOOLEAN := FALSE; --added by veramach for bug#6766479
3506 -- rmunjulu EDAT
3507 l_partial_yn VARCHAR2(3);
3508 l_khr_id NUMBER;
3509 l_date_eff_from DATE;
3510 l_line_number NUMBER;
3511 l_input_tbl OKL_BPD_TERMINATION_ADJ_PVT.input_tbl_type;
3512 lx_baj_tbl OKL_BPD_TERMINATION_ADJ_PVT.baj_tbl_type;
3513 l_meaning VARCHAR2(300);
3514
3515 -- smoduga UDS
3516 lx_sty_id NUMBER;
3517 -- rmunjulu Bug 4056364
3518 l_dummy_status VARCHAR2(3);
3519
3520 --PAGARG Bug 4044659 Cursor to obtain operational options values
3521 CURSOR l_sys_prms_csr IS
3522 SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
3523 FROM OKL_SYSTEM_PARAMS;
3524 l_sys_prms_rec l_sys_prms_csr%ROWTYPE;
3525
3526 -- rmunjulu 3985369
3527 l_inv_org_id NUMBER;
3528
3529 l_quote_accpt_date DATE; -- RMUNJULU 4547765 FUTURE_BILLS_BUG
3530 -- akrangan - BUg#5521354 - Added - Start
3531 l_future_invoices_exists VARCHAR2(3);
3532 -- akrangan - BUg#5521354 - Added - End
3533
3534 l_creation_date DATE; --SECHAWLA 05-JAN-05 4926740
3535
3536 -- rmunjulu LOANS_ENHACEMENTS
3537 l_perdiem_amt NUMBER;
3538 l_perdiem_sty_id NUMBER;
3539 l_noofdays NUMBER;
3540 l_refund_sty_id NUMBER;
3541 l_loan_refund_amount NUMBER;
3542
3543 l_regular_qte_line VARCHAR2(3); -- rmunjulu bug 4341480
3544
3545
3546 -- RRAVIKIR Legal Entity Changes
3547 CURSOR l_assetreturn_csr (cp_id IN NUMBER) IS
3548 SELECT legal_entity_id
3549 FROM okl_asset_returns_all_b
3550 WHERE id = cp_id;
3551
3552 l_legal_entity_id NUMBER;
3553
3554 -- Legal Entity Changes End
3555 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'validate_populate_quote';
3556 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
3557 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
3558 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
3559
3560 --bug#6766479 veramach start
3561 CURSOR check_cont_typ (cp_khr_id IN NUMBER) IS
3562 SELECT ORIG_SYSTEM_SOURCE_CODE
3563 FROM OKC_K_HEADERS_B
3564 WHERE id = cp_khr_id;
3565
3566 l_cont_typ VARCHAR2(30);
3567
3568 --bug#6766479 veramach end.
3569
3570 BEGIN
3571 IF (is_debug_procedure_on) THEN
3572 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
3573 END IF;
3574 IF (is_debug_statement_on) THEN
3575 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_quote_id: '|| p_quote_id);
3576 END IF;
3577
3578 -- ***************************************************
3579 -- Check that no records have been previously invoiced
3580 -- ***************************************************
3581
3582 OPEN l_tai_csr (p_quote_id);
3583 FETCH l_tai_csr INTO l_tai_rec;
3584
3585 IF l_tai_csr%FOUND THEN
3586 l_return_status := OKL_API.G_RET_STS_ERROR;
3587 -- Message Text: Unable to process request for
3588 -- a Invoice creation. Invoice(s) already exist
3589 OKL_API.SET_MESSAGE (
3590 p_app_name => G_APP_NAME,
3591 p_msg_name => 'OKL_AM_INVOICES_EXIST');
3592 END IF;
3593
3594 CLOSE l_tai_csr;
3595
3596 -- ***********************
3597 -- Get Quote Header Record
3598 -- ***********************
3599
3600 OPEN l_qte_csr (p_quote_id);
3601 FETCH l_qte_csr INTO l_qte_rec;
3602
3603 IF l_qte_csr%NOTFOUND THEN
3604 l_return_status := OKL_API.G_RET_STS_ERROR;
3605 OKC_API.SET_MESSAGE (
3606 p_app_name => G_OKC_APP_NAME,
3607 p_msg_name => G_INVALID_VALUE,
3608 p_token1 => G_COL_NAME_TOKEN,
3609 p_token1_value => 'Quote_Id');
3610
3611 ELSE
3612 l_taiv_rec.khr_id := l_qte_rec.khr_id;
3613 l_taiv_rec.description := l_qte_rec.description;
3614 l_taiv_rec.currency_code := l_qte_rec.currency_code;
3615 l_taiv_rec.date_invoiced := l_qte_rec.date_invoiced;
3616 l_taiv_rec.qte_id := p_quote_id;
3617 -- ansethur 05-jun-07 R12B Billing Architecture
3618 l_taiv_rec.OKL_SOURCE_BILLING_TRX :='TERMINATION_QUOTE';
3619
3620 -- RMUNJULU 30-DEC-02 2726739 -- Added for multi-currency
3621 l_taiv_rec.currency_conversion_type := l_qte_rec.currency_conversion_type;
3622 l_taiv_rec.currency_conversion_rate := l_qte_rec.currency_conversion_rate;
3623 l_taiv_rec.currency_conversion_date := l_qte_rec.currency_conversion_date;
3624
3625 -- RRAVIKIR Legal Entity Changes
3626 IF (l_qte_rec.qtp_code LIKE 'REP%') THEN
3627 OPEN l_assetreturn_csr(cp_id => l_qte_rec.art_id);
3628 FETCH l_assetreturn_csr INTO l_legal_entity_id;
3629 CLOSE l_assetreturn_csr;
3630
3631 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
3632 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
3633 p_msg_name => g_required_value,
3634 p_token1 => g_col_name_token,
3635 p_token1_value => 'legal_entity_id');
3636 RAISE OKC_API.G_EXCEPTION_ERROR;
3637 END IF;
3638
3639 l_taiv_rec.legal_entity_id := l_legal_entity_id;
3640 END IF;
3641 -- Legal Entity Changes End
3642
3643
3644 END IF;
3645
3646 CLOSE l_qte_csr;
3647
3648 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3649 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3650 l_overall_status := l_return_status;
3651 END IF;
3652 END IF;
3653
3654 -- ***************************
3655 -- Get Quote Recipient Records
3656 -- ***************************
3657
3658 l_cnt := 0;
3659
3660 FOR l_qpt_rec IN l_qpt_csr (p_quote_id) LOOP
3661
3662 l_cnt := l_cnt + 1;
3663 l_r_taiv_tbl(l_cnt) := l_taiv_rec;
3664
3665 IF l_qpt_rec.allc_perc IS NOT NULL
3666 AND l_qpt_rec.allc_perc <> G_MISS_NUM
3667 AND l_qpt_rec.allc_perc BETWEEN 0 AND 100 THEN
3668 l_r_taiv_tbl(l_cnt).amount := l_qpt_rec.allc_perc;
3669 l_allc_total := l_allc_total + l_qpt_rec.allc_perc;
3670 ELSIF l_qpt_rec.allc_perc < 0
3671 OR l_qpt_rec.allc_perc > 100 THEN
3672 l_alloc_status := OKL_API.G_RET_STS_ERROR;
3673 ELSE
3674 l_no_allc := l_no_allc + 1;
3675 END IF;
3676
3677 -- Bill Vendor from Vendor Program for Repurchase Quote
3678 IF l_qte_rec.qtp_code = 'REP_STANDARD' THEN
3679
3680 -- Get Customer and Bill_To linked to a Vendor
3681 Get_Vendor_Billing_Info (
3682 px_taiv_rec => l_r_taiv_tbl(l_cnt),
3683 x_return_status => l_return_status);
3684
3685 -- Bill Vendor attached as a Party Role to Lease Contract
3686 ELSIF l_qpt_rec.rle_code = 'OKL_VENDOR' THEN
3687
3688 -- Get Customer and Bill_To linked to a Vendor
3689 Get_Vendor_Billing_Info (
3690 p_cpl_id => l_qpt_rec.cpl_id,
3691 px_taiv_rec => l_r_taiv_tbl(l_cnt),
3692 x_return_status => l_return_status);
3693
3694 -- Bill Lessee
3695 ELSIF l_qpt_rec.rle_code = 'LESSEE' THEN
3696
3697 -- BPD derives billing info using KHR_ID
3698 l_return_status := OKL_API.G_RET_STS_SUCCESS;
3699
3700 -- Unidentified Party
3701 ELSE
3702
3703 l_return_status := OKL_API.G_RET_STS_ERROR;
3704 -- Message Text: Invalid value for the column Allocation Percentage
3705 OKL_API.SET_MESSAGE (
3706 p_app_name => G_OKC_APP_NAME,
3707 p_msg_name => G_INVALID_VALUE,
3708 p_token1 => G_COL_NAME_TOKEN,
3709 p_token1_value => 'Quote Recipient');
3710
3711 END IF;
3712
3713 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3714 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3715 l_overall_status := l_return_status;
3716 END IF;
3717 END IF;
3718
3719 END LOOP;
3720
3721 IF l_r_taiv_tbl.COUNT = 0 THEN
3722 l_taiv_rec.amount := 100; -- Allocate everything to Lessee
3723 l_r_taiv_tbl(1) := l_taiv_rec;
3724 ELSIF l_allc_total > 100
3725 OR (l_allc_total = 100 AND l_no_allc > 0)
3726 OR (l_allc_total < 100 AND l_no_allc = 0) THEN
3727 l_alloc_status := OKL_API.G_RET_STS_ERROR;
3728 ELSIF (l_allc_total < 100 AND l_no_allc > 0) THEN
3729 -- Divide the rest equally
3730 FOR i IN l_r_taiv_tbl.FIRST..l_r_taiv_tbl.LAST LOOP
3731 IF l_r_taiv_tbl(i).amount IS NULL
3732 OR l_r_taiv_tbl(i).amount = G_MISS_NUM THEN
3733 l_r_taiv_tbl(i).amount := (100 - l_allc_total) / l_no_allc;
3734 END IF;
3735 END LOOP;
3736 END IF;
3737
3738 IF l_alloc_status <> OKL_API.G_RET_STS_SUCCESS THEN
3739 l_return_status := OKL_API.G_RET_STS_ERROR;
3740 -- Message Text: Invalid value for the column Allocation Percentage
3741 OKL_API.SET_MESSAGE (
3742 p_app_name => G_OKC_APP_NAME,
3743 p_msg_name => G_INVALID_VALUE,
3744 p_token1 => G_COL_NAME_TOKEN,
3745 p_token1_value => 'Allocation Percentage');
3746 END IF;
3747
3748 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3749 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3750 l_overall_status := l_return_status;
3751 END IF;
3752 END IF;
3753
3754 -- **********************
3755 -- Get Quote Line Records
3756 -- **********************
3757
3758 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
3759 -- if quote is found
3760 --PAGARG Bug 4044659 Open and fetch values from l_sys_prms_csr
3761 OPEN l_sys_prms_csr;
3762 FETCH l_sys_prms_csr INTO l_sys_prms_rec;
3763 -- IF no row fetched from cursor then set the value as N for LEASE_INV_ORG_YN
3764 IF l_sys_prms_csr%NOTFOUND
3765 THEN
3766 l_sys_prms_rec.LEASE_INV_ORG_YN := 'N';
3767 END IF;
3768
3769 CLOSE l_sys_prms_csr;
3770
3771 FOR l_qlt_rec IN l_qlt_csr (p_quote_id) LOOP
3772
3773 IF l_qlt_rec.qlt_code = 'AMCSDD' THEN
3774
3775 l_sdd_invoice :=TRUE; --added by veramach for bug#6766479
3776 -- ****************************
3777 -- Security deposit disposition
3778 -- ****************************
3779
3780 -- RMUNJULU 11-FEB-03 2793710 added code to set the dispose amount
3781 l_tilv_rec.amount := l_qlt_rec.amount;
3782
3783 l_tilv_rec.qte_line_id := l_qlt_rec.quote_line_id; -- rmunjulu sales_tax_enhancement
3784
3785 Create_Scrt_Dpst_Dsps_Inv (
3786 p_api_version => l_api_version,
3787 p_init_msg_list => OKL_API.G_FALSE,
3788 x_return_status => l_return_status,
3789 x_msg_count => l_msg_count,
3790 x_msg_data => l_msg_data,
3791 p_contract_id => l_taiv_rec.khr_id,
3792 p_contract_line_id => NULL,
3793 p_dispose_amount => l_tilv_rec.amount,
3794 p_quote_id => l_taiv_rec.qte_id, --akrangan added for bug 7036873
3795 x_taiv_tbl => l_taiv_tbl);
3796 IF (is_debug_statement_on) THEN
3797 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_Scrt_Dpst_Dsps_Inv , return status: ' || l_return_status);
3798 END IF;
3799
3800 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3801 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3802 l_overall_status := l_return_status;
3803 END IF;
3804 END IF;
3805
3806 -- Update quote_id and save results
3807 l_seq := NVL (x_sdd_taiv_tbl.LAST, 0);
3808
3809 -- RMUNJULU 11-FEB-03 2793710 Added IF to check if tbl has recs
3810 IF l_taiv_tbl.COUNT > 0 THEN
3811
3812 l_cnt := l_taiv_tbl.FIRST;
3813
3814 LOOP
3815 lu_taiv_rec.id := l_taiv_tbl(l_cnt).id;
3816 --akrangan bug 6275650 fix start
3817 lu_taiv_rec.OKL_SOURCE_BILLING_TRX := l_taiv_tbl(l_cnt).OKL_SOURCE_BILLING_TRX;
3818 --akrangan bug 6275650 fix end
3819 lu_taiv_rec.qte_id := p_quote_id;
3820
3821 -- RMUNJULU 30-DEC-02 2726739 -- Added for multi-currency when doing SDD
3822 -- set the currency cols for the credit memos created
3823 lu_taiv_rec.currency_code := l_qte_rec.currency_code;
3824 lu_taiv_rec.currency_conversion_type := l_qte_rec.currency_conversion_type;
3825 lu_taiv_rec.currency_conversion_rate := l_qte_rec.currency_conversion_rate;
3826 lu_taiv_rec.currency_conversion_date := l_qte_rec.currency_conversion_date;
3827
3828
3829 IF (is_debug_statement_on) THEN
3830 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_trx_ar_invoices_pub.update_trx_ar_invoices');
3831 END IF;
3832 okl_trx_ar_invoices_pub.update_trx_ar_invoices (
3833 p_api_version => l_api_version,
3834 p_init_msg_list => OKL_API.G_FALSE,
3835 x_return_status => l_return_status,
3836 x_msg_count => l_msg_count,
3837 x_msg_data => l_msg_data,
3838 p_taiv_rec => lu_taiv_rec,
3839 x_taiv_rec => lx_taiv_rec);
3840 IF (is_debug_statement_on) THEN
3841 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_trx_ar_invoices_pub.update_trx_ar_invoices , return status: ' || l_return_status);
3842 END IF;
3843
3844 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3845 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
3846 l_overall_status := l_return_status;
3847 END IF;
3848 END IF;
3849
3850 l_seq := l_seq + 1;
3851 x_sdd_taiv_tbl(l_seq) := lx_taiv_rec;
3852
3853 EXIT WHEN (l_cnt = l_taiv_tbl.LAST);
3854 l_cnt := l_taiv_tbl.NEXT(l_cnt);
3855
3856 END LOOP;
3857
3858 END IF;
3859
3860 ELSE
3861
3862 l_regular_qte_line := 'Y'; -- rmunjulu bug 4341480
3863
3864 -- ***********************
3865 -- All non-SDD quote lines
3866 -- ***********************
3867
3868 -- rmunjulu EDAT Get if quote partial
3869 --OPEN get_partial_quote_yn_csr (p_quote_id);
3870 --FETCH get_partial_quote_yn_csr INTO l_partial_yn;
3871 --CLOSE get_partial_quote_yn_csr;
3872
3873 -- rmunjulu EDAT Added -- Do not do processing of billing adjustment
3874 -- quote lines if partial termination as rebook will take care of it
3875 --IF l_qlt_rec.qlt_code = 'BILL_ADJST' AND nvl(l_partial_yn,'N') = 'Y' THEN
3876 --null; -- no processing needed for billing adjustment quote lines if partial term
3877 --ELSE -- all other quote lines except AMCSDD and BILL_ADJST (only when partial)
3878 l_tilv_rec.line_number := l_qlt_rec.line_number;
3879 l_tilv_rec.kle_id := l_qlt_rec.kle_id;
3880 l_tilv_rec.description := l_qlt_rec.description;
3881 l_tilv_rec.amount := l_qlt_rec.amount;
3882
3883 l_tilv_rec.qte_line_id := l_qlt_rec.quote_line_id; -- rmunjulu sales_tax_enhancement
3884
3885 --PAGARG Bug 4044659 If LEASE_INV_ORG_YN is Y then set the value of
3886 --INVENTORY_ORG_ID in invoice line with contract inv_organization_id
3887 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y'
3888 THEN
3889 l_tilv_rec.inventory_org_id := l_qte_rec.inv_organization_id;
3890 END IF;
3891
3892 IF l_tilv_rec.amount > 0 THEN
3893 l_pos_amount := l_pos_amount + l_tilv_rec.amount;
3894 ELSIF l_tilv_rec.amount < 0 THEN
3895 l_neg_amount := l_neg_amount + l_tilv_rec.amount;
3896 ELSE
3897 l_amount_status := OKL_API.G_RET_STS_ERROR;
3898 END IF;
3899
3900 -- rmunjulu EDAT for Billing Adjustment the stream type will be same as original stream type
3901 --IF l_qlt_rec.qlt_code = 'BILL_ADJST' THEN
3902 --l_tilv_rec.sty_id := l_qlt_rec.sty_id;
3903 --ELSE -- all other quote lines
3904 -- smoduga +++++++++ User Defined Streams -- start ++++++++++++++++
3905 IF (is_debug_statement_on) THEN
3906 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_STREAMS_UTIL.get_primary_stream_type');
3907 END IF;
3908 OKL_STREAMS_UTIL.get_primary_stream_type(l_qte_rec.khr_id,
3909 l_qlt_rec.qlt_code,
3910 l_dummy_status, -- rmunjulu Bug 4056364 No need for this return status
3911 lx_sty_id);
3912 IF (is_debug_statement_on) THEN
3913 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_STREAMS_UTIL.get_primary_stream_type , return status: ' || l_return_status);
3914 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'lx_sty_id : ' || lx_sty_id);
3915 END IF;
3916
3917 l_tilv_rec.sty_id := lx_sty_id ; -- User Defined Streams
3918 -- smoduga +++++++++ User Defined Streams -- end ++++++++++++++++
3919 --END IF;
3920
3921 IF NVL (l_tilv_rec.sty_id, G_MISS_NUM) = G_MISS_NUM THEN
3922 -- Check if stream type is already assigned
3923 l_tilv_rec.sty_id := l_qlt_rec.sty_id;
3924 IF NVL (l_tilv_rec.sty_id, G_MISS_NUM) = G_MISS_NUM THEN
3925 l_stream_status := OKL_API.G_RET_STS_ERROR;
3926
3927 /* -- rmunjulu Bug 4056364 Do not set this message, message will be set by OKL_STREAMS_UTIL
3928 -- Stream Type is not setup in Quote Line Allocation Screen for QLT_CODE
3929 okl_am_util_pvt.set_message (
3930 p_app_name => G_APP_NAME
3931 ,p_msg_name => 'OKL_AM_NO_STREAM_TO_QUOTE'
3932 ,p_token1 => 'QLT_CODE'
3933 ,p_token1_value => l_qlt_rec.qlt_code);
3934 */
3935 END IF;
3936 END IF;
3937
3938 l_tilv_tbl(l_qlt_rec.line_number) := l_tilv_rec;
3939 --END IF; -- rmunjulu EDAT
3940 END IF;
3941
3942 END LOOP;
3943
3944 -- rmunjulu EDAT -- start ---------------- ++++++++++++++++++++++++++++
3945 -- Do processing for BILL_ADJST quote lines, get the billing adjustments
3946 -- again and create -ve invoices and do passthru disbursements too.
3947
3948 -- rmunjulu EDAT Get if quote dtls
3949 OPEN get_qte_dtls_csr (p_quote_id);
3950 FETCH get_qte_dtls_csr INTO l_partial_yn, l_khr_id, l_date_eff_from, l_quote_accpt_date,
3951 l_creation_date, --SECHAWLA 05-JAN-05 4926740 : added creation_date
3952 l_inv_org_id, l_perdiem_amt; -- rmunjulu 3985369 -- RMUNJULU 4547765 FUTURE_BILLS_BUG -- rmunjulu LOANS_ENHACEMENTS
3953 CLOSE get_qte_dtls_csr;
3954
3955 -- rmunjulu INVESTOR_DISB_ADJST
3956 -- akrangan - Bug#5521354 - Added -Start
3957 -- Check to ensure that the quote is a partial termination quote.
3958 -- This check is useful if there is partial termination quote created on a contract
3959 -- with one asset with one unit. This case is treated as full termination
3960 IF nvl(l_partial_yn,'N') = 'Y' THEN
3961
3962 -- need to check if no more assets
3963 IF (is_debug_statement_on) THEN
3964 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote');
3965 END IF;
3966 l_partial_yn := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote(
3967 p_quote_id => p_quote_id,
3968 p_contract_id => l_khr_id);
3969 IF (is_debug_statement_on) THEN
3970 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote , l_partial_yn: ' || l_partial_yn);
3971 END IF;
3972
3973 END IF;
3974 /* moving the logic to create quote invoice api for bug 5460271 -- start
3975 -- Do billing adjustment processing only in case of full termination
3976 IF nvl(l_partial_yn,'N') = 'N' THEN
3977
3978 -- get the billing adjustment amounts and bill if full termination
3979 l_input_tbl(1).khr_id := l_khr_id;
3980 l_input_tbl(1).term_date_from := l_date_eff_from;
3981
3982 -- Call BPD API to get billing from quote_effective_from_date onwards
3983 OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust(
3984 p_api_version => l_api_version,
3985 p_init_msg_list => OKL_API.G_FALSE,
3986 p_input_tbl => l_input_tbl,
3987 x_baj_tbl => lx_baj_tbl,
3988 x_return_status => l_return_status,
3989 x_msg_count => l_msg_count,
3990 x_msg_data => l_msg_data);
3991
3992 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3993
3994 -- Error getting the billing adjustments for the contract.
3995 OKL_API.set_message(
3996 p_app_name => 'OKL',
3997 p_msg_name => 'OKL_AM_ERROR_BILL_ADJST');
3998
3999 END IF;
4000
4001 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4002 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4003 l_overall_status := l_return_status;
4004 END IF;
4005 END IF;
4006
4007 -- for each value returned by BPD api create a invoice line
4008 IF lx_baj_tbl.COUNT > 0 THEN
4009
4010 -- get the last line number
4011 IF l_tilv_tbl.COUNT > 0 THEN
4012
4013 --l_line_number := l_tilv_tbl.COUNT;
4014 l_line_number := l_tilv_tbl(l_tilv_tbl.last).line_number; --rmunjulu 4610850 pick the proper line number
4015
4016 ELSE
4017
4018 l_line_number := 0;
4019
4020 END IF;
4021
4022 FOR i IN lx_baj_tbl.FIRST..lx_baj_tbl.LAST LOOP
4023
4024 IF lx_baj_tbl(i).amount <> 0
4025 AND trunc(lx_baj_tbl(i).stream_element_date) <= trunc(l_quote_accpt_date) THEN -- RMUNJULU 4547765 FUTURE_BILLS_BUG, PROCESS ONLY TILL CURRENT DATE
4026
4027 l_line_number := l_line_number + 1;
4028
4029 l_tilv_rec.line_number := l_line_number;
4030 l_tilv_rec.kle_id := lx_baj_tbl(i).kle_id;
4031
4032 -- get meaning for BILL_ADJST
4033 OPEN get_qte_ln_meaning_csr;
4034 FETCH get_qte_ln_meaning_csr INTO l_meaning;
4035 CLOSE get_qte_ln_meaning_csr;
4036
4037 l_tilv_rec.description := l_meaning;
4038 l_tilv_rec.amount := lx_baj_tbl(i).amount * -1; -- rmunjulu EDAT 07-DEC -- negate the amounts
4039
4040 IF l_tilv_rec.amount > 0 THEN
4041 l_pos_amount := l_pos_amount + l_tilv_rec.amount; -- rmunjulu EDAT 07-DEC -- + lx_baj_tbl(i).amount;
4042 ELSIF l_tilv_rec.amount < 0 THEN
4043 l_neg_amount := l_neg_amount + l_tilv_rec.amount; -- rmunjulu EDAT 07-DEC -- - lx_baj_tbl(i).amount; -- rmunjulu EDAT 09-Nov-04 Changed since the
4044 --l_neg_amount was -ve and we have to add -ve value to it
4045 END IF;
4046
4047 -- for billing adjustments the original stream type will be used
4048 -- Do not use quote line allocation streams
4049 l_tilv_rec.sty_id := lx_baj_tbl(i).sty_id;
4050
4051 -- rmunjulu 3985369 If LEASE_INV_ORG_YN is Y then set the value of
4052 -- INVENTORY_ORG_ID in invoice line with contract inv_organization_id
4053 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y' THEN
4054 l_tilv_rec.inventory_org_id := l_inv_org_id;
4055 END IF;
4056
4057 l_tilv_tbl(l_line_number) := l_tilv_rec;
4058
4059 END IF;
4060 END LOOP;
4061
4062 /*
4063 -- rmunjulu 4610850 Check if future billing adjustments exists
4064 IF l_tilv_tbl.COUNT = 0 THEN
4065 FOR i IN lx_baj_tbl.FIRST..lx_baj_tbl.LAST LOOP
4066 -- check if future bills exist
4067 IF lx_baj_tbl(i).amount <> 0
4068 AND trunc(lx_baj_tbl(i).stream_element_date) > trunc(l_quote_accpt_date) THEN
4069
4070 l_future_invoices_exists := 'Y';
4071 EXIT;
4072
4073 END IF;
4074 END LOOP;
4075 END IF;
4076 */
4077
4078 -- Do passthru disbursments if full termination and if billing adjustments needed.
4079 /*OKL_BPD_TERMINATION_ADJ_PVT.create_passthru_adj(
4080 p_api_version => l_api_version,
4081 p_init_msg_list => OKL_API.G_FALSE,
4082 p_baj_tbl => lx_baj_tbl,
4083 x_return_status => l_return_status,
4084 x_msg_count => l_msg_count,
4085 x_msg_data => l_msg_data);
4086
4087 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4088
4089 -- Error performing passthru disbursments.
4090 OKL_API.set_message(
4091 p_app_name => 'OKL',
4092 p_msg_name => 'OKL_AM_ERROR_PASS_THRU_DISB');
4093
4094 END IF;
4095
4096 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4097 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4098 l_overall_status := l_return_status;
4099 END IF;
4100 END IF;
4101
4102 END IF;
4103
4104 END IF;
4105 -- rmunjulu EDAT -- end ---------------- ++++++++++++++++++++++++++++
4106 /* moving the logic to create quote invoice api for bug 5503113 -- End */
4107 -- RMUNJULU LOANS_ENHACEMENTS BILL PERDIEM_AMOUNT -- start
4108 IF l_perdiem_amt <> 0 THEN
4109
4110 IF (is_debug_statement_on) THEN
4111 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_STREAMS_UTIL.get_primary_stream_type');
4112 END IF;
4113 OKL_STREAMS_UTIL.get_primary_stream_type(
4114 p_khr_id => l_qte_rec.khr_id,
4115 p_primary_sty_purpose => 'QUOTE_PER_DIEM',
4116 x_return_status => l_return_status,
4117 x_primary_sty_id => l_perdiem_sty_id);
4118 IF (is_debug_statement_on) THEN
4119 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_STREAMS_UTIL.get_primary_stream_type , return status: ' || l_return_status);
4120 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_perdiem_sty_id: ' || l_perdiem_sty_id);
4121 END IF;
4122
4123 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4124 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4125 l_overall_status := l_return_status;
4126 END IF;
4127 END IF;
4128
4129 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
4130
4131 --SECHAWLA 05-JAN-06 4926740 : added for prior-dated quotes : begin
4132 IF trunc(l_date_eff_from) < trunc(l_creation_date) THEN -- Prior-dated quotes
4133 IF trunc(l_quote_accpt_date) = trunc(l_creation_date) THEN
4134 l_noofdays := 1;
4135 ELSE
4136 l_noofdays := trunc(l_quote_accpt_date) - trunc(l_creation_date);
4137 END IF;
4138 ELSE --SECHAWLA 05-JAN-06 4926740 : added for prior-dated quotes : end
4139 --current / future dated quotes
4140 IF trunc(l_quote_accpt_date) = trunc(l_date_eff_from) THEN
4141 l_noofdays := 1;
4142 ELSE
4143 l_noofdays := trunc(l_quote_accpt_date) - trunc(l_date_eff_from);
4144 END IF;
4145 END IF;
4146
4147 -- get the last line number
4148 IF l_tilv_tbl.COUNT > 0 THEN
4149 l_line_number := l_tilv_tbl(l_tilv_tbl.last).line_number;
4150 ELSE
4151 l_line_number := 0;
4152 END IF;
4153
4154 l_line_number := l_line_number + 1;
4155
4156 l_tilv_rec.line_number := l_line_number;
4157 l_tilv_rec.description := 'Quote Perdiem Amount';
4158
4159 -- SECHAWLA 30-DEC-05 4917391 : Create Invoice (not credit memo) if quote perdiem is positive
4160 -- Create Credit Memo is quote per-diem is negative (e.g -ve value entered by the user)
4161 -- l_tilv_rec.amount := ABS(l_perdiem_amt) * -1 * l_noofdays; -- always create a credit memo for perdiem amt
4162 l_tilv_rec.amount := l_perdiem_amt * l_noofdays;
4163
4164 IF l_tilv_rec.amount > 0 THEN
4165 l_pos_amount := l_pos_amount + l_tilv_rec.amount;
4166 ELSIF l_tilv_rec.amount < 0 THEN
4167 l_neg_amount := l_neg_amount + l_tilv_rec.amount;
4168 END IF;
4169
4170 l_tilv_rec.sty_id := l_perdiem_sty_id;
4171
4172 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y' THEN
4173 l_tilv_rec.inventory_org_id := l_inv_org_id;
4174 END IF;
4175
4176 l_tilv_tbl(l_line_number) := l_tilv_rec;
4177 END IF;
4178 END IF;
4179 -- RMUNJULU LOANS_ENHACEMENTS BILL PERDIEM_AMOUNT -- end
4180
4181 -- RMUNJULU LOANS_ENHACEMENTS BILL REFUND_AMOUNT -- START
4182 -- create credit memos for loans refund only on full termination
4183 IF nvl(l_partial_yn,'N') = 'N' THEN
4184
4185 IF (is_debug_statement_on) THEN
4186 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_UTIL_PVT.get_excess_loan_payment');
4187 END IF;
4188 l_loan_refund_amount := OKL_AM_UTIL_PVT.get_excess_loan_payment(
4189 x_return_status => l_return_status,
4190 p_khr_id => l_khr_id);
4191 IF (is_debug_statement_on) THEN
4192 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_UTIL_PVT.get_excess_loan_payment , return status: ' || l_return_status);
4193 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_loan_refund_amount: ' || l_loan_refund_amount);
4194 END IF;
4195
4196 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4197 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4198 l_overall_status := l_return_status;
4199 END IF;
4200 END IF;
4201
4202 IF l_loan_refund_amount <> 0 THEN
4203
4204 -- get stream type ID
4205 --Bug 6266134 veramach start
4206 IF (is_debug_statement_on) THEN
4207 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_streams_util.get_dependent_stream_type');
4208 END IF;
4209 okl_streams_util.get_dependent_stream_type(
4210 p_khr_id => l_khr_id,
4211 p_primary_sty_purpose => 'RENT',
4212 p_dependent_sty_purpose => 'EXCESS_LOAN_PAYMENT_PAID',
4213 x_return_status => l_return_status,
4214 x_dependent_sty_id => l_refund_sty_id
4215 );
4216 IF (is_debug_statement_on) THEN
4217 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_streams_util.get_dependent_stream_type , return status: ' || l_return_status);
4218 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_refund_sty_id: ' || l_refund_sty_id);
4219 END IF;
4220 --Bug 6266134 veramach end
4221 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4222 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4223 l_overall_status := l_return_status;
4224 END IF;
4225 END IF;
4226
4227
4228 l_loan_refund_amount := l_loan_refund_amount *-1; -- negate the amount
4229
4230 -- get the last line number
4231 IF l_tilv_tbl.COUNT > 0 THEN
4232 l_line_number := l_tilv_tbl(l_tilv_tbl.last).line_number;
4233 ELSE
4234 l_line_number := 0;
4235 END IF;
4236
4237 l_line_number := l_line_number + 1;
4238
4239 l_tilv_rec.line_number := l_line_number;
4240 l_tilv_rec.description := 'Loan Refund Amount';
4241 l_tilv_rec.amount := l_loan_refund_amount;
4242
4243 IF l_tilv_rec.amount > 0 THEN
4244 l_pos_amount := l_pos_amount + l_tilv_rec.amount;
4245 ELSIF l_tilv_rec.amount < 0 THEN
4246 l_neg_amount := l_neg_amount + l_tilv_rec.amount;
4247 END IF;
4248
4249 l_tilv_rec.sty_id := l_refund_sty_id;
4250
4251 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y' THEN
4252 l_tilv_rec.inventory_org_id := l_inv_org_id;
4253 END IF;
4254
4255 l_tilv_tbl(l_line_number) := l_tilv_rec;
4256
4257 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4258 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4259 l_overall_status := l_return_status;
4260 END IF;
4261 END IF;
4262 END IF;
4263 END IF;
4264 -- RMUNJULU LOANS_ENHACEMENTS BILL REFUND_AMOUNT -- END
4265 -- akrangan - Bug#5521354 - Added - Start
4266 -- Adding logic to determine if there are any future invoices for billing adjustments
4267
4268 -- Check for existence of billing adjustment only for a full termination quote
4269 IF nvl(l_partial_yn,'N') = 'N' THEN
4270 -- get the billing adjustment amounts and bill if full termination
4271 l_input_tbl(1).khr_id := l_khr_id;
4272 l_input_tbl(1).term_date_from := l_date_eff_from;
4273
4274 -- Call BPD API to get billing from quote_effective_from_date onwards
4275 IF (is_debug_statement_on) THEN
4276 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust');
4277 END IF;
4278 OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust(
4279 p_api_version => l_api_version,
4280 p_init_msg_list => OKL_API.G_FALSE,
4281 p_input_tbl => l_input_tbl,
4282 x_baj_tbl => lx_baj_tbl,
4283 x_return_status => l_return_status,
4284 x_msg_count => l_msg_count,
4285 x_msg_data => l_msg_data);
4286 IF (is_debug_statement_on) THEN
4287 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust , return status: ' || l_return_status);
4288 END IF;
4289
4290 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4291 -- Error getting the billing adjustments for the contract.
4292 OKL_API.set_message(
4293 p_app_name => 'OKL',
4294 p_msg_name => 'OKL_AM_ERROR_BILL_ADJST');
4295 END IF;
4296 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4297 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4298 l_overall_status := l_return_status;
4299 END IF;
4300 END IF;
4301
4302 IF l_tilv_tbl.COUNT = 0 THEN
4303 -- Check if future billing adjustments exists
4304 IF lx_baj_tbl.COUNT > 0 THEN --akrangan added for bug 6323852
4305 FOR i IN lx_baj_tbl.FIRST..lx_baj_tbl.LAST LOOP
4306 -- check if future bills exist -- future implies future w.r.t quote effective from
4307 IF lx_baj_tbl(i).amount <> 0 THEN
4308
4309 l_future_invoices_exists := 'Y';
4310 EXIT;
4311 END IF;
4312 END LOOP;
4313 END IF;--end lx_baj_tbl.COUNT > 0 --akrangan added for bug 6323852
4314 END IF; -- end of l_tilv_tbl.COUNT = 0
4315 END IF;
4316
4317 -- akrangan - Bug#5521354 - Added - End
4318
4319 -- akrangan - Bug#5521354 - Modified - Start
4320 -- Added condition to check if there are future bills
4321 IF l_tilv_tbl.COUNT = 0
4322 AND x_sdd_taiv_tbl.COUNT = 0
4323 AND NVL(l_future_invoices_exists,'N') = 'N'
4324 THEN
4325 -- akrangan - Bug#5521354- Modified - End
4326 -- AND nvl(l_future_invoices_exists,'N') = 'N' THEN -- rmunjulu 4610850 Added condition to Check if future billing adjustments exist
4327 IF NOT l_sdd_invoice THEN --added by veramach for bug#6766479
4328 l_return_status := OKL_API.G_RET_STS_ERROR;
4329 -- Message Text: The invoice has a balance of zero.
4330 OKC_API.SET_MESSAGE (
4331 p_app_name => G_APP_NAME,
4332 p_msg_name => 'OKL_BPD_ZERO_INVOICE');
4333 END IF; --added by veramach for bug#6766479
4334 IF l_sdd_invoice THEN --veramach bug#6766479
4335
4336 OPEN check_cont_typ(l_khr_id);
4337 FETCH check_cont_typ INTO l_cont_typ;
4338 CLOSE check_cont_typ;
4339
4340 IF l_cont_typ = 'OKL_IMPORT' THEN
4341 --display meesage 'OKL_AM_INVALID_DEP_AMT' ie 'There is no security deposit disposition
4342 --amount remaining for the contract' in termination quote messages screen as per the update
4343 --by PM *** MCORNEL 01/09/08 11:07 am *** in the bug#6508911 .
4344 -- This message is already set in Create_Scrt_Dpst_Dsps_Inv.
4345
4346 OKL_AM_UTIL_PVT.process_messages(
4347 p_trx_source_table => 'OKL_TRX_QUOTES_V',
4348 p_trx_id => p_quote_id,
4349 x_return_status => l_return_status
4350 );
4351 END IF;
4352
4353 END IF; --veramach end bug#6766479
4354 END IF;
4355 IF l_amount_status <> OKL_API.G_RET_STS_SUCCESS THEN
4356 l_return_status := OKL_API.G_RET_STS_ERROR;
4357 -- Message Text: Invalid value for the column Amount
4358 OKL_API.SET_MESSAGE (
4359 p_app_name => G_OKC_APP_NAME,
4360 p_msg_name => G_INVALID_VALUE,
4361 p_token1 => G_COL_NAME_TOKEN,
4362 p_token1_value => 'Amount');
4363 END IF;
4364
4365 IF l_stream_status <> OKL_API.G_RET_STS_SUCCESS THEN
4366 l_return_status := OKL_API.G_RET_STS_ERROR;
4367 END IF;
4368
4369 END IF;
4370
4371 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4372 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
4373 l_overall_status := l_return_status;
4374 END IF;
4375 END IF;
4376
4377 x_pos_amount := l_pos_amount;
4378 x_neg_amount := l_neg_amount;
4379
4380 -- rmunjulu BUG 4341480
4381 IF NVL(l_regular_qte_line,'N') = 'N' THEN
4382 l_r_taiv_tbl.DELETE;
4383 END IF;
4384 -- end of 4341480
4385
4386 x_taiv_tbl := l_r_taiv_tbl;
4387 x_tilv_tbl := l_tilv_tbl;
4388 x_return_status := l_overall_status;
4389 IF (is_debug_procedure_on) THEN
4390 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
4391 END IF;
4392
4393 EXCEPTION
4394
4395 WHEN OTHERS THEN
4396 IF (is_debug_exception_on) THEN
4397 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
4398 || sqlcode || ' , SQLERRM : ' || sqlerrm);
4399 END IF;
4400
4401 IF l_qte_csr%ISOPEN THEN
4402 CLOSE l_qte_csr;
4403 END IF;
4404
4405 IF l_qpt_csr%ISOPEN THEN
4406 CLOSE l_qpt_csr;
4407 END IF;
4408
4409 IF l_qlt_csr%ISOPEN THEN
4410 CLOSE l_qlt_csr;
4411 END IF;
4412
4413 IF l_tai_csr%ISOPEN THEN
4414 CLOSE l_tai_csr;
4415 END IF;
4416 --PAGARG Bug 4044659 Close the cusrsor if open
4417 IF l_sys_prms_csr%ISOPEN THEN
4418 CLOSE l_sys_prms_csr;
4419 END IF;
4420
4421 -- store SQL error message on message stack for caller
4422 OKL_API.SET_MESSAGE (
4423 p_app_name => G_APP_NAME
4424 ,p_msg_name => G_UNEXPECTED_ERROR
4425 ,p_token1 => G_SQLCODE_TOKEN
4426 ,p_token1_value => sqlcode
4427 ,p_token2 => G_SQLERRM_TOKEN
4428 ,p_token2_value => sqlerrm);
4429
4430 -- notify caller of an UNEXPECTED error
4431 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4432
4433 END Validate_Populate_Quote;
4434
4435
4436 -- Start of comments
4437 --
4438 -- Procedure Name : Create_Quote_Invoice
4439 -- Description : Create Invoice from Termination or Repurchase Quote
4440 -- Business Rules :
4441 -- Parameters : quote id
4442 -- Version : 1.0
4443 -- History : RMUNJULU 11-FEB-03 2793710 Added code to check if tbl has recs
4444 -- : PAGARG 18-Aug-04 Create invoices or credit memos with
4445 -- : transaction type as rollover for rollover quote
4446 -- : rmunjulu 4547765 Added code to handle Future dated billing adjustments
4447 -- : akrangan 5521354 Moved code to create billing adjustments, outside the loop
4448 -- over non BILL_ADJ quote lines
4449 -- :RBRUNO 10/10/07 Fixed the l_roll_tilv_tbl(l_l_cnt) assignment
4450
4451 -- End of comments
4452
4453 PROCEDURE Create_Quote_Invoice (
4454 p_api_version IN NUMBER,
4455 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4456 x_msg_count OUT NOCOPY NUMBER,
4457 x_msg_data OUT NOCOPY VARCHAR2,
4458 x_return_status OUT NOCOPY VARCHAR2,
4459 p_quote_id IN NUMBER,
4460 x_taiv_tbl OUT NOCOPY taiv_tbl_type) IS
4461
4462 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4463 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4464
4465 l_api_name CONSTANT VARCHAR2(30) := 'Create_Quote_Invoice';
4466 l_api_version CONSTANT NUMBER := G_API_VERSION;
4467 l_msg_count NUMBER ;-- rmunjulu bug 4341480 := OKL_API.G_MISS_NUM;
4468 l_msg_data VARCHAR2(2000);
4469
4470 l_h_cnt NUMBER;
4471 l_l_cnt NUMBER;
4472
4473 l_pos_amount NUMBER := 0;
4474 l_neg_amount NUMBER := 0;
4475 l_allc_pos NUMBER;
4476 l_allc_neg NUMBER;
4477 l_allc_perc NUMBER;
4478
4479 l_taiv_tbl taiv_tbl_type;
4480 l_taiv_rec taiv_rec_type;
4481 l_tmp_taiv_tbl taiv_tbl_type;
4482 lx_pos_taiv_rec taiv_rec_type;
4483 lx_neg_taiv_rec taiv_rec_type;
4484 l_sdd_taiv_tbl taiv_tbl_type;
4485 l_tilv_tbl tilv_tbl_type;
4486 l_tilv_rec tilv_rec_type;
4487 lx_tilv_rec tilv_rec_type;
4488
4489 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
4490 -- Following variables are used for creating counter
4491 -- invoices/credit memos for Rollover Quote
4492 l_qtp_code okl_trx_quotes_b.qtp_code%TYPE DEFAULT NULL;
4493 l_qlt_mean fnd_lookups.meaning%TYPE := NULL;
4494 l_roll_allc_pos NUMBER;
4495 l_roll_allc_neg NUMBER;
4496 l_roll_taiv_rec taiv_rec_type;
4497 lx_pos_roll_taiv_rec taiv_rec_type;
4498 lx_neg_roll_taiv_rec taiv_rec_type;
4499 l_roll_tilv_rec tilv_rec_type;
4500 lx_roll_tilv_rec tilv_rec_type;
4501 l_roll_upd_taiv_rec taiv_rec_type;
4502 lx_roll_upd_taiv_rec taiv_rec_type;
4503 l_roll_pos_adj NUMBER := 0;
4504 l_roll_neg_adj NUMBER := 0;
4505
4506 -- Following cursor retrieves the quote type for the given quote id
4507 CURSOR quote_type_csr (p_quote_id IN NUMBER)
4508 IS
4509 SELECT qtp_code, khr_id
4510 FROM okl_trx_quotes_b
4511 WHERE id = p_quote_id;
4512
4513 quote_type_rec quote_type_csr%rowtype;
4514
4515 -- Following cursor retrieves the quote type meaning for given qlt_code
4516 CURSOR l_qlt_csr (p_qlt_code IN VARCHAR2)
4517 IS
4518 select fl.meaning
4519 from fnd_lookups fl
4520 where fl.lookup_type = 'OKL_QUOTE_LINE_TYPE'
4521 and fl.lookup_code = p_qlt_code;
4522
4523 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
4524
4525 --+++++++++++++++++++++ rmunjulu 4547765 ++++++++++++++++++++++++++++++++
4526 -- Cursor to obtain operational options values
4527 CURSOR l_sys_prms_csr IS
4528 SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
4529 FROM OKL_SYSTEM_PARAMS;
4530
4531 -- Get the quote line meaning for BILL_ADJST
4532 CURSOR get_qte_ln_meaning_csr IS
4533 SELECT fnd.meaning meaning
4534 FROM FND_LOOKUPS fnd
4535 WHERE fnd.lookup_type = 'OKL_QUOTE_LINE_TYPE'
4536 AND fnd.lookup_code = 'BILL_ADJST';
4537
4538 -- get quote details
4539 CURSOR get_qte_dtls_csr (p_quote_id IN NUMBER) IS
4540 SELECT upper(nvl(qte.partial_yn,'N')) partial_yn,
4541 qte.khr_id khr_id,
4542 qte.date_effective_from date_eff_from,
4543 qte.date_accepted date_accepted,
4544 chr.inv_organization_id inv_organization_id
4545 FROM OKL_TRX_QUOTES_B qte,
4546 OKC_K_HEADERS_B chr
4547 WHERE qte.id = p_quote_id
4548 AND qte.khr_id = chr.id;
4549
4550 l_partial_yn VARCHAR2(3);
4551 l_khr_id NUMBER;
4552 l_khr_le_id NUMBER := NULL;
4553 l_date_eff_from DATE;
4554 l_quote_accpt_date DATE;
4555 l_input_tbl OKL_BPD_TERMINATION_ADJ_PVT.input_tbl_type;
4556 lx_baj_tbl OKL_BPD_TERMINATION_ADJ_PVT.baj_tbl_type;
4557 l_meaning VARCHAR2(300);
4558 l_sys_prms_rec l_sys_prms_csr%ROWTYPE;
4559 l_inv_org_id NUMBER;
4560 l_adj_values_found VARCHAR2(3);
4561 l_adj_taiv_rec taiv_rec_type;
4562 lx_pos_adj_taiv_rec taiv_rec_type;
4563 lx_neg_adj_taiv_rec taiv_rec_type;
4564 l_adj_allc_pos NUMBER;
4565 l_adj_allc_neg NUMBER;
4566 l_adj_tilv_rec tilv_rec_type;
4567 lx_adj_tilv_rec tilv_rec_type;
4568
4569 l_adj_tilv_tbl tilv_tbl_type;
4570 lx_adj_tilv_tbl tilv_tbl_type;
4571
4572 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture Start changes
4573 -- Added For Enhanced Billing PVT
4574 l_tldv_tbl okl_tld_pvt.tldv_tbl_type;
4575 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
4576
4577 l_roll_tilv_tbl tilv_tbl_type;
4578 lx_roll_tilv_tbl tilv_tbl_type;
4579
4580 lx_tilv_tbl tilv_tbl_type;
4581 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture End changes
4582 -- akrangan - Bug#5521354 - Added - Start
4583 -- Get the quote details to populate the TAIV record for billing adjustment
4584 CURSOR l_qte_csr (cp_quote_id IN NUMBER) IS
4585 SELECT qte.khr_id khr_id,
4586 qte.qtp_code qtp_code,
4587 flo.meaning description,
4588 qte.date_accepted date_invoiced,
4589 qte.currency_code currency_code,
4590 qte.currency_conversion_type currency_conversion_type,
4591 qte.currency_conversion_rate currency_conversion_rate,
4592 qte.currency_conversion_date currency_conversion_date,
4593 khr.inv_organization_id inv_organization_id
4594 FROM okl_trx_quotes_b qte,
4595 fnd_lookups flo,
4596 okc_k_headers_b khr
4597 WHERE qte.id = cp_quote_id
4598 AND flo.lookup_type = 'OKL_QUOTE_TYPE'
4599 AND flo.lookup_code = qte.qtp_code
4600 AND khr.id = qte.khr_id;
4601
4602 l_qte_rec l_qte_csr%ROWTYPE;
4603 -- akrangan - Bug#5521354 - Added - End
4604 --akrangan BUG 6275650 start
4605 l_source_billing_trx okl_trx_ar_invoices_b.okl_source_billing_trx%type := 'TERMINATION_QUOTE';
4606 --akrangan BUG 6275650 end
4607 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_quote_invoice';
4608 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
4609 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
4610 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
4611
4612 l_tmp_tilv_tbl tilv_tbl_type; -- rmunjulu bug 6791004
4613 BEGIN
4614 IF (is_debug_procedure_on) THEN
4615 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
4616 END IF;
4617 IF (is_debug_statement_on) THEN
4618 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_quote_id: '|| p_quote_id);
4619 END IF;
4620
4621 -- ***************************************************************
4622 -- Check API version, initialize message list and create savepoint
4623 -- ***************************************************************
4624
4625 l_return_status := OKL_API.START_ACTIVITY (
4626 l_api_name,
4627 G_PKG_NAME,
4628 p_init_msg_list,
4629 l_api_version,
4630 p_api_version,
4631 '_PVT',
4632 x_return_status);
4633
4634 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4635 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4636 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4637 RAISE OKL_API.G_EXCEPTION_ERROR;
4638 END IF;
4639
4640 -- *******************
4641 -- Validate parameters
4642 -- *******************
4643
4644 IF p_quote_id IS NULL
4645 OR p_quote_id = G_MISS_NUM THEN
4646
4647 OKC_API.SET_MESSAGE (
4648 p_app_name => G_OKC_APP_NAME,
4649 p_msg_name => 'OKC_NO_PARAMS',
4650 p_token1 => 'PARAM',
4651 p_token1_value => 'QUOTE_ID',
4652 p_token2 => 'PROCESS',
4653 p_token2_value => l_api_name);
4654
4655 RAISE OKL_API.G_EXCEPTION_ERROR;
4656
4657 END IF;
4658
4659 -- Validate p_quote_id
4660 -- Populate all header fields
4661 -- Populate all invoice line fields for all quote line
4662
4663 Validate_Populate_Quote (
4664 p_quote_id => p_quote_id,
4665 x_pos_amount => l_pos_amount,
4666 x_neg_amount => l_neg_amount,
4667 x_taiv_tbl => l_tmp_taiv_tbl,
4668 x_tilv_tbl => l_tilv_tbl,
4669 x_sdd_taiv_tbl => l_sdd_taiv_tbl,
4670 x_return_status => l_return_status);
4671 IF (is_debug_statement_on) THEN
4672 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Validate_Populate_Quote , return status: ' || l_return_status);
4673 END IF;
4674
4675 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4677 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4678 RAISE OKL_API.G_EXCEPTION_ERROR;
4679 END IF;
4680
4681 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
4682 OPEN quote_type_csr(p_quote_id);
4683 FETCH quote_type_csr INTO quote_type_rec;
4684 CLOSE quote_type_csr;
4685
4686 l_qtp_code := quote_type_rec.qtp_code;
4687 l_khr_id := quote_type_rec.khr_id;
4688
4689 IF l_qtp_code LIKE 'TER%' then
4690 l_khr_le_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id (l_khr_id);
4691 End If;
4692
4693 OPEN l_qlt_csr('BILL_ADJST');
4694 FETCH l_qlt_csr INTO l_qlt_mean;
4695 CLOSE l_qlt_csr;
4696 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
4697
4698 l_taiv_tbl := l_sdd_taiv_tbl;
4699 l_h_cnt := NVL (l_taiv_tbl.COUNT, 0);
4700
4701 -- *********************
4702 -- Create Invoice Header
4703 -- *********************
4704
4705 -- RMUNJULU 11-FEB-03 2793710 Added check to see if tbl has recs
4706 IF l_tmp_taiv_tbl.COUNT > 0 THEN
4707
4708 FOR i IN l_tmp_taiv_tbl.FIRST..l_tmp_taiv_tbl.LAST LOOP
4709
4710 l_taiv_rec := l_tmp_taiv_tbl(i);
4711 l_allc_perc := l_taiv_rec.amount;
4712 l_allc_pos := l_pos_amount * (l_allc_perc / 100);
4713 l_allc_neg := l_neg_amount * (l_allc_perc / 100);
4714
4715 l_taiv_rec.legal_entity_id := l_khr_le_id;
4716
4717 l_tmp_tilv_tbl.delete; -- rmunjulu bug 6791004
4718 l_tmp_tilv_tbl := l_tilv_tbl; -- rmunjulu bug 6791004
4719
4720 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture Start changes
4721 -- Included loop to update amount in tilv_tbl and call to Enhanced billing API
4722 l_l_cnt := l_tilv_tbl.FIRST;
4723 LOOP
4724 -- l_tilv_tbl(l_l_cnt).amount := l_tilv_tbl(l_l_cnt).amount * (l_allc_perc / 100); -- rmunjulu bug 6791004
4725 -- rmunjulu bug 6791004 Use another tmp tbl or else values are incorrect in the second round
4726 l_tmp_tilv_tbl (l_l_cnt).amount := l_tilv_tbl(l_l_cnt).amount * (l_allc_perc / 100);
4727
4728 EXIT WHEN ( l_l_cnt = l_tilv_tbl.LAST OR l_return_status <> OKL_API.G_RET_STS_SUCCESS);
4729 l_l_cnt := l_tilv_tbl.NEXT(l_l_cnt);
4730 END LOOP;
4731 --AKRANGAN BUG 6275650 START
4732 l_taiv_rec.okl_source_billing_trx := l_source_billing_trx;
4733 --AKRANGAN BUG 6275650 END
4734 Create_billing_invoices ( p_taiv_rec => l_taiv_rec ,--changed the rec type associated l_adj_taiv_rec ,
4735 p_pos_amount =>l_allc_pos ,
4736 p_neg_amount =>l_allc_neg,
4737 p_tilv_tbl =>l_tmp_tilv_tbl ,-- rmunjulu bug 6791004 Use tmp_tilv_tbl or else second round tilv values are becoming incorrect.
4738 x_tilv_tbl =>lx_tilv_tbl,
4739 x_pos_taiv_rec =>lx_pos_taiv_rec ,
4740 x_neg_taiv_rec =>lx_neg_taiv_rec,
4741 x_return_status=>l_return_status);
4742 IF (is_debug_statement_on) THEN
4743 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_billing_invoices , return status: ' || l_return_status);
4744 END IF;
4745
4746 /* -- ANSETHUR 08-MAR-2007 Commented For billing Achitecture
4747 Create_AR_Invoice_Header (
4748 p_taiv_rec => l_taiv_rec,
4749 p_pos_amount => l_allc_pos,
4750 p_neg_amount => l_allc_neg,
4751 x_pos_taiv_rec => lx_pos_taiv_rec,
4752 x_neg_taiv_rec => lx_neg_taiv_rec,
4753 x_return_status => l_return_status);
4754 */
4755 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4756 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4757 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4758 RAISE OKL_API.G_EXCEPTION_ERROR;
4759 END IF;
4760
4761 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture End changes
4762
4763 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
4764 -- Create invoice headers for negative and positive quote lines
4765 -- for rollover transaction.
4766 -- Use the same processing of rollover quote for Release quote
4767 -- pagarg +++ T and A +++
4768 IF l_qtp_code LIKE 'TER_ROLL%' OR l_qtp_code = 'TER_RELEASE_WO_PURCHASE'
4769 THEN
4770
4771 l_roll_taiv_rec := l_taiv_rec;
4772 l_roll_allc_pos := l_allc_neg * -1;
4773 l_roll_allc_neg := l_allc_pos * -1;
4774
4775 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture Start changes
4776 -- Added Loop to assign value to tilv_tbl for roll over quotes and sum up the adjustment
4777 -- The sum of adjustment is again updated the the amount of taiv_rec for the roll over quote
4778 l_l_cnt := l_tilv_tbl.FIRST;
4779 LOOP
4780 --START RBRUNO 10/10/07 Fixed the l_roll_tilv_tbl(l_l_cnt) assignment
4781 l_roll_tilv_tbl(l_l_cnt) := l_tilv_tbl(l_l_cnt);
4782 --END RBRUNO 10/10/07 Fixed the l_roll_tilv_tbl(l_l_cnt) assignment
4783 -- l_roll_tilv_tbl(l_l_cnt) := l_tilv_rec;
4784 --l_roll_tilv_tbl(l_l_cnt).legal_entity_id := l_khr_le_id;
4785 -- l_roll_tilv_tbl(l_l_cnt).amount := l_roll_tilv_tbl(l_l_cnt).amount * -1 ; -- rmunjulu bug 6791004
4786
4787 -- rmunjulu bug 6791004 Multiply with allocation percent to get the right rollover values
4788 l_roll_tilv_tbl(l_l_cnt).amount := l_roll_tilv_tbl(l_l_cnt).amount * -1 * (l_allc_perc / 100);
4789
4790 IF l_roll_tilv_rec.description = l_qlt_mean
4791 THEN
4792
4793 IF l_roll_tilv_rec.amount > 0
4794 THEN
4795 l_roll_pos_adj := l_roll_pos_adj + l_roll_tilv_tbl(l_l_cnt).amount;
4796 ELSIF l_roll_tilv_rec.amount < 0
4797 THEN
4798 l_roll_neg_adj := l_roll_neg_adj + l_roll_tilv_tbl(l_l_cnt).amount;
4799 END IF;
4800 END IF;
4801
4802
4803 EXIT WHEN ( l_l_cnt = l_tilv_tbl.LAST OR l_return_status <> OKL_API.G_RET_STS_SUCCESS);
4804 l_l_cnt := l_tilv_tbl.NEXT(l_l_cnt);
4805 END LOOP;
4806
4807 IF l_roll_pos_adj > 0
4808 THEN
4809 l_roll_taiv_rec.amount := l_roll_taiv_rec.amount - l_roll_pos_adj;
4810 END IF;
4811
4812 IF l_roll_neg_adj < 0
4813 THEN
4814 l_roll_taiv_rec.amount := l_roll_taiv_rec.amount - l_roll_neg_adj;
4815 END IF;
4816 --AKRANGAN BUG 6275650 START
4817 l_roll_taiv_rec.okl_source_billing_trx := l_source_billing_trx;
4818 --AKRANGAN BUG 6275650 END
4819
4820
4821 Create_billing_invoices ( p_taiv_rec =>l_roll_taiv_rec ,
4822 p_pos_amount =>l_roll_allc_pos ,
4823 p_neg_amount =>l_roll_allc_neg,
4824 p_quote_type =>l_qtp_code ,
4825 p_trans_type => 'REVERSE',
4826 p_tilv_tbl =>l_roll_tilv_tbl,
4827 x_tilv_tbl =>lx_roll_tilv_tbl,
4828 x_pos_taiv_rec =>lx_pos_roll_taiv_rec ,
4829 x_neg_taiv_rec =>lx_neg_roll_taiv_rec,
4830 x_return_status=>l_return_status);
4831 IF (is_debug_statement_on) THEN
4832 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_billing_invoices , return status: ' || l_return_status);
4833 END IF;
4834 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4835 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4836 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4837 RAISE OKL_API.G_EXCEPTION_ERROR;
4838 END IF;
4839 END IF; -- for Rollover quote
4840
4841 /* -- ANSETHUR 08-MAR-2007 Commented For billing Achitecture
4842 Create_AR_Invoice_Header (
4843 p_taiv_rec => l_roll_taiv_rec,
4844 p_pos_amount => l_roll_allc_pos,
4845 p_neg_amount => l_roll_allc_neg,
4846 p_quote_type => l_qtp_code,
4847 -- pagarg +++ T and A +++
4848 p_trans_type => 'REVERSE',
4849 x_pos_taiv_rec => lx_pos_roll_taiv_rec,
4850 x_neg_taiv_rec => lx_neg_roll_taiv_rec,
4851 x_return_status => l_return_status);
4852
4853 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4854 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4855 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4856 RAISE OKL_API.G_EXCEPTION_ERROR;
4857 END IF;
4858
4859
4860
4861
4862 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
4863
4864 -- *********************************************
4865 -- Create Invoice Lines for each record in table
4866 -- *********************************************
4867
4868 -- pagarg +++ Rollover +++
4869 -- intialize the negative and postive adjustment variables
4870 l_roll_pos_adj := 0;
4871 l_roll_neg_adj := 0;
4872
4873 l_l_cnt := l_tilv_tbl.FIRST;
4874 LOOP
4875
4876 l_tilv_rec := l_tilv_tbl(l_l_cnt);
4877 l_tilv_rec.amount := l_tilv_rec.amount * (l_allc_perc / 100);
4878
4879 IF l_tilv_rec.amount > 0 THEN
4880 l_tilv_rec.tai_id := lx_pos_taiv_rec.id;
4881 ELSIF l_tilv_rec.amount < 0 THEN
4882 l_tilv_rec.tai_id := lx_neg_taiv_rec.id;
4883 ELSE
4884 l_tilv_rec.tai_id := NULL;
4885 END IF;
4886
4887 Create_AR_Invoice_Lines (
4888 p_tilv_rec => l_tilv_rec,
4889 x_tilv_rec => lx_tilv_rec,
4890 x_return_status => l_return_status);
4891
4892 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
4893
4894 -- Process the invoice lines for rollover quote. Negate the amount and set the
4895 -- header id accordingly and call insert for invoice lines for rollover.
4896 -- Use the same processing of rollover quote for Release quote
4897 -- pagarg +++ T and A +++
4898 IF l_qtp_code LIKE 'TER_ROLL%' OR l_qtp_code = 'TER_RELEASE_WO_PURCHASE'
4899 THEN
4900 l_roll_tilv_rec := l_tilv_rec;
4901 --l_roll_tilv_rec.legal_entity_id := l_khr_le_id;
4902 l_roll_tilv_rec.amount := l_tilv_rec.amount * -1;
4903 IF l_roll_tilv_rec.amount > 0
4904 THEN
4905 l_roll_tilv_rec.tai_id := lx_pos_roll_taiv_rec.id;
4906 ELSIF l_roll_tilv_rec.amount < 0
4907 THEN
4908 l_roll_tilv_rec.tai_id := lx_neg_roll_taiv_rec.id;
4909 ELSE
4910 l_roll_tilv_rec.tai_id := NULL;
4911 END IF;
4912
4913 -- If quote line is not Estimated Billing Adjustments then create rollover
4914 -- invoice line else add them in adjusted amount variable and update the header
4915 -- after the loop is over.
4916 IF l_tilv_rec.description <> l_qlt_mean
4917 THEN
4918 Create_AR_Invoice_Lines (
4919 p_tilv_rec => l_roll_tilv_rec,
4920 x_tilv_rec => lx_roll_tilv_rec,
4921 x_return_status => l_return_status);
4922 ELSE
4923 IF l_roll_tilv_rec.amount > 0
4924 THEN
4925 l_roll_pos_adj := l_roll_pos_adj + l_roll_tilv_rec.amount;
4926 ELSIF l_roll_tilv_rec.amount < 0
4927 THEN
4928 l_roll_neg_adj := l_roll_neg_adj + l_roll_tilv_rec.amount;
4929 END IF;
4930 END IF;
4931 END IF;
4932
4933 EXIT WHEN ( l_l_cnt = l_tilv_tbl.LAST
4934 OR l_return_status <> OKL_API.G_RET_STS_SUCCESS);
4935 l_l_cnt := l_tilv_tbl.NEXT(l_l_cnt);
4936 END LOOP;
4937
4938 -- If there is any amount to be adjusted in the variable then call the procedure
4939 -- to update the corresponding header
4940 IF l_roll_pos_adj > 0
4941 THEN
4942 l_roll_upd_taiv_rec.id := lx_pos_roll_taiv_rec.id;
4943 l_roll_upd_taiv_rec.legal_entity_id := l_khr_le_id;
4944 l_roll_upd_taiv_rec.amount := l_roll_upd_taiv_rec.amount - l_roll_pos_adj;
4945 okl_trx_ar_invoices_pub.update_trx_ar_invoices(
4946 p_api_version => l_api_version,
4947 p_init_msg_list => OKL_API.G_FALSE,
4948 x_return_status => l_return_status,
4949 x_msg_count => l_msg_count,
4950 x_msg_data => l_msg_data,
4951 p_taiv_rec => l_roll_upd_taiv_rec,
4952 x_taiv_rec => lx_roll_upd_taiv_rec);
4953 END IF;
4954 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4956 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4957 RAISE OKL_API.G_EXCEPTION_ERROR;
4958 END IF;
4959
4960 IF l_roll_neg_adj < 0
4961 THEN
4962 l_roll_upd_taiv_rec.id := lx_neg_roll_taiv_rec.id;
4963 l_roll_upd_taiv_rec.legal_entity_id := l_khr_le_id;
4964 l_roll_upd_taiv_rec.amount := l_roll_upd_taiv_rec.amount - l_roll_neg_adj;
4965 okl_trx_ar_invoices_pub.update_trx_ar_invoices(
4966 p_api_version => l_api_version,
4967 p_init_msg_list => OKL_API.G_FALSE,
4968 x_return_status => l_return_status,
4969 x_msg_count => l_msg_count,
4970 x_msg_data => l_msg_data,
4971 p_taiv_rec => l_roll_upd_taiv_rec,
4972 x_taiv_rec => lx_roll_upd_taiv_rec);
4973 END IF;
4974 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4975 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4976 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4977 RAISE OKL_API.G_EXCEPTION_ERROR;
4978 END IF;
4979 */
4980 -- ANSETHUR 08-MAR-2007 Added For billing Achitecture End changes
4981
4982 -- ************
4983 -- Save results
4984 -- ************
4985
4986 IF l_pos_amount > 0 THEN
4987 l_h_cnt := l_h_cnt + 1;
4988 l_taiv_tbl (l_h_cnt) := lx_pos_taiv_rec;
4989 END IF;
4990
4991 IF l_neg_amount < 0 THEN
4992 l_h_cnt := l_h_cnt + 1;
4993 l_taiv_tbl (l_h_cnt) := lx_neg_taiv_rec;
4994 END IF;
4995
4996 --+++++++++++ pagarg +++ Rollover +++++++ Start ++++++++++
4997
4998 -- pagarg +++ T and A +++
4999 IF l_qtp_code LIKE 'TER_ROLL%' OR l_qtp_code = 'TER_RELEASE_WO_PURCHASE'
5000 THEN
5001 IF l_roll_allc_pos > 0
5002 THEN
5003 l_h_cnt := l_h_cnt + 1;
5004 l_taiv_tbl (l_h_cnt) := lx_pos_roll_taiv_rec;
5005 END IF;
5006 IF l_roll_allc_neg < 0 THEN
5007 l_h_cnt := l_h_cnt + 1;
5008 l_taiv_tbl (l_h_cnt) := lx_neg_roll_taiv_rec;
5009 END IF;
5010 END IF;
5011 --+++++++++++ pagarg +++ Rollover +++++++ End ++++++++++
5012 END LOOP;
5013 END IF;
5014 -- akrangan - Bug#5521354 - Moved - Start
5015 -- Moved code to check for future bills outside the block where quote lines other than
5016 -- billing adjustments are being handled
5017 --++++++ START ++++++ rmunjulu 4547765 CREDIT FUTURE BILLS ON FUTURE DATE +++
5018 -- Do billing adjustment processing only in case of full termination
5019 --IF nvl(l_partial_yn,'N') = 'N' THEN
5020
5021 -- GET QUOTE DETAILS
5022 OPEN get_qte_dtls_csr (p_quote_id);
5023 FETCH get_qte_dtls_csr INTO l_partial_yn, l_khr_id, l_date_eff_from, l_quote_accpt_date, l_inv_org_id;
5024 CLOSE get_qte_dtls_csr;
5025
5026 -- rmunjulu INVESTOR_DISB_ADJST
5027 IF nvl(l_partial_yn,'N') = 'Y' THEN
5028
5029 -- need to check if no more assets
5030 IF (is_debug_statement_on) THEN
5031 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote');
5032 END IF;
5033 l_partial_yn := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote(
5034 p_quote_id => p_quote_id,
5035 p_contract_id => l_khr_id);
5036 IF (is_debug_statement_on) THEN
5037 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_AM_LEASE_LOAN_TRMNT_PVT.check_true_partial_quote , l_partial_yn: ' || l_partial_yn);
5038 END IF;
5039
5040 END IF;
5041
5042 -- Do billing adjustment processing only in case of full termination
5043 IF nvl(l_partial_yn,'N') = 'N' THEN
5044
5045 -- get meaning for BILL_ADJST
5046 OPEN get_qte_ln_meaning_csr;
5047 FETCH get_qte_ln_meaning_csr INTO l_meaning;
5048 CLOSE get_qte_ln_meaning_csr;
5049
5050 -- GET SYS OPTIONS SETUP DETAILS
5051 OPEN l_sys_prms_csr;
5052 FETCH l_sys_prms_csr INTO l_sys_prms_rec;
5053 -- IF no row fetched from cursor then set the value as N for LEASE_INV_ORG_YN
5054 IF l_sys_prms_csr%NOTFOUND THEN
5055 l_sys_prms_rec.LEASE_INV_ORG_YN := 'N';
5056 END IF;
5057 CLOSE l_sys_prms_csr;
5058
5059 -- get the billing adjustment amounts and bill if full termination
5060 l_input_tbl(1).khr_id := l_khr_id;
5061 l_input_tbl(1).term_date_from := l_date_eff_from;
5062
5063 -- Call BPD API to get billing from quote_effective_from_date onwards
5064 IF (is_debug_statement_on) THEN
5065 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust');
5066 END IF;
5067 OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust(
5068 p_api_version => l_api_version,
5069 p_init_msg_list => OKL_API.G_FALSE,
5070 p_input_tbl => l_input_tbl,
5071 x_baj_tbl => lx_baj_tbl,
5072 x_return_status => l_return_status,
5073 x_msg_count => l_msg_count,
5074 x_msg_data => l_msg_data);
5075 IF (is_debug_statement_on) THEN
5076 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_BPD_TERMINATION_ADJ_PVT.get_billing_adjust , return status: ' || l_return_status);
5077 END IF;
5078
5079 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
5080
5081 -- Error getting the billing adjustments for the contract.
5082 OKL_API.set_message(
5083 p_app_name => 'OKL',
5084 p_msg_name => 'OKL_AM_ERROR_BILL_ADJST');
5085 END IF;
5086
5087 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5088 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5089 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5090 RAISE OKL_API.G_EXCEPTION_ERROR;
5091 END IF;
5092
5093 -- for each value returned by BPD api
5094 IF lx_baj_tbl.COUNT > 0 THEN
5095 FOR i IN lx_baj_tbl.FIRST..lx_baj_tbl.LAST LOOP
5096
5097 -- process only if amount <> 0 and future bills
5098 IF lx_baj_tbl(i).amount <> 0 then
5099 -- akrangan commenting out following condition for bug 5503113 -- start
5100 --AND trunc(lx_baj_tbl(i).stream_element_date) > trunc(l_quote_accpt_date) THEN
5101 --akrangan commenting for bug 5503113 end
5102 l_adj_values_found := 'Y';
5103 --akrangan bug#5521354 start
5104 IF (l_taiv_rec.qte_id is NULL OR l_taiv_rec.qte_id = G_MISS_NUM )
5105 and (l_taiv_rec.khr_id is NULL OR l_taiv_rec.khr_id = G_MISS_NUM ) then
5106 -- ***********************
5107 -- Get Quote Header Record
5108 -- ***********************
5109
5110 OPEN l_qte_csr (p_quote_id);
5111 FETCH l_qte_csr INTO l_qte_rec;
5112
5113 IF l_qte_csr%NOTFOUND THEN
5114 l_return_status := OKL_API.G_RET_STS_ERROR;
5115 OKC_API.SET_MESSAGE (
5116 p_app_name => G_OKC_APP_NAME,
5117 p_msg_name => G_INVALID_VALUE,
5118 p_token1 => G_COL_NAME_TOKEN,
5119 p_token1_value => 'Quote_Id');
5120 ELSE
5121 l_taiv_rec.khr_id := l_qte_rec.khr_id;
5122 l_taiv_rec.description := l_qte_rec.description;
5123 l_taiv_rec.currency_code := l_qte_rec.currency_code;
5124 l_taiv_rec.date_invoiced := l_qte_rec.date_invoiced;
5125 l_taiv_rec.qte_id := p_quote_id;
5126
5127 l_taiv_rec.currency_conversion_type := l_qte_rec.currency_conversion_type;
5128 l_taiv_rec.currency_conversion_rate := l_qte_rec.currency_conversion_rate;
5129 l_taiv_rec.currency_conversion_date := l_qte_rec.currency_conversion_date;
5130 END IF;
5131
5132 CLOSE l_qte_csr;
5133
5134 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
5135 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
5136 l_overall_status := l_return_status;
5137 END IF;
5138 END IF;
5139 END IF;
5140 --akrangan bug#5521354 end
5141 -- set header record
5142 l_adj_taiv_rec := l_taiv_rec;
5143 l_adj_taiv_rec.legal_entity_id := l_khr_le_id;
5144 --akrangan bug 5503113 -- start
5145 l_adj_taiv_rec.ibt_id := NULL;
5146 l_adj_taiv_rec.ixx_id := NULL;
5147 l_adj_taiv_rec.irt_id := NULL;
5148
5149 IF (trunc(lx_baj_tbl(i).stream_element_date) <= trunc(l_quote_accpt_date)) THEN -- bug 5503113 -- start
5150 l_adj_taiv_rec.date_invoiced :=l_adj_taiv_rec.date_invoiced;
5151 ELSE
5152 l_adj_taiv_rec.date_invoiced := lx_baj_tbl(i).stream_element_date;
5153 END IF;
5154 --akrangan bug 5503113 -- end
5155 --akrangan bug 5521354 start
5156 -- l_adj_taiv_rec.amount := lx_baj_tbl(i).amount * -1 * (l_allc_perc / 100); -- negate amount and set based on allc percent
5157 -- reverse the entire amount and bill this to lessee
5158 -- allocation not considered as billing adjustments are
5159 -- billed only to lessee and not to other recipients
5160 l_adj_taiv_rec.amount := lx_baj_tbl(i).amount * -1 ;
5161 --akrangan bug 5521354 end
5162 IF l_adj_taiv_rec.amount > 0 THEN
5163 l_adj_allc_pos := l_adj_taiv_rec.amount;
5164 ELSE
5165 l_adj_allc_neg := l_adj_taiv_rec.amount;
5166 END IF;
5167
5168
5169 -- Added for the tilv rec
5170 l_adj_tilv_rec.line_number := 1;
5171 l_adj_tilv_rec.kle_id := lx_baj_tbl(i).kle_id; -- Asset Id
5172
5173 l_adj_tilv_rec.description := l_meaning; -- Estimated Billing Adjustment
5174 l_adj_tilv_rec.amount := l_adj_taiv_rec.amount; -- same as header amount
5175
5176 -- for billing adjustments the original stream type will be used
5177 -- Do not use quote line allocation streams
5178 l_adj_tilv_rec.sty_id := lx_baj_tbl(i).sty_id;
5179
5180 -- If LEASE_INV_ORG_YN is Y then set the value of
5181 -- INVENTORY_ORG_ID in invoice line with contract inv_organization_id
5182 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y' THEN
5183 l_adj_tilv_rec.inventory_org_id := l_inv_org_id;
5184 END IF;
5185
5186 l_adj_tilv_tbl(0) := l_adj_tilv_rec;
5187 --AKRANGAN BUG 6275650 START
5188 l_adj_taiv_rec.okl_source_billing_trx := l_source_billing_trx;
5189 --AKRANGAN BUG 6275650 END
5190
5191 Create_billing_invoices (
5192 p_taiv_rec =>l_adj_taiv_rec ,
5193 p_pos_amount =>l_adj_allc_pos ,
5194 p_neg_amount =>l_adj_allc_neg,
5195 p_quote_type =>l_qtp_code ,
5196 p_tilv_tbl =>l_adj_tilv_tbl ,
5197 x_tilv_tbl =>lx_adj_tilv_tbl ,
5198 x_pos_taiv_rec =>lx_pos_adj_taiv_rec ,
5199 x_neg_taiv_rec =>lx_neg_adj_taiv_rec,
5200 x_return_status=>l_return_status);
5201 IF (is_debug_statement_on) THEN
5202 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called Create_billing_invoices , return status: ' || l_return_status);
5203 END IF;
5204
5205 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5206 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5207 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5208 RAISE OKL_API.G_EXCEPTION_ERROR;
5209 END IF;
5210 /*
5211 -- create invoice header
5212 Create_AR_Invoice_Header (
5213 p_taiv_rec => l_adj_taiv_rec,
5214 p_pos_amount => l_adj_allc_pos,
5215 p_neg_amount => l_adj_allc_neg,
5216 p_quote_type => l_qtp_code,
5217 x_pos_taiv_rec => lx_pos_adj_taiv_rec,
5218 x_neg_taiv_rec => lx_neg_adj_taiv_rec,
5219 x_return_status => l_return_status);
5220
5221 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5223 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5224 RAISE OKL_API.G_EXCEPTION_ERROR;
5225 END IF;
5226
5227 -- set detail record
5228 IF l_adj_taiv_rec.amount > 0 THEN
5229 l_adj_tilv_rec.tai_id := lx_pos_adj_taiv_rec.id;
5230 ELSE
5231 l_adj_tilv_rec.tai_id := lx_neg_adj_taiv_rec.id;
5232 END IF;
5233 l_adj_tilv_rec.line_number := 1;
5234 l_adj_tilv_rec.kle_id := lx_baj_tbl(i).kle_id; -- Asset Id
5235
5236 l_adj_tilv_rec.description := l_meaning; -- Estimated Billing Adjustment
5237 l_adj_tilv_rec.amount := l_adj_taiv_rec.amount; -- same as header amount
5238
5239 -- for billing adjustments the original stream type will be used
5240 -- Do not use quote line allocation streams
5241 l_adj_tilv_rec.sty_id := lx_baj_tbl(i).sty_id;
5242
5243 -- If LEASE_INV_ORG_YN is Y then set the value of
5244 -- INVENTORY_ORG_ID in invoice line with contract inv_organization_id
5245 IF l_sys_prms_rec.LEASE_INV_ORG_YN = 'Y' THEN
5246 l_adj_tilv_rec.inventory_org_id := l_inv_org_id;
5247 END IF;
5248
5249 -- create invoice line
5250 Create_AR_Invoice_Lines (
5251 p_tilv_rec => l_adj_tilv_rec,
5252 x_tilv_rec => lx_adj_tilv_rec,
5253 x_return_status => l_return_status);
5254
5255 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5256 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5257 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5258 RAISE OKL_API.G_EXCEPTION_ERROR;
5259 END IF;
5260 */
5261
5262 IF l_adj_allc_pos > 0 THEN
5263 l_h_cnt := l_h_cnt + 1;
5264 l_taiv_tbl (l_h_cnt) := lx_pos_adj_taiv_rec;
5265 END IF;
5266 IF l_adj_allc_neg < 0 THEN
5267 l_h_cnt := l_h_cnt + 1;
5268 l_taiv_tbl (l_h_cnt) := lx_neg_adj_taiv_rec;
5269 END IF;
5270 END IF;
5271 END LOOP;
5272 --akrangan bug 5503113 start
5273 IF (is_debug_statement_on) THEN
5274 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_BPD_TERMINATION_ADJ_PVT.create_passthru_adj');
5275 END IF;
5276 OKL_BPD_TERMINATION_ADJ_PVT.create_passthru_adj(
5277 p_api_version => l_api_version,
5278 p_init_msg_list => OKL_API.G_FALSE,
5279 p_baj_tbl => lx_baj_tbl,
5280 x_return_status => l_return_status,
5281 x_msg_count => l_msg_count,
5282 x_msg_data => l_msg_data);
5283 IF (is_debug_statement_on) THEN
5284 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_BPD_TERMINATION_ADJ_PVT.create_passthru_adj , return status: ' || l_return_status);
5285 END IF;
5286
5287
5288 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
5289
5290 -- Error performing passthru disbursments.
5291 OKL_API.set_message(
5292 p_app_name => 'OKL',
5293 p_msg_name => 'OKL_AM_ERROR_PASS_THRU_DISB');
5294
5295 END IF;
5296 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5297 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5298 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5299 RAISE OKL_API.G_EXCEPTION_ERROR;
5300 END IF;
5301 --akrangan bug 5503113 end
5302 END IF;
5303 END IF;
5304 --++++++ END ++++++ rmunjulu 4547765 CREDIT FUTURE BILLS ON FUTURE DATE +++
5305
5306 --akrangan Bug#5521354 - Moved - End
5307 /* END LOOP;
5308
5309 END IF; */
5310 --akrangan Bug#5521354 - Moved - End
5311
5312 -- **************
5313 -- Return results
5314 -- **************
5315
5316 x_taiv_tbl := l_taiv_tbl;
5317 x_return_status := l_overall_status;
5318
5319 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
5320 IF (is_debug_procedure_on) THEN
5321 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
5322 END IF;
5323
5324 EXCEPTION
5325
5326 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5327 IF (is_debug_exception_on) THEN
5328 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
5329 END IF;
5330 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5331 (
5332 l_api_name,
5333 G_PKG_NAME,
5334 'OKL_API.G_RET_STS_ERROR',
5335 x_msg_count,
5336 x_msg_data,
5337 '_PVT'
5338 );
5339
5340 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5341 IF (is_debug_exception_on) THEN
5342 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
5343 END IF;
5344 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5345 (
5346 l_api_name,
5347 G_PKG_NAME,
5348 'OKL_API.G_RET_STS_UNEXP_ERROR',
5349 x_msg_count,
5350 x_msg_data,
5351 '_PVT'
5352 );
5353
5354 WHEN OTHERS THEN
5355 IF (is_debug_exception_on) THEN
5356 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
5357 || sqlcode || ' , SQLERRM : ' || sqlerrm);
5358 END IF;
5359
5360 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5361 (
5362 l_api_name,
5363 G_PKG_NAME,
5364 'OTHERS',
5365 x_msg_count,
5366 x_msg_data,
5367 '_PVT'
5368 );
5369
5370 END Create_Quote_Invoice;
5371
5372 END OKL_AM_INVOICES_PVT;