1 PACKAGE BODY OKL_PROCESS_TAX AS
2 /* $Header: OKLRTAXB.pls 120.12 2006/08/11 10:44:39 gboomina noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- SUBTYPE tax_rec_type IS OKL_TAX_PVT.okl_tax_lines_v_rec_type;
9
10
11 /*=======================================================================+
12 | Package Global Constants
13 +=======================================================================*/
14
15 /*========================================================================
16 | PRIVATE FUNCTION Check_Tax_Exempt
17 |
18 | DESCRIPTION
19 | This function checks whether the asset line is exempt from tax
20 |
21 | CALLED FROM PROCEDURES/FUNCTIONS
22 | Create_Tax_Schedule
23 |
24 | CALLS PROCEDURES/FUNCTIONS
25 |
26 |
27 | PARAMETERS
28 | p_kle_id IN Contract Line Id
29 |
30 | KNOWN ISSUES
31 |
32 | NOTES
33 |
34 |
35 | MODIFICATION HISTORY
36 | Date Author Description of Changes
37 | 24-MAY-2004 RKUTTIYA Created
38 |
39 *=======================================================================*/
40 /*
41 FUNCTION Tax_Exempt(p_kle_id IN NUMBER)
42 RETURN BOOLEAN IS */
43 /* ------------------------------------------*/
44 -- Cursor Declarations
45 /*--------------------------------------------*/
46 /* CURSOR c_exempt_status(p_kle_id IN NUMBER) IS
47 SELECT rul.rule_information1
48 FROM okc_rule_groups_b rgp,
49 okc_rules_b rul
50 WHERE rgp.rgd_code = 'LAASTX'
51 AND RGP.ID = RUL.rgp_id
52 AND RUL.RULE_INFORMATION_CATEGORY = 'LAASTX'
53 AND RGP.CLE_ID = p_kle_id; */
54 /*---------------------------------------------*/
55 -- Local Variable Declarations
56 /*---------------------------------------------*/
57 /* l_tax_status VARCHAR2(1);
58 BEGIN
59 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Tax_Exempt','Begin(+)');
61 END IF;
62 --Print Input Variables
63 print_to_log('p_kle_id :'||p_kle_id);
64
65 OPEN c_exempt_status(p_kle_id);
66 FETCH c_exempt_status INTO l_tax_status;
67 CLOSE c_exempt_status;
68
69 print_to_log('Tax Status :'||l_tax_status);
70 --If Tax Exempt return TRUE Else If not Tax Exempt return FALSE
71 IF (l_tax_status = 'E') or (l_tax_status = 'Y') THEN
72 RETURN TRUE;
73 ELSE
74 RETURN FALSE;
75 END IF;
76 EXCEPTION
77 WHEN OKL_API.G_EXCEPTION_ERROR THEN
78 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
79 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Tax_Exempt',
80 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
81 END IF;
82 IF c_exempt_status%ISOPEN THEN
83 CLOSE c_exempt_status;
84 END IF;
85 RAISE;
86 WHEN OTHERS THEN
87 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
88 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Tax_Exempt ',
89 'EXCEPTION :'||sqlerrm);
90 END IF;
91 RAISE;
92 END Tax_Exempt; */
93 /*========================================================================
94 | PRIVATE PROCEDURE Get_Asset_Details
95 |
96 | DESCRIPTION
97 | This function returns the asset details like Asset Id, Asset Number
98 | ship_to_site_use_id,ship_to_location when passed the financial Asset line id
99 |
100 | CALLED FROM PROCEDURES/FUNCTIONS
101 | Create_Tax_Schedule
102 |
103 | CALLS PROCEDURES/FUNCTIONS
104 |
105 |
106 | PARAMETERS
107 | p_kle_id IN Contract Line Id
108 |
109 | KNOWN ISSUES
110 |
111 | NOTES
112 |
113 |
114 | MODIFICATION HISTORY
115 | Date Author Description of Changes
116 | 24-MAY-2004 RKUTTIYA Created
117 | 17-JAN-2005 RKUTTIYA Bug: 3977770
118 | Made changes in the procedure Get_Asset_details
119 | in cursor c_get_shiptositeid
120 | removed reference to cust_acct_site_id
121 | changed it to site_use_id
122 | 20-JAN-2005 RKUTTIYA Added FND debug messages
123 *=======================================================================*/
124 /*
125 PROCEDURE Get_Asset_Details(p_api_version IN NUMBER,
126 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 p_flag IN VARCHAR2,
131 p_cust_acct_id IN NUMBER,
132 p_kle_id IN NUMBER,
133 px_asset_id OUT NOCOPY NUMBER,
134 px_asset_number OUT NOCOPY VARCHAR2,
135 px_ship_to_siteuseid OUT NOCOPY NUMBER,
136 px_ship_to_locid OUT NOCOPY NUMBER,
137 px_postal_code OUT NOCOPY VARCHAR2)
138 AS */
139 /* ------------------------------------------*/
140 -- Cursor Declarations
141 /*--------------------------------------------*/
142 --Cursor to get the Asset Id and Asset Number
143 /*
144 CURSOR c_asset_number(p_kle_id IN NUMBER) IS
145 SELECT CTL.NAME,
146 CIM.object1_id1
147 FROM OKC_K_LINES_B CLE1,
148 OKC_K_LINEs_B CLE2,
149 OKC_LINE_STYLES_B CLS1,
150 OKC_LINE_STYLES_B CLS2,
151 OKC_K_LINES_TL CTL,
152 OKC_K_ITEMS CIM
153 WHERE CLE1.LSE_ID = CLS1.ID
154 AND CLS1.LTY_CODE = 'FREE_FORM1'
155 AND CLE1.id = CLE2.cle_id
156 AND CLE2.lse_id = CLS2.id
157 AND CLS2.lty_code = 'FIXED_ASSET'
158 AND CLE1.id = CTL.id
159 AND CTL.LANGUAGE(+) = USERENV('LANG')
160 AND CIM.CLE_ID = CLE2.id
161 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
162 AND CLE1.ID = p_kle_id;
163 --Cursor to get the install_location_id of the asset
164 CURSOR c_get_instlocid(p_kle_id IN NUMBER) IS
165 SELECT csi.install_location_id,
166 -- csi.location_id
167 csi.install_location_type_code
168 FROM csi_item_instances csi,
169 okc_k_items cim,
170 okc_k_lines_b inst,
171 okc_k_lines_b ib,
172 okc_line_styles_b lse
173 WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
174 AND cim.cle_id = ib.id
175 AND ib.cle_id = inst.id
176 AND inst.lse_id = lse.id
177 AND lse.lty_code = 'FREE_FORM2'
178 AND inst.cle_id = p_kle_id ;
179
180 --Cursor to get the corresponding hz_location id for the install location id
181 CURSOR c_get_location_id(p_party_site_id IN NUMBER) IS
182 SELECT hzp.location_id
183 FROM HZ_PARTY_SITES HZP
184 WHERE HZP.PARTY_SITE_ID = p_party_site_id;
185
186 --Cursor to get the corresponding party_site_id FOR a location id
187 CURSOR c_get_party_site_id(p_location_id IN NUMBER) IS
188 SELECT HZP.PARTY_SITE_ID
189 FROM HZ_PARTY_SITES HZP,
190 HZ_PARTY_SITE_USES HZU
191 WHERE HZP.LOCATION_ID = p_location_id
192 AND HZP.party_site_id = HZU.PARTY_SITE_ID
193 AND HZU.SITE_USE_TYPE = 'INSTALL_AT' ;
194
195
196 */
197 --Cursor to get the ship_to_site_use_id corresponding to the install_location_id of the asset
198 /*
199 CURSOR c_get_shiptositeid(p_cust_acct_id IN NUMBER, p_inst_loc_id IN NUMBER,p_loc_id IN NUMBER) IS
200 SELECT
201 --rkuttiya modified to site use id for bug:3977770
202 b.site_use_id
203 FROM hz_cust_acct_sites_all a,
204 hz_cust_site_uses_all b,
205 hz_party_sites c
206 WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
207 AND b.site_use_code = 'SHIP_TO'
208 AND a.party_site_id = c.party_site_id
209 AND a.cust_account_id = p_cust_acct_id
210 AND a.org_id = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99)
211 AND c.party_site_id = p_inst_loc_id
212 AND c.location_id = p_loc_id;
213
214 --Cursor to get the ship_to_location_id (loc ccid)
215 CURSOR c_get_shiptolocid(p_location_id IN NUMBER) IS
216 SELECT HZA.loc_id,
217 HZ.postal_code
218 FROM HZ_LOC_ASSIGNMENTS HZA,
219 HZ_LOCATIONS HZ
220 WHERE HZ.location_id = p_location_id
221 AND HZ.LOCATION_ID = HZA.LOCATION_ID
222 AND ORG_ID = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99);
223 */
224 /*---------------------------------------------*/
225 -- Local Variable Declarations
226 /*---------------------------------------------*/
227
228 /*
229 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
230 l_asset_id NUMBER;
231 l_asset_number VARCHAR2(150);
232 l_inst_loc_id NUMBER;
233 l_inst_loc_type_code VARCHAR2(30);
234 l_loc_id NUMBER;
235 l_ship_to_id NUMBER;
236 l_ship_to_locid NUMBER;
237 BEGIN
238 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
239 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Get_Asset_Details','Begin(+)');
240 END IF;
241 --Print Input Variables
242 print_to_log('Input variables to Get_Asset_details');
243 print_to_log('p_api_version :'||p_api_version);
244 print_to_log('p_init_msg_list :'||p_init_msg_list);
245 print_to_log('p_flag :'||p_flag);
246 print_to_log('p_cust_acct_id :'||p_cust_acct_id);
247 print_to_log('p_kle_id :'||p_kle_id);
248 print_to_log('px_asset_id :'||px_asset_id);
249 print_to_log('px_asset_number :'||px_asset_number);
250 print_to_log('px_ship_to_siteuseid :'||px_ship_to_siteuseid);
251 print_to_log('px_postal_code :'||px_postal_code);
252
253
254 --Get the Asset Id and the Asset Number
255 OPEN c_asset_number(p_kle_id);
256 FETCH c_asset_number INTO px_asset_number,px_asset_id;
257 CLOSE c_asset_number;
258
259 IF p_flag = 'Y' THEN
260 --get the install location id of the asset
261 OPEN c_get_instlocid(p_kle_id);
262 FETCH c_get_instlocid INTO l_inst_loc_id,l_inst_loc_type_code;
263 CLOSE c_get_instlocid;
264
265 print_to_log('l_inst_loc_id :'||l_inst_loc_id);
266 print_to_log('l_inst_loc_type_code :'||l_inst_loc_type_code);
267
268 IF l_inst_loc_id IS NULL THEN
269 -- Install Location id is required
270 OKL_API.set_message( p_app_name => 'OKL',
271 p_msg_name => G_REQUIRED_VALUE,
272 p_token1 => G_COL_NAME_TOKEN,
273 p_token1_value => 'LOCATION_ID');
274 RAISE OKL_API.G_EXCEPTION_ERROR;
275 END IF;
276
277 --Check the source of the install location id
278 IF l_inst_loc_type_code = 'HZ_PARTY_SITES' THEN
279 OPEN c_get_location_id(l_inst_loc_id);
280 FETCH c_get_location_id INTO l_loc_id;
281 CLOSE c_get_location_id;
282 ELSIF l_inst_loc_type_code = 'HZ_LOCATIONS' THEN
283 l_loc_id := l_inst_loc_id;
284 OPEN c_get_party_site_id(l_loc_id);
285 FETCH c_get_party_site_id INTO l_inst_loc_id;
286 CLOSE c_get_party_site_id;
287 END IF;
288
289 --get the ship to site use id of the asset
290 OPEN c_get_shiptositeid(p_cust_acct_id,l_inst_loc_id,l_loc_id);
291 FETCH c_get_shiptositeid INTO px_ship_to_siteuseid;
292 CLOSE c_get_shiptositeid;
293 print_to_log('px_ship_to_siteuseid :'||px_ship_to_siteuseid);
294
295 IF px_ship_to_siteuseid IS NULL THEN
296 -- Install Location id is required
297 OKL_API.set_message( p_app_name => 'OKL',
298 p_msg_name => G_REQUIRED_VALUE,
299 p_token1 => G_COL_NAME_TOKEN,
300 p_token1_value => 'SHIP_TO');
301 RAISE OKL_API.G_EXCEPTION_ERROR;
302 END IF;
303 --get the ship to location id
304 OPEN c_get_shiptolocid(l_loc_id);
305 FETCH c_get_shiptolocid INTO px_ship_to_locid,px_postal_code;
306 CLOSE c_get_shiptolocid;
307
308 print_to_log('px_ship_to_locid :'||px_ship_to_locid);
309 print_to_log('px_postal_code :'||px_postal_code);
310 ELSE
311 px_ship_to_siteuseid := NULL;
312 px_ship_to_locid := NULL;
313 px_postal_code := NULL;
314 END IF;
315 x_return_status := l_return_status;
316 EXCEPTION
317 WHEN OKL_API.G_EXCEPTION_ERROR THEN
318 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
319 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Get_Asset_Details',
320 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
321 END IF;
322 IF c_asset_number%ISOPEN THEN
323 CLOSE c_asset_number;
324 END IF;
325 IF c_get_instlocid%ISOPEN THEN
326 CLOSE c_get_instlocid;
327 END IF;
328 IF c_get_shiptositeid%ISOPEN THEN
329 CLOSE c_get_shiptositeid;
330 END IF;
331 IF c_get_shiptolocid%ISOPEN THEN
332 CLOSE c_get_shiptolocid;
333 END IF;
334 RAISE;
335 WHEN OTHERS THEN
336 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Get_Asset_Details ',
338 'EXCEPTION :'||sqlerrm);
339 END IF;
340 IF c_asset_number%ISOPEN THEN
341 CLOSE c_asset_number;
342 END IF;
343 IF c_get_instlocid%ISOPEN THEN
344 CLOSE c_get_instlocid;
345 END IF;
346 IF c_get_shiptositeid%ISOPEN THEN
347 CLOSE c_get_shiptositeid;
348 END IF;
349 IF c_get_shiptolocid%ISOPEN THEN
350 CLOSE c_get_shiptolocid;
351 END IF;
352 RAISE;
353 END Get_Asset_Details;
354
355 */
356 /*========================================================================
357 | PUBLIC PROCEDURE Create_Tax_Schedule
358 |
359 | DESCRIPTION
360 | This procedure will query all streams for a contract, pass the stream amounts to
361 | the Global Tax Engine for calculating tax for each of the amounts and create tax schedules in
362 | OKL_TAX_LINES
363 |
364 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
365 | Enter a list of all local procedures and functions which
366 | are call this package.
367 |
368 |
369 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
370 | Enter a list of all local procedures and cuntions which
371 | this package calls.
372 |
373 | PARAMETERS
374 | p_contract_id IN Contract Identifier
375 | p_trx_date IN Schedule Request Date
376 | p_date_from IN Date From
377 | p_date_to IN Date To
378 | x_return_status OUT Return Status
379 |
380 | KNOWN ISSUES
381 |
382 | NOTES
383 | Any interesting aspect of the code in the package body which needs
384 | to be stated.
385 |
386 | MODIFICATION HISTORY
387 | Date Author Description of Changes
388 | 24-MAY-2004 RKUTTIYA Created
389 |
390 *=======================================================================*/
391
392 PROCEDURE Create_Tax_Schedule( p_api_version IN NUMBER,
393 p_init_msg_list IN VARCHAR2,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397 p_tax_in_rec IN okl_tax_rec_type) IS
398
399 /*-----------------------------------------------------------------------+
400 | Cursor Declarations |
401 +-----------------------------------------------------------------------*/
402 --Cursor to get the customer_account_id for the customer
403 /*
404
405 CURSOR c_get_custacctid(p_khr_id IN NUMBER) IS
406 SELECT cust_acct_id
407 FROM OKC_K_HEADERS_B
408 WHERE ID = p_khr_id;
409
410 --Cursor to get all the stream associated with the contract
411 CURSOR c_contract_streams(p_contract_id IN NUMBER,
412 p_date_from IN DATE,
413 p_date_to IN DATE) IS
414 SELECT typ.code Stream_Type,
415 typ.billable_yn Billable,
416 strm.id stream_id,
417 strm.transaction_number,
418 strm.sty_id type_id,
419 strm.kle_id line_id,
420 strm.khr_id contract_id,
421 selm.id stream_element_id,
422 selm.amount,
423 selm.stream_element_date
424 FROM okl_strm_type_b typ,
425 okl_streams_v strm,
426 okl_strm_elements_v selm
427 WHERE strm.sty_id = typ.id
428 AND strm.id = selm.stm_id
429 AND typ.billable_yn = 'Y'
430 AND typ.taxable_default_yn = 'Y'
431 AND strm.say_code ='CURR'
432 and strm.active_yn = 'Y'
433 and strm.purpose_code is null
434 and strm.khr_id = p_contract_id
435 and selm.stream_element_date between p_date_from and p_date_to
436 AND NOT EXISTS
437 (SELECT NULL
438 FROM okl_cnsld_ar_strms_b CNSLD
439 WHERE cnsld.sel_id = selm.id
440 and cnsld.receivables_invoice_id IS NOT NULL);
441
442 --Cursor to obtain streams that have been invoiced to AR
443
444 CURSOR c_invoiced_streams( p_contract_id IN NUMBER,
445 p_date_from IN DATE,
446 p_date_to IN DATE) IS
447 SELECT typ.code Stream_Type,
448 typ.billable_yn Billable,
449 selm.id stream_element_id,
450 strm.transaction_number,
451 strm.sty_id type_id,
452 strm.kle_id line_id,
453 strm.khr_id contract_id,
454 selm.amount,
455 selm.stream_element_date,
456 rtrh.invoice_currency_code,
457 rtrl.extended_amount,
458 rtrl.tax_rate,
459 rtrl.taxable_amount,
460 ATX.tax_code,
461 RTRL.SALES_TAX_ID,
462 rtrl.customer_trx_id,
463 RTRL.tax_exemption_id,
464 RTRL.item_exception_rate_id
465 FROM okl_strm_type_b typ,
466 okl_streams_v strm,
467 okl_strm_elements_v selm,
468 OKL_CNSLD_AR_STRMS_B CNSLD,
469 RA_CUSTOMER_TRX_ALL RTRH,
470 RA_CUSTOMER_TRX_LINES_ALL RTRL,
471 AR_VAT_TAX_ALL ATX
472 WHERE strm.khr_id = p_contract_id
473 AND strm.sty_id = typ.id
474 AND strm.id = selm.stm_id
475 AND typ.billable_yn = 'Y'
476 AND typ.taxable_default_yn = 'Y'
477 AND strm.say_code ='CURR'
478 AND strm.active_yn = 'Y'
479 AND strm.purpose_code is null
480 AND selm.id = cnsld.sel_id
481 AND selm.stream_element_date between p_date_from and p_date_to
482 AND cnsld.receivables_invoice_id = rtrh.customer_trx_id
483 AND rtrh.customer_trx_id = rtrl.customer_trx_id
484 AND RTRL.ORG_ID = NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99)
485 AND RTRL.VAT_TAX_ID = ATX.VAT_TAX_ID
486 AND rtrl.line_type = 'TAX' ;
487
488
489 CURSOR c_get_billto_location ( p_bill_to_site_use_id IN NUMBER, p_cust_acct_id IN NUMBER) IS
490 SELECT loc_assign.loc_id location_id
491 FROM HZ_PARTY_SITES party_site,
492 HZ_LOC_ASSIGNMENTS loc_assign,
493 HZ_LOCATIONS loc,
494 HZ_CUST_ACCT_SITES_ALL acct_site,
495 HZ_PARTIES party,
496 HZ_CUST_ACCOUNTS cust_acct,
497 HZ_CUST_SITE_USES cust_site_uses
498 WHERE acct_site.party_site_id = party_site.party_site_id
499 AND loc.location_id = party_site.location_id
500 AND loc.location_id = loc_assign.location_id
501 AND acct_site.cust_acct_site_id = cust_site_uses.cust_acct_site_id
502 AND party.party_id = cust_acct.party_id
503 AND cust_site_uses.site_use_id = p_bill_to_site_use_id
504 AND cust_acct.cust_account_id = p_cust_acct_id
505 AND loc_assign.org_id= NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99);
506
507 -- Cursor to get the line id for the linked asset
508 CURSOR c_linked_asset_line(p_kle_id IN NUMBER) IS
509 SELECT fa.id
510 FROM okc_k_lines_b fa,
511 okc_line_styles_b stl,
512 okc_k_lines_b top_cle,
513 okc_line_styles_b top_stl,
514 okc_k_lines_b sub_cle,
515 okc_line_styles_b sub_stl,
516 okc_k_items cim
517 WHERE top_cle.lse_id = top_stl.id
518 AND top_stl.lty_code in ('SOLD_SERVICE','FEE')
519 AND top_cle.id = sub_cle.cle_id
520 AND sub_cle.lse_id = sub_stl.id
521 AND sub_stl.lty_code in ('LINK_SERV_ASSET','LINK FEE ASSET')
522 AND cim.cle_id = sub_cle.id
523 AND CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST'
524 AND CIM.OBJECT1_ID1 = FA.ID
525 AND FA.LSE_ID = STL.ID
526 AND STL.LTY_CODE = 'FREE_FORM1'
527 AND sub_cle.id = p_kle_id;
528
529 -- Cursor to get the lty_code for the give line
530 CURSOR c_lty_code(p_kle_id IN NUMBER) IS
531 SELECT B.LTY_CODE
532 FROM OKC_K_LINES_B A,
533 OKC_LINE_STYLES_B B
534 WHERE A.LSE_ID = B.ID
535 AND A.ID = p_kle_id;
536 */
537 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
538 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tax_Schedule';
539 l_api_version CONSTANT NUMBER := 1;
540 /*-----------------------------------------------------------------------+
541 | Local Variable Declarations and initializations |
542 +-----------------------------------------------------------------------*/
543 /* l_exist VARCHAR2(1);
544 lx_asset_id NUMBER;
545 lx_asset_number VARCHAR2(150);
546 l_lty_code VARCHAR2(150);
547 l_inst_loc_id NUMBER;
548 l_loc_id NUMBER;
549 l_ship_to_id NUMBER;
550 l_cust_acct_id NUMBER(15,0);
551 l_org_id NUMBER;
552 l_sob_id NUMBER :=NULL;
553 l_currency VARCHAR2(15) := NULL;
554 l_precision NUMBER(1,0) := NULL;
555 l_min_acc_unit NUMBER := NULL;
556 l_cust_site_use_id NUMBER(15,0) := NULL;
557 l_cust_account_id NUMBER := NULL;
558 lx_ship_to_siteuseid NUMBER(15,0);
559 lx_ship_to_locid NUMBER;
560 lx_postal_code VARCHAR2(60);
561 l_bill_to_postal_code VARCHAR2(60);
562 l_bill_to_locid NUMBER;
563 l_line_id NUMBER;
564 l_count NUMBER;
565 l_tax_amt NUMBER;
566 l_object_name VARCHAR2(200);
567 l_tax_tbl ARP_TAX.om_tax_out_tab_type;
568 l_bill_to_rec okx_cust_site_uses_v%ROWTYPE;
569 l_gte_tax_rec ARP_TAX.tax_info_rec_type;
570 l_okl_tax_rec tax_rec_type;
571 lx_tax_rec tax_rec_type;
572 l_newrec_count NUMBER :=0;
573 l_call_tax_api VARCHAR2(1) := 'Y';
574 l_tax_rate NUMBER;
575 l_tax_code VARCHAR2(60);
576 */
577 BEGIN
578 IF (G_DEBUG_ENABLED = 'Y') THEN
579 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
580 END IF;
581 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Create_Tax_Schedule','Begin(+)');
583 END IF;
584
585 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
586 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Input variables in Create_Tax_Schedule');
587 END IF;
588 --Print Input Variables
589 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
590 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_contract_id :'||p_tax_in_rec.contract_id);
591 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_trx_id :'||p_tax_in_rec.trx_id);
592 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_trx_date :'||p_tax_in_rec.trx_date);
593 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_line_type :'||p_tax_in_rec.line_type);
594 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_date_from :'||p_tax_in_rec.date_from);
595 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_date_to :'||p_tax_in_rec.date_to);
596 END IF;
597
598 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
599 G_PKG_NAME,
600 p_init_msg_list,
601 l_api_version,
602 p_api_version,
603 '_PVT',
604 x_return_status);
605 /*
606 --Get the Customer Account Id for the contract
607 OPEN c_get_custacctid(p_tax_in_rec.contract_id);
608 FETCH c_get_custacctid INTO l_cust_acct_id;
609 CLOSE c_get_custacctid;
610
611 print_to_log('Customer Acount id'|| l_cust_acct_id);
612
613 --Get the org id , currency, precision, minimum accounting unit
614 l_org_id := okl_am_util_pvt.get_chr_org_id (p_tax_in_rec.contract_id);
615 l_sob_id := okc_currency_api.get_ou_sob (l_org_id);
616 l_currency := okc_currency_api.get_sob_currency (l_sob_id);
617
618 okl_am_util_pvt.get_currency_info
619 (l_currency, l_precision, l_min_acc_unit);
620
621
622 --Query all streams for the contract which satisfy
623 --date range, Billable, Taxable, Active, Current, Purpose Code
624 FOR contract_streams_rec IN c_contract_streams(p_tax_in_rec.contract_id,p_tax_in_rec.date_from,p_tax_in_rec.date_to) LOOP
625 -- Initialising all the loop variable to null for every row.
626 l_cust_site_use_id := NULL;
627 l_cust_account_id := NULL;
628 lx_asset_id := NULL;
629 lx_asset_number := NULL;
630 lx_ship_to_siteuseid := NULL;
631 lx_ship_to_locid := NULL;
632 l_bill_to_locid := NULL;
633 lx_postal_code := NULL;
634 l_bill_to_postal_code := NULL;
635
636 IF contract_streams_rec.line_id IS NULL THEN
637 l_lty_code := NULL;
638 ELSE
639 OPEN c_lty_code(contract_streams_rec.line_id);
640 FETCH c_lty_code INTO l_lty_code;
641 IF c_lty_code%NOTFOUND THEN
642 -- The case of a bad data need to be handled (Bad line id in the streams record)
643 OKL_API.set_message(p_app_name => g_app_name,
644 p_msg_name => 'OKL_INVALID_DATA');
645 RAISE OKL_API.G_EXCEPTION_ERROR;
646 END IF;
647 CLOSE c_lty_code;
648 END IF;
649 print_to_log('lty_code :'||l_lty_code);
650 IF (l_lty_code = 'FREE_FORM1') OR
651 (l_lty_code = 'LINK_SERV_ASSET')OR
652 (l_lty_code = 'LINK_FEE_ASSET') THEN
653 IF l_lty_code = 'FREE_FORM1' THEN -- Financial Asset line
654 l_line_id := contract_streams_rec.line_id;
655 ELSE -- Linked Asset Line - Service or Fee
656 --get the line id of the asset linked to the service or fee
657 OPEN c_linked_asset_line(contract_streams_rec.line_id);
658 FETCH c_linked_asset_line INTO l_line_id;
659 CLOSE c_linked_asset_line;
660 END IF;
661 --set the variable to call the tax api, after checking whether the line is tax exempt or not.
662 IF Tax_Exempt(l_line_id) THEN
663 l_call_tax_api := 'N';
664 ELSE
665 l_call_tax_api := 'Y';
666 END IF;
667 print_to_log('call tax api :'||l_call_tax_api);
668
669 --If the line is tax exempt , there is no need to get asset details
670 IF NOT Tax_Exempt(l_line_id) THEN
671 -- Get the Asset Details
672 Get_Asset_Details(p_api_version => p_api_version,
673 p_init_msg_list => p_init_msg_list,
674 x_return_status => l_return_status,
675 x_msg_count => x_msg_count,
676 x_msg_data => x_msg_data,
677 p_flag => 'Y',
678 p_cust_acct_id => l_cust_acct_id,
679 p_kle_id => l_line_id,
680 px_asset_id => lx_asset_id,
681 px_asset_number => lx_asset_number,
682 px_ship_to_siteuseid => lx_ship_to_siteuseid,
683 px_ship_to_locid => lx_ship_to_locid,
684 px_postal_code => lx_postal_code);
685 print_to_log('return status from Get_Asset_Details :'|| l_return_status);
686 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
687 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
688 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
689 RAISE OKL_API.G_EXCEPTION_ERROR;
690 END IF;
691
692
693 END IF; -- Tax Exempt
694 ELSIF (l_lty_code = 'SOLD_SERVICE') OR -- Line with no assets
695 (l_lty_code = 'FEE') OR -- Line with no assets
696 (l_lty_code = 'INSURANCE') OR -- Insurance line
697 (contract_streams_rec.line_id IS NULL) THEN -- Contract level payment
698
699 -- get the contract bill to site useid, bill to location
700 l_bill_to_rec := NULL;
701 okl_am_util_pvt.get_bill_to_address (
702 p_contract_id => p_tax_in_rec.contract_id,
703 p_message_yn => FALSE,
704 x_bill_to_address_rec => l_bill_to_rec,
705 x_return_status => l_return_status);
706 print_to_log('return status from get_bill_to_address :'|| l_return_status);
707
708 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
709 l_cust_site_use_id := l_bill_to_rec.id1;
710 l_cust_account_id := l_bill_to_rec.cust_account_id;
711 l_bill_to_postal_code := l_bill_to_rec.postal_code;
712 OPEN c_get_billto_location(l_cust_site_use_id,l_cust_account_id);
713 FETCH c_get_billto_location INTO l_bill_to_locid;
714 CLOSE c_get_billto_location;
715 END IF;
716 print_to_log('customer bill to site_use_id :'|| l_cust_site_use_id);
717 print_to_log('customer bill to location_id :'|| l_bill_to_locid);
718 print_to_log('customer bill to postal_code :'|| l_bill_to_postal_code);
719
720 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS)
721 OR (l_cust_site_use_id IS NULL)
722 OR (l_cust_account_id IS NULL) THEN
723 -- l_overall_status := OKL_API.G_RET_STS_ERROR;
724 OKL_API.SET_MESSAGE (p_app_name => OKL_API.G_APP_NAME,
725 p_msg_name => 'OKL_AM_TAX_NO_BILL_TO',
726 p_token1 => 'OBJECT',
727 p_token1_value => l_object_name);
728 END IF;
729
730 END IF; --lty code check
731
732 IF l_call_tax_api = 'Y' THEN
733
734 --Reset the GLOBAL tax_info_rec rec type with empty rec type
735 ARP_TAX.tax_info_rec := l_gte_tax_rec;
736
737 -- prepare the input tax record structure
738 ARP_TAX.tax_info_rec.trx_date := p_tax_in_rec.trx_date;
739 ARP_TAX.tax_info_rec.extended_amount := contract_streams_rec.amount;
740 ARP_TAX.tax_info_rec.trx_currency_code := l_currency;
741 ARP_TAX.tax_info_rec.PRECISION := l_precision;
742 ARP_TAX.tax_info_rec.minimum_accountable_unit := l_min_acc_unit;
743 ARP_TAX.tax_info_rec.ship_to_cust_id := l_cust_acct_id;
744 ARP_TAX.tax_info_rec.bill_to_cust_id := l_cust_acct_id;
745 ARP_TAX.tax_info_rec.ship_to_site_use_id := lx_ship_to_siteuseid;
746 ARP_TAX.tax_info_rec.ship_to_location_id := lx_ship_to_locid;
747 ARP_TAX.tax_info_rec.ship_to_postal_code := lx_postal_code;
748 ARP_TAX.tax_info_rec.bill_to_site_use_id := l_cust_site_use_id;
749 ARP_TAX.tax_info_rec.bill_to_location_id := l_bill_to_locid;
750 ARP_TAX.tax_info_rec.bill_to_postal_code := l_bill_to_postal_code;
751
752
753 -- make call to tax engine
754 BEGIN
755 ARP_TAX_CRM_INTEGRATION_PKG.summary
756 (p_set_of_books_id => l_sob_id
757 ,x_crm_tax_out_tbl => l_tax_tbl
758 ,p_new_tax_amount => l_tax_amt);
759
760 EXCEPTION
761
762
763 --Tax API logs the messages in a debug file in case of exceptions raised. hence coding in this manner.
764 WHEN OTHERS THEN
765 -- exceptions raised by Tax engine
766 OKL_API.set_message(p_app_name => g_app_name,
767 p_msg_name => 'OKL_CS_TAX_FAILED');
768
769 RAISE OKL_API.G_EXCEPTION_ERROR;
770 END;--call to tax api.
771
772
773 -- get the out put tax record structure;
774 l_count := l_tax_tbl.COUNT;
775 print_to_log('count in table :'|| l_tax_tbl.COUNT);
776
777 IF l_count > 0 THEN
778 FOR i IN 1..l_count LOOP
779 l_okl_tax_rec.khr_id := p_tax_in_rec.contract_id;
780 l_okl_tax_rec.kle_id := contract_streams_rec.line_id;
781 l_okl_tax_rec.asset_id := lx_asset_id;
782 l_okl_tax_rec.asset_number := lx_asset_number;
783 l_okl_tax_rec.tax_line_type := p_tax_in_rec.line_type;
784 l_okl_tax_rec.sel_id := contract_streams_rec.stream_element_id;
785 l_okl_tax_rec.tax_due_date := contract_streams_rec.stream_element_date;
786 l_okl_tax_rec.tax_type := l_tax_tbl(i).tax_type;
787 l_okl_tax_rec.tax_rate_code := l_tax_tbl(i).tax_code;
788 l_okl_tax_rec.taxable_amount := contract_streams_rec.amount;
789 l_okl_tax_rec.tax_exemption_id := l_tax_tbl(i).tax_exemption_id;
790 l_okl_tax_rec.tax_rate := l_tax_tbl(i).tax_rate;
791 l_okl_tax_rec.tax_amount := l_tax_tbl(i).tax_amount;
792 l_okl_tax_rec.sales_tax_id := l_tax_tbl(i).sales_tax_id;
793 l_okl_tax_rec.trq_id := p_tax_in_rec.trx_id;
794 l_okl_tax_rec.actual_yn := 'E'; --Estimated Tax
795 l_okl_tax_rec.org_id := l_org_id;
796 l_okl_tax_rec.history_yn := 'N';
797
798 print_to_log(i||'tax rate'||l_okl_tax_rec.tax_rate);
799 print_to_log(i||'tax code'||l_okl_tax_rec.tax_rate_code);
800 print_to_log(i||'taxable amount'||l_okl_tax_rec.taxable_amount);
801 print_to_log(i||'tax amount'||l_okl_tax_rec.tax_amount);
802
803 l_newrec_count := l_newrec_count+1;
804
805 -- if a new record is inserted to okl_tax_lines then historize the rest active schedules
806 IF l_newrec_count = 1 THEN
807 update okl_tax_lines
808 set history_yn = 'Y'
809 where history_yn = 'N'
810 and khr_id = p_tax_in_rec.contract_id;
811 END IF;
812
813 -- call to simple apis to insert the tax lines into OKL_TAX_LINES
814 OKL_TAX_PVT.insert_row(p_api_version => l_api_version,
815 p_init_msg_list => 'F',
816 x_return_status => l_return_status,
817 x_msg_count => x_msg_count,
818 x_msg_data => x_msg_data,
819 p_okl_tax_lines_v_rec => l_okl_tax_rec,
820 x_okl_tax_lines_v_rec => lx_tax_rec);
821
822 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
823 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
824 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
825 RAISE OKL_API.G_EXCEPTION_ERROR;
826 END IF;
827
828 END LOOP; -- end processing output tax table
829
830 END IF;-- If count > 0
831 END IF; -- If call_tax_api = 'y'
832 END LOOP; -- end processing of contract streams
833
834
835 print_to_log('Processing Invoiced Streams');
836 --Start processing invoiced streams with tax records
837 FOR c_invoice_rec IN c_invoiced_streams(p_tax_in_rec.contract_id,p_tax_in_rec.date_from,p_tax_in_rec.date_to) LOOP
838
839 -- Initialising all the loop variable to null for every row.
840 lx_asset_id := NULL;
841 lx_asset_number := NULL;
842
843 IF c_invoice_rec.line_id IS NULL THEN
844 l_lty_code := NULL;
845 ELSE
846 OPEN c_lty_code(c_invoice_rec.line_id);
847 FETCH c_lty_code INTO l_lty_code;
848 IF c_lty_code%NOTFOUND THEN
849 -- The case of a bad data need to be handled (Bad line id in the streams record)
850 OKL_API.set_message(p_app_name => g_app_name,
851 p_msg_name => 'OKL_INVALID_DATA');
852 RAISE OKL_API.G_EXCEPTION_ERROR;
853 END IF;
854 CLOSE c_lty_code;
855 print_to_log('lty_code'||l_lty_code);
856 END IF;
857
858 IF (l_lty_code = 'FREE_FORM1') OR
859 (l_lty_code = 'LINK_SERV_ASSET')OR
860 (l_lty_code = 'LINK_FEE_ASSET') THEN
861 IF l_lty_code = 'FREE_FORM1' THEN -- Financial Asset line
862 l_line_id := c_invoice_rec.line_id;
863 ELSE -- Linked Asset Line - Service or Fee
864 --get the line id of the asset linked to the service or fee
865 OPEN c_linked_asset_line(c_invoice_rec.line_id);
866 FETCH c_linked_asset_line INTO l_line_id;
867 CLOSE c_linked_asset_line;
868 END IF;
869
870 Get_Asset_Details(p_api_version => p_api_version,
871 p_init_msg_list => p_init_msg_list,
872 x_return_status => l_return_status,
873 x_msg_count => x_msg_count,
874 x_msg_data => x_msg_data,
875 p_flag => 'N',
876 p_cust_acct_id => l_cust_acct_id,
877 p_kle_id => l_line_id,
878 px_asset_id => lx_asset_id,
879 px_asset_number => lx_asset_number,
880 px_ship_to_siteuseid => lx_ship_to_siteuseid,
881 px_ship_to_locid => lx_ship_to_locid,
882 px_postal_code => lx_postal_code);
883
884 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
885 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
886 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
887 RAISE OKL_API.G_EXCEPTION_ERROR;
888 END IF;
889
890 ELSIF (l_lty_code = 'SOLD_SERVICE') OR -- Line with no assets
891 (l_lty_code = 'FEE') OR -- Line with no assets
892 (c_invoice_rec.line_id IS NULL) THEN
893 lx_asset_id := NULL;
894 lx_asset_number := NULL;
895 END IF;
896
897 --prepare the input record for inserting into the OKL Tax Entity
898 l_okl_tax_rec.khr_id := p_tax_in_rec.contract_id;
899 l_okl_tax_rec.kle_id := c_invoice_rec.line_id;
900 l_okl_tax_rec.asset_id := lx_asset_id;
901 l_okl_tax_rec.asset_number := lx_asset_number;
902 l_okl_tax_rec.tax_line_type := p_tax_in_rec.line_type;
903 l_okl_tax_rec.sel_id := c_invoice_rec.stream_element_id;
904 l_okl_tax_rec.tax_due_date := c_invoice_rec.stream_element_date;
905 l_okl_tax_rec.tax_rate_code := c_invoice_rec.tax_code;
906 l_okl_tax_rec.taxable_amount := c_invoice_rec.taxable_amount;
907 l_okl_tax_rec.tax_exemption_id := c_invoice_rec.tax_exemption_id;
908 l_okl_tax_rec.tax_rate := c_invoice_rec.tax_rate;
909 l_okl_tax_rec.tax_amount := c_invoice_rec.extended_amount;
910 l_okl_tax_rec.sales_tax_id := c_invoice_rec.sales_tax_id;
911 l_okl_tax_rec.actual_yn := 'A'; --Actual Tax
912 l_okl_tax_rec.trq_id := p_tax_in_rec.trx_id;
913 l_okl_tax_rec.org_id := l_org_id;
914 l_okl_tax_rec.history_yn := 'N';
915
916
917 l_newrec_count := l_newrec_count+1;
918 -- if a new record is inserted to okl_tax_lines then historize the rest active schedules
919 IF l_newrec_count = 1 THEN
920 update okl_tax_lines
921 set history_yn = 'Y'
922 where history_yn = 'N'
923 and khr_id = p_tax_in_rec.contract_id;
924 END IF;
925
926 -- call to simple apis to insert the actual tax lines from AR into OKL_TAX_LINES
927 OKL_TAX_PVT.insert_row(p_api_version => l_api_version,
928 p_init_msg_list => 'F',
929 x_return_status => l_return_status,
930 x_msg_count => x_msg_count,
931 x_msg_data => x_msg_data,
932 p_okl_tax_lines_v_rec => l_okl_tax_rec,
933 x_okl_tax_lines_v_rec => lx_tax_rec);
934
935 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
936 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
937 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
938 RAISE OKL_API.G_EXCEPTION_ERROR;
939 END IF;
940
941 END LOOP; --End Processing AR Tax records.
942
943 print_to_log('return status from simple entity'||l_return_status);
944 */
945
946 x_return_status := l_return_status;
947 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
948 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
949 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Create_Tax_Schedule ','End(-)');
950 END IF;
951 EXCEPTION
952 WHEN OKL_API.G_EXCEPTION_ERROR THEN
953 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
955 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_ERROR');
956 END IF;
957 /* IF c_get_custacctid%ISOPEN THEN
958 CLOSE c_get_custacctid;
959 END IF;
960 IF c_contract_streams%ISOPEN THEN
961 CLOSE c_contract_streams;
962 END IF;
963 IF c_get_billto_location%ISOPEN THEN
964 CLOSE c_get_billto_location;
965 END IF;
966 IF c_linked_asset_line%ISOPEN THEN
967 CLOSE c_linked_asset_line;
968 END IF;
969 IF c_invoiced_streams%ISOPEN THEN
970 CLOSE c_invoiced_streams;
971 END IF;
972 */
973 x_return_status := OKL_API.G_RET_STS_ERROR;
974
975 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
976 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
977 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
978 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
979 END IF;
980 /*IF c_get_custacctid%ISOPEN THEN
981 CLOSE c_get_custacctid;
982 END IF;
983 IF c_contract_streams%ISOPEN THEN
984 CLOSE c_contract_streams;
985 END IF;
986 IF c_get_billto_location%ISOPEN THEN
987 CLOSE c_get_billto_location;
988 END IF;
989 IF c_linked_asset_line%ISOPEN THEN
990 CLOSE c_linked_asset_line;
991 END IF;
992 IF c_invoiced_streams%ISOPEN THEN
993 CLOSE c_invoiced_streams;
994 END IF;
995 */
996 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
997
998 WHEN OTHERS THEN
999 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
1001 'EXCEPTION :'||sqlerrm);
1002 END IF;
1003 /*IF c_get_custacctid%ISOPEN THEN
1004 CLOSE c_get_custacctid;
1005 END IF;
1006 IF c_contract_streams%ISOPEN THEN
1007 CLOSE c_contract_streams;
1008 END IF;
1009 IF c_get_billto_location%ISOPEN THEN
1010 CLOSE c_get_billto_location;
1011 END IF;
1012 IF c_linked_asset_line%ISOPEN THEN
1013 CLOSE c_linked_asset_line;
1014 END IF;
1015 IF c_invoiced_streams%ISOPEN THEN
1016 CLOSE c_invoiced_streams;
1017 END IF;
1018 */
1019 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1020 -- unexpecetd error
1021 OKL_API.set_message(p_app_name => g_app_name,
1022 p_msg_name => g_unexpected_error,
1023 p_token1 => g_sqlcode_token,
1024 p_token1_value => sqlcode,
1025 p_token2 => g_sqlerrm_token,
1026 p_token2_value => sqlerrm);
1027 END Create_Tax_Schedule;
1028
1029
1030 /*========================================================================
1031 | PUBLIC PROCEDURE Create_Tax_Schedule
1032 |
1033 | DESCRIPTION
1034 | This procedure will query all streams for a contract, pass the stream amounts to
1035 | the Global Tax Engine for calculating tax for each of the amounts and create tax schedules in
1036 | OKL_TAX_LINES. This procedure takes parameters in the table structure.
1037 |
1038 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1039 | Enter a list of all local procedures and functions which
1040 | are call this package.
1041 |
1042 |
1043 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1044 | Enter a list of all local procedures and cuntions which
1045 | this package calls.
1046 |
1047 | PARAMETERS
1048 | p_contract_id IN Contract Identifier
1049 | p_trx_date IN Schedule Request Date
1050 | p_date_from IN Date From
1051 | p_date_to IN Date To
1052 | x_return_status OUT Return Status
1053 |
1054 | KNOWN ISSUES
1055 |
1056 | NOTES
1057 | Any interesting aspect of the code in the package body which needs
1058 | to be stated.
1059 |
1060 | MODIFICATION HISTORY
1061 | Date Author Description of Changes
1062 | 24-MAY-2004 RKUTTIYA Created
1063 |
1064 *=======================================================================*/
1065
1066
1067 PROCEDURE Create_Tax_Schedule( p_api_version IN NUMBER,
1068 p_init_msg_list IN VARCHAR2,
1069 x_return_status OUT NOCOPY VARCHAR2,
1070 x_msg_count OUT NOCOPY NUMBER,
1071 x_msg_data OUT NOCOPY VARCHAR2,
1072 p_tax_in_tbl IN okl_tax_tbl_type)
1073 IS
1074 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1075 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tax_Schedule';
1076 l_api_version CONSTANT NUMBER := 1;
1077 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1078 i NUMBER;
1079 BEGIN
1080 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1081 G_PKG_NAME,
1082 p_init_msg_list,
1083 l_api_version,
1084 p_api_version,
1085 '_PVT',
1086 x_return_status);
1087 -- check if activity started successfully
1088 If (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1089 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1090 Elsif (l_return_status = OKL_API.G_RET_STS_ERROR) then
1091 raise OKL_API.G_EXCEPTION_ERROR;
1092 End If;
1093 -- Make sure PL/SQL table has records in it before passing
1094 /* IF (p_tax_in_tbl.COUNT > 0) THEN
1095 i := p_tax_in_tbl.FIRST;
1096 --Print Input Variables
1097 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1099 'P_contract_id :'||p_tax_in_tbl(i).contract_id);
1100 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1101 'P_trx_id :'||p_tax_in_tbl(i).trx_id);
1102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1103 'p_trx_date :'||p_tax_in_tbl(i).trx_date);
1104 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1105 'p_line_type :'||p_tax_in_tbl(i).line_type);
1106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1107 'p_date_from :'||p_tax_in_tbl(i).date_from);
1108 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PROCESS_TAX.Create_Tax_Schedule',
1109 'p_date_to :'||p_tax_in_tbl(i).date_to);
1110 END IF;
1111 LOOP
1112 Create_Tax_Schedule (
1113 p_api_version => l_api_version,
1114 p_init_msg_list => OKL_API.G_FALSE,
1115 x_return_status => x_return_status,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data,
1118 p_tax_in_rec => p_tax_in_tbl(i));
1119 -- store the highest degree of error
1120 If x_return_status <> OKL_API.G_RET_STS_SUCCESS Then
1121 If l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR Then
1122 l_overall_status := x_return_status;
1123 End If;
1124 End If;
1125 EXIT WHEN (i = p_tax_in_tbl.LAST);
1126 i := p_tax_in_tbl.NEXT(i);
1127 END LOOP;
1128 -- return overall status
1129 x_return_status := l_overall_status;
1130 END IF;
1131 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
1132 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1133 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
1134 raise OKL_API.G_EXCEPTION_ERROR;
1135 End If;
1136 */
1137 x_return_status := l_return_status;
1138
1139 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
1140 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1141 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Create_Tax_Schedule ','End(-)');
1142 END IF;
1143 EXCEPTION
1144 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1145 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
1147 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_ERROR');
1148 END IF;
1149 x_return_status := OKL_API.G_RET_STS_ERROR;
1150 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1151 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1152 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
1153 'EXCEPTION :'|| 'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1154 END IF;
1155 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1156 WHEN OTHERS THEN
1157 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1158 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PROCESS_TAX.Create_Tax_Schedule ',
1159 'EXCEPTION :'||sqlerrm);
1160 END IF;
1161 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1162 -- unexpected error
1163 OKL_API.set_message(p_app_name => g_app_name,
1164 p_msg_name => g_unexpected_error,
1165 p_token1 => g_sqlcode_token,
1166 p_token1_value => sqlcode,
1167 p_token2 => g_sqlerrm_token,
1168 p_token2_value => sqlerrm);
1169 END Create_Tax_Schedule;
1170
1171 END OKL_PROCESS_TAX;
1172
1173
[Home] [Help]