[Home] [Help]
PACKAGE BODY: APPS.OKL_PAY_INVOICES_TRANS_PVT
Source
1 PACKAGE BODY OKL_PAY_INVOICES_TRANS_PVT AS
2 /* $Header: OKLRPIIB.pls 120.25 2008/05/07 22:12:07 cklee noship $ */
3
4 --start:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
5 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
6 --end:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
7 -----------------------------------------------------------------
8 --30/May/02 Added vendor_id and line type for NVL
9 -----------------------------------------------------------------
10
11 PROCEDURE transfer
12 (p_api_version IN NUMBER
13 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
14 ,x_return_status OUT NOCOPY VARCHAR2
15 ,x_msg_count OUT NOCOPY NUMBER
16 ,x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18
19 /* rkuttiya modified removed old code */
20
21 l_xpi_id okl_ext_pay_invs_b.id%type;
22 v_description ap_invoices_interface.description%type;
23
24 l_api_version CONSTANT NUMBER := 1;
25 l_api_name CONSTANT VARCHAR2(30) := 'TRANSFER';
26 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
27 l_self_bill_invnum VARCHAR2(150);
28 l_contract_number VARCHAR2(120);
29 l_vendor_id po_vendor_sites_all.vendor_id%TYPE;
30 l_amount_includes_tax_flag po_vendor_sites_all.amount_includes_tax_flag%TYPE;
31 l_taxable_yn VARCHAR2(1);
32
33 --start:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
34 CURSOR c_account_derivation IS
35 select account_derivation,
36 PAY_DIST_SET_ID
37 from OKL_SYS_ACCT_OPTS;
38
39 l_account_derivation OKL_SYS_ACCT_OPTS_ALL.account_derivation%type := NULL;
40 l_PAY_DIST_SET_ID OKL_SYS_ACCT_OPTS_ALL.PAY_DIST_SET_ID%type := NULL;
41 --end:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
42
43 CURSOR c_invoice_hdr IS
44 SELECT *
45 FROM okl_ext_pay_invs_b
46 WHERE trx_status_code = 'ENTERED'
47 FOR UPDATE OF TRX_STATUS_CODE;
48
49
50 CURSOR c_invoice_lines(p_xpi_id NUMBER) IS
51 SELECT *
52 FROM okl_xtl_pay_invs_b
53 WHERE xpi_id_details = p_xpi_id;
54
55 cursor c_cnsld_hdr(p_cnsld_ap_inv_id IN NUMBER) IS
56 SELECT self_bill_inv_num
57 FROM okl_cnsld_ap_invs
58 WHERE cnsld_ap_inv_id = p_cnsld_ap_inv_id;
59
60 CURSOR c_taxable_yn(p_tpl_id IN NUMBER) IS
61 SELECT taxable_yn
62 FROM okl_txl_ap_inv_lns_b
63 WHERE id = p_tpl_id;
64
65 -- sjalasut, modified code to refer khr_id from okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b
66 -- changes made as part of OKLR12B disbursements project
67 CURSOR c_num_csr ( p_id NUMBER ) IS
68 SELECT CHR.CONTRACT_NUMBER
69 FROM okc_k_headers_b chr
70 ,okl_txl_ap_inv_lns_all_b tpl
71 ,okl_xtl_pay_invs_all_b xlp
72 WHERE XLP.ID = p_id
73 AND XLP.tpl_id = TPL.id
74 AND TPL.khr_id = chr.id;
75
76
77 -- XLP.tpl_id has tap_id
78 -- sjalasut, modified code to refer khr_id from okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b
79 -- changes made as part of OKLR12B disbursements project
80 /* rkuttiya commented this cursor as no longer required */
81 /* CURSOR c_fun_csr ( p_id NUMBER ) IS
82 SELECT CHR.CONTRACT_NUMBER
83 FROM okc_k_headers_b chr
84 ,okl_trx_ap_invoices_b tap
85 ,okl_txl_ap_inv_lns_all_b tpl
86 ,okl_xtl_pay_invs_all_b xlp
87 WHERE XLP.XPI_ID_DETAILS = p_id
88 AND XLP.tap_id = TAP.id
89 AND tpl.tap_id = tap.id
90 AND tpl.khr_id = chr.id; */
91
92 CURSOR v_id_csr( p_site_id NUMBER ) IS
93 SELECT povs.vendor_id
94 FROM po_vendor_sites_all povs
95 WHERE povs.vendor_site_id = p_site_id;
96
97 --start:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
98 --| 2. Fixed try_id, kle_id issues |
99
100 CURSOR c_try_name(p_tpl_id IN NUMBER) IS
101 SELECT try.name try_name,
102 tpl.sty_id
103 FROM okl_txl_ap_inv_lns_b tpl
104 , okl_trx_ap_invoices_b tap
105 , okl_trx_types_v try
106 WHERE tpl.id = p_tpl_id
107 AND tpl.tap_id = tap.id
108 AND tap.try_id = try.id;
109
110 CURSOR get_top_line_name ( p_cle_id NUMBER ) IS
111 select cle.name
112 from OKC_K_LINES_V cle
113 where cle.id = p_cle_id;
114
115 CURSOR c_stream_type_purpose (p_sty_id NUMBER ) IS
116 select stream_type_purpose
117 from OKL_STRM_TYPE_B
118 where id = p_sty_id;
119
120 l_asset_number OKC_K_LINES_V.name%type;
121 l_stream_type_purpose OKL_STRM_TYPE_B.stream_type_purpose%type;
122 l_sty_id OKL_STRM_TYPE_B.id%type;
123
124 --end:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
125 --| 2. Fixed try_id, kle_id issues |
126
127 CURSOR c_top_line(p_tpl_id IN NUMBER) IS
128 SELECT NVL(kle.cle_id, kle.id) top_kle_id
129 , kle.dnz_chr_id khr_id
130 -- , try.name try_name -- cklee 06/19/2007
131 FROM OKC_K_LINES_B kle
132 , okl_txl_ap_inv_lns_b tpl
133 , okl_trx_ap_invoices_b tap
134 , okl_trx_types_v try
135 WHERE tpl.id = p_tpl_id
136 AND tpl.kle_id = kle.id
137 AND tpl.tap_id = tap.id
138 AND tap.try_id = try.id;
139
140 /*
141 CURSOR Ship_to_csr( p_top_kle_id IN NUMBER ) IS
142 SELECT csi.install_location_id
143 , csi.location_id
144 FROM csi_item_instances csi,
145 okc_k_items cim,
146 okc_k_lines_b inst,
147 okc_k_lines_b ib,
148 okc_line_styles_b lse
149 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
150 AND cim.cle_id = ib.id
151 AND ib.cle_id = inst.id
152 AND inst.lse_id = lse.id
153 AND lse.lty_code = 'FREE_FORM2'
154 AND inst.cle_id = p_top_kle_id;
155 */
156 --start:07-May-2008 cklee -- Fixed bug:7015970
157 CURSOR Ship_to_csr( p_top_kle_id IN NUMBER ) IS
158 SELECT csi.install_location_id
159 , csi.location_id
160 , csi.install_location_type_code
161 -- , csi.location_type_code
162 FROM csi_item_instances csi,
163 okc_k_items cim,
164 okc_k_lines_b inst,
165 okc_k_lines_b ib,
166 okc_line_styles_b lse
167 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
168 AND cim.cle_id = ib.id
169 AND ib.cle_id = inst.id
170 AND inst.lse_id = lse.id
171 AND lse.lty_code = 'FREE_FORM2'
172 AND inst.cle_id = p_top_kle_id;
173
174 Cursor location_csr(p_party_site_id IN number) is
175 select hps.location_id
176 from hz_party_sites hps
177 where hps.party_site_id = p_party_site_id;
178
179 --Following Logic Applicable only for after Book:
180 --If install_location_type_code = 'HZ_LOCATIONS'
181 --then take the install_location_id from Ship_to_csr
182 --Else If install_location_type_code = 'HZ_PARTY_SITES'
183 --then execute location_csr by passing install_location_id
184 --as p_party_site_id parameter and take location_id from
185 -- location_csr
186 --end: 07-May-2008 cklee -- Fixed bug:7015970
187
188 --
189 --start:| 06-Jul-2007 cklee -- Fixed ship to issue |
190 CURSOR Ship_to_csr_before_booked( p_top_kle_id IN NUMBER ) IS
191 select hps.party_site_id install_location_id,
192 hl.location_id
193 from hz_locations hl,
194 hz_party_sites hps,
195 hz_party_site_uses hpsu,
196 okl_txl_itm_insts tii,
197 okc_k_lines_b cleb_ib,
198 okc_k_lines_b cleb_inst,
199 okc_line_styles_b lse1,
200 okc_line_styles_b lse2
201 where hl.location_id = hps.location_id
202 and hps.party_site_id = hpsu.party_site_id
203 and hpsu.party_site_use_id = tii.object_id1_new
204 and tii.jtot_object_code_new = 'OKX_PARTSITE'
205 and tii.kle_id = cleb_ib.id
206 and cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
207 and cleb_ib.cle_id = cleb_inst.id
208 and cleb_ib.lse_id = lse1.id
209 and lse1.lty_code = 'INST_ITEM'
210 and cleb_inst.cle_id = p_top_kle_id
211 and cleb_inst.lse_id = lse2.id
212 and lse2.lty_code = 'FREE_FORM2';
213
214 --end:| 06-Jul-2007 cklee -- Fixed ship to issue |
215 --
216
217 CURSOR get_khr_id_csr ( p_khr_id VARCHAR2 ) IS
218 SELECT cust_acct_id,
219 sts_code --07-May-2008 cklee -- Fixed bug:7015970
220 FROM okc_k_headers_b khr
221 where khr.id = p_khr_id;
222
223 /*--07-May-2008 cklee -- Fixed bug:7015970
224 CURSOR Ship_to_csr2( p_customer_num NUMBER, p_install_location NUMBER, p_location NUMBER) IS
225 SELECT a.CUST_ACCT_SITE_ID
226 FROM hz_cust_acct_sites_all a,
227 hz_cust_site_uses_all b,
228 hz_party_sites c
229 WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
230 b.site_use_code = 'SHIP_TO' AND
231 a.party_site_id = c.party_site_id AND
232 a.cust_account_id = p_customer_num AND
233 c.party_site_id = p_install_location AND
234 c.location_id = p_location;
235 --07-May-2008 cklee -- Fixed bug:7015970*/
236
237
238 l_top_kle_id NUMBER;
239 l_khr_id NUMBER;
240 l_install_location_id NUMBER;
241 l_location_id NUMBER;
242 l_install_location_type_code csi_item_instances.install_location_type_code%type; -- 07-May-2008 cklee -- Fixed bug:7015970
243 l_customer_id NUMBER;
244 l_sts_code okc_k_headers_all_b.sts_code%type; -- 07-May-2008 cklee -- Fixed bug:7015970
245 l_ship_to NUMBER;
246 l_try_name okl_trx_types_v.name%type;
247
248 --Get the inventory for a financial asset line or service line.
249 --A line can either be a fin asset or service line
250 CURSOR get_inv_item_id ( p_cle_id NUMBER ) IS
251 SELECT c.OBJECT1_ID1
252 FROM okc_k_lines_b a,
253 okc_line_styles_b b,
254 okc_k_items c
255 WHERE a.cle_id = p_cle_id
256 AND a.lse_id = b.id
257 AND b.lty_code = 'ITEM'
258 AND a.id = c.cle_id
259 UNION
260 SELECT c.object1_id1
261 FROM okc_k_lines_v a,
262 okc_line_styles_v b,
263 okc_k_items c
264 WHERE a.id = p_cle_id
265 AND a.lse_id = b.id
266 AND b.lty_code = 'SOLD_SERVICE'
267 AND c.cle_id = a.id;
268
269 l_inventory_item_id NUMBER;
270 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
271 lx_tax_det_rec OKL_PROCESS_SALES_TAX_PVT.tax_det_rec_type;
272 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
273 l_tax_call_success_flag varchar2(1) := 'Y';
274 x_msg_index_out NUMBER;
275 BEGIN
276
277 ------------------------------------------------------------
278 -- Start processing
279 ------------------------------------------------------------
280
281 x_return_status := OKL_API.G_RET_STS_SUCCESS;
282
283 l_return_status := OKL_API.START_ACTIVITY(
284 p_api_name => l_api_name,
285 p_pkg_name => g_pkg_name,
286 p_init_msg_list => p_init_msg_list,
287 l_api_version => l_api_version,
288 p_api_version => p_api_version,
289 p_api_type => '_PVT',
290 x_return_status => l_return_status);
291
292 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
294 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
295 RAISE OKL_API.G_EXCEPTION_ERROR;
296 END IF;
297 /*rkuttiya 02-Feb-2007
298 Start Changes*/
299
300 --start:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
301 OPEN c_account_derivation;
302 FETCH c_account_derivation INTO
303 l_account_derivation,
304 l_PAY_DIST_SET_ID;
305 CLOSE c_account_derivation;
306
307 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Account derivation: ' || l_account_derivation || '. Distribution set id: ' || l_PAY_DIST_SET_ID);
308 IF l_account_derivation = 'AMB' THEN
309 IF l_PAY_DIST_SET_ID IS NULL THEN
310 -- log error message
311 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Distribution set id is missing, please setup accordingly.');
312 RAISE G_EXCEPTION_HALT_VALIDATION;
313 END IF;
314 END IF;
315 --end:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
316
317 -----------------------------------------------------------------------------
318 -- Pick up Invoice Headers from the External table
319 -----------------------------------------------------------------------------
320 FOR r_invoice_hdr in c_invoice_hdr LOOP
321
322 SAVEPOINT C_INVOICE_POINT;
323
324 --Get the Supplier Tax Invoice Number
325 OPEN c_cnsld_hdr(r_invoice_hdr.cnsld_ap_inv_id);
326 FETCH c_cnsld_hdr INTO l_self_bill_invnum;
327 CLOSE c_cnsld_hdr;
328
329
330 /*OPEN v_id_csr( r_invoice_hdr.vendor_site_id );
331 FETCH v_id_csr INTO l_vendor_id;
332 CLOSE v_id_csr;*/
333
334
335 BEGIN
336 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '==================================================================');
337 -- FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Contract: '||l_contract_number|| 'Vendor Id: ' || r_invoice_hdr.vendor_id); -- removed by 12/04/2007 cklee
338 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Vendor Id: ' || r_invoice_hdr.vendor_id);
339 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++ Invoice #: '||r_invoice_hdr.INVOICE_NUM||' Vendor Invoice Number: '||r_invoice_hdr.VENDOR_INVOICE_NUMBER);
340 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++ Invoice Date: '||r_invoice_hdr.INVOICE_DATE||' Invoice Amount: '||r_invoice_hdr.INVOICE_AMOUNT);
341 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '==================================================================');
342
343 INSERT INTO AP_INVOICES_INTERFACE(
344 Invoice_type_lookup_code
345 ,accts_pay_code_combination_id
346 ,attribute1
347 ,attribute10
348 ,attribute11
349 ,attribute12
350 ,attribute13
351 ,attribute14
352 ,attribute15
353 ,attribute2
354 ,attribute3
355 ,attribute4
356 ,attribute5
357 ,attribute6
358 ,attribute7
359 ,attribute8
360 ,attribute9
361 ,attribute_category
362 ,created_by
363 ,creation_date
364 ,description
365 ,doc_category_code
366 ,gl_date
367 ,invoice_amount
368 ,invoice_currency_code
369 ,exchange_rate
370 ,exchange_rate_type
371 ,exchange_date
372 ,invoice_date
373 ,invoice_id
374 ,invoice_num
375 ,voucher_num
376 ,last_updated_by
377 ,last_update_date
378 ,last_update_login
379 ,org_id
380 ,payment_method_lookup_code
381 --start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
382 ,payment_method_code
383 --end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
384 ,request_id
385 ,source
386 ,terms_id
387 ,vendor_id
388 ,vendor_site_id
389 ,workflow_flag
390 ,PAY_GROUP_LOOKUP_CODE
391 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
392 ,legal_entity_id
393 ,application_id
394 ,product_table
395 ,reference_key1
396 ,supplier_tax_invoice_number
397 )
398 values(
399 r_invoice_hdr.invoice_type
400 ,r_invoice_hdr.accts_pay_cc_id
401 ,r_invoice_hdr.attribute1
402 ,r_invoice_hdr.attribute10
403 ,r_invoice_hdr.attribute11
404 ,r_invoice_hdr.attribute12
405 ,r_invoice_hdr.attribute13
406 ,r_invoice_hdr.attribute14
407 ,r_invoice_hdr.attribute15
408 ,r_invoice_hdr.attribute2
409 ,r_invoice_hdr.attribute3
410 ,r_invoice_hdr.attribute4
411 ,r_invoice_hdr.attribute5
412 ,r_invoice_hdr.attribute6
413 ,r_invoice_hdr.attribute7
414 ,r_invoice_hdr.attribute8
415 ,r_invoice_hdr.attribute9
416 ,r_invoice_hdr.attribute_category
417 ,fnd_global.user_id
418 ,sysdate
419 ,null
420 ,r_invoice_hdr.doc_category_code
421 ,r_invoice_hdr.gl_date
422 ,r_invoice_hdr.invoice_amount
423 ,r_invoice_hdr.invoice_currency_code
424 ,r_invoice_hdr.CURRENCY_CONVERSION_RATE
425 ,r_invoice_hdr.CURRENCY_CONVERSION_TYPE
426 ,r_invoice_hdr.CURRENCY_CONVERSION_DATE
427 ,r_invoice_hdr.invoice_date
428 ,r_invoice_hdr.invoice_id
429 ,r_invoice_hdr.vendor_invoice_number
430 ,r_invoice_hdr.invoice_num
431 ,fnd_global.user_id
432 ,sysdate
433 ,fnd_global.login_id
434 ,r_invoice_hdr.org_id
435 ,r_invoice_hdr.payment_method
436 --start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
437 ,r_invoice_hdr.payment_method
438 --end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
439 ,fnd_global.conc_request_id
440 ,'OKL'
441 ,r_invoice_hdr.terms_id
442 ,r_invoice_hdr.vendor_id
443 ,r_invoice_hdr.vendor_site_id
444 ,r_invoice_hdr.workflow_flag
445 ,r_invoice_hdr.pay_group_lookup_code
446 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
447 ,r_invoice_hdr.legal_entity_id
448 ,fnd_global.prog_appl_id
449 ,'OKL_CNSLD_AP_INVS_ALL'
450 ,r_invoice_hdr.cnsld_ap_inv_id
451 ,l_self_bill_invnum
452 );
453
454 -----------------------------------------------------------------------------
455 -- Pick up Invoice Lines from the External Lines table
456 -----------------------------------------------------------------------------
457
458 FOR r_invoice_lines IN c_invoice_lines(r_invoice_hdr.id) LOOP
459
460 --Get the value of the taxable yn column from the lines transaction table
461 OPEN c_taxable_yn(r_invoice_lines.tpl_id);
462 FETCH c_taxable_yn INTO l_taxable_yn;
463 CLOSE c_taxable_yn;
464
465 l_contract_number := null;
466 -- Get the Contract Number
467 OPEN c_num_csr(r_invoice_lines.id);
468 FETCH c_num_csr INTO l_contract_number;
469 CLOSE c_num_csr;
470
471 --Set the value of the amount includes tax flag based on the taxable yn flag
472 IF l_taxable_yn = 'N' THEN
473 l_amount_includes_tax_flag := 'Y';
474 ELSE
475 l_amount_includes_tax_flag := 'N';
476 END IF;
477
478 l_top_kle_id := null;
479 l_khr_id := null;
480 l_install_location_id := null;
481 l_location_id := null;
482 l_install_location_type_code := null; --cklee 7015970
483 l_customer_id := null;
484 l_sts_code := null; -- 07-May-2008 cklee -- Fixed bug:7015970
485 l_ship_to := null;
486 l_inventory_item_id := null;
487
488 OPEN c_top_line(r_invoice_lines.tpl_id);
489 FETCH c_top_line INTO l_top_kle_id, l_khr_id;--, l_try_name; cklee 06/20/07
490 CLOSE c_top_line;
491
492 --start:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
493 --| 2. Fixed try_id, kle_id issues |
494 OPEN c_try_name(r_invoice_lines.tpl_id);
495 FETCH c_try_name INTO l_try_name, l_sty_id;
496 CLOSE c_try_name;
497
498 OPEN get_top_line_name(l_top_kle_id);
499 FETCH get_top_line_name INTO l_asset_number;
500 CLOSE get_top_line_name;
501
502 OPEN c_stream_type_purpose(l_sty_id);
503 FETCH c_stream_type_purpose INTO l_stream_type_purpose;
504 CLOSE c_stream_type_purpose;
505
506 --end:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
507 --| 2. Fixed try_id, kle_id issues |
508
509 OPEN Ship_to_csr(l_top_kle_id);
510 FETCH Ship_to_csr INTO l_install_location_id,
511 l_location_id,
512 l_install_location_type_code; -- cklee
513 CLOSE Ship_to_csr;
514
515 --start:| 06-Jul-2007 cklee -- Fixed ship to issue |
516 IF l_install_location_id is null and l_location_id is null THEN
517 OPEN Ship_to_csr_before_booked(l_top_kle_id);
518 FETCH Ship_to_csr_before_booked INTO l_install_location_id, l_location_id;
519 CLOSE Ship_to_csr_before_booked;
520
521 -- start: 07-May-2008 cklee -- Fixed bug:7015970
522 l_ship_to := l_location_id;
523
524 ELSE -- other than before booked case
525
526 --Following Logic Applicable only for after Book:
527 --If install_location_type_code = 'HZ_LOCATIONS'
528 --then take the install_location_id from Ship_to_csr
529 --Else If install_location_type_code = 'HZ_PARTY_SITES'
530 --then execute location_csr by passing install_location_id
531 --as p_party_site_id parameter and take location_id from
532 -- location_csr
533 IF l_install_location_type_code = 'HZ_LOCATIONS' THEN
534 l_ship_to := l_install_location_id;
535 ELSIF l_install_location_type_code = 'HZ_PARTY_SITES' THEN
536 OPEN location_csr(l_install_location_id);
537 FETCH location_csr INTO l_ship_to;
538 CLOSE location_csr;
539 ELSE
540 -- error log
541 l_ship_to := null;
542 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'install_location_type_code is other than HZ_LOCATIONS or HZ_PARTY_SITES');
543 END IF;
544 -- end: 07-May-2008 cklee -- Fixed bug:7015970
545 END IF;
546 --end:| 06-Jul-2007 cklee -- Fixed ship to issue |
547
548
549 OPEN get_khr_id_csr(l_khr_id);
550 FETCH get_khr_id_csr INTO l_customer_id,
551 l_sts_code; --07-May-2008 cklee -- Fixed bug:7015970
552 CLOSE get_khr_id_csr;
553
554
555
556 -- Note: okl_txl_ap_inv_lns_all_b.kle_id may be null, so the l_ship_to and l_inventory_item_id
557 -- may also null
558
559 /*--07-May-2008 cklee -- Fixed bug:7015970
560 OPEN Ship_to_csr2( l_customer_id, l_install_location_id, l_location_id);
561 FETCH Ship_to_csr2 INTO l_ship_to;
562 CLOSE Ship_to_csr2;
563 --07-May-2008 cklee -- Fixed bug:7015970*/
564
565 OPEN get_inv_item_id(l_top_kle_id);
566 FETCH get_inv_item_id INTO l_inventory_item_id;
567 CLOSE get_inv_item_id;
568
569 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Contract id: ' || l_khr_id || ' Line id: ' || l_top_kle_id);
570 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Install location id: ' || l_install_location_id || ' Location id: ' || l_location_id || ' Ship to id: ' || l_ship_to);
571 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Inventory item id: ' || l_inventory_item_id);
572
573 --Call to the Tax API to get the tax determinants.
574 -- Trx Business Category, Product Fiscal Classification, Product Type, Ship To Location Id
575 -- This code has been commented since this API is not available yet. This will be uncommented once eb Tax impacts are coded.
576 -- Please remove these comments then.
577 l_tax_call_success_flag := 'Y';
578 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
579
580 OKL_PROCESS_SALES_TAX_PVT.get_tax_determinants(p_api_version => p_api_version
581 ,p_init_msg_list => p_init_msg_list
582 ,x_return_status => x_return_status
583 ,x_msg_count => x_msg_count
584 ,x_msg_data => x_msg_data
585 ,p_source_trx_id => r_invoice_lines.tpl_id
586 ,p_source_trx_name => l_try_name
587 ,p_source_table => 'OKL_TXL_AP_INV_LNS_B'
588 ,x_tax_det_rec => lx_tax_det_rec);
589
590
591 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
592 l_tax_call_success_flag := 'N';
593 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Tax call failed for invoice line id: ' || r_invoice_lines.invoice_line_id || ' of invoice id: ' || r_invoice_hdr.invoice_id);
594 --start| 20-Sep-2007 cklee -- Fixed error message display issue for tax call |
595 FOR j in 1..x_msg_count
596 LOOP
597 FND_MSG_PUB.GET(
598 p_msg_index => j,
599 p_encoded => FND_API.G_FALSE,
600 p_data => x_msg_data,
601 p_msg_index_out => x_msg_index_out
602 );
603
604 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Tax call failed:' || to_char(j)||': '||x_msg_data);
605 END LOOP;
606 --end| 20-Sep-2007 cklee -- Fixed error message display issue for tax call |
607
608 --deleting invoice lines
609 DELETE FROM ap_invoice_lines_interface
610 WHERE invoice_id = r_invoice_hdr.invoice_id;
611
612 --deleting invoice header
613 DELETE FROM AP_INVOICES_INTERFACE
614 WHERE invoice_id = r_invoice_hdr.invoice_id;
615
616 EXIT;
617 END IF;
618
619 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
620
621 INSERT INTO ap_invoice_lines_interface(
622 accounting_date
623 ,amount
624 ,amount_includes_tax_flag
625 ,attribute1
626 ,attribute10
627 ,attribute11
628 ,attribute12
629 ,attribute13
630 ,attribute14
631 ,attribute15
632 ,attribute2
633 ,attribute3
634 ,attribute4
635 ,attribute5
636 ,attribute6
637 ,attribute7
638 ,attribute8
639 ,attribute9
640 ,attribute_category
641 ,created_by
642 ,creation_date
643 ,dist_code_combination_id
644 ,invoice_id
645 ,invoice_line_id
646 ,last_updated_by
647 ,last_update_date
648 ,last_update_login
649 ,line_number
650 ,line_type_lookup_code
651 ,org_id
652 ,tax_code
653 ,application_id
654 ,product_table
655 ,reference_key1
656 ,description
657 ,TAX_CLASSIFICATION_CODE
658 ,TRX_BUSINESS_CATEGORY
659 ,PRODUCT_CATEGORY
660 ,PRODUCT_TYPE
661 ,PRIMARY_INTENDED_USE
662 ,USER_DEFINED_FISC_CLASS
663 ,ASSESSABLE_VALUE
664 ,SHIP_TO_LOCATION_ID
665 ,INVENTORY_ITEM_ID
666 ,DISTRIBUTION_SET_ID--:| 16-Oct-2007 cklee -- Fixed bug:6502786
667 )
668 values(
669 r_invoice_lines.accounting_date
670 ,r_invoice_lines.amount
671 ,l_amount_includes_tax_flag
672 ,r_invoice_lines.attribute1
673 ,r_invoice_lines.attribute10
674 ,r_invoice_lines.attribute11
675 ,r_invoice_lines.attribute12
676 ,r_invoice_lines.attribute13
677 ,r_invoice_lines.attribute14
678 ,r_invoice_lines.attribute15
679 ,r_invoice_lines.attribute2
680 ,r_invoice_lines.attribute3
681 ,r_invoice_lines.attribute4
682 ,r_invoice_lines.attribute5
683 ,r_invoice_lines.attribute6
684 ,r_invoice_lines.attribute7
685 ,r_invoice_lines.attribute8
686 ,r_invoice_lines.attribute9
687 ,r_invoice_lines.attribute_category
688 ,fnd_global.user_id
689 ,sysdate
690 --start:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
691 -- ,NVL(r_invoice_lines.dist_code_combination_id, -1) --change for SLA impact
692 ,r_invoice_lines.dist_code_combination_id
693 --end:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
694 ,r_invoice_hdr.invoice_id
695 ,r_invoice_lines.invoice_line_id
696 ,fnd_global.user_id
697 ,sysdate
698 ,fnd_global.login_id
699 ,r_invoice_lines.line_number
700 ,r_invoice_lines.line_type
701 ,r_invoice_lines.org_id
702 ,r_invoice_lines.tax_code
703 ,fnd_global.prog_appl_id
704 ,'OKL_TXL_AP_INV_LNS_ALL_B'
705 ,r_invoice_lines.tpl_id
706 ,trim(substr(trim(substr(l_contract_number,1,100)) || '/' || trim(substr(l_asset_number,1,100)) || '/' || trim(substr(l_stream_type_purpose,1,38)), 1,240))
707 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
708
709 ,lx_tax_det_rec.x_tax_code
710 ,lx_tax_det_rec.x_trx_business_category
711 ,lx_tax_det_rec.x_product_category
712 ,lx_tax_det_rec.x_product_type
713 ,lx_tax_det_rec.x_line_intended_use
714 ,lx_tax_det_rec.x_user_defined_fisc_class
715 ,lx_tax_det_rec.x_assessable_value
716 /*
717 ,NULL
718 ,NULL
719 ,NULL
720 ,NULL
721 ,NULL
722 ,NULL
723 ,NULL
724 */
725 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
726 ,l_ship_to
727 ,l_inventory_item_id
728 ,l_PAY_DIST_SET_ID --:| 16-Oct-2007 cklee -- Fixed bug:6502786
729 );
730 -- added 12/04/2007 cklee
731 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Contract: '||l_contract_number|| '/Asset or Fee: ' ||
732 l_asset_number || '/Stream Type Purpose: ' || l_stream_type_purpose);
733
734 END LOOP;
735
736 IF (l_tax_call_success_flag = 'Y') THEN
737 UPDATE okl_ext_pay_invs_b
738 SET trx_status_code = 'PROCESSED'
739 WHERE CURRENT OF c_invoice_hdr;
740
741 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
742
743 UPDATE ap_invoices_interface
744 SET TAXATION_COUNTRY = lx_tax_det_rec.X_DEFAULT_TAXATION_COUNTRY
745 WHERE invoice_id = r_invoice_hdr.invoice_id;
746
747 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
748 END IF;
749
750 EXCEPTION
751
752 WHEN OTHERS THEN
753 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
754 ROLLBACK TO C_INVOICE_POINT;
755 END;
756 END LOOP;
757
758 EXCEPTION
759
760 ------------------------------------------------------------
761 -- Exception handling
762 ------------------------------------------------------------
763 WHEN OKL_API.G_EXCEPTION_ERROR THEN
764 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
765 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
766 p_api_name => l_api_name,
767 p_pkg_name => G_PKG_NAME,
768 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
769 x_msg_count => x_msg_count,
770 x_msg_data => x_msg_data,
771 p_api_type => '_PVT');
772
773 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
774 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
775 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
776 p_api_name => l_api_name,
777 p_pkg_name => G_PKG_NAME,
778 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
779 x_msg_count => x_msg_count,
780 x_msg_data => x_msg_data,
781 p_api_type => '_PVT');
782
783 WHEN OTHERS THEN
784 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
785 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
786 p_api_name => l_api_name,
787 p_pkg_name => G_PKG_NAME,
788 p_exc_name => 'OTHERS',
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data,
791 p_api_type => '_PVT');
792 END transfer;
793
794 END OKL_PAY_INVOICES_TRANS_PVT;