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