[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.28 2011/10/21 21:10:27 sechawla ship $ */
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 c.OBJECT1_ID2 --21-Oct-2011 sechawla Bug 12888543 : added to get the inventory org id
253 FROM okc_k_lines_b a,
254 okc_line_styles_b b,
255 okc_k_items c
256 WHERE a.cle_id = p_cle_id
257 AND a.lse_id = b.id
258 AND b.lty_code = 'ITEM'
259 AND a.id = c.cle_id
260 UNION
261 SELECT c.object1_id1,
262 c.OBJECT1_ID2 --21-Oct-2011 sechawla Bug 12888543 : added to get the inventory org id
263 FROM okc_k_lines_v a,
264 okc_line_styles_v b,
265 okc_k_items c
266 WHERE a.id = p_cle_id
267 AND a.lse_id = b.id
268 AND b.lty_code = 'SOLD_SERVICE'
269 AND c.cle_id = a.id;
270
271 l_inventory_item_id NUMBER;
272 l_inventory_org_id NUMBER; --21-Oct-2011 sechawla Bug 12888543
273 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
274 lx_tax_det_rec OKL_PROCESS_SALES_TAX_PVT.tax_det_rec_type;
275 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
276 l_tax_call_success_flag varchar2(1) := 'Y';
277 x_msg_index_out NUMBER;
278 BEGIN
279
280 ------------------------------------------------------------
281 -- Start processing
282 ------------------------------------------------------------
283
284 x_return_status := OKL_API.G_RET_STS_SUCCESS;
285
286 l_return_status := OKL_API.START_ACTIVITY(
287 p_api_name => l_api_name,
288 p_pkg_name => g_pkg_name,
289 p_init_msg_list => p_init_msg_list,
290 l_api_version => l_api_version,
291 p_api_version => p_api_version,
292 p_api_type => '_PVT',
293 x_return_status => l_return_status);
294
295 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
296 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
297 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
298 RAISE OKL_API.G_EXCEPTION_ERROR;
299 END IF;
300 /*rkuttiya 02-Feb-2007
301 Start Changes*/
302
303 --start:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
304 OPEN c_account_derivation;
305 FETCH c_account_derivation INTO
306 l_account_derivation,
307 l_PAY_DIST_SET_ID;
308 CLOSE c_account_derivation;
309
310 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Account derivation: ' || l_account_derivation || '. Distribution set id: ' || l_PAY_DIST_SET_ID);
311 IF l_account_derivation = 'AMB' THEN
312 IF l_PAY_DIST_SET_ID IS NULL THEN
313 -- log error message
314 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Distribution set id is missing, please setup accordingly.');
315 RAISE G_EXCEPTION_HALT_VALIDATION;
316 END IF;
317 END IF;
318 --end:| 16-Oct-2007 cklee -- Fixed bug:6502786 |
319
320 -----------------------------------------------------------------------------
321 -- Pick up Invoice Headers from the External table
322 -----------------------------------------------------------------------------
323 FOR r_invoice_hdr in c_invoice_hdr LOOP
324
325 SAVEPOINT C_INVOICE_POINT;
326
327 --Get the Supplier Tax Invoice Number
328 OPEN c_cnsld_hdr(r_invoice_hdr.cnsld_ap_inv_id);
329 FETCH c_cnsld_hdr INTO l_self_bill_invnum;
330 CLOSE c_cnsld_hdr;
331
332
333 /*OPEN v_id_csr( r_invoice_hdr.vendor_site_id );
334 FETCH v_id_csr INTO l_vendor_id;
335 CLOSE v_id_csr;*/
336
337
338 BEGIN
339 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '==================================================================');
340 -- 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
341 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Vendor Id: ' || r_invoice_hdr.vendor_id);
342 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++ Invoice #: '||r_invoice_hdr.INVOICE_NUM||' Vendor Invoice Number: '||r_invoice_hdr.VENDOR_INVOICE_NUMBER);
343 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '++++++++++ Invoice Date: '||r_invoice_hdr.INVOICE_DATE||' Invoice Amount: '||r_invoice_hdr.INVOICE_AMOUNT);
344 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '==================================================================');
345
346 INSERT INTO AP_INVOICES_INTERFACE(
347 Invoice_type_lookup_code
348 ,accts_pay_code_combination_id
349 ,attribute1
350 ,attribute10
351 ,attribute11
352 ,attribute12
353 ,attribute13
354 ,attribute14
355 ,attribute15
356 ,attribute2
357 ,attribute3
358 ,attribute4
359 ,attribute5
360 ,attribute6
361 ,attribute7
362 ,attribute8
363 ,attribute9
364 ,attribute_category
365 ,created_by
366 ,creation_date
367 ,description
368 ,doc_category_code
369 ,gl_date
370 ,invoice_amount
371 ,invoice_currency_code
372 ,exchange_rate
373 ,exchange_rate_type
374 ,exchange_date
375 ,invoice_date
376 ,invoice_id
377 ,invoice_num
378 ,voucher_num
379 ,last_updated_by
380 ,last_update_date
381 ,last_update_login
382 ,org_id
383 ,payment_method_lookup_code
384 --start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
385 ,payment_method_code
386 --end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
387 ,request_id
388 ,source
389 ,terms_id
390 ,vendor_id
391 ,vendor_site_id
392 ,workflow_flag
393 ,PAY_GROUP_LOOKUP_CODE
394 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
395 ,legal_entity_id
396 ,application_id
397 ,product_table
398 ,reference_key1
399 ,supplier_tax_invoice_number
400 ,CALC_TAX_DURING_IMPORT_FLAG
401 --Bug# 11705655
402 ,ADD_TAX_TO_INV_AMT_FLAG
403 )
404 values(
405 r_invoice_hdr.invoice_type
406 ,r_invoice_hdr.accts_pay_cc_id
407 ,r_invoice_hdr.attribute1
408 ,r_invoice_hdr.attribute10
409 ,r_invoice_hdr.attribute11
410 ,r_invoice_hdr.attribute12
411 ,r_invoice_hdr.attribute13
412 ,r_invoice_hdr.attribute14
413 ,r_invoice_hdr.attribute15
414 ,r_invoice_hdr.attribute2
415 ,r_invoice_hdr.attribute3
416 ,r_invoice_hdr.attribute4
417 ,r_invoice_hdr.attribute5
418 ,r_invoice_hdr.attribute6
419 ,r_invoice_hdr.attribute7
420 ,r_invoice_hdr.attribute8
421 ,r_invoice_hdr.attribute9
422 ,r_invoice_hdr.attribute_category
423 ,fnd_global.user_id
424 ,sysdate
425 ,null
426 ,r_invoice_hdr.doc_category_code
427 ,r_invoice_hdr.gl_date
428 ,r_invoice_hdr.invoice_amount
429 ,r_invoice_hdr.invoice_currency_code
430 ,r_invoice_hdr.CURRENCY_CONVERSION_RATE
431 ,r_invoice_hdr.CURRENCY_CONVERSION_TYPE
432 ,r_invoice_hdr.CURRENCY_CONVERSION_DATE
433 ,r_invoice_hdr.invoice_date
434 ,r_invoice_hdr.invoice_id
435 ,r_invoice_hdr.vendor_invoice_number
436 ,r_invoice_hdr.invoice_num
437 ,fnd_global.user_id
438 ,sysdate
439 ,fnd_global.login_id
440 ,r_invoice_hdr.org_id
441 ,r_invoice_hdr.payment_method
442 --start: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
443 ,r_invoice_hdr.payment_method
444 --end: 01-May-2007 cklee Fixed the following for R12 Disbursement project |
445 ,fnd_global.conc_request_id
446 ,'OKL'
447 ,r_invoice_hdr.terms_id
448 ,r_invoice_hdr.vendor_id
449 ,r_invoice_hdr.vendor_site_id
450 ,r_invoice_hdr.workflow_flag
451 ,r_invoice_hdr.pay_group_lookup_code
452 -- 01-NOV-2006 ANSETHUR R12B - Legal Entity
453 ,r_invoice_hdr.legal_entity_id
454 ,fnd_global.prog_appl_id
455 ,'OKL_CNSLD_AP_INVS_ALL'
456 ,r_invoice_hdr.cnsld_ap_inv_id
457 ,l_self_bill_invnum
458 ,'Y'
459 --Bug# 11705655
460 ,'Y'
461 );
462
463 -----------------------------------------------------------------------------
464 -- Pick up Invoice Lines from the External Lines table
465 -----------------------------------------------------------------------------
466
467 FOR r_invoice_lines IN c_invoice_lines(r_invoice_hdr.id) LOOP
468
469 --Get the value of the taxable yn column from the lines transaction table
470 OPEN c_taxable_yn(r_invoice_lines.tpl_id);
471 FETCH c_taxable_yn INTO l_taxable_yn;
472 CLOSE c_taxable_yn;
473
474 l_contract_number := null;
475 -- Get the Contract Number
476 OPEN c_num_csr(r_invoice_lines.id);
477 FETCH c_num_csr INTO l_contract_number;
478 CLOSE c_num_csr;
479
480 --Set the value of the amount includes tax flag based on the taxable yn flag
481 IF l_taxable_yn = 'N' THEN
482 l_amount_includes_tax_flag := 'Y';
483 ELSE
484 l_amount_includes_tax_flag := 'N';
485 END IF;
486
487 l_top_kle_id := null;
488 l_khr_id := null;
489 l_install_location_id := null;
490 l_location_id := null;
491 l_install_location_type_code := null; --cklee 7015970
492 l_customer_id := null;
493 l_sts_code := null; -- 07-May-2008 cklee -- Fixed bug:7015970
494 l_ship_to := null;
495 l_inventory_item_id := null;
496 l_inventory_org_id := null; --21-Oct-2011 sechawla Bug 12888543 : added
497
498 OPEN c_top_line(r_invoice_lines.tpl_id);
499 FETCH c_top_line INTO l_top_kle_id, l_khr_id;--, l_try_name; cklee 06/20/07
500 CLOSE c_top_line;
501
502 --start:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
503 --| 2. Fixed try_id, kle_id issues |
504 OPEN c_try_name(r_invoice_lines.tpl_id);
505 FETCH c_try_name INTO l_try_name, l_sty_id;
506 CLOSE c_try_name;
507
508 OPEN get_top_line_name(l_top_kle_id);
509 FETCH get_top_line_name INTO l_asset_number;
510 CLOSE get_top_line_name;
511
512 OPEN c_stream_type_purpose(l_sty_id);
513 FETCH c_stream_type_purpose INTO l_stream_type_purpose;
514 CLOSE c_stream_type_purpose;
515
516 --end:| 19-Jun-2007 cklee -- 1. Revert Tax call back |
517 --| 2. Fixed try_id, kle_id issues |
518
519 OPEN Ship_to_csr(l_top_kle_id);
520 FETCH Ship_to_csr INTO l_install_location_id,
521 l_location_id,
522 l_install_location_type_code; -- cklee
523 CLOSE Ship_to_csr;
524
525 --start:| 06-Jul-2007 cklee -- Fixed ship to issue |
526 IF l_install_location_id is null and l_location_id is null THEN
527 OPEN Ship_to_csr_before_booked(l_top_kle_id);
528 FETCH Ship_to_csr_before_booked INTO l_install_location_id, l_location_id;
529 CLOSE Ship_to_csr_before_booked;
530
531 -- start: 07-May-2008 cklee -- Fixed bug:7015970
532 l_ship_to := l_location_id;
533
534 ELSE -- other than before booked case
535
536 --Following Logic Applicable only for after Book:
537 --If install_location_type_code = 'HZ_LOCATIONS'
538 --then take the install_location_id from Ship_to_csr
539 --Else If install_location_type_code = 'HZ_PARTY_SITES'
540 --then execute location_csr by passing install_location_id
541 --as p_party_site_id parameter and take location_id from
542 -- location_csr
543 IF l_install_location_type_code = 'HZ_LOCATIONS' THEN
544 l_ship_to := l_install_location_id;
545 ELSIF l_install_location_type_code = 'HZ_PARTY_SITES' THEN
546 OPEN location_csr(l_install_location_id);
547 FETCH location_csr INTO l_ship_to;
548 CLOSE location_csr;
549 ELSE
550 -- error log
551 l_ship_to := null;
552 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'install_location_type_code is other than HZ_LOCATIONS or HZ_PARTY_SITES');
553 END IF;
554 -- end: 07-May-2008 cklee -- Fixed bug:7015970
555 END IF;
556 --end:| 06-Jul-2007 cklee -- Fixed ship to issue |
557
558
559 OPEN get_khr_id_csr(l_khr_id);
560 FETCH get_khr_id_csr INTO l_customer_id,
561 l_sts_code; --07-May-2008 cklee -- Fixed bug:7015970
562 CLOSE get_khr_id_csr;
563
564
565
566 -- Note: okl_txl_ap_inv_lns_all_b.kle_id may be null, so the l_ship_to and l_inventory_item_id
567 -- may also null
568
569 /*--07-May-2008 cklee -- Fixed bug:7015970
570 OPEN Ship_to_csr2( l_customer_id, l_install_location_id, l_location_id);
571 FETCH Ship_to_csr2 INTO l_ship_to;
572 CLOSE Ship_to_csr2;
573 --07-May-2008 cklee -- Fixed bug:7015970*/
574
575 OPEN get_inv_item_id(l_top_kle_id);
576 FETCH get_inv_item_id INTO l_inventory_item_id, l_inventory_org_id; --21-Oct-2011 sechawla Bug 12888543 : added inv org id
577 CLOSE get_inv_item_id;
578
579 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Contract id: ' || l_khr_id || ' Line id: ' || l_top_kle_id);
580 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);
581 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Inventory item id: ' || l_inventory_item_id);
582 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '--Inventory org id: ' || l_inventory_org_id);
583
584 --Call to the Tax API to get the tax determinants.
585 -- Trx Business Category, Product Fiscal Classification, Product Type, Ship To Location Id
586 -- This code has been commented since this API is not available yet. This will be uncommented once eb Tax impacts are coded.
587 -- Please remove these comments then.
588 l_tax_call_success_flag := 'Y';
589 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
590
591 OKL_PROCESS_SALES_TAX_PVT.get_tax_determinants(p_api_version => p_api_version
592 ,p_init_msg_list => p_init_msg_list
593 ,x_return_status => x_return_status
594 ,x_msg_count => x_msg_count
595 ,x_msg_data => x_msg_data
596 ,p_source_trx_id => r_invoice_lines.tpl_id
597 ,p_source_trx_name => l_try_name
598 ,p_source_table => 'OKL_TXL_AP_INV_LNS_B'
599 ,x_tax_det_rec => lx_tax_det_rec);
600
601
602 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
603 l_tax_call_success_flag := 'N';
604 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);
605 --start| 20-Sep-2007 cklee -- Fixed error message display issue for tax call |
606 FOR j in 1..x_msg_count
607 LOOP
608 FND_MSG_PUB.GET(
609 p_msg_index => j,
610 p_encoded => FND_API.G_FALSE,
611 p_data => x_msg_data,
612 p_msg_index_out => x_msg_index_out
613 );
614
615 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Tax call failed:' || to_char(j)||': '||x_msg_data);
616 END LOOP;
617 --end| 20-Sep-2007 cklee -- Fixed error message display issue for tax call |
618
619 --deleting invoice lines
620 DELETE FROM ap_invoice_lines_interface
621 WHERE invoice_id = r_invoice_hdr.invoice_id;
622
623 --deleting invoice header
624 DELETE FROM AP_INVOICES_INTERFACE
625 WHERE invoice_id = r_invoice_hdr.invoice_id;
626
627 EXIT;
628 END IF;
629
630 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
631
632 INSERT INTO ap_invoice_lines_interface(
633 accounting_date
634 ,amount
635 ,amount_includes_tax_flag
636 ,attribute1
637 ,attribute10
638 ,attribute11
639 ,attribute12
640 ,attribute13
641 ,attribute14
642 ,attribute15
643 ,attribute2
644 ,attribute3
645 ,attribute4
646 ,attribute5
647 ,attribute6
648 ,attribute7
649 ,attribute8
650 ,attribute9
651 ,attribute_category
652 ,created_by
653 ,creation_date
654 ,dist_code_combination_id
655 ,invoice_id
656 ,invoice_line_id
657 ,last_updated_by
658 ,last_update_date
659 ,last_update_login
660 ,line_number
661 ,line_type_lookup_code
662 ,org_id
663 ,tax_code
664 ,application_id
665 ,product_table
666 ,reference_key1
667 ,reference_key5 --21-Oct-2011 sechawla Bug 12888543 : added to pass inventory org id
668 ,description
669 ,TAX_CLASSIFICATION_CODE
670 ,TRX_BUSINESS_CATEGORY
671 ,PRODUCT_CATEGORY
672 ,PRODUCT_TYPE
673 ,PRIMARY_INTENDED_USE
674 ,USER_DEFINED_FISC_CLASS
675 ,ASSESSABLE_VALUE
676 ,SHIP_TO_LOCATION_ID
677 ,INVENTORY_ITEM_ID
678 ,DISTRIBUTION_SET_ID--:| 16-Oct-2007 cklee -- Fixed bug:6502786
679 )
680 values(
681 r_invoice_lines.accounting_date
682 ,r_invoice_lines.amount
683 ,l_amount_includes_tax_flag
684 ,r_invoice_lines.attribute1
685 ,r_invoice_lines.attribute10
686 ,r_invoice_lines.attribute11
687 ,r_invoice_lines.attribute12
688 ,r_invoice_lines.attribute13
689 ,r_invoice_lines.attribute14
690 ,r_invoice_lines.attribute15
691 ,r_invoice_lines.attribute2
692 ,r_invoice_lines.attribute3
693 ,r_invoice_lines.attribute4
694 ,r_invoice_lines.attribute5
695 ,r_invoice_lines.attribute6
696 ,r_invoice_lines.attribute7
697 ,r_invoice_lines.attribute8
698 ,r_invoice_lines.attribute9
699 ,r_invoice_lines.attribute_category
700 ,fnd_global.user_id
701 ,sysdate
702 --start:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
703 -- ,NVL(r_invoice_lines.dist_code_combination_id, -1) --change for SLA impact
704 ,r_invoice_lines.dist_code_combination_id
705 --end:| 15-Oct-2007 cklee -- Fixed bug:6502786 |
706 ,r_invoice_hdr.invoice_id
707 ,r_invoice_lines.invoice_line_id
708 ,fnd_global.user_id
709 ,sysdate
710 ,fnd_global.login_id
711 ,r_invoice_lines.line_number
712 ,r_invoice_lines.line_type
713 ,r_invoice_lines.org_id
714 ,r_invoice_lines.tax_code
715 ,fnd_global.prog_appl_id
716 ,'OKL_TXL_AP_INV_LNS_ALL_B'
717 ,r_invoice_lines.tpl_id
718 ,l_inventory_org_id --21-Oct-2011 sechawla Bug 12888543 : added inv org id
719 ,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))
720 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
721
722 ,lx_tax_det_rec.x_tax_code
723 ,lx_tax_det_rec.x_trx_business_category
724 ,lx_tax_det_rec.x_product_category
725 ,lx_tax_det_rec.x_product_type
726 ,lx_tax_det_rec.x_line_intended_use
727 ,lx_tax_det_rec.x_user_defined_fisc_class
728 ,lx_tax_det_rec.x_assessable_value
729 /*
730 ,NULL
731 ,NULL
732 ,NULL
733 ,NULL
734 ,NULL
735 ,NULL
736 ,NULL
737 */
738 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
739 ,l_ship_to
740 ,l_inventory_item_id
741 ,l_PAY_DIST_SET_ID --:| 16-Oct-2007 cklee -- Fixed bug:6502786
742 );
743 -- added 12/04/2007 cklee
744 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing Contract: '||l_contract_number|| '/Asset or Fee: ' ||
745 l_asset_number || '/Stream Type Purpose: ' || l_stream_type_purpose);
746
747 END LOOP;
748
749 IF (l_tax_call_success_flag = 'Y') THEN
750 UPDATE okl_ext_pay_invs_b
751 SET trx_status_code = 'PROCESSED'
752 WHERE CURRENT OF c_invoice_hdr;
753
754 --start:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
755
756 UPDATE ap_invoices_interface
757 SET TAXATION_COUNTRY = lx_tax_det_rec.X_DEFAULT_TAXATION_COUNTRY
758 WHERE invoice_id = r_invoice_hdr.invoice_id;
759
760 --end:| 03-May-2007 cklee -- Commented out OKL_PROCESS_SALES_TAX_PVT related code. |
761 END IF;
762
763 EXCEPTION
764
765 WHEN OTHERS THEN
766 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
767 ROLLBACK TO C_INVOICE_POINT;
768 END;
769 END LOOP;
770
771 EXCEPTION
772
773 ------------------------------------------------------------
774 -- Exception handling
775 ------------------------------------------------------------
776 WHEN OKL_API.G_EXCEPTION_ERROR THEN
777 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
778 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
779 p_api_name => l_api_name,
780 p_pkg_name => G_PKG_NAME,
781 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data,
784 p_api_type => '_PVT');
785
786 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
787 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
788 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
789 p_api_name => l_api_name,
790 p_pkg_name => G_PKG_NAME,
791 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
792 x_msg_count => x_msg_count,
793 x_msg_data => x_msg_data,
794 p_api_type => '_PVT');
795
796 WHEN OTHERS THEN
797 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=> ERROR: '||SQLERRM);
798 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
799 p_api_name => l_api_name,
800 p_pkg_name => G_PKG_NAME,
801 p_exc_name => 'OTHERS',
802 x_msg_count => x_msg_count,
803 x_msg_data => x_msg_data,
804 p_api_type => '_PVT');
805 END transfer;
806
807 END OKL_PAY_INVOICES_TRANS_PVT;