[Home] [Help]
PACKAGE BODY: APPS.OKL_ARINTF_PVT
Source
1 PACKAGE BODY Okl_Arintf_Pvt AS
2 /* $Header: OKLRAINB.pls 120.79.12010000.15 2009/02/10 12:02:46 nikshah ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.plsql.okl_arintf_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
9 L_DEBUG_ENABLED VARCHAR2(10);
10 -- L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
11 L_LEVEL_PROCEDURE NUMBER;
12 IS_DEBUG_PROCEDURE_ON BOOLEAN;
13 -- End of wraper code generated automatically by Debug code generator
14 --start: | 15-FEB-07 cklee R12 Billing enhancement project
15 -- vpanwar 20-July-07 -- interface line length increase from 30 to 150
16 G_AR_DATA_LENGTH CONSTANT VARCHAR2(4) := '150';
17 -- end vpanwar 20-July-07 -- interface line length increase from 30 to 150
18 G_ACC_SYS_OPTION VARCHAR2(4);
19 --end: | 15-FEB-07 cklee R12 Billing enhancement project |
20
21 -- rmunjulu R12 Fixes
22 TYPE dist_rec_type IS RECORD (
23 account_class RA_INTERFACE_DISTRIBUTIONS_ALL.account_class%TYPE,
24 dist_amount OKL_TRNS_ACC_DSTRS.amount%TYPE,
25 dist_percent OKL_TRNS_ACC_DSTRS.percentage%TYPE,
26 code_combination_id OKL_TRNS_ACC_DSTRS.code_combination_id%TYPE);
27
28 -- rmunjulu R12 Fixes
29 TYPE dist_tbl_type IS TABLE OF dist_rec_type INDEX BY BINARY_INTEGER;
30 --
31 ----------------------------------------------------------------------------------
32 -- Start of comments
33 --
34 -- Procedure Name : get_cust_config_from_line
35 -- Description : This logic is migrated from okl_stream_billing_pvt
36 -- Business Rules : irm_id, ibt_id, and customer_bank_account will be
37 -- overrided if contract line setting exists
38 -- Parameters :
39 --
40 -- Version : 1.0
41 -- HISTORY: : 12-Feb-2008 Bug 6755333 Populate line bill to address only if
42 -- line level bill to adress is not null
43 -- End of comments
44 ----------------------------------------------------------------------------------
45
46 PROCEDURE get_cust_config_from_line(
47 p_kle_id IN NUMBER
48 ,p_customer_address_id IN NUMBER
49 ,p_customer_bank_account_id IN NUMBER
50 ,p_receipt_method_id IN NUMBER
51 ,x_customer_address_id OUT NOCOPY NUMBER
52 ,x_customer_bank_account_id OUT NOCOPY NUMBER
53 ,x_receipt_method_id OUT NOCOPY NUMBER
54 -- BANK-ACCOUNT-UPTAKE-START
55 ,x_creation_method_code OUT NOCOPY VARCHAR2
56 ,x_bank_line_id1 OUT NOCOPY NUMBER
57 -- BANK-ACCOUNT-UPTAKE-START
58 )
59 IS
60 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
61 -- -----------------------------------------------------
62 -- Variable definitions for line level bill-to_support
63 -- -----------------------------------------------------
64 l_pmth_line_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
65 l_rct_line_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
66 lp_rct_line_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
67 l_bank_line_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
68
69 l_line_cust_bank_acct_id OKL_TRX_AR_INVOICES_V.customer_bank_account_id%TYPE;
70 l_line_cust_address_id OKL_TRX_AR_INVOICES_V.ibt_id%TYPE;
71 l_line_receipt_method_id OKL_TRX_AR_INVOICES_V.irm_id%TYPE;
72 l_line_term_id OKL_TRX_AR_INVOICES_V.irt_id%TYPE;
73
74 l_khr_id okc_k_headers_b.id%type;
75
76 CURSOR get_chr_id (p_kle_id NUMBER) IS
77 select dnz_chr_id
78 from okc_k_lines_b
79 where id = p_kle_id;
80
81 -- nikshah -- Bug # 5484903 Fixed,
82 -- Changed CURSOR line_bill_to_csr SQL definition
83 CURSOR line_bill_to_csr(p_khr_id NUMBER, p_kle_id NUMBER) IS
84 SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
85 FROM okc_k_headers_b khr
86 , okx_cust_site_uses_v cs
87 , okc_k_lines_b cle
88 , hz_customer_profiles cp
89 WHERE khr.id = p_khr_id
90 AND cle.dnz_chr_id = khr.id
91 AND cle.chr_id IS NOT NULL
92 AND cle.id = p_kle_id
93 AND cle.BILL_TO_SITE_USE_ID = cs.id1
94 AND khr.bill_to_site_use_id = cp.site_use_id(+)
95 UNION
96 SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
97 FROM okc_k_headers_b khr
98 , okc_k_lines_b cle
99 , okc_k_items item
100 , okc_k_lines_b linked_asset
101 , okx_cust_site_uses_v cs
102 , hz_customer_profiles cp
103 WHERE khr.id = p_khr_id
104 AND cle.dnz_chr_id = khr.id
105 AND cle.id = p_kle_id
106 AND cle.chr_id IS NULL
107 AND cle.id = item.cle_id
108 AND item.object1_id1 = linked_asset.id
109 AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
110 AND khr.bill_to_site_use_id = cp.site_use_id(+);
111
112 CURSOR cust_line_pmth_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
113 SELECT object1_id1
114 FROM OKC_RULES_B rul,
115 Okc_rule_groups_B rgp
116 WHERE rul.rgp_id = rgp.id AND
117 rgp.rgd_code = 'LABILL' AND
118 rgp.cle_id = p_kle_id AND
119 rul.rule_information_category = 'LAPMTH' AND
120 rgp.dnz_chr_id = p_khr_id
121 UNION
122 SELECT rul.object1_id1
123 FROM okc_k_lines_b cle
124 , okc_k_items_v item
125 , okc_k_lines_b linked_asset
126 , OKC_RULES_B rul
127 , Okc_rule_groups_B rgp
128 WHERE cle.dnz_chr_id = p_khr_id AND
129 cle.id = p_kle_id AND
130 cle.chr_id IS NULL AND
131 cle.id = item.cle_id AND
132 item.object1_id1 = linked_asset.id AND
133 linked_asset.id = rgp.cle_id AND
134 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
135 rgp.rgd_code = 'LABILL' AND
136 rul.rgp_id = rgp.id AND
137 rul.rule_information_category = 'LAPMTH';
138
139 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
140 SELECT C.RECEIPT_METHOD_ID
141 FROM RA_CUST_RECEIPT_METHODS C
142 WHERE C.cust_receipt_method_id = p_cust_rct_mthd;
143
144 -- Bank Account Cursor
145 CURSOR rcpt_method_csr ( p_rct_method_id NUMBER) IS
146 SELECT C.CREATION_METHOD_CODE
147 FROM AR_RECEIPT_METHODS M,
148 AR_RECEIPT_CLASSES C
149 WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
150 M.receipt_method_id = p_rct_method_id;
151
152 CURSOR cust_line_bank_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
153 SELECT object1_id1
154 FROM OKC_RULES_B rul,
155 Okc_rule_groups_B rgp
156 WHERE rul.rgp_id = rgp.id AND
157 rgp.cle_id = p_kle_id AND
158 rgp.rgd_code = 'LABILL' AND
159 rul.rule_information_category = 'LABACC' AND
160 rgp.dnz_chr_id = p_khr_id
161 UNION
162 SELECT rul.object1_id1
163 FROM okc_k_lines_b cle
164 , okc_k_items_v item
165 , okc_k_lines_b linked_asset
166 , OKC_RULES_B rul
167 , Okc_rule_groups_B rgp
168 WHERE cle.dnz_chr_id = p_khr_id AND
169 cle.id = p_kle_id AND
170 cle.chr_id IS NULL AND
171 cle.id = item.cle_id AND
172 item.object1_id1 = linked_asset.id AND
173 linked_asset.id = rgp.cle_id AND
174 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
175 rgp.rgd_code = 'LABILL' AND
176 rul.rgp_id = rgp.id AND
177 rul.rule_information_category = 'LABACC';
178
179 CURSOR bank_acct_csr(p_id1 NUMBER) IS
180 SELECT bank_account_id
181 FROM OKX_RCPT_METHOD_ACCOUNTS_V
182 WHERE id1 = p_id1;
183
184 BEGIN
185
186 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
187 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','p_kle_id:'||p_kle_id);
188 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','p_customer_address_id:'||p_customer_address_id);
189 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','p_customer_bank_account_id:'||p_customer_bank_account_id);
190 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','p_receipt_method_id:'||p_receipt_method_id);
191 END IF;
192
193 -- assign to out parameters 1st
194 x_customer_address_id := p_customer_address_id;
195 x_customer_bank_account_id := p_customer_bank_account_id;
196 x_receipt_method_id := p_receipt_method_id;
197
198 -- -------------------------------------------------------
199 -- Get bill-to_site and payment term, receipt method and
200 -- bank account id
201 -- -------------------------------------------------------
202 l_line_cust_address_id := NULL;
203 l_line_term_id := NULL;
204
205 OPEN rcpt_method_csr (p_receipt_method_id);
206 FETCH rcpt_method_csr INTO l_rct_line_method_code;
207 CLOSE rcpt_method_csr;
208
209 -- BANK-ACCOUNT-UPTAKE-START
210 x_creation_method_code := l_rct_line_method_code;
211 -- BANK-ACCOUNT-UPTAKE-END
212
213 IF (p_kle_id IS NOT NULL AND p_kle_id <> okl_api.g_miss_num) THEN
214
215 OPEN get_chr_id(p_kle_id);
216 FETCH get_chr_id INTO l_khr_id;
217 CLOSE get_chr_id;
218
219 OPEN line_bill_to_csr(l_khr_id, p_kle_id );
220 FETCH line_bill_to_csr INTO l_line_cust_address_id, l_line_term_id;
221 CLOSE line_bill_to_csr;
222
223 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
224 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_khr_id:'||l_khr_id);
225 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_line_cust_address_id:'||l_line_cust_address_id);
226 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_line_term_id:'||l_line_term_id);
227 END IF;
228
229
230 -- IF l_line_cust_address_id IS NOT NULL THEN --sosharma commented bug 6788194
231
232 l_pmth_line_id1 := NULL;
233 l_line_receipt_method_id := NULL;
234 -- l_rct_line_method_code := NULL;
235 l_bank_line_id1 := NULL;
236
237 OPEN cust_line_pmth_csr (l_khr_id, p_kle_id );
238 FETCH cust_line_pmth_csr INTO l_pmth_line_id1;
239 CLOSE cust_line_pmth_csr;
240
241 OPEN rcpt_mthd_csr( l_pmth_line_id1 );
242 FETCH rcpt_mthd_csr INTO l_line_receipt_method_id;
243 CLOSE rcpt_mthd_csr;
244
245 OPEN rcpt_method_csr (l_line_receipt_method_id);
246 FETCH rcpt_method_csr INTO lp_rct_line_method_code;
247 CLOSE rcpt_method_csr;
248
249 -- BANK-ACCOUNT-UPTAKE-START
250 -- sosharma changes for setting variables only if valus is prsent
251 IF lp_rct_line_method_code IS NOT NULL THEN
252 x_creation_method_code := lp_rct_line_method_code;
253 l_rct_line_method_code:=lp_rct_line_method_code;
254 END IF;
255 -- BANK-ACCOUNT-UPTAKE-END
256 IF (l_rct_line_method_code <> 'MANUAL') THEN
257 OPEN cust_line_bank_csr ( l_khr_id, p_kle_id );
258 FETCH cust_line_bank_csr INTO l_bank_line_id1;
259 CLOSE cust_line_bank_csr;
260 -- sosharma changes for setting variables only if value is prsent
261 IF l_bank_line_id1 IS NOT NULL THEN
262 x_bank_line_id1 := l_bank_line_id1;
263 END IF;
264
265 OPEN bank_acct_csr( l_bank_line_id1 );
266 FETCH bank_acct_csr INTO l_line_cust_bank_acct_id;
267 CLOSE bank_acct_csr;
268 END IF;
269
270 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
271 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_pmth_line_id1:'||l_pmth_line_id1);
272 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_line_receipt_method_id:'||l_line_receipt_method_id);
273 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_rct_line_method_code:'||l_rct_line_method_code);
274 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_bank_line_id1:'||l_bank_line_id1);
275 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','l_line_cust_address_id:'||l_line_cust_address_id);
276 END IF;
277
278
279 -- END IF;-- sosharma commented bug 6788194
280
281 -- Bug 6755333
282 -- Populate line cust address id and line receipt method
283 IF l_line_cust_address_id IS NOT NULL THEN -- rmunjulu bug 5594606 added
284
285 x_customer_address_id := l_line_cust_address_id; -- rmunjulu R12 Fixes -- added
286 END IF;
287
288 IF l_line_receipt_method_id IS NOT NULL THEN
289 x_receipt_method_id := l_line_receipt_method_id; -- rmunjulu bug 5594606 -- l_ext_receipt_method_id; -- rmunjulu R12 Fixes -- added
290 END IF;
291
292 IF l_line_cust_bank_acct_id IS NOT NULL THEN
293 x_customer_bank_account_id := l_line_cust_bank_acct_id;
294 END IF;
295
296 END IF; --IF p_kle_id IS NOT NULL THEN
297
298 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
299 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','x_customer_address_id:'||x_customer_address_id);
300 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','x_receipt_method_id:'||x_receipt_method_id);
301 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_cust_config_from_line.debug','x_customer_bank_account_id:'||x_customer_bank_account_id);
302 END IF;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
307 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in get_cust_config_from_line');
308 END IF;
309
310 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
311 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
312 'EXCEPTION :'||'OTHERS');
313 END IF;
314
315 END get_cust_config_from_line;
316 --
317
318 ----------------------------------------------------------------------------------
319 -- Start of comments
320 --
321 -- Procedure Name : nullout_rec_method
322 -- Description : This logic is migrated from okl_internal_to_external
323 -- Business Rules :
324 -- Parameters :
325 --
326 -- Version : 1.0
327 -- added p_payment_trxn_extension_id and x_payment_trxn_extension_id parameters for bug 6788231
328 --
329 -- End of comments
330 ----------------------------------------------------------------------------------
331 PROCEDURE nullout_rec_method(
332 p_contract_id IN NUMBER
333 ,p_Quote_number IN NUMBER
334 ,p_sty_id IN NUMBER
335 ,p_customer_bank_account_id IN NUMBER
336 ,p_receipt_method_id IN NUMBER -- irm_id
337 ,p_payment_trxn_extension_id IN NUMBER
338 ,x_customer_bank_account_id OUT NOCOPY NUMBER
339 ,x_receipt_method_id OUT NOCOPY NUMBER
340 ,x_payment_trxn_extension_id OUT NOCOPY NUMBER
341 )
342 IS
343 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
344 lx_remrkt_sty_id number;
345
346 BEGIN
347 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
348 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_contract_id:'||p_contract_id);
349 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_Quote_number:'||p_Quote_number);
350 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_sty_id:'||p_sty_id);
351 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_customer_bank_account_id:'||p_customer_bank_account_id);
352 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_receipt_method_id:'||p_receipt_method_id);
353 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','p_payment_trxn_extension_id:'||p_payment_trxn_extension_id);
354 END IF;
355 IF p_contract_id IS NOT NULL THEN
356 --bug 5160519 : Sales Order Billing
357 -- Order Management sales for remarketing, these billing details are
358 --purely from the Order, so if payment method,Bank Account is not passed,
359 --then pass as NULL.
360
361 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
362 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','calling Okl_Streams_Util.get_primary_stream_type');
363 END IF;
364 --get primary stream type for remarketing stream
365 Okl_Streams_Util.get_primary_stream_type(p_contract_id,
366 'ASSET_SALE_RECEIVABLE',
367 l_return_status,
368 lx_remrkt_sty_id);
369 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
370 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','Okl_Streams_Util.get_primary_stream_type returned l_return_status:'||l_return_status||' and lx_remrkt_sty_id:'||lx_remrkt_sty_id);
371 END IF;
372 IF l_return_status = Okl_Api.g_ret_sts_success THEN
373
374 IF(lx_remrkt_sty_id = p_sty_id) THEN
375
376 x_customer_bank_account_id := NULL;
377 x_receipt_method_id := NULL;
378 x_payment_trxn_extension_id:=NULL; -- added for bug 6788231
379 ELSE
380 x_customer_bank_account_id := p_customer_bank_account_id;
381 x_receipt_method_id := p_receipt_method_id;
382 x_payment_trxn_extension_id:= p_payment_trxn_extension_id;
383 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
384 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_customer_bank_account_id:'||x_customer_bank_account_id);
385 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_receipt_method_id:'||x_receipt_method_id);
386 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_payment_trxn_extension_id:'||x_payment_trxn_extension_id);
387 END IF;
388 END IF;
389 END IF;
390
391 --bug 5160519 : end
392
393 --bug 5160519 : Lease Vendor Billing
394 -- For termination quote to Lease Vendor AND repurchase quote to Lease Vendor
395 -- on VPA...the payment method should be taken from the Vendor Billing Details,
396 -- if NULL, then as per above, pass nothing to AR and let AR default to Primary
397 -- payment method
398
399 IF p_Quote_number IS NOT NULL THEN
400 -- if termination record
401 x_receipt_method_id := NULL;
402 x_customer_bank_account_id := NULL;
403 x_payment_trxn_extension_id:=NULL; -- added for bug 6788231
404 ELSE
405 x_customer_bank_account_id := p_customer_bank_account_id;
406 x_receipt_method_id := p_receipt_method_id;
407 x_payment_trxn_extension_id:= p_payment_trxn_extension_id; -- added for bug 6788231
408 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
409 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_customer_bank_account_id:'||x_customer_bank_account_id);
410 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_receipt_method_id:'||x_receipt_method_id);
411 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.nullout_rec_method.debug','x_payment_trxn_extension_id:'||x_payment_trxn_extension_id);
412 END IF;
413 END IF;
414 -- If p_contract_id IS NULL then, return the same values as passed into the procedure
415 ELSE
416 x_customer_bank_account_id := p_customer_bank_account_id;
417 x_receipt_method_id := p_receipt_method_id;
418 x_payment_trxn_extension_id:= p_payment_trxn_extension_id;
419 END IF; -- IF p_contract_id IS NOT NULL THEN
420 --bug 5160519:end
421
422 --bug 5160519
423 --if not remarketing invoice
424
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
429 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in nullout_rec_method');
430 END IF;
431
432 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
434 'EXCEPTION :'||'OTHERS');
435 END IF;
436
437 END nullout_rec_method;
438
439 ----------------------------------------------------------------------------------
440 -- Start of comments
441 --
442 -- Procedure Name : Get_chr_inv_grp
443 -- Description :
444 -- Business Rules :
445 -- Parameters :
446 --
447 -- Version : 1.0
448 -- End of comments
449 ----------------------------------------------------------------------------------
450 PROCEDURE Get_chr_inv_grp(
451 p_inf_id IN NUMBER
452 ,p_sty_id IN NUMBER
453 ,x_group_by_contract_yn OUT NOCOPY VARCHAR2
454 ,x_contract_level_yn OUT NOCOPY VARCHAR2
455 ,x_group_asset_yn OUT NOCOPY VARCHAR2
456 ,x_invoice_group OUT NOCOPY VARCHAR2
457 )
458 is
459
460 CURSOR inv_format_csr ( p_format_id IN NUMBER, p_stream_id IN NUMBER ) IS
461 SELECT inf.contract_level_yn, -- Multi-contract Y/N
462 ity.group_by_contract_yn, -- Provide Contract Details
463 ity.group_asset_yn, -- Combine Assets
464 inf.name -- invoice group
465 FROM okl_invoice_formats_v inf,
466 okl_invoice_types_v ity,
467 okl_invc_line_types_v ilt,
468 okl_invc_frmt_strms_v frs,
469 okl_strm_type_v sty
470 WHERE inf.id = ity.inf_id
471 AND ity.inf_id = p_format_id
472 AND ilt.ity_id = ity.id
473 AND frs.ilt_id = ilt.id
474 AND sty.id = frs.sty_id
475 AND frs.sty_id = p_stream_id;
476
477 CURSOR inv_format_default_csr ( p_format_id IN NUMBER ) IS
478 SELECT inf.contract_level_yn, -- Multi-contract Y/N
479 ity.group_by_contract_yn, -- Provide Contract Details
480 ity.group_asset_yn, -- Combine Assets
481 inf.name -- invoice group
482 FROM okl_invoice_formats_v inf,
483 okl_invoice_types_v ity,
484 okl_invc_line_types_v ilt
485 WHERE inf.id = ity.inf_id
486 AND ity.inf_id = p_format_id
487 AND ilt.ity_id = ity.id;
488
489 begin
490
491 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
492 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','p_inf_id:'||p_inf_id);
493 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','p_sty_id:'||p_sty_id);
494 END IF;
495
496 IF p_inf_id IS NOT NULL and p_sty_id IS NOT NULL THEN
497
498 OPEN inv_format_csr ( p_inf_id, p_sty_id);
499 FETCH inv_format_csr INTO x_contract_level_yn,
500 x_group_by_contract_yn,
501 x_group_asset_yn,
502 x_invoice_group;
503 CLOSE inv_format_csr;
504
505 ELSIF p_inf_id IS NOT NULL and p_sty_id IS NULL THEN
506
507 OPEN inv_format_default_csr ( p_inf_id);
508 FETCH inv_format_default_csr INTO x_contract_level_yn,
509 x_group_by_contract_yn,
510 x_group_asset_yn,
511 x_invoice_group;
512 CLOSE inv_format_default_csr;
513
514 ELSE
515
516 x_group_by_contract_yn := NULL;
517 x_contract_level_yn := NULL;
518 x_group_asset_yn := NULL;
519 x_invoice_group := NULL;
520
521 END IF;
522
523 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
524 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','x_group_by_contract_yn:'||x_group_by_contract_yn);
525 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','x_contract_level_yn:'||x_contract_level_yn);
526 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','x_group_asset_yn:'||x_group_asset_yn);
527 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_chr_inv_grp.debug','x_invoice_group:'||x_invoice_group);
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
533 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_chr_inv_grp');
534 END IF;
535
536 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
537 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
538 'EXCEPTION :'||'OTHERS');
539 END IF;
540
541 end Get_chr_inv_grp;
542
543 ----------------------------------------------------------------------------------
544 -- Start of comments
545 --
546 -- Procedure Name : Get_acct_disb
547 -- Description :
548 -- Business Rules :
549 -- Parameters :
550 --
551 -- Version : 1.0
552 -- rmunjulu : R12 Fixes - changed the out parameters to table type
553 -- End of comments
554 ----------------------------------------------------------------------------------
555 PROCEDURE Get_acct_disb(
556 p_tld_id IN NUMBER
557 ,x_dist_tbl OUT NOCOPY dist_tbl_type
558 --,x_account_class OUT NOCOPY VARCHAR2
559 --,x_dist_amount OUT NOCOPY VARCHAR2
560 --,x_dist_percent OUT NOCOPY VARCHAR2
561 --,x_code_combination_id OUT NOCOPY VARCHAR2
562 )
563 is
564
565 -- Selects distributions created by the accounting Engine
566 CURSOR acc_dstrs_csr(p_source_id IN NUMBER, p_source_table IN VARCHAR2) IS
567 SELECT cr_dr_flag,
568 code_combination_id,
569 source_id,
570 amount,
571 percentage,
572 --Start code changes for rev rec by fmiao on 10/05/2004
573 NVL(comments, '-99') comments --End code changes for rev rec by fmiao on 10/05/2004
574 FROM okl_trns_acc_dstrs
575 WHERE source_id = p_source_id
576 AND source_table = p_source_table;
577
578 i NUMBER := 1; -- rmunjulu R12 Fixes
579 l_account_class varchar2(150); -- rmunjulu R12 Fixes
580 BEGIN
581 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
582 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','p_tld_id:'||p_tld_id);
583 END IF;
584 -- rmunjulu R12 Fixes -- modified code to populate the new out TBL type
585
586 -- rmunjulu R12 Fixes -- modified code to populate x_dist_tbl with values from cursor
587
588 FOR acc_dtls_rec IN acc_dstrs_csr(p_tld_id, 'OKL_TXD_AR_LN_DTLS_B') LOOP
589
590 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
591 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.cr_dr_flag'||acc_dtls_rec.cr_dr_flag);
592 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.code_combination_id'||acc_dtls_rec.code_combination_id);
593 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.source_id'||acc_dtls_rec.source_id);
594 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.amount'||acc_dtls_rec.amount);
595 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.percentage'||acc_dtls_rec.percentage);
596 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','acc_dtls_rec.comments'||acc_dtls_rec.comments);
597 END IF;
598 -- rmunjulu R12 Fixes -- commented and moved below
599 --x_dist_tbl(i).code_combination_id := acc_dtls_rec.code_combination_id;
600 --x_dist_tbl(i).dist_amount := acc_dtls_rec.amount;
601 --x_dist_tbl(i).dist_percent := acc_dtls_rec.percentage;
602
603 -- rmunjulu R12 Fixes Use local variable for l_account_class
604 IF acc_dtls_rec.amount > 0 THEN
605
606 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
607 --x_dist_tbl(i).account_class := 'REV'; -- rmunjulu R12 Fixes
608 l_account_class := 'REV'; -- rmunjulu R12 Fixes
609 ELSE
610 --x_dist_tbl(i).account_class := 'REC'; -- rmunjulu R12 Fixes
611 l_account_class := 'REC'; -- rmunjulu R12 Fixes
612 END IF;
613 ELSE
614 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
615 --x_dist_tbl(i).account_class := 'REC'; -- rmunjulu R12 Fixes
616 l_account_class := 'REC'; -- rmunjulu R12 Fixes
617 ELSE
618 --x_dist_tbl(i).account_class := 'REV'; -- rmunjulu R12 Fixes
619 l_account_class := 'REV'; -- rmunjulu R12 Fixes
620 END IF;
621 END IF;
622
623 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
624 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','l_account_class'||l_account_class);
625 END IF;
626
627 --Start code changes for rev rec by fmiao on 10/05/2004
628 -- rmunjulu R12 Fixes, modified the condition to create
629 -- distributions only in case of REV
630 /*
631 02-Apr-2008 ankushar
632 Bug# 6491269 Removed the condition on 'REC' since this needs to be now passed to AR interface
633 after AR has given the fix for multiple REC accounts
634 Start Changes
635 */
636 /*IF (((acc_dtls_rec.comments = 'CASH_RECEIPT'
637 AND l_account_class <> 'REC') OR(acc_dtls_rec.comments <> 'CASH_RECEIPT'))
638 AND l_account_class <> 'REC') THEN*/
639
640 -- rmunjulu R12 Fixes moved to here from above
641 x_dist_tbl(i).code_combination_id := acc_dtls_rec.code_combination_id;
642 x_dist_tbl(i).dist_amount := acc_dtls_rec.amount;
643 x_dist_tbl(i).dist_percent := acc_dtls_rec.percentage;
644
645 -- rmunjulu R12 Fixes set account class
646 x_dist_tbl(i).account_class := l_account_class;
647
648 IF(acc_dtls_rec.comments = 'CASH_RECEIPT') THEN
649 x_dist_tbl(i).account_class := 'UNEARN';
650 END IF;
651
652 -- i := i + 1; -- rmunjulu R12 Fixes -- Increment inside IF --nikshah Bug 8238593, incrementing i after below if statement
653
654 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
655 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','x_dist_tbl('||i||').code_combination_id:'||x_dist_tbl(i).code_combination_id);
656 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','x_dist_tbl('||i||').dist_amount:'||x_dist_tbl(i).dist_amount);
657 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','x_dist_tbl('||i||').dist_amount:'||x_dist_tbl(i).dist_amount);
658 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','x_dist_tbl('||i||').dist_percent:'||x_dist_tbl(i).dist_percent);
659 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','x_dist_tbl('||i||').account_class:'||x_dist_tbl(i).account_class);
660 END IF;
661 -- END IF; -- ankushar Commented this as part of fix Bug # 6491269
662 /* 02-Apr-2008 ankushar
663 End Changes
664 */
665 i := i + 1; -- rmunjulu R12 Fixes -- comment and move up --nikshah Bug 8238593 incrementing i after if statement
666
667 END LOOP;
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
672 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_acct_disb');
673 END IF;
674
675 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
676 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
677 'EXCEPTION :'||'OTHERS');
678 END IF;
679
680 end Get_acct_disb;
681
682 ---------------------------------------------------------------------------
683 -- PROCEDURE get_customer_id from a contract
684 ---------------------------------------------------------------------------
685
686 PROCEDURE get_customer_id
687 ( l_contract_number IN VARCHAR2
688 ,l_customer_id OUT NOCOPY NUMBER
689 )
690 IS
691 --modified by pgomes on 21-aug-2003 for rules migration
692 CURSOR get_khr_id_csr ( p_contract_number VARCHAR2 ) IS
693 SELECT cust_acct_id
694 FROM okc_k_headers_b
695 where contract_number = p_contract_number;
696
697 --commented out by pgomes on 21-aug-2003 for rules migration
698 BEGIN
699 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
700 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','l_contract_number:'||l_contract_number);
701 END IF;
702 --modified by pgomes on 21-aug-2003 for rules migration
703 -- Get the customer acct id
704 OPEN get_khr_id_csr(l_contract_number);
705 FETCH get_khr_id_csr INTO l_customer_id;
706 CLOSE get_khr_id_csr;
707 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
708 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.Get_acct_disb.debug','l_customer_id:'||l_customer_id);
709 END IF;
710 --commented out by pgomes on 21-aug-2003 for rules migration
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
715 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Failure in Get_Customer_Id');
716 END IF;
717
718 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
720 'EXCEPTION :'||'OTHERS');
721 END IF;
722
723 END get_customer_id;
724
725 ---------------------------------------------------------------------------
726 -- PROCEDURE get_auto_bank_dtls
727 ----------------------------------------------------------------------------------
728 -- Start of comments
729 --
730 -- Procedure Name : get_auto_bank_dtls
731 -- Description : Moved the code to derive the Payment Transaction Extension ID
732 -- into a separate procedure
733 -- Business Rules : Derives the Payment Transaction Extension ID from IBY
734 -- Parameters :
735 --
736 -- Version : 1.0
737 -- HISTORY: : schodava 25-Feb-08 created
738 -- End of comments
739 ----------------------------------------------------------------------------------
740 PROCEDURE get_auto_bank_dtls
741 ( p_api_version IN NUMBER,
742 p_init_msg_list IN VARCHAR2,
743 p_khr_id IN NUMBER,
744 p_customer_address_id IN NUMBER,
745 p_bank_id IN VARCHAR2,
746 p_trx_date IN DATE,
747 x_payment_trxn_extension_id OUT NOCOPY NUMBER,
748 x_customer_bank_account_id OUT NOCOPY NUMBER,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2
752
753 ) IS
754 -- BANK-ACCOUNT-UPTAKE-START
755 l_bank_id okc_rules_b.object1_id1%TYPE := NULL;
756 l_return_status VARCHAR2(1);
757 l_msg_count NUMBER;
758 l_msg_data VARCHAR2(32767);
759
760 l_payer IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
761 l_payer_equivalency VARCHAR2(500);
762 l_pmt_channel IBY_FNDCPT_PMT_CHNNLS_VL.payment_channel_code%TYPE;
763 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.trxnextension_rec_type;
764 l_entity_id NUMBER;
765 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
766 -- This values based on global variables from FND_API G_TRUE and G_FALSE;
767 l_true VARCHAR2(1) := 'T';
768
769 CURSOR instr_type_csr(p_instr_use_id NUMBER)
770 IS
771 SELECT ipiu.payment_function
772 , fpc.payment_channel_code
773 , ipiu.payment_flow
774 FROM iby_pmt_instr_uses_all ipiu
775 , iby_fndcpt_pmt_chnnls_vl fpc
776 WHERE instrument_id = p_instr_use_id
777 AND fpc.instrument_type = ipiu.instrument_type;
778
779 instr_type_rec instr_type_csr%ROWTYPE;
780
781 CURSOR chr_dtls_csr (cp_khr_id IN NUMBER)
782 IS
783 SELECT cpr.object1_id1 party_id
784 , khr.authoring_org_id org_id
785 , khr.cust_acct_id
786 , khr.contract_number
787 , khr.application_id app_id
788 , khr.BILL_TO_SITE_USE_ID
789 , khr.CURRENCY_CODE
790 FROM OKL_K_HEADERS_FULL_V khr
791 , OKC_K_PARTY_ROLES_B CPR
792 WHERE khr.ID = cpr.chr_id
793 AND cpr.rle_code = 'LESSEE'
794 AND khr.ID = cp_khr_id;
795 chr_dtls_rec chr_dtls_csr%ROWTYPE;
796
797 -- Fetch the Contract Level Bank Id
798 CURSOR cust_bank_csr ( cp_khr_id NUMBER ) IS
799 SELECT object1_id1
800 FROM OKC_RULES_B rul,
801 Okc_rule_groups_B rgp
802 WHERE rul.rgp_id = rgp.id AND
803 rgp.rgd_code = 'LABILL' AND
804 rgp.dnz_chr_id = rgp.chr_id AND
805 rul.rule_information_category = 'LABACC' AND
806 rgp.dnz_chr_id = cp_khr_id;
807 cust_bank_rec cust_bank_csr%ROWTYPE;
808
809
810 -- BANK-ACCOUNT-UPTAKE-END
811 -- gboomina modified for bug 6832065 - Start
812 -- Cursor to get site use id
813 CURSOR cust_site_use_id_csr (cp_customer_address_id NUMBER) IS
814 Select site_use_id
815 from HZ_CUST_SITE_USES
816 where cust_acct_site_id=cp_customer_address_id
817 and site_use_code = 'BILL_TO';
818 l_site_use_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
819 -- gboomina modified for bug 6832065 - End
820
821 --sosharma bug 6608452
822 --instrument_payment_use_id to be passed to IBY Bug 7162253
823 CURSOR instrument_payment_use_id_csr(
824 p_payment_function iby_pmt_instr_uses_all.payment_function%TYPE,
825 p_party_id okc_k_party_roles_b.object1_id1%TYPE,
826 p_cust_acct_id okl_k_headers_full_v.cust_acct_id%TYPE,
827 p_site_use_id hz_cust_site_uses_all.site_use_id%TYPE,
828 p_org_id okl_k_headers_full_v.authoring_org_id%TYPE,
829 p_payment_flow iby_pmt_instr_uses_all.payment_flow%TYPE) IS
830 SELECT instrument_payment_use_id
831 FROM iby_pmt_instr_uses_all ibyinstr, iby_external_payers_all pay
832 WHERE ibyinstr.instrument_id = p_bank_id
833 AND ibyinstr.payment_flow = p_payment_flow
834 AND pay.payment_function = p_payment_function
835 AND ibyinstr.EXT_PMT_PARTY_ID = pay.EXT_PAYER_ID
836 AND pay.party_id = p_party_id
837 AND pay.org_id = p_org_id
838 AND pay.cust_account_id = p_cust_acct_id
839 AND pay.acct_site_use_id = p_site_use_id;
840
841 l_instrument_payment_use_id iby_pmt_instr_uses_all.instrument_payment_use_id%type;
842
843 CURSOR cust_instr_payment_use_id_csr (
844 p_cust_acct_id okl_k_headers_full_v.cust_acct_id%TYPE,
845 p_site_use_id hz_cust_site_uses_all.site_use_id%TYPE,
846 p_currency_code okl_k_headers_full_v.currency_code%type
847 ) IS
848 Select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(instr_assignment_id),
849 '1' || to_char(instr_assignment_id))), 2)
850 from IBY_FNDCPT_payer_assgn_instr_v
851 where instrument_type = 'BANKACCOUNT'
852 and cust_account_id = p_cust_acct_id
853 and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
854 and currency_code = p_currency_code
855 and order_of_preference = (select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(order_of_preference),
856 '1' || to_char(order_of_preference))), 2) from IBY_FNDCPT_payer_assgn_instr_v
857 where instrument_type = 'BANKACCOUNT'
858 and cust_account_id = p_cust_acct_id
859 and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
860 and currency_code = p_currency_code
861 and p_trx_date between NVL(assignment_start_date, p_trx_date)
862 and NVL(assignment_end_date, p_trx_date)
863 )
864 and p_trx_date between NVL(assignment_start_date,p_trx_date)
865 and NVL(assignment_end_date,p_trx_date);
866
867 cursor instrument_id_csr (p_instr_payment_use_id iby_pmt_instr_uses_all.instrument_payment_use_id%type)
868 is
869 select instrument_id
870 from iby_pmt_instr_uses_all
871 where instrument_payment_use_id = p_instr_payment_use_id;
872
873 l_instrument_id iby_pmt_instr_uses_all.instrument_id%type;
874
875 BEGIN
876
877 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
878
879 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
880 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_api_version:'||p_api_version);
881 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_init_msg_list:'||p_init_msg_list);
882 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_khr_id:'||p_khr_id);
883 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_customer_address_id:'||p_customer_address_id);
884 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_bank_id:'||p_bank_id);
885 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','p_trx_date:'||p_trx_date);
886 END IF;
887
888 -- Fetch contract details
889 OPEN chr_dtls_csr (cp_khr_id => p_khr_id);
890 FETCH chr_dtls_csr INTO chr_dtls_rec;
891 CLOSE chr_dtls_csr;
892
893 OPEN cust_site_use_id_csr (p_customer_address_id);
894 FETCH cust_site_use_id_csr INTO l_site_use_id;
895 CLOSE cust_site_use_id_csr;
896
897 l_payer.party_id := chr_dtls_rec.party_id;
898 l_payer.org_type := 'OPERATING_UNIT';
899 l_payer.org_id := chr_dtls_rec.org_id;
900 l_payer.cust_account_id := chr_dtls_rec.cust_Acct_id;
901 l_payer.account_site_id := l_site_use_id;
902
903 l_bank_id := p_bank_id;
904
905 -- gboomina added for bug 7513216 - start
906 -- If Bank account is not passed from contract, get the bank account
907 -- defined at AR Customer setup
908 IF l_bank_id IS NOT NULL THEN
909 OPEN instr_type_csr(l_bank_id);
910 FETCH instr_type_csr INTO instr_type_rec;
911 CLOSE instr_type_csr;
912 OPEN instrument_payment_use_id_csr(instr_type_rec.payment_function,chr_dtls_rec.party_id,
913 chr_dtls_rec.cust_acct_id,l_site_use_id,
914 chr_dtls_rec.org_id, instr_type_rec.payment_flow );
915 FETCH instrument_payment_use_id_csr INTO l_instrument_payment_use_id;
916 CLOSE instrument_payment_use_id_csr;
917 ELSE
918 OPEN cust_instr_payment_use_id_csr(chr_dtls_rec.cust_Acct_id ,
919 l_site_use_id,
920 chr_dtls_rec.currency_code);
921 FETCH cust_instr_payment_use_id_csr INTO l_instrument_payment_use_id;
922 CLOSE cust_instr_payment_use_id_csr;
923 -- If Bank account is not defined at AR Customer Setup also, then throw error
924 IF l_instrument_payment_use_id IS NULL THEN
925 -- Write Contract Number in the log so that user can identify
926 -- for which contract it is failing
927 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Contract Number :'||chr_dtls_rec.contract_number);
928 fnd_message.set_name('AR', 'AR_RAXTRX-1763');
929 fnd_msg_pub.add;
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932
933 OPEN instrument_id_csr(l_instrument_payment_use_id);
934 FETCH instrument_id_csr INTO l_instrument_id;
935 CLOSE instrument_id_csr;
936
937 OPEN instr_type_csr(l_instrument_id);
938 FETCH instr_type_csr INTO instr_type_rec;
939 CLOSE instr_type_csr;
940
941 END IF;
942 -- gboomina bug 7513216 - end
943
944 -- l_instr_assignment := l_bank_id;
945 l_payer_equivalency := NULL;
946 l_payer.payment_function := instr_type_rec.payment_function;
947 l_pmt_channel := instr_type_rec.payment_channel_code;
948 -- ansethur 07-feb-2007 for 6788194
949 l_trxn_attribs.originating_application_id := chr_dtls_rec.app_id;
950 l_trxn_attribs.order_id := chr_dtls_rec.contract_number; -- Some dummy value, not sure of the significance, need to investigate further
951
952 l_trxn_attribs.po_number := NULL;
953 l_trxn_attribs.po_line_number := NULL;
954 l_trxn_attribs.trxn_ref_number1 := NULL;
955 l_trxn_attribs.trxn_ref_number2 := NULL;
956 l_trxn_attribs.instrument_security_code := NULL;
957 l_trxn_attribs.voiceauth_flag := NULL;
958 l_trxn_attribs.voiceauth_code := NULL;
959 l_trxn_attribs.voiceauth_date := NULL;
960 l_trxn_attribs.additional_info := NULL;
961
962 -- Default value to UPWARD
963 IF (l_payer_equivalency IS NULL)
964 THEN
965 l_payer_equivalency := 'UPWARD';
966 END IF;
967
968 -- Call to insert the transaction extension through Payments PL/SQL API
969 IBY_FNDCPT_TRXN_PUB.CREATE_TRANSACTION_EXTENSION(
970 p_api_version => p_api_version,
971 p_init_msg_list => p_init_msg_list,
972 p_commit => l_true,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_payer => l_payer,
977 p_payer_equivalency => l_payer_equivalency,
978 p_pmt_channel => l_pmt_channel,
979 p_instr_assignment => l_instrument_payment_use_id, --sosharma bug 6608452
980 p_trxn_attribs => l_trxn_attribs,
981 x_entity_id => l_entity_id,
982 x_response => l_response);
983
984
985 -- The values are based on FND_API. S, E, U (Success, Error, Unexpected
986 IF (x_return_status = 'S') THEN
987 okl_debug_pub.logmessage('AUTOINVOICE:Transaction Extension Id: '|| l_entity_id);
988 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
989 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_entity_id:'||l_entity_id);
990 END IF;
991
992 -- Assign out variables
993 x_payment_trxn_extension_id := l_entity_id;
994 ELSE
995 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
998 RAISE FND_API.G_EXC_ERROR;
999 END IF;
1000 END IF;
1001
1002 -- For a receipt method code of 'automatic', customer bank account id column is not used
1003 x_customer_bank_account_id := null;
1004
1005 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1006 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','x_payment_trxn_extension_id:'||x_payment_trxn_extension_id);
1007 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_auto_bank_dtls.debug','x_customer_bank_account_id:'||x_customer_bank_account_id);
1008 END IF;
1009
1010 EXCEPTION
1011 WHEN FND_API.G_EXC_ERROR THEN
1012 x_return_status := OKL_API.G_RET_STS_ERROR;
1013 FND_MSG_PUB.Count_And_Get
1014 (p_count => x_msg_count,
1015 p_data => x_msg_data);
1016
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1019 FND_MSG_PUB.Count_And_Get
1020 (p_count => x_msg_count,
1021 p_data => x_msg_data);
1022
1023 WHEN OTHERS THEN
1024 x_return_status := Okl_Api.G_RET_STS_ERROR;
1025 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1026 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, SQLERRM);
1027 END IF;
1028 END get_auto_bank_dtls;
1029
1030 ---------------------------------------------------------------------------
1031 -- PROCEDURE Get_rec_feeder
1032 ---------------------------------------------------------------------------
1033 PROCEDURE Get_REC_FEEDER
1034 ( p_api_version IN NUMBER
1035 , p_init_msg_list IN VARCHAR2
1036 , x_return_status OUT NOCOPY VARCHAR2
1037 , x_msg_count OUT NOCOPY NUMBER
1038 , x_msg_data OUT NOCOPY VARCHAR2
1039 , p_trx_date_from IN DATE
1040 , p_trx_date_to IN DATE
1041 , p_assigned_process IN VARCHAR2
1042 ) IS
1043
1044 l_xfer_tbl xfer_tbl_type;
1045
1046 type inv_lines_tbl_type is table of ra_interface_lines_all%rowtype index by binary_integer;
1047 type inv_dist_tbl_type is table of ra_interface_distributions_all%rowtype index by pls_integer;
1048 type sales_credits_tbl_type is table of ra_interface_salescredits_all%rowtype index by pls_integer;
1049 type ar_contingency_tbl_type is table of ar_interface_conts_all%rowtype index by pls_integer;
1050
1051 inv_lines_tbl inv_lines_tbl_type;
1052 inv_dist_tbl inv_dist_tbl_type;
1053 sales_credits_tbl sales_credits_tbl_type;
1054 ar_contingency_tbl ar_contingency_tbl_type;
1055
1056 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1057 l_group_by_contract_yn okl_invoice_types_v.group_by_contract_yn%type;
1058 l_group_asset_yn okl_invoice_types_v.group_asset_yn%type;
1059 l_contract_level_yn okl_invoice_formats_v.contract_level_yn%type;
1060 l_invoice_group okl_invoice_formats_v.name%type;
1061 l_khr_id okc_k_headers_b.id%type;
1062 l_bank_line_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
1063
1064
1065 CURSOR acc_sys_option is
1066 select account_derivation
1067 from okl_sys_acct_opts;
1068
1069 CURSOR xfer_csr IS
1070 SELECT
1071 TAI.ID TAI_ID
1072 -- , TIL.AMOUNT AMOUNT
1073 -- 19-Mar-2007 cklee -- Change amount referece to TLD instead |
1074 , TLD.AMOUNT AMOUNT
1075 , TIL.DESCRIPTION LINE_DESCRIPTION
1076 , NVL(TLD.INVENTORY_ITEM_ID, TIL.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
1077 , TIL.inv_receiv_line_code LINE_TYPE
1078 , TIL.QUANTITY
1079 , TIL.LINE_NUMBER
1080 , NVL(TLD.STY_ID, TIL.STY_ID) STY_ID
1081 , KHR.ID KHR_ID
1082 , KHR.CONTRACT_NUMBER
1083 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1084 -- , KLE.NAME ASSET_NUMBER
1085 , NULL ASSET_NUMBER
1086 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1087 , TLD.INVOICE_FORMAT_LINE_TYPE -- STREAM_GROUP
1088 , STY.NAME STREAM_TYPE
1089 , TAI.CURRENCY_CODE
1090 , TAI.currency_conversion_date
1091 , TAI.currency_conversion_rate
1092 , TAI.currency_conversion_type
1093 , TAI.CUST_TRX_TYPE_ID
1094 , TAI.IBT_ID CUSTOMER_ADDRESS_ID
1095 -- , TAI.CUSTOMER_BANK_ACCOUNT_ID
1096 , NVL(TIL.bank_acct_id, TAI.CUSTOMER_BANK_ACCOUNT_ID) CUSTOMER_BANK_ACCOUNT_ID
1097 , TAI.IXX_ID CUSTOMER_ID
1098 , TAI.DESCRIPTION HDR_DESCRIPTION
1099 , NULL INVOICE_MESSAGE
1100 , TAI.ORG_ID
1101 , TAI.IRM_ID RECEIPT_METHOD_ID
1102 , TAI.SET_OF_BOOKS_ID
1103 , TAI.TAX_EXEMPT_FLAG
1104 , TAI.IRT_ID TERM_ID
1105 , TAI.DATE_INVOICED TRX_DATE
1106 -- , TAI.TRX_NUMBER
1107 --if auto-transaction generation is turn on, invoice_number (trx_number) is not a required column.
1108 , NULL TRX_NUMBER -- refer to metalink: Note:277086.1
1109 , TAI.CONSOLIDATED_INVOICE_NUMBER
1110 , TLD.INVOICE_FORMAT_TYPE
1111 , TAI.INVOICE_PULL_YN
1112 , TAI.PRIVATE_LABEL
1113 , TAI.LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
1114 , NULL ACCOUNT_CLASS
1115 , NULL DIST_AMOUNT
1116 , NULL DIST_PERCENT
1117 , NULL CODE_COMBINATION_ID
1118 -- , XLS.LSM_ID
1119 , STY.ACCRUAL_YN rev_rec_basis
1120 , NULL CM_ACCT_RULE
1121 , TLD.TLD_ID_REVERSES rev_txn_id
1122 -- , NULL REV_LSM_ID
1123 , NVL(TLD.INVENTORY_ORG_ID, TIL.INVENTORY_ORG_ID) INVENTORY_ORG_ID
1124 , KHR.inv_organization_id WARE_HOUSE_ID
1125 , NVL(TLD.KLE_ID, TIL.KLE_ID) KLE_ID
1126 , NULL SHIP_TO
1127 , NULL l_inv_id
1128 , NULL uom_code
1129 , TLD.ID TXN_ID
1130 --
1131 -- R12 additional columns pass to AR interface
1132 , TAI.OKL_SOURCE_BILLING_TRX
1133 , TAI.Investor_Agreement_Number
1134 , TAI.Investor_Name
1135 , (select qte.quote_number from OKL_TRX_QUOTES_B qte where qte.id = TAI.QTE_ID) Quote_number
1136 , NULL rbk_request_number
1137 , TLD.RBK_ORI_INVOICE_NUMBER
1138 , TLD.RBK_ORI_INVOICE_LINE_NUMBER
1139 , TLD.RBK_ADJUSTMENT_DATE
1140 , TAI.INF_ID
1141 , TAI.TRY_ID
1142 , TRYT.NAME TRY_NAME
1143 -- bug 6744584: contingency fix, added contingecy_id..racheruv
1144 , STY.CONTINGENCY_ID
1145 , TLD.INVOICE_FORMAT_LINE_TYPE INVOICE_LINE_TYPE -- Bug 7045347
1146 FROM OKL_TXD_AR_LN_DTLS_B TLD,
1147 OKL_TXL_AR_INV_LNS_V TIL,
1148 OKL_TRX_AR_INVOICES_V TAI,
1149 OKC_K_HEADERS_ALL_B KHR,
1150 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1151 -- OKC_K_LINES_V KLE,
1152 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1153 OKL_STRM_TYPE_V STY,
1154 OKL_TRX_TYPES_TL TRYT,
1155 OKL_PARALLEL_PROCESSES OPP
1156 WHERE TLD.STY_ID = STY.ID
1157 AND TLD.TIL_ID_DETAILS = TIL.ID
1158 AND TIL.TAI_ID = TAI.ID
1159 AND TAI.KHR_ID = KHR.ID
1160 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1161 -- AND KLE.ID = TIL.KLE_ID
1162 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1163 AND TAI.TRY_ID = TRYT.ID
1164 AND TRYT.LANGUAGE = 'US'
1165 AND TAI.TRX_STATUS_CODE = 'SUBMITTED'
1166 AND OPP.OBJECT_TYPE = 'XTRX_CONTRACT'
1167 AND OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER
1168 AND OPP.ASSIGNED_PROCESS = p_assigned_process
1169 ORDER BY TAI.ID
1170 ;
1171
1172 -- l_try_name OKL_TRX_TYPES_TL.name%type;
1173 x_tax_det_rec OKL_PROCESS_SALES_TAX_PVT.tax_det_rec_type;
1174
1175 -- ------------------------------------------------
1176 -- Printing and debug log
1177 -- ------------------------------------------------
1178 l_request_id NUMBER;
1179
1180 CURSOR req_id_csr IS
1181 SELECT
1182 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
1183 FROM dual;
1184
1185 ------------------------------------------------------------
1186 -- Operating Unit
1187 ------------------------------------------------------------
1188 CURSOR op_unit_csr IS
1189 SELECT NAME
1190 FROM hr_operating_units
1191 WHERE organization_id = mo_global.get_current_org_id;
1192
1193
1194 -- For R12, OKL_TXD_AR_LN_DTLS_B will be invoice line.
1195 --
1196 CURSOR tld_cnt_csr( p_req_id NUMBER, p_sts VARCHAR2 ) IS
1197 SELECT count(1)
1198 FROM OKL_TXD_AR_LN_DTLS_B TLD
1199 WHERE EXISTS
1200 (SELECT 1
1201 FROM OKL_TRX_AR_INVOICES_B TAI,
1202 OKL_TXL_AR_INV_LNS_B TIL
1203 WHERE TLD.TIL_ID_DETAILS = TIL.ID
1204 AND TIL.TAI_ID = TAI.ID
1205 AND TAI.trx_status_code = p_sts
1206 AND TAI.request_id = p_req_id);
1207
1208
1209 --end: | 15-FEB-07 cklee R12 Billing enhancement project |
1210
1211 l_succ_cnt NUMBER;
1212 l_err_cnt NUMBER;
1213 l_op_unit_name hr_operating_units.name%TYPE;
1214 lx_msg_data VARCHAR2(450);
1215 l_msg_index_out NUMBER :=0;
1216
1217 -- ------------------------------------------------
1218 -- Bind variables to address issues in bug 3761940
1219 -- ------------------------------------------------
1220 processed_sts okl_trx_ar_invoices_b.trx_status_code%TYPE;
1221 error_sts okl_trx_ar_invoices_b.trx_status_code%TYPE;
1222
1223 -- -------------------------
1224 -- Bulk Fetch Size
1225 -- -------------------------
1226 L_FETCH_SIZE NUMBER := 10000;
1227 l_hdr_id NUMBER := -9999;
1228
1229 l_commit_cnt NUMBER := 0;
1230
1231 -- ----------------------
1232 -- Std Who columns
1233 -- ----------------------
1234 lx_last_updated_by okl_trx_ar_invoices_b.last_updated_by%TYPE := Fnd_Global.USER_ID;
1235 lx_last_update_login okl_trx_ar_invoices_b.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
1236 lx_request_id okl_trx_ar_invoices_b.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
1237
1238 lx_program_application_id
1239 okl_trx_ar_invoices_b.program_application_id%TYPE := Fnd_Global.PROG_APPL_ID;
1240 lx_program_id okl_trx_ar_invoices_b.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
1241
1242 -- ---------------------------
1243 -- Bulk Insert changes
1244 -- ---------------------------
1245
1246 CURSOR l_get_inv_org_yn_csr(cp_org_id IN NUMBER) IS
1247 SELECT lease_inv_org_yn
1248 FROM OKL_SYSTEM_PARAMS
1249 WHERE org_id = cp_org_id;
1250
1251 l_rev_rec_basis okl_strm_type_b.accrual_yn%type;
1252 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
1253 l_use_inv_org VARCHAR2(10) := NULL;
1254
1255
1256 --vthiruva - Bug 4222231..start
1257 --modified sales_rep_csr to remove hard coded where clause
1258 CURSOR sales_rep_csr(p_salesrep_id IN ra_salesreps.salesrep_id%TYPE) IS
1259 SELECT SALESREP_ID, SALESREP_NUMBER
1260 FROM ra_salesreps
1261 --WHERE NAME = 'No Sales Credit';
1262 WHERE SALESREP_ID = p_salesrep_id;
1263
1264 --added new cursor to fetch the sales rep for the contract.
1265 CURSOR get_sales_rep(p_contract_number okc_k_headers_b.contract_number%TYPE) IS
1266 SELECT contact.object1_id1
1267 FROM okc_k_headers_b hdr, okc_contacts contact
1268 WHERE contact.dnz_chr_id = hdr.id
1269 AND hdr.contract_number = p_contract_number
1270 AND contact.cro_code = 'SALESPERSON';
1271
1272 l_prev_contract_num okc_k_headers_b.contract_number%TYPE;
1273 l_sales_person okc_contacts_v.object1_id1%TYPE;
1274 --vthiruva - Bug 4222231..end
1275
1276 CURSOR sales_type_credit_csr IS
1277 SELECT sales_credit_type_id
1278 FROM so_sales_credit_types
1279 WHERE name = 'Quota Sales Credit';
1280
1281 l_salesrep_id ra_salesreps.SALESREP_ID%TYPE;
1282 l_salesrep_number ra_salesreps.SALESREP_NUMBER%TYPE;
1283 l_sales_type_credit so_sales_credit_types.sales_credit_type_id%TYPE;
1284
1285 l_kle_id NUMBER;
1286 l_top_kle_id NUMBER;
1287
1288 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1289 l_is_top_line number;
1290 is_top_line_flag boolean;
1291
1292 CURSOR is_top_line ( p_cle_id NUMBER ) IS
1293 SELECT 1
1294 FROM okc_k_lines_b kle
1295 where kle.id = p_cle_id
1296 and kle.cle_id is null; -- it's top line
1297
1298 CURSOR get_top_line ( p_cle_id NUMBER ) IS
1299 select cle.id--, lse.lty_code
1300 from okc_k_lines_b cle--,
1301 -- okc_line_styles_b lse
1302 -- where lse.id = cle.lse_id
1303 -- and cle.cle_id is null
1304 where cle.cle_id is null -- it's top line
1305 start with cle.id = p_cle_id
1306 connect by cle.id = prior cle.cle_id;
1307
1308 CURSOR get_top_line_name ( p_cle_id NUMBER ) IS
1309 select cle.name
1310 from OKC_K_LINES_V cle
1311 where cle.id = p_cle_id;
1312
1313 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1314
1315 l_chr_id okc_k_lines_b.chr_id%TYPE;
1316
1317 l_ship_to NUMBER;
1318
1319 lx_customer_id NUMBER;
1320
1321 -- Local Variables
1322 l_install_location_id NUMBER;
1323 l_location_id NUMBER;
1324
1325 -- Code Changed for Bug 3044872
1326 CURSOR get_inv_item_id ( p_fin_asset_line_id NUMBER ) IS
1327 SELECT c.OBJECT1_ID1
1328 FROM okc_k_lines_b a,
1329 okc_line_styles_b b,
1330 okc_k_items c
1331 WHERE a.cle_id = p_fin_asset_line_id
1332 AND b.lty_code = 'ITEM'
1333 AND a.lse_id = b.id
1334 AND a.id = c.cle_id;
1335
1336 l_inv_id NUMBER;
1337
1338 l_uom_code mtl_system_items.primary_uom_code%TYPE;
1339
1340 CURSOR get_uom_code ( p_inv_item_id NUMBER ) IS
1341 SELECT primary_uom_code
1342 FROM mtl_system_items
1343 WHERE inventory_item_id = p_inv_item_id;
1344
1345 l_temp_sold_fee VARCHAR2(1);
1346
1347
1348 CURSOR Ship_to_csr( p_kle_top_line IN NUMBER ) IS
1349 SELECT --cim.object1_id1 item_instance,
1350 --cim.object1_id2 "#",
1351 csi.install_location_id
1352 , csi.location_id
1353 FROM csi_item_instances csi,
1354 okc_k_items cim,
1355 okc_k_lines_b inst,
1356 okc_k_lines_b ib,
1357 okc_line_styles_b lse
1358 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
1359 AND cim.cle_id = ib.id
1360 AND ib.cle_id = inst.id
1361 AND inst.lse_id = lse.id
1362 AND lse.lty_code = 'FREE_FORM2'
1363 AND inst.cle_id = p_kle_top_line;
1364
1365 CURSOR Ship_to_csr2( p_customer_num NUMBER, p_install_location NUMBER, p_location NUMBER, p_org_id NUMBER ) IS
1366 SELECT a.CUST_ACCT_SITE_ID
1367 FROM hz_cust_acct_sites_all a,
1368 hz_cust_site_uses_all b,
1369 hz_party_sites c
1370 WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
1371 b.site_use_code = 'SHIP_TO' AND
1372 a.party_site_id = c.party_site_id AND
1373 a.cust_account_id = p_customer_num AND
1374 a.org_id = p_org_id AND
1375 c.party_site_id = p_install_location AND
1376 c.location_id = p_location;
1377
1378 CURSOR sold_service_fee_csr ( p_cle_id NUMBER ) IS
1379 SELECT '1'
1380 FROM okc_k_lines_v a,
1381 okc_line_styles_v b
1382 WHERE a.lse_id = b.id
1383 AND b.lty_code = 'SOLD_SERVICE'
1384 AND a.id = p_cle_id;
1385
1386 CURSOR get_service_inv_csr ( p_cle_id NUMBER ) IS
1387 SELECT c.object1_id1
1388 FROM okc_k_lines_v a,
1389 okc_line_styles_v b,
1390 okc_k_items c
1391 WHERE a.lse_id = b.id
1392 AND b.lty_code = 'SOLD_SERVICE'
1393 AND a.id = p_cle_id
1394 AND c.cle_id = a.id;
1395
1396
1397 type error_tbl_type is table of error_rec_type index by binary_integer;
1398
1399 error_tbl error_tbl_type;
1400
1401 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1402 --l_error_xsi_id okl_ext_sell_invs_v.id%TYPE;
1403 l_error_tai_id okl_trx_ar_invoices_b.id%TYPE;
1404 l_error_txd_id OKL_TXD_AR_LN_DTLS_B.id%TYPE;
1405 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1406
1407 hdr_cnt NUMBER;
1408 sales_cr_cnt NUMBER;
1409 cont_cnt NUMBER;
1410
1411 -- Bug 6619311
1412 /*CURSOR get_memo_line_id_csr IS
1413 SELECT MEMO_LINE_ID
1414 FROM ar_memo_lines
1415 WHERE NAME = 'Lease Upfront Tax';
1416
1417 l_memo_line_id ar_memo_lines.memo_line_id%TYPE;
1418 */
1419
1420 Cursor is_legacy_invoice (p_rev_txn_id OKL_TXD_AR_LN_DTLS_B.id%type) is
1421 select lsm_id
1422 from OKL_TXD_AR_LN_DTLS_B
1423 where id = p_rev_txn_id;
1424
1425 l_lsm_id OKL_TXD_AR_LN_DTLS_B.lsm_id%type;
1426
1427 Cursor get_invoice_line_id (p_rev_txn_id OKL_TXD_AR_LN_DTLS_B.id%type) is
1428 select a.customer_trx_line_id
1429 from ra_customer_trx_lines_all a
1430 where a.INTERFACE_LINE_ATTRIBUTE14 = to_char(p_rev_txn_id); -- AKP
1431
1432 l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type;
1433
1434 --akrangan addeed ebtax billing impacts coding start
1435 CURSOR tax_sources_csr(p_tai_id IN NUMBER
1436 ,p_tld_id IN number
1437 )
1438 IS
1439 SELECT txs.trx_id,
1440 txs.trx_line_id,
1441 txs.trx_level_type,
1442 txs.application_id,
1443 txs.event_class_code,
1444 txs.entity_code,
1445 inv.tax_line_id -- Bug 6619311
1446 FROM okl_tax_sources txs,
1447 okl_txl_ar_inv_lns_b inv
1448 ,okl_txd_ar_ln_dtls_b tld
1449 WHERE txs.trx_id = inv.txs_trx_id
1450 AND trx_line_id = inv.txs_trx_line_id
1451 AND inv.id = tld.til_id_details
1452 AND inv.tai_id = p_tai_id
1453 AND tld.id = p_tld_id;
1454
1455 tax_sources_rec tax_sources_csr%ROWTYPE;
1456 -- Bug 6619311
1457 CURSOR zx_lines_csr(p_zx_lines_id IN NUMBER)
1458 IS
1459 SELECT HISTORICAL_FLAG,
1460 TAX_REGIME_CODE,
1461 TAX,
1462 TAX_STATUS_CODE,
1463 TAX_RATE_CODE,
1464 TAX_JURISDICTION_CODE,
1465 TAXABLE_AMT,
1466 LEGAL_ENTITY_ID
1467 FROM ZX_LINES
1468 WHERE TAX_LINE_ID = p_zx_lines_id;
1469 zx_lines_rec zx_lines_csr%ROWTYPE;
1470
1471 tx NUMBER;
1472 l_tx NUMBER;
1473 --akrangan added ebtax billing impacts coding end
1474
1475 lx_dist_tbl dist_tbl_type; -- rmunjulu R12 Fixes
1476 n NUMBER; -- rmunjulu R12 Fixes
1477 l_creation_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
1478
1479 -- gboomina added for bug 7513216 - start
1480 cursor get_khr_id_csr (p_contract_number okc_k_headers_all_b.contract_number%type)
1481 is
1482 select id from okc_k_headers_all_b
1483 where contract_number = p_contract_number;
1484 -- gboomina added for bug 7513216 - end
1485 BEGIN
1486
1487 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
1488
1489 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1490 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_arintf_pvt','Begin(+)');
1491 END IF;
1492
1493 -- ------------------------
1494 -- Print Input variables
1495 -- ------------------------
1496 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1497 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_trx_date_from '||p_trx_date_from);
1498 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_trx_date_to '||p_trx_date_to);
1499 END IF;
1500 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1501 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','p_trx_date_from:'||p_trx_date_from);
1502 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','p_trx_date_to:'||p_trx_date_to);
1503 END IF;
1504
1505
1506 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1507
1508 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1509 OPEN acc_sys_option;
1510 FETCH acc_sys_option INTO G_ACC_SYS_OPTION;
1511 CLOSE acc_sys_option;
1512 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1513
1514 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1515 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','G_ACC_SYS_OPTION:'||G_ACC_SYS_OPTION);
1516 END IF;
1517
1518 -- ----------------------------
1519 -- Work out common parameters
1520 -- ----------------------------
1521
1522 OPEN l_get_inv_org_yn_csr( l_org_id );
1523 FETCH l_get_inv_org_yn_csr INTO l_use_inv_org;
1524 CLOSE l_get_inv_org_yn_csr;
1525
1526 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1527 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_use_inv_org:'||l_use_inv_org);
1528 END IF;
1529
1530 --vthiruva - Bug 4222231..start
1531 --defaulted salesrep variables to 'No Sales Credit'
1532 l_salesrep_id := -3;
1533 l_salesrep_number := -3;
1534 l_prev_contract_num := NULL;
1535 --vthiruva - Bug 4222231..end
1536
1537 l_sales_type_credit := NULL;
1538 OPEN sales_type_credit_csr;
1539 FETCH sales_type_credit_csr INTO l_sales_type_credit;
1540 CLOSE sales_type_credit_csr;
1541
1542 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1543 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_sales_type_credit:'||l_sales_type_credit);
1544 END IF;
1545
1546 -- Bug 6619311
1547 /*
1548 -- get memo line id for tax only invoices
1549 l_memo_line_id := NULL;
1550 OPEN get_memo_line_id_csr;
1551 FETCH get_memo_line_id_csr INTO l_memo_line_id;
1552 CLOSE get_memo_line_id_csr;
1553
1554 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1555 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_memo_line_id:'||l_memo_line_id);
1556 END IF;
1557
1558 if l_memo_line_id is null then
1559 FND_FILE.PUT_LINE(FND_FILE.LOG,
1560 'WARNING: A memo line with name -- Lease Upfront Tax,
1561 must exist to import tax-only invoices.');
1562 end if;
1563 */
1564
1565 -- -----------------------------
1566 -- Start Bulk Fetch Code
1567 -- -----------------------------
1568 OPEN xfer_csr;
1569 LOOP
1570 l_xfer_tbl.delete;
1571 FETCH xfer_csr BULK COLLECT INTO l_xfer_tbl LIMIT L_FETCH_SIZE;
1572
1573 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_xfer_tbl count is: '||l_xfer_tbl.COUNT);
1574 -- --------------------------------------
1575 -- Process bulk-fetched records
1576 -- --------------------------------------
1577 IF l_xfer_tbl.COUNT > 0 THEN
1578
1579 --l_hdr_id := -9999; -- Bug 7234827
1580
1581 l_commit_cnt := 0;
1582
1583 -- ---------------------------------------------------
1584 -- Update rest of the table records with missing data
1585 -- ---------------------------------------------------
1586 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1587 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updating records with missing data');
1588 END IF;
1589
1590 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1591 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Updating records with missing data');
1592 END IF;
1593
1594 hdr_cnt := 0;
1595 FOR K IN l_xfer_tbl.FIRST..l_xfer_tbl.LAST LOOP
1596
1597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1598 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','starting l_xfer_tbl with k:'||k);
1599 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').tai_id:'||l_xfer_tbl(k).tai_id);
1600 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').amount:'||l_xfer_tbl(k).amount);
1601 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').line_description:'||l_xfer_tbl(k).line_description);
1602 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').inventory_item_id:'||l_xfer_tbl(k).inventory_item_id);
1603 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').line_type:'||l_xfer_tbl(k).line_type);
1604 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').quantity:'||l_xfer_tbl(k).quantity);
1605 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').line_number:'||l_xfer_tbl(k).line_number);
1606 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').sty_id:'||l_xfer_tbl(k).sty_id);
1607 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').khr_id:'||l_xfer_tbl(k).khr_id);
1608 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').contract_number:'||l_xfer_tbl(k).contract_number);
1609 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').asset_number:'||l_xfer_tbl(k).asset_number);
1610 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').invoice_format_line_type:'||l_xfer_tbl(k).invoice_format_line_type);
1611 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').stream_type:'||l_xfer_tbl(k).stream_type);
1612 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').currency_code:'||l_xfer_tbl(k).currency_code);
1613 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').currency_conversion_date:'||l_xfer_tbl(k).currency_conversion_date);
1614 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').currency_conversion_rate:'||l_xfer_tbl(k).currency_conversion_rate);
1615 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').currency_conversion_type:'||l_xfer_tbl(k).currency_conversion_type);
1616 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').cust_trx_type_id:'||l_xfer_tbl(k).cust_trx_type_id);
1617 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').customer_address_id:'||l_xfer_tbl(k).customer_address_id);
1618 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').customer_bank_account_id:'||l_xfer_tbl(k).customer_bank_account_id);
1619 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').customer_id:'||l_xfer_tbl(k).customer_id);
1620 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').hdr_description:'||l_xfer_tbl(k).hdr_description);
1621 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').invoice_message:'||l_xfer_tbl(k).invoice_message);
1622 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').org_id:'||l_xfer_tbl(k).org_id);
1623 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').receipt_method_id:'||l_xfer_tbl(k).receipt_method_id);
1624 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').set_of_books_id:'||l_xfer_tbl(k).set_of_books_id);
1625 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').tax_exempt_flag:'||l_xfer_tbl(k).tax_exempt_flag);
1626 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').term_id:'||l_xfer_tbl(k).term_id);
1627 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').trx_date:'||l_xfer_tbl(k).trx_date);
1628 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').trx_number:'||l_xfer_tbl(k).trx_number);
1629 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').consolidated_invoice_number:'||l_xfer_tbl(k).consolidated_invoice_number);
1630 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').invoice_format_type:'||l_xfer_tbl(k).invoice_format_type);
1631 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').invoice_pull_yn:'||l_xfer_tbl(k).invoice_pull_yn);
1632 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').private_label:'||l_xfer_tbl(k).private_label);
1633 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').legal_entity_id:'||l_xfer_tbl(k).legal_entity_id);
1634 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').account_class:'||l_xfer_tbl(k).account_class);
1635 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').dist_amount:'||l_xfer_tbl(k).dist_amount);
1636 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').dist_percent:'||l_xfer_tbl(k).dist_percent);
1637 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').code_combination_id:'||l_xfer_tbl(k).code_combination_id);
1638 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rev_rec_basis:'||l_xfer_tbl(k).rev_rec_basis);
1639 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').cm_acct_rule:'||l_xfer_tbl(k).cm_acct_rule);
1640 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rev_txn_id:'||l_xfer_tbl(k).rev_txn_id);
1641 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').inventory_org_id:'||l_xfer_tbl(k).inventory_org_id);
1642 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').ware_house_id:'||l_xfer_tbl(k).ware_house_id);
1643 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').kle_id:'||l_xfer_tbl(k).kle_id);
1644 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').ship_to:'||l_xfer_tbl(k).ship_to);
1645 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').l_inv_id:'||l_xfer_tbl(k).l_inv_id);
1646 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').uom_code:'||l_xfer_tbl(k).uom_code);
1647 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').txn_id:'||l_xfer_tbl(k).txn_id);
1648 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').okl_source_billing_trx:'||l_xfer_tbl(k).okl_source_billing_trx);
1649 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').investor_agreement_number:'||l_xfer_tbl(k).investor_agreement_number);
1650 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').investor_name:'||l_xfer_tbl(k).investor_name);
1651 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').quote_number:'||l_xfer_tbl(k).quote_number);
1652 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rbk_request_number:'||l_xfer_tbl(k).rbk_request_number);
1653 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rbk_ori_invoice_number:'||l_xfer_tbl(k).rbk_ori_invoice_number);
1654 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rbk_ori_invoice_line_number:'||l_xfer_tbl(k).rbk_ori_invoice_line_number);
1655 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').rbk_adjustment_date:'||l_xfer_tbl(k).rbk_adjustment_date);
1656 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').inf_id:'||l_xfer_tbl(k).inf_id);
1657 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').try_id:'||l_xfer_tbl(k).try_id);
1658 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').try_name:'||l_xfer_tbl(k).try_name);
1659 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').invoice_line_type:'||l_xfer_tbl(k).invoice_line_type);
1660 END IF;
1661
1662
1663 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1664 -- It seems like the folowing is a block of dead code. l_rev_rec_basis initial as null
1665 -- so the "then" will never be happened
1666 -- Populate CM acct rule
1667 -- IF ( l_rev_rec_basis = 'CASH_RECEIPT'
1668 -- cklee : start: For bug 5387704/R12: 5447521
1669 IF ( l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT'
1670 -- cklee : end: For bug 5387704/R12: 5447521
1671 -- AND l_xfer_tbl(k).XLS_AMOUNT < 0 )
1672 AND l_xfer_tbl(k).AMOUNT < 0 )
1673 THEN
1674 l_xfer_tbl(k).cm_acct_rule := 'PRORATE';
1675 END IF;
1676
1677 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1678 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').cm_acct_rule:'||l_xfer_tbl(k).cm_acct_rule);
1679 END IF;
1680
1681 -- To find the top line kle_id
1682 l_kle_id := NULL;
1683 --start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1684 -- 1. Check if it's a top line
1685 OPEN is_top_line(l_xfer_tbl(k).kle_id);
1686 FETCH is_top_line INTO l_is_top_line;
1687 is_top_line_flag := is_top_line%FOUND;
1688 CLOSE is_top_line;
1689
1690 IF is_top_line_flag THEN
1691 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1692 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug',l_xfer_tbl(k).kle_id || ' is top line');
1693 END IF;
1694 ELSE
1695 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1696 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug',l_xfer_tbl(k).kle_id || ' is not a top line');
1697 END IF;
1698 END IF;
1699
1700 -- 2. get top line if needed
1701 IF NOT is_top_line_flag THEN
1702 OPEN get_top_line(l_xfer_tbl(k).kle_id);
1703 FETCH get_top_line INTO l_xfer_tbl(k).kle_id;
1704 CLOSE get_top_line;
1705 END IF;
1706
1707 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1708 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug',l_xfer_tbl(k).kle_id || ' fetched as a top line');
1709 END IF;
1710
1711 -- 3. get top line name (asset number)
1712 OPEN get_top_line_name(l_xfer_tbl(k).kle_id);
1713 FETCH get_top_line_name INTO l_xfer_tbl(k).ASSET_NUMBER;
1714 CLOSE get_top_line_name;
1715
1716 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1717 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug',l_xfer_tbl(k).ASSET_NUMBER || ' fetched as asset number');
1718 END IF;
1719
1720 --end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
1721 l_top_kle_id := l_xfer_tbl(k).kle_id;
1722
1723 -- Populate warehouse_id
1724 IF (NVL(l_use_inv_org, 'N') = 'Y') THEN
1725 --if it is a Remarketing invoice
1726 IF (l_xfer_tbl(k).inventory_org_id IS NOT NULL) THEN
1727 l_xfer_tbl(k).ware_house_id := l_xfer_tbl(k).inventory_org_id;
1728 END IF;
1729 ELSE
1730 l_xfer_tbl(k).ware_house_id := NULL;
1731 END IF;
1732
1733 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1734 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').ware_house_id:'||l_xfer_tbl(k).ware_house_id);
1735 END IF;
1736
1737 l_chr_id := l_xfer_tbl(k).khr_id;
1738 l_kle_id := l_xfer_tbl(k).kle_id;
1739
1740 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1741
1742 -- Bug 4523079; stmathew
1743 l_install_location_id := NULL;
1744 l_location_id := NULL;
1745 -- End Code; Bug 4523079; stmathew
1746
1747 OPEN ship_to_csr(l_kle_id);
1748 FETCH ship_to_csr INTO l_install_location_id, l_location_id;
1749 CLOSE ship_to_csr;
1750
1751 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1752 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_install_location_id:'||l_install_location_id);
1753 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_location_id:'||l_location_id);
1754 END IF;
1755
1756 l_temp_sold_fee := NULL;
1757 OPEN sold_service_fee_csr ( l_kle_id );
1758 FETCH sold_service_fee_csr INTO l_temp_sold_fee;
1759 CLOSE sold_service_fee_csr;
1760
1761 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1762 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_temp_sold_fee:'||l_temp_sold_fee);
1763 END IF;
1764
1765 IF l_temp_sold_fee = '1' THEN
1766 -- Get Inventory_item_id
1767 l_inv_id := NULL;
1768 OPEN get_service_inv_csr ( l_kle_id );
1769 FETCH get_service_inv_csr INTO l_inv_id;
1770 CLOSE get_service_inv_csr;
1771 ELSE
1772 -- Get Inventory_item_id
1773 l_inv_id := NULL;
1774 OPEN get_inv_item_id ( l_kle_id );
1775 FETCH get_inv_item_id INTO l_inv_id;
1776 CLOSE get_inv_item_id;
1777 END IF;
1778
1779 -- -------------------------------------------
1780 -- Store inventory item id for the item on
1781 -- contract line
1782 -- -------------------------------------------
1783 l_xfer_tbl(k).l_inv_id := l_inv_id;
1784
1785 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1786 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_inv_id:'||l_inv_id);
1787 END IF;
1788
1789 -- Get UOM Code
1790 l_uom_code := NULL;
1791 IF l_xfer_tbl(k).INVENTORY_ITEM_ID IS NULL THEN
1792 l_xfer_tbl(k).INVENTORY_ITEM_ID := l_inv_id;
1793 OPEN get_uom_code ( l_inv_id );
1794 FETCH get_uom_code INTO l_uom_code;
1795 CLOSE get_uom_code;
1796 ELSE
1797 OPEN get_uom_code ( l_xfer_tbl(k).INVENTORY_ITEM_ID );
1798 FETCH get_uom_code INTO l_uom_code;
1799 CLOSE get_uom_code;
1800 END IF;
1801
1802 l_xfer_tbl(k).uom_code := l_uom_code;
1803 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1804 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_uom_code:'||l_uom_code);
1805 END IF;
1806 -- Check if Vendor is the same as the customer on the Contract
1807 lx_customer_id := NULL;
1808 get_customer_id( l_xfer_tbl(k).CONTRACT_NUMBER ,lx_customer_id );
1809 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1810 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','lx_customer_id:'||lx_customer_id);
1811 END IF;
1812 -- Bug 4523079; stmathew
1813 l_ship_to := NULL;
1814 -- End code Bug 4523079; stmathew
1815
1816 OPEN Ship_to_csr2( lx_customer_id, l_install_location_id, l_location_id, l_xfer_tbl(k).ORG_ID);
1817 FETCH Ship_to_csr2 INTO l_ship_to;
1818 CLOSE Ship_to_csr2;
1819
1820 IF ( lx_customer_id = l_xfer_tbl(k).CUSTOMER_ID ) THEN
1821 NULL;
1822 ELSE
1823 l_ship_to := NULL;
1824 END IF;
1825
1826 l_xfer_tbl(k).ship_to := l_ship_to;
1827 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1828 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').ship_to:'||l_xfer_tbl(k).ship_to);
1829 END IF;
1830 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1831 -- Bug 7622476
1832 /*
1833 -- get invoice group related data
1834 Get_chr_inv_grp(
1835 p_inf_id => l_xfer_tbl(k).inf_id
1836 ,p_sty_id => l_xfer_tbl(k).sty_id
1837 ,x_group_by_contract_yn => l_group_by_contract_yn
1838 ,x_contract_level_yn => l_contract_level_yn
1839 ,x_group_asset_yn => l_group_asset_yn
1840 ,x_invoice_group => l_invoice_group
1841 );
1842
1843 l_khr_id := l_xfer_tbl(k).KHR_ID;
1844 l_xfer_tbl(k).KHR_ID := NULL;
1845 --l_kle_id := l_xfer_tbl(k).KLE_ID;
1846 --l_xfer_tbl(k).KLE_ID := NULL;
1847 IF (l_group_by_contract_yn = 'Y' OR l_contract_level_yn = 'N') THEN
1848 l_xfer_tbl(k).KHR_ID := l_khr_id;
1849 --IF l_group_by_assets_yn = 'N' THEN
1850 -- l_xfer_tbl(k).KLE_ID := l_kle_id;
1851 --END IF;
1852 END IF; */
1853 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1854
1855 END LOOP; -- Populate missing values
1856
1857 inv_lines_tbl.delete;
1858 inv_dist_tbl.delete;
1859 sales_credits_tbl.delete;
1860 ar_contingency_tbl.delete;
1861 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1862 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','finished deleting pl/sql tables');
1863 END IF;
1864 -- Build ra_interface_lines_rec
1865 -- Build ra_interface_distributions_rec
1866 -- Build ra_interface_salescredits_all
1867 -- Build ar_interface_conts_all
1868
1869 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1870 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Building AR records');
1871 END IF;
1872
1873 n := 1; -- rmunjulu R12 Fixes -- initialize n
1874 FOR K IN l_xfer_tbl.FIRST..l_xfer_tbl.LAST LOOP
1875 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1876 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','starting l_xfer_tbl loop with k:'||k);
1877 END IF;
1878 -- ----------------------------------
1879 -- Update prev header status
1880 -- Except for first record
1881 -- ----------------------------------
1882 IF l_hdr_id <> -9999 THEN
1883 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1884 -- UPDATE OKL_EXT_SELL_INVS_B
1885 UPDATE okl_trx_ar_invoices_b
1886 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1887 SET trx_status_code = 'PROCESSED',
1888 last_update_date = sysdate,
1889 last_updated_by = lx_last_updated_by,
1890 last_update_login = lx_last_update_login,
1891 request_id = lx_request_id,
1892 program_update_date = sysdate,
1893 program_application_id = lx_program_application_id,
1894 program_id = lx_program_id
1895 WHERE ID = l_hdr_id;
1896 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1897 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updating header record with id: '||l_hdr_id);
1898 END IF;
1899 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1900 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Updating header record with id: '||l_hdr_id);
1901 END IF;
1902 END IF;
1903
1904 -- --------------------------
1905 -- Increment Commit counter
1906 -- --------------------------
1907
1908 l_commit_cnt := l_commit_cnt + 1;
1909
1910 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1911 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1912 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updating header record with id: '||l_xfer_tbl(k).xsi_id );
1913 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updating header record with id: '||l_xfer_tbl(k).tai_id );
1914 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1915 END IF;
1916 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1917 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Updating header record with id: '||l_xfer_tbl(k).tai_id);
1918 END IF;
1919 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1920 -- IF l_hdr_id <> l_xfer_tbl(k).xsi_id THEN
1921 --commented out for R12 IF l_hdr_id <> l_xfer_tbl(k).tai_id THEN
1922 -- hdr_cnt := hdr_cnt + 1;
1923 hdr_cnt := k;
1924 --end: | 15-FEB-07 cklee R12 Billing enhancement project
1925
1926 --vthiruva - Bug 4222231..start
1927 --for each new contract fetch the sales rep is and number and if there is no
1928 --sales rep attached to the contract, default it to 'No Sales Credit'(-3)
1929 -- Bug 7622476
1930 -- get invoice group related data
1931 Get_chr_inv_grp(
1932 p_inf_id => l_xfer_tbl(k).inf_id
1933 ,p_sty_id => l_xfer_tbl(k).sty_id
1934 ,x_group_by_contract_yn => l_group_by_contract_yn
1935 ,x_contract_level_yn => l_contract_level_yn
1936 ,x_group_asset_yn => l_group_asset_yn
1937 ,x_invoice_group => l_invoice_group
1938 );
1939
1940 l_khr_id := l_xfer_tbl(k).KHR_ID;
1941 l_xfer_tbl(k).KHR_ID := NULL;
1942 --l_kle_id := l_xfer_tbl(k).KLE_ID;
1943 --l_xfer_tbl(k).KLE_ID := NULL;
1944 IF (l_group_by_contract_yn = 'Y' OR l_contract_level_yn = 'N') THEN
1945 l_xfer_tbl(k).KHR_ID := l_khr_id;
1946 --IF l_group_by_assets_yn = 'N' THEN
1947 -- l_xfer_tbl(k).KLE_ID := l_kle_id;
1948 --END IF;
1949 END IF;
1950
1951 IF (l_prev_contract_num IS NULL OR
1952 l_prev_contract_num <> l_xfer_tbl(k).CONTRACT_NUMBER) THEN
1953
1954 OPEN get_sales_rep(l_xfer_tbl(k).CONTRACT_NUMBER);
1955 FETCH get_sales_rep INTO l_sales_person;
1956 IF get_sales_rep%NOTFOUND THEN
1957 l_salesrep_id := -3;
1958 l_salesrep_number := -3;
1959 ELSE
1960 OPEN sales_rep_csr(l_sales_person);
1961 FETCH sales_rep_csr INTO l_salesrep_id, l_salesrep_number;
1962 CLOSE sales_rep_csr;
1963 END IF;
1964 CLOSE get_sales_rep;
1965 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1966 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_salesrep_id:'||l_salesrep_id);
1967 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_salesrep_number:'||l_salesrep_number);
1968 END IF;
1969 l_prev_contract_num := l_xfer_tbl(k).CONTRACT_NUMBER;
1970
1971 END IF;
1972 --vthiruva - Bug 4222231..end
1973
1974 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1975 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Preparing Ra_interface_lines_all record.');
1976 END IF;
1977
1978 -- -------------------------------------
1979 -- Build Invoice Lines Table
1980 -- -------------------------------------
1981 inv_lines_tbl(hdr_cnt).WAREHOUSE_ID := l_xfer_tbl(k).ware_house_id;
1982
1983 IF (l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT') THEN
1984 --Added if clause by bkatraga for bug 5616268
1985 --Accounting_rule_id will not be populated in case of on-account credit memo
1986 IF ((l_xfer_tbl(k).AMOUNT >= 0) OR (l_xfer_tbl(k).rev_txn_id IS NOT NULL)) THEN
1987 inv_lines_tbl(hdr_cnt).ACCOUNTING_RULE_ID := 1;
1988 END IF;
1989 --end bkatraga
1990 ELSE
1991 inv_lines_tbl(hdr_cnt).ACCOUNTING_RULE_ID := NULL;
1992 END IF;
1993
1994 inv_lines_tbl(hdr_cnt).ACCOUNTING_RULE_DURATION := NULL;
1995 inv_lines_tbl(hdr_cnt).AGREEMENT_ID := NUll;
1996 --start: | 15-FEB-07 cklee R12 Billing enhancement project
1997 -- inv_lines_tbl(hdr_cnt).AMOUNT := l_xfer_tbl(k).XLS_AMOUNT;
1998 inv_lines_tbl(hdr_cnt).AMOUNT := l_xfer_tbl(k).AMOUNT;
1999 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2000
2001 -- tax-only invoice
2002 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2003 -- if l_xfer_tbl(k).XLS_AMOUNT = 0 then
2004 -- Bug 6619311
2005 /*
2006 if l_xfer_tbl(k).AMOUNT = 0 then
2007 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2008 inv_lines_tbl(hdr_cnt).memo_line_id := l_memo_line_id;
2009 end if;
2010 */
2011
2012 inv_lines_tbl(hdr_cnt).BATCH_SOURCE_NAME := 'OKL_CONTRACTS';
2013 inv_lines_tbl(hdr_cnt).COMMENTS := l_xfer_tbl(k).INVOICE_MESSAGE;
2014 inv_lines_tbl(hdr_cnt).CONVERSION_DATE := l_xfer_tbl(k).currency_conversion_date;
2015 inv_lines_tbl(hdr_cnt).CONVERSION_RATE := l_xfer_tbl(k).currency_conversion_rate;
2016 inv_lines_tbl(hdr_cnt).CONVERSION_TYPE := l_xfer_tbl(k).currency_conversion_type;
2017 inv_lines_tbl(hdr_cnt).CREATED_BY := G_user_id;
2018 inv_lines_tbl(hdr_cnt).CREATION_DATE := sysdate;
2019 inv_lines_tbl(hdr_cnt).CREDIT_METHOD_FOR_ACCT_RULE := l_xfer_tbl(k).cm_acct_rule;
2020 inv_lines_tbl(hdr_cnt).CREDIT_METHOD_FOR_INSTALLMENTS := NULL;
2021 inv_lines_tbl(hdr_cnt).CURRENCY_CODE := l_xfer_tbl(k).CURRENCY_CODE;
2022 inv_lines_tbl(hdr_cnt).CUST_TRX_TYPE_ID := l_xfer_tbl(k).CUST_TRX_TYPE_ID;
2023 /*inv_lines_tbl(hdr_cnt).DESCRIPTION := NVL (NVL (l_xfer_tbl(k).LINE_DESCRIPTION,
2024 l_xfer_tbl(k).HDR_DESCRIPTION), 'OKL Billing');*/
2025 -- Bug 7045347
2026 inv_lines_tbl(hdr_cnt).DESCRIPTION := NVL(l_xfer_tbl(k).INVOICE_LINE_TYPE, SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH));
2027 inv_lines_tbl(hdr_cnt).LAST_UPDATED_BY := G_user_id;
2028 inv_lines_tbl(hdr_cnt).LAST_UPDATE_DATE := sysdate;
2029 inv_lines_tbl(hdr_cnt).LINE_TYPE := l_xfer_tbl(k).LINE_TYPE;
2030
2031 inv_lines_tbl(hdr_cnt).TRX_NUMBER := l_xfer_tbl(k).TRX_NUMBER;
2032 inv_lines_tbl(hdr_cnt).TRX_DATE := l_xfer_tbl(k).TRX_DATE;
2033 inv_lines_tbl(hdr_cnt).GL_DATE := l_xfer_tbl(k).TRX_DATE;
2034 inv_lines_tbl(hdr_cnt).PRINTING_OPTION := NULL;
2035
2036 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2037 inv_lines_tbl(hdr_cnt).CONS_BILLING_NUMBER := NULL;
2038 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'INVESTOR_STAKE' THEN
2039 -- gkhuntet 24-JUL-2007 added for Investor Invoices Start.
2040 inv_lines_tbl(hdr_cnt).BATCH_SOURCE_NAME := 'OKL_INVESTOR';
2041 -- gkhuntet 24-JUL-2007 added for Investor Invoices End.
2042 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE1 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Agreement_Number),1,G_AR_DATA_LENGTH);
2043 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE2 := NULL;
2044 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE3 := NULL;
2045 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Name),1,G_AR_DATA_LENGTH);
2046 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE5 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2047 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE6 := NULL;
2048 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE7 := NULL;
2049 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE8 := NULL;
2050 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE9 := NULL;
2051 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2052 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE11 := NULL;
2053 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE12 := NULL;
2054 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE13 := NULL;
2055 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2056 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE15 := NULL;
2057 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_CONTEXT := 'OKL_INVESTOR';
2058
2059 ELSE
2060 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE1 := NULL;
2061 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE2 := SUBSTR(TRIM(l_invoice_group),1,G_AR_DATA_LENGTH);
2062 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE3 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_PULL_YN),1,G_AR_DATA_LENGTH);
2063 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).PRIVATE_LABEL),1,G_AR_DATA_LENGTH);
2064 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE5 := NULL;
2065 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE6 := SUBSTR(TRIM(l_xfer_tbl(k).CONTRACT_NUMBER),1,G_AR_DATA_LENGTH);
2066 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE7 := SUBSTR(TRIM(l_xfer_tbl(k).ASSET_NUMBER),1,G_AR_DATA_LENGTH);
2067 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE8 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_LINE_TYPE),1,G_AR_DATA_LENGTH);
2068 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE9 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2069 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2070 -- if the source of the billing trx is termination quote, the OKL billing trx number is Quite_number
2071 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'TERMINATION_QUOTE' THEN
2072 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE11 := SUBSTR(TRIM(l_xfer_tbl(k).Quote_number),1,G_AR_DATA_LENGTH);
2073 END IF;
2074 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE12 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).KHR_ID)),1,G_AR_DATA_LENGTH);
2075 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE13 := SUBSTR(TRIM(l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX),1,G_AR_DATA_LENGTH);
2076 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2077 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_ATTRIBUTE15 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_TYPE),1,G_AR_DATA_LENGTH);
2078 inv_lines_tbl(hdr_cnt).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2079 END IF;
2080 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2081
2082 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2083 -- start: cklee 3/22/2007
2084 -- Credit memo:
2085 -- If It's a credit memo invoice, we need to assign the invoice reference
2086 -- to interfcae table for the following:
2087 -- 1. If the invoice reference is a legacy invoice (up to OKL.H), assign FK to
2088 -- REFERENCE_LINExxx
2089 -- 2. If the invoice reference is NOT a legacy invoice (R12 going forward), get the AR
2090 -- invoice line and then assign to REFERENCE_LINE_ID.
2091 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2092 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').accounting_rule_id:'||inv_lines_tbl(hdr_cnt).accounting_rule_id);
2093 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').amount:'||inv_lines_tbl(hdr_cnt).amount);
2094 -- Bug 6619311
2095 /*
2096 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').memo_line_id:'||inv_lines_tbl(hdr_cnt).memo_line_id);
2097 */
2098 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').batch_source_name:'||inv_lines_tbl(hdr_cnt).batch_source_name);
2099 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').comments:'||inv_lines_tbl(hdr_cnt).comments);
2100 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').conversion_date:'||inv_lines_tbl(hdr_cnt).conversion_date);
2101 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').conversion_rate:'||inv_lines_tbl(hdr_cnt).conversion_rate);
2102 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').conversion_type:'||inv_lines_tbl(hdr_cnt).conversion_type);
2103 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').created_by:'||inv_lines_tbl(hdr_cnt).created_by);
2104 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').creation_date:'||inv_lines_tbl(hdr_cnt).creation_date);
2105 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').credit_method_for_acct_rule:'||inv_lines_tbl(hdr_cnt).credit_method_for_acct_rule);
2106 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').credit_method_for_installments:'|| inv_lines_tbl(hdr_cnt).credit_method_for_installments);
2107 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').currency_code:'||inv_lines_tbl(hdr_cnt).currency_code);
2108 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').cust_trx_type_id:'||inv_lines_tbl(hdr_cnt).cust_trx_type_id);
2109 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').description:'||inv_lines_tbl(hdr_cnt).description);
2110 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').last_updated_by:'||inv_lines_tbl(hdr_cnt).last_updated_by);
2111 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').last_update_date:'||inv_lines_tbl(hdr_cnt).last_update_date);
2112 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').line_type:'||inv_lines_tbl(hdr_cnt).line_type);
2113 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').trx_number:'||inv_lines_tbl(hdr_cnt).trx_number);
2114 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').trx_date:'||inv_lines_tbl(hdr_cnt).trx_date);
2115 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').gl_date:'||inv_lines_tbl(hdr_cnt).gl_date);
2116 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').printing_option:'||inv_lines_tbl(hdr_cnt).printing_option);
2117 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').batch_source_name:'||inv_lines_tbl(hdr_cnt).batch_source_name);
2118 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute1:'||inv_lines_tbl(hdr_cnt).interface_line_attribute1);
2119 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute2:'||inv_lines_tbl(hdr_cnt).interface_line_attribute2);
2120 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute3:'||inv_lines_tbl(hdr_cnt).interface_line_attribute3);
2121 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute4:'||inv_lines_tbl(hdr_cnt).interface_line_attribute4);
2122 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute5:'||inv_lines_tbl(hdr_cnt).interface_line_attribute5);
2123 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute6:'||inv_lines_tbl(hdr_cnt).interface_line_attribute6);
2124 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute7:'||inv_lines_tbl(hdr_cnt).interface_line_attribute7);
2125 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute8:'||inv_lines_tbl(hdr_cnt).interface_line_attribute8);
2126 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute9:'||inv_lines_tbl(hdr_cnt).interface_line_attribute9);
2127 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute10:'||inv_lines_tbl(hdr_cnt).interface_line_attribute10);
2128 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute11:'||inv_lines_tbl(hdr_cnt).interface_line_attribute11);
2129 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute12:'||inv_lines_tbl(hdr_cnt).interface_line_attribute12);
2130 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute13:'||inv_lines_tbl(hdr_cnt).interface_line_attribute13);
2131 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute14:'||inv_lines_tbl(hdr_cnt).interface_line_attribute14);
2132 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_attribute15:'||inv_lines_tbl(hdr_cnt).interface_line_attribute15);
2133 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').interface_line_context:'||inv_lines_tbl(hdr_cnt).interface_line_context);
2134
2135 END IF;
2136
2137
2138 IF (l_xfer_tbl(k).rev_txn_id IS NOT NULL) THEN
2139
2140
2141 open is_legacy_invoice(l_xfer_tbl(k).rev_txn_id);
2142 fetch is_legacy_invoice into l_lsm_id;
2143 close is_legacy_invoice;
2144
2145
2146 -- it's a legacy invoice for which the credit memo is applied
2147 IF l_lsm_id is not null THEN
2148
2149
2150 inv_lines_tbl(hdr_cnt).REFERENCE_LINE_CONTEXT := 'OKL_CONTRACTS';
2151 inv_lines_tbl(hdr_cnt).REFERENCE_LINE_ATTRIBUTE14
2152 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).rev_txn_id)),1,G_AR_DATA_LENGTH);
2153
2154 ELSE
2155
2156
2157 OPEN get_invoice_line_id(l_xfer_tbl(k).rev_txn_id);
2158 fetch get_invoice_line_id into l_customer_trx_line_id;
2159 close get_invoice_line_id;
2160
2161
2162 inv_lines_tbl(hdr_cnt).REFERENCE_LINE_ID := TO_CHAR(l_customer_trx_line_id);
2163
2164
2165 END IF;
2166 END IF;
2167 -- end: cklee 3/22/2007
2168
2169 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2170 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_lsm_id:'||l_lsm_id);
2171 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').reference_line_context:'||inv_lines_tbl(hdr_cnt).reference_line_context);
2172 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').reference_line_id:'||inv_lines_tbl(hdr_cnt).reference_line_id);
2173 END IF;
2174
2175
2176 --akrangan added ebtax billing impacts coding start
2177 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'TAX_ONLY_INVOICE_TAX' THEN
2178 OPEN tax_sources_csr(p_tai_id => l_xfer_tbl(k).tai_id,
2179 p_tld_id =>l_xfer_tbl(k).txn_id);
2180 FETCH tax_sources_csr INTO tax_sources_rec;
2181 CLOSE tax_sources_csr;
2182 --populating the invoice lines interface tbl with tax sources rec
2183 inv_lines_tbl(hdr_cnt).source_trx_id := tax_sources_rec.trx_id;
2184 inv_lines_tbl(hdr_cnt).source_trx_line_id := tax_sources_rec.trx_line_id;
2185 inv_lines_tbl(hdr_cnt).source_trx_line_type := tax_sources_rec.trx_level_type;
2186 inv_lines_tbl(hdr_cnt).source_application_id := tax_sources_rec.application_id;
2187 inv_lines_tbl(hdr_cnt).source_event_class_code := tax_sources_rec.event_class_code;
2188 inv_lines_tbl(hdr_cnt).source_entity_code := tax_sources_rec.entity_code;
2189 -- Bug 6619311
2190 inv_lines_tbl(hdr_cnt).SOURCE_TRX_DETAIL_TAX_LINE_ID := tax_sources_rec.tax_line_id;
2191 inv_lines_tbl(hdr_cnt).TAXED_UPSTREAM_FLAG := 'Y';
2192 inv_lines_tbl(hdr_cnt).TAXABLE_FLAG := 'N';
2193 OPEN zx_lines_csr(tax_sources_rec.tax_line_id);
2194 FETCH zx_lines_csr into zx_lines_rec;
2195 CLOSE zx_lines_csr;
2196 inv_lines_tbl(hdr_cnt).HISTORICAL_FLAG := zx_lines_rec.HISTORICAL_FLAG;
2197 inv_lines_tbl(hdr_cnt).TAX_REGIME_CODE := zx_lines_rec.TAX_REGIME_CODE;
2198 inv_lines_tbl(hdr_cnt).TAX := zx_lines_rec.TAX;
2199 inv_lines_tbl(hdr_cnt).TAX_STATUS_CODE := zx_lines_rec.TAX_STATUS_CODE;
2200 inv_lines_tbl(hdr_cnt).TAX_RATE_CODE := zx_lines_rec.TAX_RATE_CODE;
2201 inv_lines_tbl(hdr_cnt).TAX_JURISDICTION_CODE := zx_lines_rec.TAX_JURISDICTION_CODE;
2202 inv_lines_tbl(hdr_cnt).TAXABLE_AMOUNT := zx_lines_rec.TAXABLE_AMT;
2203 inv_lines_tbl(hdr_cnt).LEGAL_ENTITY_ID := zx_lines_rec.LEGAL_ENTITY_ID;
2204 /*inv_lines_tbl(hdr_cnt).TRX_BUSINESS_CATEGORY := NULL;
2205 inv_lines_tbl(hdr_cnt).TAX_CODE := NULL;
2206 inv_lines_tbl(hdr_cnt).PRODUCT_CATEGORY := NULL;
2207 inv_lines_tbl(hdr_cnt).PRODUCT_TYPE := NULL;
2208 inv_lines_tbl(hdr_cnt).LINE_INTENDED_USE := NULL;
2209 inv_lines_tbl(hdr_cnt).USER_DEFINED_FISC_CLASS := NULL;
2210 inv_lines_tbl(hdr_cnt).ASSESSABLE_VALUE := NULL;
2211 --inv_lines_tbl(hdr_cnt).DEFAULT_TAXATION_COUNTRY := NULL;*/
2212 inv_lines_tbl(hdr_cnt).DEFAULT_TAXATION_COUNTRY := x_tax_det_rec.X_DEFAULT_TAXATION_COUNTRY;
2213
2214 END IF;
2215 --akrangan added ebtax billing impacts coding end
2216 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2217 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_trx_id:'||inv_lines_tbl(hdr_cnt).source_trx_id);
2218 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_trx_line_id:'||inv_lines_tbl(hdr_cnt).source_trx_line_id);
2219 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_trx_line_type:'||inv_lines_tbl(hdr_cnt).source_trx_line_type);
2220 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_application_id:'||inv_lines_tbl(hdr_cnt).source_application_id);
2221 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_event_class_code:'||inv_lines_tbl(hdr_cnt).source_event_class_code);
2222 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').source_entity_code:'||inv_lines_tbl(hdr_cnt).source_entity_code);
2223 END IF;
2224 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2225
2226 IF l_xfer_tbl(k).INVENTORY_ITEM_ID IS NULL
2227 AND l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX <> 'TAX_ONLY_INVOICE_TAX' THEN
2228 inv_lines_tbl(hdr_cnt).INVENTORY_ITEM_ID := l_xfer_tbl(k).l_inv_id;
2229 ELSIF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX <> 'TAX_ONLY_INVOICE_TAX' THEN
2230 inv_lines_tbl(hdr_cnt).INVENTORY_ITEM_ID := l_xfer_tbl(k).INVENTORY_ITEM_ID;
2231 END IF;
2232
2233 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2234 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').inventory_item_id:'||inv_lines_tbl(hdr_cnt).inventory_item_id);
2235 END IF;
2236
2237 IF (l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT') THEN
2238 --Added if clause by bkatraga for bug 5616268
2239 --Accounting_rule_id will not be populated in case of on-account credit memo
2240 IF ((l_xfer_tbl(k).AMOUNT >= 0) OR (l_xfer_tbl(k).rev_txn_id IS NOT NULL)) THEN
2241 inv_lines_tbl(hdr_cnt).INVOICING_RULE_ID := -2;
2242 END IF;
2243 --end bkatraga
2244 ELSE
2245 inv_lines_tbl(hdr_cnt).INVOICING_RULE_ID := NULL;
2246 END IF;
2247
2248 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2249 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').invoicing_rule_id:'||inv_lines_tbl(hdr_cnt).invoicing_rule_id);
2250 END IF;
2251
2252 IF (l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT') THEN
2253 inv_lines_tbl(hdr_cnt).OVERRIDE_AUTO_ACCOUNTING_FLAG := 'Y';
2254 ELSE
2255 inv_lines_tbl(hdr_cnt).OVERRIDE_AUTO_ACCOUNTING_FLAG := NULL;
2256 END IF;
2257 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2258 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').override_auto_accounting_flag:'||inv_lines_tbl(hdr_cnt).override_auto_accounting_flag);
2259 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').RECEIPT_METHOD_ID:'||l_xfer_tbl(k).RECEIPT_METHOD_ID);
2260 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl('||k||').KLE_ID:'||l_xfer_tbl(k).KLE_ID);
2261 END IF;
2262
2263 --start: | 28-Mar-07 cklee R12 Billing enhancement project
2264 -- get bill-to information from contract line if any
2265 get_cust_config_from_line(
2266 p_kle_id => l_xfer_tbl(k).KLE_ID
2267 ,p_customer_address_id => l_xfer_tbl(k).CUSTOMER_ADDRESS_ID
2268 ,p_customer_bank_account_id => l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID
2269 ,p_receipt_method_id => l_xfer_tbl(k).RECEIPT_METHOD_ID
2270 ,x_customer_address_id => l_xfer_tbl(k).CUSTOMER_ADDRESS_ID
2271 ,x_customer_bank_account_id => l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID
2272 ,x_receipt_method_id => l_xfer_tbl(k).RECEIPT_METHOD_ID
2273 -- BANK-ACCOUNT-UPTAKE-START
2274 ,x_creation_method_code => l_creation_method_code
2275 ,x_bank_line_id1 => l_bank_line_id1
2276 -- BANK-ACCOUNT-UPTAKE-END
2277 );
2278 --end: | 28-Mar-07 cklee R12 Billing enhancement project
2279
2280 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_BILL_CUSTOMER_ID := l_xfer_tbl(k).CUSTOMER_ID;
2281 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_BILL_ADDRESS_ID := l_xfer_tbl(k).CUSTOMER_ADDRESS_ID;
2282 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_SHIP_CUSTOMER_ID := l_xfer_tbl(k).CUSTOMER_ID;
2283
2284 IF l_xfer_tbl(k).ship_to IS NOT NULL THEN
2285 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_SHIP_ADDRESS_ID := l_xfer_tbl(k).ship_to;
2286 ELSE
2287 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_SHIP_ADDRESS_ID := l_xfer_tbl(k).CUSTOMER_ADDRESS_ID;
2288 END IF;
2289
2290 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2291 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').orig_system_bill_customer_id:'||inv_lines_tbl(hdr_cnt).orig_system_bill_customer_id);
2292 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').orig_system_bill_address_id:'||inv_lines_tbl(hdr_cnt).orig_system_bill_address_id);
2293 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').orig_system_ship_customer_id:'||inv_lines_tbl(hdr_cnt).orig_system_ship_customer_id);
2294 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').orig_system_ship_address_id:'||inv_lines_tbl(hdr_cnt).orig_system_ship_address_id);
2295 END IF;
2296
2297 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_BILL_CONTACT_ID := NULL;
2298 inv_lines_tbl(hdr_cnt).ORIG_SYSTEM_SOLD_CUSTOMER_ID := NULL;
2299 --vthiruva - Bug 4222231..start..removed hardcoding of sales rep
2300 inv_lines_tbl(hdr_cnt).PRIMARY_SALESREP_NUMBER := l_salesrep_number;
2301 inv_lines_tbl(hdr_cnt).PRIMARY_SALESREP_ID := l_salesrep_id;
2302 --vthiruva - Bug 4222231..end
2303 inv_lines_tbl(hdr_cnt).PURCHASE_ORDER := NULL;
2304 inv_lines_tbl(hdr_cnt).PURCHASE_ORDER_REVISION := NULL;
2305 inv_lines_tbl(hdr_cnt).PURCHASE_ORDER_DATE := NULL;
2306 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2307 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').primary_salesrep_number:'||inv_lines_tbl(hdr_cnt).primary_salesrep_number);
2308 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').primary_salesrep_id:'||inv_lines_tbl(hdr_cnt).primary_salesrep_id);
2309 END IF;
2310
2311 -- BANK-ACCOUNT-UPTAKE-START
2312 IF (l_creation_method_code = 'AUTOMATIC') THEN
2313 -- call procedure get_auto_bank_dtls to derive banking details for the
2314 -- automatic receipt creation method
2315 -- gboomina Bug 6832065 - Start
2316 -- get khr_id from contract number. khr_id is not populated in l_xfer_tbl for all records.
2317 -- some of l_xfer_tbl records can have khr_id as NULL. so getting khr_id using l_xfer_tbl(k).contract_number.
2318 if (l_xfer_tbl(k).khr_id is null) then
2319 open get_khr_id_csr(l_xfer_tbl(k).contract_number);
2320 fetch get_khr_id_csr into l_khr_id;
2321 close get_khr_id_csr;
2322 else
2323 l_khr_id := l_xfer_tbl(k).khr_id;
2324 end if;
2325
2326 get_auto_bank_dtls(p_api_version => p_api_version,
2327 p_init_msg_list => p_init_msg_list,
2328 p_khr_id => l_khr_id, -- gboomina for bug 7513216
2329 p_customer_address_id => l_xfer_tbl(k).CUSTOMER_ADDRESS_ID,
2330 p_bank_id => l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID,
2331 -- gboomina added p_trx_date for bug 7513216
2332 p_trx_date => l_xfer_tbl(k).trx_date,
2333 x_payment_trxn_extension_id => inv_lines_tbl(hdr_cnt).payment_trxn_extension_id,
2334 x_customer_bank_account_id => inv_lines_tbl(hdr_cnt).customer_bank_account_id,
2335 x_return_status => x_return_status,
2336 x_msg_count => x_msg_count,
2337 x_msg_data => x_msg_data
2338 );
2339 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2341 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2342 RAISE FND_API.G_EXC_ERROR;
2343 END IF;
2344
2345 -- For Automatic receipt method, bank account id should be NULL as the column is obsoleted
2346 l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID := NULL;
2347 -- gboomina Bug 6832065 - End
2348 END IF;
2349
2350 --start: | 23-Mar-07 cklee R12 Billing enhancement project
2351 -- Null out receive mathod and bank account for Sales Order and Termination Quote,
2352 -- These values will be taken from the AR setup for the customer.
2353 -- 08-feb-2008 ansethur added payment trx extension as well to get nulled out
2354 nullout_rec_method(
2355 p_contract_id => l_xfer_tbl(k).khr_id
2356 ,p_Quote_number => l_xfer_tbl(k).Quote_number
2357 ,p_sty_id => l_xfer_tbl(k).sty_id
2358 ,p_customer_bank_account_id => l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID
2359 ,p_receipt_method_id => l_xfer_tbl(k).RECEIPT_METHOD_ID -- irm_id
2360 ,p_payment_trxn_extension_id => inv_lines_tbl(hdr_cnt).PAYMENT_TRXN_EXTENSION_ID
2361 ,x_customer_bank_account_id => inv_lines_tbl(hdr_cnt).CUSTOMER_BANK_ACCOUNT_ID
2362 ,x_receipt_method_id => inv_lines_tbl(hdr_cnt).RECEIPT_METHOD_ID
2363 ,x_payment_trxn_extension_id => inv_lines_tbl(hdr_cnt).PAYMENT_TRXN_EXTENSION_ID
2364 );
2365
2366 -- inv_lines_tbl(hdr_cnt).CUSTOMER_BANK_ACCOUNT_ID := l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID;
2367 -- inv_lines_tbl(hdr_cnt).RECEIPT_METHOD_ID := l_xfer_tbl(k).RECEIPT_METHOD_ID;
2368 --end: | 23-Mar-07 cklee R12 Billing enhancement project
2369 inv_lines_tbl(hdr_cnt).RECEIPT_METHOD_NAME := NULL;
2370
2371 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2372 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_creation_method_code:'||l_creation_method_code);
2373 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl(k).CUSTOMER_ADDRESS_ID:'||l_xfer_tbl(k).CUSTOMER_ADDRESS_ID);
2374 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID'||l_xfer_tbl(k).CUSTOMER_BANK_ACCOUNT_ID);
2375 END IF;
2376
2377
2378
2379 -- tax-only invoice
2380 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2381 -- if l_xfer_tbl(k).XLS_AMOUNT = 0 then
2382 if l_xfer_tbl(k).AMOUNT = 0 then
2383 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2384 inv_lines_tbl(hdr_cnt).QUANTITY := 0;
2385 else
2386 inv_lines_tbl(hdr_cnt).QUANTITY := l_xfer_tbl(k).QUANTITY;
2387 end if;
2388
2389
2390 inv_lines_tbl(hdr_cnt).QUANTITY_ORDERED := NULL;
2391 inv_lines_tbl(hdr_cnt).REASON_CODE := NULL;
2392 inv_lines_tbl(hdr_cnt).REASON_CODE_MEANING := NULL;
2393 -- inv_lines_tbl(hdr_cnt).REFERENCE_LINE_ID := NULL;
2394 inv_lines_tbl(hdr_cnt).RULE_START_DATE := NULL;
2395 inv_lines_tbl(hdr_cnt).SALES_ORDER := NULL;
2396 inv_lines_tbl(hdr_cnt).SALES_ORDER_LINE := NULL;
2397 inv_lines_tbl(hdr_cnt).SALES_ORDER_DATE := NULL;
2398 inv_lines_tbl(hdr_cnt).SALES_ORDER_SOURCE := NULL;
2399 inv_lines_tbl(hdr_cnt).SET_OF_BOOKS_ID := l_xfer_tbl(k).SET_OF_BOOKS_ID;
2400
2401 IF l_xfer_tbl(k).TAX_EXEMPT_FLAG = 'S' THEN
2402 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_FLAG := 'S';
2403 ELSIF l_xfer_tbl(k).TAX_EXEMPT_FLAG = 'E' THEN
2404 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_FLAG := 'E';
2405 ELSIF l_xfer_tbl(k).TAX_EXEMPT_FLAG = 'R' THEN
2406 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_FLAG := 'R';
2407 ELSE
2408 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_FLAG := 'S';
2409 END IF;
2410
2411 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_NUMBER := NULL;
2412
2413 IF l_xfer_tbl(k).TAX_EXEMPT_FLAG = 'E' THEN
2414 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_REASON_CODE := 'MANUFACTURER';
2415 ELSE
2416 inv_lines_tbl(hdr_cnt).TAX_EXEMPT_REASON_CODE := NULL;
2417 END IF;
2418
2419 inv_lines_tbl(hdr_cnt).TERM_ID := l_xfer_tbl(k).TERM_ID;
2420 inv_lines_tbl(hdr_cnt).UNIT_SELLING_PRICE := NULL;
2421 inv_lines_tbl(hdr_cnt).UNIT_STANDARD_PRICE := NULL;
2422 inv_lines_tbl(hdr_cnt).UOM_CODE := l_xfer_tbl(k).uom_code;
2423 inv_lines_tbl(hdr_cnt).ORG_ID := l_xfer_tbl(k).ORG_ID;
2424 inv_lines_tbl(hdr_cnt).LEGAL_ENTITY_ID := l_xfer_tbl(k).LEGAL_ENTITY_ID; -- for LE Uptake project 08-11-2006
2425
2426
2427 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2428 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Preparing Ra_interface_lines_all record.');
2429 END IF;
2430 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2431 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').legal_entity_id:' || inv_lines_tbl(hdr_cnt).legal_entity_id);
2432 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').org_id:' || inv_lines_tbl(hdr_cnt).org_id);
2433 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').quantity:' || inv_lines_tbl(hdr_cnt).quantity);
2434 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').quantity_ordered:' || inv_lines_tbl(hdr_cnt).quantity_ordered);
2435 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').reason_code:' || inv_lines_tbl(hdr_cnt).reason_code);
2436 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').reason_code_meaning:' || inv_lines_tbl(hdr_cnt).reason_code_meaning);
2437 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').receipt_method_name:' || inv_lines_tbl(hdr_cnt).receipt_method_name);
2438 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').rule_start_date:' || inv_lines_tbl(hdr_cnt).rule_start_date);
2439 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').sales_order:' || inv_lines_tbl(hdr_cnt).sales_order);
2440 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').sales_order_date:' || inv_lines_tbl(hdr_cnt).sales_order_date);
2441 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').sales_order_line:' || inv_lines_tbl(hdr_cnt).sales_order_line);
2442 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').sales_order_source:' || inv_lines_tbl(hdr_cnt).sales_order_source);
2443 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').set_of_books_id:' || inv_lines_tbl(hdr_cnt).set_of_books_id);
2444 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').tax_exempt_flag:' || inv_lines_tbl(hdr_cnt).tax_exempt_flag);
2445 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').tax_exempt_number:' || inv_lines_tbl(hdr_cnt).tax_exempt_number);
2446 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').tax_exempt_reason_code:' || inv_lines_tbl(hdr_cnt).tax_exempt_reason_code);
2447 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').term_id:' || inv_lines_tbl(hdr_cnt).term_id);
2448 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').unit_selling_price:' || inv_lines_tbl(hdr_cnt).unit_selling_price);
2449 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').unit_standard_price:' || inv_lines_tbl(hdr_cnt).unit_standard_price);
2450 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').uom_code:' || inv_lines_tbl(hdr_cnt).uom_code);
2451 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Done Preparing Ra_interface_lines_all record.');
2452 END IF;
2453 -- Start Sales Tax Code
2454
2455 -- -------------------------------------
2456 -- Insert Tax record
2457 -- -------------------------------------
2458
2459 -- Insert tax record for ivoices and on-account credit memos
2460 IF NVL(l_xfer_tbl(k).TAX_EXEMPT_FLAG, 'S') <> 'E' THEN
2461
2462 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2463 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Preparing Ra_interface_lines_all tax record.');
2464 END IF;
2465 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2466 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Preparing Ra_interface_lines_all tax record.');
2467 END IF;
2468 --start| 02-MAR-07 cklee R12 Billing enhancement project |
2469 -- ebtax integration
2470 /*
2471 open c_try_name(l_xfer_tbl(k).try_id);
2472 fetch c_try_name into l_try_name;
2473 close c_try_name;
2474 */
2475
2476 IF (((l_xfer_tbl(k).try_name = 'Credit Memo') AND (l_xfer_tbl(k).rev_txn_id IS NULL))
2477 OR (l_xfer_tbl(k).try_name = 'Billing')) THEN --- vpanwar for bug no 6401432
2478 -- IF l_xfer_tbl(k).try_name IN ('Billing', 'Credit Memo') THEN --- vpanwar for bug no 6401432
2479 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2480 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','calling okl_process_sales_tax_pvt.get_tax_determinants');
2481 END IF;
2482 OKL_PROCESS_SALES_TAX_PVT.get_tax_determinants(
2483 p_api_version => p_api_version,
2484 p_init_msg_list => p_init_msg_list,
2485 x_return_status => x_return_status,
2486 x_msg_count => x_msg_count,
2487 x_msg_data => x_msg_data,
2488 p_source_trx_id => l_xfer_tbl(k).TXN_ID,
2489 p_source_trx_name => l_xfer_tbl(k).try_name,
2490 p_source_table => 'OKL_TXD_AR_LN_DTLS_B',
2491 x_tax_det_rec => x_tax_det_rec); -- 5902234
2492 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2493 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','okl_process_sales_tax_pvt.get_tax_determinants returned with x_return_status:'||x_return_status);
2494 END IF;
2495
2496
2497 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2499 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2500 RAISE FND_API.G_EXC_ERROR;
2501 END IF;
2502 --akrangan code fix begin
2503 --added to eliminate tax attributes for tax only invoices
2504 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX <> 'TAX_ONLY_INVOICE_TAX' THEN
2505 -- 5902234
2506 inv_lines_tbl(hdr_cnt).TAX_CODE := x_tax_det_rec.X_TAX_CODE;
2507 inv_lines_tbl(hdr_cnt).TRX_BUSINESS_CATEGORY := x_tax_det_rec.X_TRX_BUSINESS_CATEGORY;
2508 inv_lines_tbl(hdr_cnt).PRODUCT_CATEGORY := x_tax_det_rec.X_PRODUCT_CATEGORY;
2509 inv_lines_tbl(hdr_cnt).PRODUCT_TYPE := x_tax_det_rec.X_PRODUCT_TYPE;
2510 inv_lines_tbl(hdr_cnt).LINE_INTENDED_USE := x_tax_det_rec.X_LINE_INTENDED_USE;
2511 --added by akrangan for ebtax billing impacts start
2512 inv_lines_tbl(hdr_cnt).USER_DEFINED_FISC_CLASS := x_tax_det_rec.X_USER_DEFINED_FISC_CLASS;
2513 --akrangan code fix begin
2514 END IF; --IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX <> 'TAX_ONLY_INVOICE_TAX'
2515 --akrangan code fix end
2516 inv_lines_tbl(hdr_cnt).TAXED_UPSTREAM_FLAG := 'Y';
2517 --added by akrangan for ebtax billing impacts end
2518 inv_lines_tbl(hdr_cnt).ASSESSABLE_VALUE := x_tax_det_rec.X_ASSESSABLE_VALUE;
2519 inv_lines_tbl(hdr_cnt).DEFAULT_TAXATION_COUNTRY := x_tax_det_rec.X_DEFAULT_TAXATION_COUNTRY;
2520 /*inv_lines_tbl(hdr_cnt).UPSTREAM_TRX_REPORTED_FLAG := x_tax_det_rec.X_UPSTREAM_TRX_REPORTED_FLAG; */
2521 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2522 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').tax_code:' || inv_lines_tbl(hdr_cnt).tax_code);
2523 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').trx_business_category:' || inv_lines_tbl(hdr_cnt).trx_business_category);
2524 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').product_category:' || inv_lines_tbl(hdr_cnt).product_category);
2525 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').product_type:' || inv_lines_tbl(hdr_cnt).product_type);
2526 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').line_intended_use:' || inv_lines_tbl(hdr_cnt).line_intended_use);
2527 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').user_defined_fisc_class :' || inv_lines_tbl(hdr_cnt).user_defined_fisc_class );
2528 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').taxed_upstream_flag:' || inv_lines_tbl(hdr_cnt).taxed_upstream_flag);
2529 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').assessable_value:' || inv_lines_tbl(hdr_cnt).assessable_value);
2530 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').default_taxation_country:' || inv_lines_tbl(hdr_cnt).default_taxation_country);
2531 END IF;
2532 END IF;
2533
2534 --end| 02-MAR-07 cklee R12 Billing enhancement project |
2535
2536 --start| 02-MAR-07 cklee R12 Billing enhancement project |
2537
2538
2539 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2540 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Preparing Ra_interface_lines_all tax record.');
2541 END IF;
2542 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2543 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Done Preparing Ra_interface_lines_all tax record.');
2544 END IF;
2545 END IF;
2546
2547 -- End Sales Tax Code
2548
2549
2550 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2551 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Preparing Ra_Sales_Credits_all record.');
2552 END IF;
2553 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2554 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Preparing Ra_Sales_Credits_all record.');
2555 END IF;
2556 -- -------------------------------------
2557 -- Build Sales Credits Table
2558 -- -------------------------------------
2559 sales_cr_cnt := ( sales_credits_tbl.count + 1 );
2560 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2561 -- Change variable name to match the main cursor query name
2562 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'INVESTOR_STAKE' THEN
2563 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE1 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Agreement_Number),1,G_AR_DATA_LENGTH);
2564 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE2 := NULL;
2565 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE3 := NULL;
2566 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Name),1,G_AR_DATA_LENGTH);
2567 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE5 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2568 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE6 := NULL;
2569 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE7 := NULL;
2570 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE8 := NULL;
2571 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE9 := NULL;
2572 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2573 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE11 := NULL;
2574 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE12 := NULL;
2575 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE13 := NULL;
2576 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2577 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE15 := NULL;
2578 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_CONTEXT := 'OKL_INVESTOR';
2579
2580 ELSE
2581 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE1 := NULL;
2582 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE2 := SUBSTR(TRIM(l_invoice_group),1,G_AR_DATA_LENGTH);
2583 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE3 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_PULL_YN),1,G_AR_DATA_LENGTH);
2584 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).PRIVATE_LABEL),1,G_AR_DATA_LENGTH);
2585 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE5 := NULL;
2586 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE6 := SUBSTR(TRIM(l_xfer_tbl(k).CONTRACT_NUMBER),1,G_AR_DATA_LENGTH);
2587 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE7 := SUBSTR(TRIM(l_xfer_tbl(k).ASSET_NUMBER),1,G_AR_DATA_LENGTH);
2588 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE8 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_LINE_TYPE),1,G_AR_DATA_LENGTH);
2589 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE9 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2590 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2591 -- if the source of the billing trx is termination quote, the OKL billing trx number is Quite_number
2592 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'TERMINATION_QUOTE' THEN
2593 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE11 := SUBSTR(TRIM(l_xfer_tbl(k).Quote_number),1,G_AR_DATA_LENGTH);
2594 END IF;
2595 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE12 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).KHR_ID)),1,G_AR_DATA_LENGTH);
2596 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE13 := SUBSTR(TRIM(l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX),1,G_AR_DATA_LENGTH);
2597 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2598 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_ATTRIBUTE15 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_TYPE),1,G_AR_DATA_LENGTH);
2599 sales_credits_tbl(sales_cr_cnt).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2600 END IF;
2601
2602 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2603
2604 sales_credits_tbl(sales_cr_cnt).SALES_CREDIT_AMOUNT_SPLIT := NULL;
2605 sales_credits_tbl(sales_cr_cnt).SALES_CREDIT_PERCENT_SPLIT := 100;
2606 sales_credits_tbl(sales_cr_cnt).SALES_CREDIT_TYPE_ID := l_sales_type_credit;
2607 sales_credits_tbl(sales_cr_cnt).SALES_CREDIT_TYPE_NAME := 'Quota Sales Credit';
2608 --vthiruva - Bug 4222231..start..removed hardcoding of sales rep
2609 sales_credits_tbl(sales_cr_cnt).SALESREP_ID := l_salesrep_id;
2610 sales_credits_tbl(sales_cr_cnt).SALESREP_NUMBER := l_salesrep_number;
2611 --vthiruva - Bug 4222231..end
2612 sales_credits_tbl(sales_cr_cnt).CREATED_BY := G_user_id;
2613 sales_credits_tbl(sales_cr_cnt).CREATION_DATE := sysdate;
2614 sales_credits_tbl(sales_cr_cnt).LAST_UPDATED_BY := G_user_id;
2615 sales_credits_tbl(sales_cr_cnt).LAST_UPDATE_DATE := sysdate;
2616 sales_credits_tbl(sales_cr_cnt).ORG_ID := l_xfer_tbl(k).ORG_ID;
2617 sales_credits_tbl(sales_cr_cnt).CREATED_BY := G_user_id;
2618 sales_credits_tbl(sales_cr_cnt).CREATION_DATE := sysdate;
2619 sales_credits_tbl(sales_cr_cnt).LAST_UPDATED_BY := G_user_id;
2620 sales_credits_tbl(sales_cr_cnt).LAST_UPDATE_DATE := sysdate;
2621
2622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2623 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').created_by:' || sales_credits_tbl(sales_cr_cnt).created_by);
2624 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').creation_date:' || sales_credits_tbl(sales_cr_cnt).creation_date);
2625 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute1:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute1);
2626 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute10 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute10);
2627 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute11 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute11);
2628 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute12 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute12);
2629 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute13 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute13);
2630 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute14 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute14);
2631 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute15 :' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute15);
2632 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute2:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute2);
2633 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute3:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute3);
2634 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute4:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute4);
2635 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute5:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute5);
2636 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute6:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute6);
2637 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute7:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute7);
2638 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute8:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute8);
2639 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_attribute9:' || sales_credits_tbl(sales_cr_cnt).interface_line_attribute9);
2640 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').interface_line_context:' || sales_credits_tbl(sales_cr_cnt).interface_line_context);
2641 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').last_update_date:' || sales_credits_tbl(sales_cr_cnt).last_update_date);
2642 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').last_updated_by:' || sales_credits_tbl(sales_cr_cnt).last_updated_by);
2643 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').org_id:' || sales_credits_tbl(sales_cr_cnt).org_id);
2644 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').sales_credit_amount_split:' || sales_credits_tbl(sales_cr_cnt).sales_credit_amount_split);
2645 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').sales_credit_percent_split :' || sales_credits_tbl(sales_cr_cnt).sales_credit_percent_split);
2646 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').sales_credit_type_id:' || sales_credits_tbl(sales_cr_cnt).sales_credit_type_id);
2647 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').sales_credit_type_name:' || sales_credits_tbl(sales_cr_cnt).sales_credit_type_name);
2648 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').salesrep_id:' || sales_credits_tbl(sales_cr_cnt).salesrep_id);
2649 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').salesrep_number:' || sales_credits_tbl(sales_cr_cnt).salesrep_number);
2650 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Done Preparing Ra_Sales_Credits_all record.');
2651 END IF;
2652
2653 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2654 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Preparing Ra_Sales_Credits_all record.');
2655 END IF;
2656 -- -------------------------------------
2657 -- Build Contingency table
2658 -- -------------------------------------
2659 IF (l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT') THEN
2660 -- -------------------------------------
2661 -- Build AR contingencies table
2662 -- -------------------------------------
2663 --begin bug 5474184
2664 cont_cnt := ( ar_contingency_tbl.count + 1 );
2665
2666 BEGIN
2667 SELECT AR_INTERFACE_CONTS_S.NEXTVAL
2668 INTO ar_contingency_tbl(cont_cnt).INTERFACE_CONTINGENCY_ID
2669 FROM DUAL;
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'ERROR retrieving ar_contingency_tbl(cont_cnt).INTERFACE_CONTINGENCY_ID: '
2674 ||sqlerrm);
2675 END IF;
2676 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2677 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ERROR retrieving ar_contingency_tbl(cont_cnt).INTERFACE_CONTINGENCY_ID:'||sqlerrm);
2678 END IF;
2679 END;
2680 --end bug 5474184
2681
2682 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2683 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Preparing ar_contingency record.');
2684 END IF;
2685 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2686 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Preparing ar_contingency record.');
2687 END IF;
2688 -- cont_cnt := ( ar_contingency_tbl.count + 1 );
2689 -- start: cklee 3/29/2007
2690 /* 6472168
2691 cont_cnt := hdr_cnt;
2692 --ar_contingency_tbl(cont_cnt).CONTINGENCY_ID := ar_contingency_tbl(hdr_cnt).INTERFACE_CONTINGENCY_ID; */
2693 -- 6472168
2694 -- start: bug 6744584 .. contingency_id populated from stream type table rather than
2695 -- a sequence id.. contingency_code is no longer used...racheruv
2696 --ar_contingency_tbl(cont_cnt).CONTINGENCY_ID := ar_contingency_tbl(cont_cnt).INTERFACE_CONTINGENCY_ID;
2697 ar_contingency_tbl(cont_cnt).CONTINGENCY_ID := l_xfer_tbl(k).contingency_id;
2698
2699 --ar_contingency_tbl(cont_cnt).CONTINGENCY_CODE := 'OKL_COLLECTIBILITY';
2700 -- end: bug 6744584 .. contingency_id populated from stream type table rather than
2701 -- end: cklee 3/29/2007
2702
2703 ar_contingency_tbl(cont_cnt).EXPIRATION_DATE := NULL;
2704 ar_contingency_tbl(cont_cnt).EXPIRATION_DAYS := NULL;
2705 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ID := NULL;
2706 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2707 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2708 -- Change variable name to match the main cursor query name
2709
2710 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'INVESTOR_STAKE' THEN
2711 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE1 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Agreement_Number),1,G_AR_DATA_LENGTH);
2712 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE2 := NULL;
2713 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE3 := NULL;
2714 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Name),1,G_AR_DATA_LENGTH);
2715 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE5 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2716 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE6 := NULL;
2717 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE7 := NULL;
2718 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE8 := NULL;
2719 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE9 := NULL;
2720 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2721 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE11 := NULL;
2722 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE12 := NULL;
2723 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE13 := NULL;
2724 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2725 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE15 := NULL;
2726 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_CONTEXT := 'OKL_INVESTOR';
2727
2728 ELSE
2729 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE1 := NULL;
2730 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE2 := SUBSTR(TRIM(l_invoice_group),1,G_AR_DATA_LENGTH);
2731 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE3 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_PULL_YN),1,G_AR_DATA_LENGTH);
2732 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).PRIVATE_LABEL),1,G_AR_DATA_LENGTH);
2733 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE5 := NULL;
2734 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE6 := SUBSTR(TRIM(l_xfer_tbl(k).CONTRACT_NUMBER),1,G_AR_DATA_LENGTH);
2735 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE7 := SUBSTR(TRIM(l_xfer_tbl(k).ASSET_NUMBER),1,G_AR_DATA_LENGTH);
2736 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE8 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_LINE_TYPE),1,G_AR_DATA_LENGTH);
2737 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE9 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2738 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2739 -- if the source of the billing trx is termination quote, the OKL billing trx number is Quite_number
2740 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'TERMINATION_QUOTE' THEN
2741 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE11 := SUBSTR(TRIM(l_xfer_tbl(k).Quote_number),1,G_AR_DATA_LENGTH);
2742 END IF;
2743 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE12 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).KHR_ID)),1,G_AR_DATA_LENGTH);
2744 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE13 := SUBSTR(TRIM(l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX),1,G_AR_DATA_LENGTH);
2745 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2746 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_ATTRIBUTE15 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_TYPE),1,G_AR_DATA_LENGTH);
2747 ar_contingency_tbl(cont_cnt).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2748
2749 END IF;
2750 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2751
2752
2753 ar_contingency_tbl(cont_cnt).ORG_ID := l_xfer_tbl(k).ORG_ID;
2754 ar_contingency_tbl(cont_cnt).REQUEST_ID := NULL;
2755 ar_contingency_tbl(cont_cnt).CREATED_BY := G_user_id;
2756 ar_contingency_tbl(cont_cnt).CREATION_DATE := sysdate;
2757 ar_contingency_tbl(cont_cnt).LAST_UPDATED_BY := G_user_id;
2758 ar_contingency_tbl(cont_cnt).LAST_UPDATE_DATE := sysdate;
2759 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2760 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Preparing ar_contingency record.');
2761 END IF;
2762 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2763 --bug 6744584 .. contingency_code is not used anymore.
2764 --fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').contingency_code:' || ar_contingency_tbl(cont_cnt).contingency_code);
2765 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').contingency_id:' || ar_contingency_tbl(cont_cnt).contingency_id);
2766 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').expiration_date:' || ar_contingency_tbl(cont_cnt).expiration_date);
2767 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').expiration_days:' || ar_contingency_tbl(cont_cnt).expiration_days);
2768 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute1:' || ar_contingency_tbl(cont_cnt).interface_line_attribute1);
2769 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute10 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute10);
2770 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute11 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute11);
2771 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute12 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute12);
2772 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute13 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute13);
2773 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute14 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute14);
2774 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute15 :' || ar_contingency_tbl(cont_cnt).interface_line_attribute15);
2775 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute2:' || ar_contingency_tbl(cont_cnt).interface_line_attribute2);
2776 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute3:' || ar_contingency_tbl(cont_cnt).interface_line_attribute3);
2777 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute4:' || ar_contingency_tbl(cont_cnt).interface_line_attribute4);
2778 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute5:' || ar_contingency_tbl(cont_cnt).interface_line_attribute5);
2779 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute6:' || ar_contingency_tbl(cont_cnt).interface_line_attribute6);
2780 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute7:' || ar_contingency_tbl(cont_cnt).interface_line_attribute7);
2781 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute8:' || ar_contingency_tbl(cont_cnt).interface_line_attribute8);
2782 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_attribute9:' || ar_contingency_tbl(cont_cnt).interface_line_attribute9);
2783 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_context:' || ar_contingency_tbl(cont_cnt).interface_line_context);
2784 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').interface_line_id:' || ar_contingency_tbl(cont_cnt).interface_line_id);
2785 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').org_id:' || ar_contingency_tbl(cont_cnt).org_id);
2786 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').request_id:' || ar_contingency_tbl(cont_cnt).request_id);
2787 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').created_by:' || ar_contingency_tbl(cont_cnt).created_by);
2788 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').creation_date:' || ar_contingency_tbl(cont_cnt).creation_date);
2789 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').last_updated_by:' || ar_contingency_tbl(cont_cnt).last_updated_by);
2790 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').last_update_date:' || ar_contingency_tbl(cont_cnt).last_update_date);
2791 END IF;
2792 END IF; -- Contingency Table check
2793 -- cklee: start: 3/7/07
2794 --commented out for R12 END IF;
2795 -- cklee: end: 3/7/07
2796
2797 -- -------------------------------------
2798 -- Build AR invoice distributions table
2799 -- -------------------------------------
2800 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2801 -- IF l_xfer_tbl(k).XLS_AMOUNT <> 0 THEN
2802 IF l_xfer_tbl(k).AMOUNT <> 0 THEN
2803 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2804
2805 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2806 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Preparing inv_dist record.');
2807 END IF;
2808
2809 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2810 IF G_ACC_SYS_OPTION = 'ATS' THEN
2811 -- get accounting disb via internal billing details table
2812 -- rmunjulu R12 Fixes, modified to use the new signature of Get_acct_disb
2813 Get_acct_disb(
2814 p_tld_id => l_xfer_tbl(k).txn_id,
2815 x_dist_tbl => lx_dist_tbl);
2816
2817 -- rmunjulu R12 Fixes, loop through the lx_dist_tbl and populate inv_dist_tbl
2818 -- Note : the inv_dist_tbl loop has changed from k to n ( n might be more than k)
2819 IF lx_dist_tbl.COUNT > 0 THEN
2820 FOR m in lx_dist_tbl.FIRST .. lx_dist_tbl.LAST LOOP
2821
2822 --Commented by ssiruvol for bug 5946084
2823 --akrangan uncommnetd the code here for enabling billing distribution begin
2824 IF (l_xfer_tbl(k).rev_rec_basis = 'CASH_RECEIPT' AND l_xfer_tbl(k).AMOUNT < 0
2825 AND lx_dist_tbl(m).ACCOUNT_CLASS = 'REV') THEN
2826 inv_dist_tbl(n).ACCOUNT_CLASS := 'UNEARN';
2827 ELSE
2828 inv_dist_tbl(n).ACCOUNT_CLASS := lx_dist_tbl(m).ACCOUNT_CLASS;
2829 END IF;
2830 --akrangan uncommnetd the code here for enabling billing distribution end
2831 /*
2832 --code commented by akrangan since it is breaking the billing distribution flow
2833 inv_dist_tbl(k).ACCOUNT_CLASS := l_xfer_tbl(k).ACCOUNT_CLASS; --Added by ssiruvol for Bug 5946084
2834 */
2835
2836 inv_dist_tbl(n).AMOUNT := lx_dist_tbl(m).DIST_AMOUNT;
2837 inv_dist_tbl(n).PERCENT := lx_dist_tbl(m).DIST_PERCENT;
2838
2839 inv_dist_tbl(n).CODE_COMBINATION_ID := lx_dist_tbl(m).CODE_COMBINATION_ID;
2840 inv_dist_tbl(n).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2841
2842 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'INVESTOR_STAKE' THEN
2843 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE1 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Agreement_Number),1,G_AR_DATA_LENGTH);
2844 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE2 := NULL;
2845 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE3 := NULL;
2846 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).Investor_Name),1,G_AR_DATA_LENGTH);
2847 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE5 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2848 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE6 := NULL;
2849 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE7 := NULL;
2850 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE8 := NULL;
2851 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE9 := NULL;
2852 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2853 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE11 := NULL;
2854 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE12 := NULL;
2855 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE13 := NULL;
2856 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2857 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE15 := NULL;
2858 inv_dist_tbl(n).INTERFACE_LINE_CONTEXT := 'OKL_INVESTOR';
2859
2860 ELSE
2861 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE1 := NULL;
2862 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE2 := SUBSTR(TRIM(l_invoice_group),1,G_AR_DATA_LENGTH);
2863 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE3 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_PULL_YN),1,G_AR_DATA_LENGTH);
2864 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE4 := SUBSTR(TRIM(l_xfer_tbl(k).PRIVATE_LABEL),1,G_AR_DATA_LENGTH);
2865 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE5 := NULL;
2866 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE6 := SUBSTR(TRIM(l_xfer_tbl(k).CONTRACT_NUMBER),1,G_AR_DATA_LENGTH);
2867 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE7 := SUBSTR(TRIM(l_xfer_tbl(k).ASSET_NUMBER),1,G_AR_DATA_LENGTH);
2868 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE8 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_LINE_TYPE),1,G_AR_DATA_LENGTH);
2869 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE9 := SUBSTR(TRIM(l_xfer_tbl(k).STREAM_TYPE),1,G_AR_DATA_LENGTH);
2870 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE10 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2871 -- if the source of the billing trx is termination quote, the OKL billing trx number is Quite_number
2872 IF l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX = 'TERMINATION_QUOTE' THEN
2873 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE11 := SUBSTR(TRIM(l_xfer_tbl(k).Quote_number),1,G_AR_DATA_LENGTH);
2874 END IF;
2875 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE12 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).KHR_ID)),1,G_AR_DATA_LENGTH);
2876 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE13 := SUBSTR(TRIM(l_xfer_tbl(k).OKL_SOURCE_BILLING_TRX),1,G_AR_DATA_LENGTH);
2877 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE14 := SUBSTR(TRIM(TO_CHAR(l_xfer_tbl(k).TXN_ID)),1,G_AR_DATA_LENGTH);
2878 inv_dist_tbl(n).INTERFACE_LINE_ATTRIBUTE15 := SUBSTR(TRIM(l_xfer_tbl(k).INVOICE_FORMAT_TYPE),1,G_AR_DATA_LENGTH);
2879 inv_dist_tbl(n).INTERFACE_LINE_CONTEXT := 'OKL_CONTRACTS';
2880 END IF;
2881
2882 inv_dist_tbl(n).ORG_ID := l_xfer_tbl(k).ORG_ID;
2883 inv_dist_tbl(n).CREATED_BY := G_user_id;
2884 inv_dist_tbl(n).CREATION_DATE := sysdate;
2885 inv_dist_tbl(n).LAST_UPDATED_BY := G_user_id;
2886 inv_dist_tbl(n).LAST_UPDATE_DATE := sysdate;
2887
2888 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2889 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Preparing inv_dist record.');
2890 END IF;
2891 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2892 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').account_class:' || inv_dist_tbl(n).account_class);
2893 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').amount:' || inv_dist_tbl(n).amount);
2894 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').code_combination_id:' || inv_dist_tbl(n).code_combination_id);
2895 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').created_by:' || inv_dist_tbl(n).created_by);
2896 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').creation_date:' || inv_dist_tbl(n).creation_date);
2897 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute1:' || inv_dist_tbl(n).interface_line_attribute1 );
2898 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute10 :' || inv_dist_tbl(n).interface_line_attribute10);
2899 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute11 :' || inv_dist_tbl(n).interface_line_attribute11);
2900 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute12 :' || inv_dist_tbl(n).interface_line_attribute12);
2901 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute13 :' || inv_dist_tbl(n).interface_line_attribute13);
2902 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute14 :' || inv_dist_tbl(n).interface_line_attribute14);
2903 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute15 :' || inv_dist_tbl(n).interface_line_attribute15);
2904 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute2:' || inv_dist_tbl(n).interface_line_attribute2 );
2905 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute3:' || inv_dist_tbl(n).interface_line_attribute3 );
2906 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute4:' || inv_dist_tbl(n).interface_line_attribute4 );
2907 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute5:' || inv_dist_tbl(n).interface_line_attribute5 );
2908 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute6:' || inv_dist_tbl(n).interface_line_attribute6 );
2909 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute7:' || inv_dist_tbl(n).interface_line_attribute7 );
2910 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute8:' || inv_dist_tbl(n).interface_line_attribute8 );
2911 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_attribute9:' || inv_dist_tbl(n).interface_line_attribute9 );
2912 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').interface_line_context:' || inv_dist_tbl(n).interface_line_context);
2913 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').last_update_date:' || inv_dist_tbl(n).last_update_date);
2914 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').last_updated_by:' || inv_dist_tbl(n).last_updated_by);
2915 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').org_id:' || inv_dist_tbl(n).org_id);
2916 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').percent:' || inv_dist_tbl(n).percent);
2917 END IF;
2918 n := n + 1;
2919 END LOOP;
2920
2921 END IF;
2922
2923 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2924 END IF; -- IF G_ACC_SYS_OPTION = 'ATS' THEN
2925 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2926
2927 END IF; -- IF l_xfer_tbl(k).AMOUNT <> 0 THEN
2928 -- ---------------------------------
2929 -- Check and reset commit counter
2930 -- ---------------------------------
2931
2932 IF l_commit_cnt > G_COMMIT_SIZE THEN
2933 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2934 -- Additional check to see if all TAI related TXD records have been completed
2935 --Added below if condition by nikshah for bug 7696685
2936 IF l_xfer_tbl.exists(k+1) THEN
2937 IF l_xfer_tbl(k).TAI_ID <> l_xfer_tbl(k+1).TAI_ID THEN
2938 l_commit_cnt := 0;
2939 COMMIT;
2940 END IF;
2941 END IF;
2942 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2943 END IF;
2944
2945 -- ---------------------------------
2946 -- Set header_id local variable
2947 -- ---------------------------------
2948
2949 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2950 -- l_hdr_id := l_xfer_tbl(k).xsi_id;
2951 l_hdr_id := l_xfer_tbl(k).tai_id;
2952 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2953
2954 END LOOP;
2955
2956 -- ----------------------------------
2957 -- Update prev header status
2958 -- ----------------------------------
2959 --start: | 15-FEB-07 cklee R12 Billing enhancement project
2960 -- UPDATE OKL_EXT_SELL_INVS_B
2961 -- Update internal billing table instead
2962 UPDATE okl_trx_ar_invoices_b
2963 --end: | 15-FEB-07 cklee R12 Billing enhancement project
2964 SET trx_status_code = 'PROCESSED',
2965 last_update_date = sysdate,
2966 last_updated_by = lx_last_updated_by,
2967 last_update_login = lx_last_update_login,
2968 request_id = lx_request_id,
2969 program_update_date = sysdate,
2970 program_application_id = lx_program_application_id,
2971 program_id = lx_program_id
2972 WHERE ID = l_hdr_id;
2973
2974 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2975 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Updating last record with id: '||l_hdr_id);
2976 END IF;
2977 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2978 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updating last record with id: '||l_hdr_id);
2979 END IF;
2980 COMMIT;
2981
2982 -- -------------------------------------
2983 -- Clear error table before bulk insert
2984 -- -------------------------------------
2985 error_tbl.delete;
2986
2987 -- ---------------------------------------------
2988 -- Transfer line records to the AR interface
2989 -- ---------------------------------------------
2990
2991 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2992 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into ra_interface_lines_all');
2993 END IF;
2994 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2995 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into ra_interface_lines_all');
2996 END IF;
2997
2998 IF inv_lines_tbl.COUNT > 0 THEN
2999 FORALL indx in inv_lines_tbl.first..inv_lines_tbl.LAST
3000 SAVE EXCEPTIONS
3001 INSERT INTO ra_interface_lines_all
3002 VALUES inv_lines_tbl(indx);
3003 END IF;
3004
3005 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3006 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into ra_interface_lines_all');
3007 END IF;
3008 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3009 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Done Inserting into ra_interface_lines_all');
3010 END IF;
3011 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
3012 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3013 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3014 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'For interface lines, error ' || i || ' occurred during '||
3015 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3016 END IF;
3017
3018 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3019 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Oracle error is ' ||
3020 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3021 -- bug 5474184
3022 END IF;
3023 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3024 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','For interface lines, error ' || i || ' occurred during iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3025 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3026 -- bug 5474184
3027 END IF;
3028 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3029 -- error_tbl(error_tbl.count + 1).id := to_number(inv_lines_tbl(i).interface_line_attribute10||
3030 -- inv_lines_tbl(i).interface_line_attribute11);
3031 error_tbl(error_tbl.count + 1).id := to_number(inv_lines_tbl(i).interface_line_attribute14);
3032 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3033
3034 END LOOP;
3035 END IF;
3036
3037 -- ---------------------------------------------
3038 -- Transfer sales credits records to the AR interface
3039 -- ---------------------------------------------
3040 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3041 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into sales_credits');
3042 END IF;
3043 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3044 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into sales_credits');
3045 END IF;
3046 IF sales_credits_tbl.COUNT > 0 THEN
3047 FORALL indx in sales_credits_tbl.first..sales_credits_tbl.LAST
3048 SAVE EXCEPTIONS
3049 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
3050 VALUES sales_credits_tbl(indx);
3051 END IF;
3052
3053 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
3054 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3055 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3056 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'For sales credits, error ' || i || ' occurred during '||
3057 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3058 END IF;
3059 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3060 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Oracle error is ' ||
3061 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3062 -- bug 5474184
3063 END IF;
3064 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3065 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','For sales credits, error ' || i || ' occurred during iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3066 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3067 -- bug 5474184
3068 END IF;
3069 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3070 -- error_tbl(error_tbl.count + 1).id := to_number(sales_credits_tbl(i).interface_line_attribute10||
3071 -- sales_credits_tbl(i).interface_line_attribute11);
3072 error_tbl(error_tbl.count + 1).id := to_number(sales_credits_tbl(i).interface_line_attribute14);
3073 --end: | 15-FEB-07 cklee R12 Billing enhancement project
3074 END LOOP;
3075 END IF;
3076
3077 -- ---------------------------------------------
3078 -- Transfer contingency records to the AR interface
3079 -- ---------------------------------------------
3080
3081 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3082 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into ar_contingency');
3083 END IF;
3084 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3085 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into ar_contingency');
3086 END IF;
3087 IF ar_contingency_tbl.COUNT > 0 THEN
3088
3089 FORALL indx in ar_contingency_tbl.first..ar_contingency_tbl.LAST
3090 SAVE EXCEPTIONS
3091 INSERT INTO AR_INTERFACE_CONTS_ALL
3092 VALUES ar_contingency_tbl(indx);
3093
3094 END IF;
3095
3096 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
3097 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3098 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3099 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'For AR contingencies, error ' || i || ' occurred during '||
3100 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3101 END IF;
3102
3103 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3104 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Oracle error is ' ||
3105 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3106 -- bug 5474184
3107 END IF;
3108 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3109 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','For AR contingencies, error ' || i || ' occurred during iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3110 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3111 -- bug 5474184
3112 END IF;
3113 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3114 -- error_tbl(error_tbl.count + 1).id := to_number(ar_contingency_tbl(i).interface_line_attribute10||
3115 -- ar_contingency_tbl(i).interface_line_attribute11);
3116 error_tbl(error_tbl.count + 1).id := to_number(ar_contingency_tbl(i).interface_line_attribute14);
3117 --end: | 15-FEB-07 cklee R12 Billing enhancement project
3118 END LOOP;
3119 END IF;
3120
3121 -- ----------------------------------------------------------
3122 -- Transfer invoice distribution records to the AR interface
3123 -- ----------------------------------------------------------
3124 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3125 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into inv_dist');
3126 END IF;
3127 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3128 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into inv_dist');
3129 END IF;
3130 IF inv_dist_tbl.count > 0 THEN
3131 FORALL indx in inv_dist_tbl.first..inv_dist_tbl.LAST
3132 SAVE EXCEPTIONS
3133 INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
3134 VALUES inv_dist_tbl(indx);
3135 END IF;
3136
3137 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
3138 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3139 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3140 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'For distributions, error ' || i || ' occurred during '||
3141 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3142 END IF;
3143
3144 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3145 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Oracle error is ' ||
3146 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3147 -- bug 5474184
3148 END IF;
3149 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3150 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','For distributions, error ' || i || ' occurred during iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3151 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3152 -- bug 5474184
3153 END IF;
3154 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3155 -- error_tbl(error_tbl.count + 1).id := to_number(inv_dist_tbl(i).interface_line_attribute10||
3156 -- inv_dist_tbl(i).interface_line_attribute11);
3157 error_tbl(error_tbl.count + 1).id := to_number(inv_dist_tbl(i).interface_line_attribute14);
3158 --end: | 15-FEB-07 cklee R12 Billing enhancement project
3159
3160 END LOOP;
3161 END IF;
3162
3163 if error_tbl.count > 0 then
3164 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3165 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error Processing');
3166 END IF;
3167 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3168 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Error Processing');
3169 END IF;
3170 for indx in error_tbl.FIRST..error_tbl.LAST loop
3171
3172 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3173 -- Modify the error table keys to internal billing tables
3174 -- UPDATE XSI trx_status_code to 'WORKING'
3175 -- l_error_xsi_id := NULL;
3176 Begin
3177 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3178 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'** XSI_ID is : '||l_error_xsi_id);
3179 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'** TXD_ID is : '||error_tbl(indx).id);
3180 END IF;
3181 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3182 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','** TXD_ID is : '||error_tbl(indx).id);
3183 END IF;
3184 Exception
3185 When Others Then
3186 -- l_error_xsi_id := NULL;
3187 l_error_txd_id := NULL;
3188 l_error_tai_id := NULL;
3189
3190 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3191 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'** Exception ** error_xsi_id: '||l_error_xsi_id);
3192 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'** Exception ** error_txd_id: '||error_tbl(indx).id);
3193 END IF;
3194 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3195 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','** Exception ** error_txd_id: '||error_tbl(indx).id);
3196 END IF;
3197 End;
3198 -- update okl_ext_sell_invs_b
3199 update okl_trx_ar_invoices_b tai
3200 set tai.trx_status_code = 'WORKING'
3201 -- where id = l_error_xsi_id;
3202 where id = l_error_tai_id;
3203 --end: | 15-FEB-07 cklee R12 Billing enhancement project
3204
3205 -- --------------------------------------------
3206 -- delete records from sales credits interface
3207 -- --------------------------------------------
3208 delete from RA_INTERFACE_SALESCREDITS
3209 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3210 -- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
3211 -- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
3212 where interface_line_attribute14 = to_char(error_tbl(indx).id);
3213 --endt: | 15-FEB-07 cklee R12 Billing enhancement project
3214
3215 -- ----------------------------------
3216 -- delete records from contingencies
3217 -- ----------------------------------
3218 delete from AR_INTERFACE_CONTS
3219 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3220 -- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
3221 -- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
3222 where interface_line_attribute14 = to_char(error_tbl(indx).id);
3223 --endt: | 15-FEB-07 cklee R12 Billing enhancement project
3224
3225 -- ---------------------------
3226 -- delete records from distributions
3227 -- ---------------------------
3228 delete from RA_INTERFACE_DISTRIBUTIONS
3229 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3230 -- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
3231 -- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
3232 where interface_line_attribute14 = to_char(error_tbl(indx).id);
3233 --endt: | 15-FEB-07 cklee R12 Billing enhancement project
3234
3235 -- ---------------------------
3236 -- delete records from lines
3237 -- ---------------------------
3238 delete from ra_interface_lines
3239 --start: | 15-FEB-07 cklee R12 Billing enhancement project
3240 -- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
3241 -- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
3242 where interface_line_attribute14 = to_char(error_tbl(indx).id);
3243 --endt: | 15-FEB-07 cklee R12 Billing enhancement project
3244
3245 end loop;
3246 end if;
3247
3248 END IF; -- If PL/sql table has any records
3249 EXIT WHEN xfer_csr%NOTFOUND;
3250 END LOOP;
3251 CLOSE xfer_csr;
3252
3253 l_xfer_tbl.delete;
3254 -- -----------------------------
3255 -- End Bulk Fetch Code
3256 -- -----------------------------
3257
3258 ------------------------------------------------------------
3259 -- Print log and output messages
3260 ------------------------------------------------------------
3261
3262 -- Get the request Id
3263 l_request_id := NULL;
3264 OPEN req_id_csr;
3265 FETCH req_id_csr INTO l_request_id;
3266 CLOSE req_id_csr;
3267
3268 processed_sts := 'PROCESSED';
3269 error_sts := 'ERROR';
3270
3271 l_succ_cnt := 0;
3272 l_err_cnt := 0;
3273
3274 --start: | 15-FEB-07 cklee R12 Billing enhancement project |
3275 -- Success Count
3276 OPEN tld_cnt_csr( l_request_id, processed_sts );
3277 FETCH tld_cnt_csr INTO l_succ_cnt;
3278 CLOSE tld_cnt_csr;
3279
3280 -- Error Count
3281 OPEN tld_cnt_csr( l_request_id, error_sts );
3282 FETCH tld_cnt_csr INTO l_err_cnt;
3283 CLOSE tld_cnt_csr;
3284 --end: | 15-FEB-07 cklee R12 Billing enhancement project |
3285
3286
3287 ----------------------------------------
3288 -- Get Operating unit name
3289 ----------------------------------------
3290 l_op_unit_name := NULL;
3291 OPEN op_unit_csr;
3292 FETCH op_unit_csr INTO l_op_unit_name;
3293 CLOSE op_unit_csr;
3294
3295 -- Start New Out File stmathew 15-OCT-2004
3296 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 54, ' ')||'Oracle Leasing and Finance Management'||lpad(' ', 55, ' '));
3297 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3298 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 49, ' ')||'Receivables Invoice Transfer To AR'||lpad(' ', 49, ' '));
3299 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 49, ' ')||'----------------------------------'||lpad(' ', 49, ' '));
3300 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3301 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3302 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Operating Unit: '||l_op_unit_name);
3303 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Request Id: '||l_request_id||lpad(' ',74,' ') ||'Run Date: '||to_char(sysdate));
3304 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
3305 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('-', 132, '-'));
3306 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'From Bill Date : ' ||p_trx_date_from);
3307 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'To Bill Date : ' ||p_trx_date_to);
3308 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('-', 132, '-'));
3309 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3310 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3311
3312 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Processing Details:'||lpad(' ', 113, ' '));
3313 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3314 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Invoice Lines Selected: '||(l_succ_cnt+l_err_cnt));
3315 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Invoice Lines Transferred: '||l_succ_cnt);
3316 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Invoice Lines Errored: '||l_err_cnt);
3317 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3318
3319 -- End New Out File stmathew 15-OCT-2004
3320 IF x_msg_count > 0 THEN
3321 FOR i IN 1..x_msg_count LOOP
3322 IF i = 1 THEN
3323 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Details of Errored Invoice Lines:'||lpad(' ', 97, ' '));
3324 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3325 END IF;
3326 fnd_msg_pub.get (p_msg_index => i,
3327 p_encoded => 'F',
3328 p_data => lx_msg_data,
3329 p_msg_index_out => l_msg_index_out);
3330
3331 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,TO_CHAR(i) || ': ' || lx_msg_data);
3332
3333 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3334 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
3335 TO_CHAR(i) || ': ' || lx_msg_data);
3336 END IF;
3337
3338 END LOOP;
3339 END IF;
3340
3341
3342 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3343 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_arintf_pvt'
3344 ,'End(-)');
3345 END IF;
3346
3347 -- ----------------------------------------------------------
3348 -- End processing
3349 -- ----------------------------------------------------------
3350
3351 -- -------------------------------------------
3352 -- Purge data from the Parallel process Table
3353 -- -------------------------------------------
3354 IF p_assigned_process IS NOT NULL THEN
3355
3356 DELETE okl_parallel_processes
3357 WHERE assigned_process = p_assigned_process;
3358
3359 COMMIT;
3360
3361 END IF;
3362
3363
3364 EXCEPTION
3365
3366 WHEN bulk_errors THEN
3367
3368 -- -------------------------------------------
3369 -- Purge data from the Parallel process Table
3370 -- -------------------------------------------
3371 IF p_assigned_process IS NOT NULL THEN
3372 DELETE okl_parallel_processes
3373 WHERE assigned_process = p_assigned_process;
3374 COMMIT;
3375 END IF;
3376
3377 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
3378 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
3379 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3380 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Exception: For interface lines, error ' || i || ' occurred during '||
3381 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3382 END IF;
3383 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3384 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Oracle error is ' ||
3385 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3386 -- bug 5474184
3387 END IF;
3388 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3389 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Exception: For interface lines, error ' || i || ' occurred during iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
3390 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
3391 -- bug 5474184
3392 END IF;
3393 END LOOP;
3394 END IF;
3395
3396 WHEN FND_API.G_EXC_ERROR THEN
3397 x_return_status := OKL_API.G_RET_STS_ERROR;
3398 FND_MSG_PUB.Count_And_Get
3399 (p_count => x_msg_count,
3400 p_data => x_msg_data);
3401
3402 IF x_msg_count > 0 THEN
3403 FOR i IN 1..x_msg_count LOOP
3404
3405 fnd_msg_pub.get (p_msg_index => i,
3406 p_encoded => 'F',
3407 p_data => lx_msg_data,
3408 p_msg_index_out => l_msg_index_out);
3409
3410 FND_FILE.put_line(fnd_file.log,'ERROR (OKL_ARIntf_Pvt.Get_REC_FEEDER): ' || lx_msg_data);
3411 END LOOP;
3412 END IF;
3413
3414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3415 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3416 FND_MSG_PUB.Count_And_Get
3417 (p_count => x_msg_count,
3418 p_data => x_msg_data);
3419
3420 IF x_msg_count > 0 THEN
3421 FOR i IN 1..x_msg_count LOOP
3422
3423 fnd_msg_pub.get (p_msg_index => i,
3424 p_encoded => 'F',
3425 p_data => lx_msg_data,
3426 p_msg_index_out => l_msg_index_out);
3427
3428 FND_FILE.put_line(fnd_file.log,'ERROR (OKL_ARIntf_Pvt.Get_REC_FEEDER): ' || lx_msg_data);
3429 END LOOP;
3430 END IF;
3431
3432 WHEN OTHERS THEN
3433 x_return_status := Okl_Api.G_RET_STS_ERROR;
3434 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3435 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'INSERT Failed ' || SQLERRM);
3436 END IF;
3437 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3438 fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','INSERT Failed ' || SQLERRM);
3439 END IF;
3440 -- -------------------------------------------
3441 -- Purge data from the Parallel process Table
3442 -- -------------------------------------------
3443 IF p_assigned_process IS NOT NULL THEN
3444 DELETE okl_parallel_processes
3445 WHERE assigned_process = p_assigned_process;
3446 COMMIT;
3447 END IF;
3448
3449 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3450 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_arintf_pvt',
3451 'EXCEPTION :'||'OTHERS');
3452 END IF;
3453
3454 END Get_REC_FEEDER;
3455
3456 END Okl_Arintf_Pvt;