[Home] [Help]
PACKAGE BODY: APPS.OKS_TAX_UTIL_PVT
Source
1 PACKAGE BODY OKS_TAX_UTIL_PVT AS
2 /* $Header: OKSTAXUB.pls 120.31.12010000.5 2009/04/24 10:09:37 harlaksh ship $*/
3
4 --Declare the cursors and records for this package
5 --We do not want to expose them to other APIs, so declare here instead of
6 --in the spec
7 CURSOR okc_hdr_csr (p_chr_id NUMBER) IS
8 SELECT
9 ID,
10 AUTHORING_ORG_ID,
11 INV_ORGANIZATION_ID,
12 START_DATE,
13 END_DATE,
14 CONVERSION_TYPE,
15 CONVERSION_RATE,
16 CONVERSION_RATE_DATE,
17 CUST_ACCT_ID,
18 BILL_TO_SITE_USE_ID,
19 INV_RULE_ID,
20 SHIP_TO_SITE_USE_ID,
21 PAYMENT_TERM_ID,
22 ORG_ID,
23 CURRENCY_CODE,
24 CUST_PO_NUMBER,
25 ESTIMATED_AMOUNT
26 FROM OKC_K_HEADERS_ALL_B
27 WHERE id = p_chr_id;
28
29 CURSOR oks_hdr_csr (p_chr_id NUMBER) IS
30 SELECT
31 ID,
32 INV_TRX_TYPE,
33 TAX_STATUS,
34 EXEMPT_CERTIFICATE_NUMBER,
35 EXEMPT_REASON_CODE,
36 TAX_EXEMPTION_ID,
37 TAX_CLASSIFICATION_CODE,
38 TAX_CODE
39 FROM OKS_K_HEADERS_B
40 WHERE chr_id = p_chr_id;
41
42
43 CURSOR okc_line_csr (p_chr_id NUMBER, p_line_id NUMBER) IS
44 SELECT
45 ID,
46 PRICE_NEGOTIATED,
47 PRICE_UNIT,
48 CUST_ACCT_ID,
49 BILL_TO_SITE_USE_ID,
50 INV_RULE_ID,
51 START_DATE,
52 SHIP_TO_SITE_USE_ID,
53 PAYMENT_TERM_ID,
54 CLE_ID,
55 --npalepu added lse_id for bug # 5223699
56 LSE_ID
57 --end npalepu
58 FROM OKC_K_LINES_B
59 WHERE dnz_chr_id = p_chr_id
60 AND id = p_line_id
61 AND chr_id IS NOT NULL;
62
63 CURSOR oks_line_csr(p_chr_id NUMBER, p_line_id NUMBER) IS
64 SELECT
65 ID,
66 TAX_STATUS,
67 EXEMPT_CERTIFICATE_NUMBER,
68 EXEMPT_REASON_CODE,
69 TAX_EXEMPTION_ID,
70 TAX_CLASSIFICATION_CODE,
71 TAX_CODE
72 FROM OKS_K_LINES_B
73 WHERE dnz_chr_id = p_chr_id
74 AND cle_id = p_line_id;
75
76 ------------------------ End pakcage body declarations ----------------------------------
77
78 /*
79 Internal procedure that prints a record of type OKS_TAX_UTIL_PVT.RA_REC_TYPE.
80 */
81
82 PROCEDURE PRINT_G_RAIL_REC
83 (
84 p_rail_rec IN ra_rec_type,
85 p_msg IN VARCHAR2,
86 p_level IN NUMBER
87 )
88 IS
89 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_G_RAIL_REC';
90 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
91 l_error_text VARCHAR(512);
92
93 BEGIN
94
95 IF (p_level >= FND_LOG.g_current_runtime_level) THEN
96
97 FND_LOG.string(p_level, l_mod_name||'.rec_details', p_msg||
98 ' ,HEADER_ID='|| p_rail_rec.HEADER_ID||
99 ' ,LINE_ID='|| p_rail_rec.LINE_ID||
100 ' ,ORG_ID='|| p_rail_rec.ORG_ID||
101 ' ,AMOUNT='|| p_rail_rec.AMOUNT||
102 ' ,UNIT_SELLING_PRICE='|| p_rail_rec.UNIT_SELLING_PRICE||
103 ' ,PRICE_NEGOTIATED='|| p_rail_rec.PRICE_NEGOTIATED||
104 ' ,QUANTITY='|| p_rail_rec.QUANTITY||
105 ' ,TAX_VALUE='|| p_rail_rec.TAX_VALUE||
106 ' ,TAX_RATE='|| p_rail_rec.TAX_RATE||
107 ' ,AMOUNT_INCLUDES_TAX_FLAG='|| p_rail_rec.AMOUNT_INCLUDES_TAX_FLAG||
108 ' ,TOTAL_PLUS_TAX='|| p_rail_rec.TOTAL_PLUS_TAX||
109 ' ,TAX_EXEMPT_FLAG='|| p_rail_rec.TAX_EXEMPT_FLAG||
110 ' ,TAX_CLASSIFICATION_CODE='|| p_rail_rec.TAX_CLASSIFICATION_CODE||
111 ' ,EXEMPT_CERTIFICATE_NUMBER='|| p_rail_rec.EXEMPT_CERTIFICATE_NUMBER||
112 ' ,EXEMPT_REASON_CODE='|| p_rail_rec.EXEMPT_REASON_CODE||
113 ' ,CUST_TRX_TYPE_ID='|| p_rail_rec.CUST_TRX_TYPE_ID||
114 ' ,SHIP_TO_SITE_USE_ID='|| p_rail_rec.SHIP_TO_SITE_USE_ID||
115 ' ,BILL_TO_SITE_USE_ID='|| p_rail_rec.BILL_TO_SITE_USE_ID||
116 ' ,SHIP_TO_PARTY_ID='|| p_rail_rec.SHIP_TO_PARTY_ID||
117 ' ,SHIP_TO_PARTY_SITE_ID='|| p_rail_rec.SHIP_TO_PARTY_SITE_ID||
118 ' ,SHIP_TO_CUST_ACCT_ID='|| p_rail_rec.SHIP_TO_CUST_ACCT_ID||
119 ' ,SHIP_TO_CUST_ACCT_SITE_ID='|| p_rail_rec.SHIP_TO_CUST_ACCT_SITE_ID||
120 ' ,SHIP_TO_CUST_ACCT_SITE_USE_ID='|| p_rail_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID||
121 ' ,BILL_TO_PARTY_ID='|| p_rail_rec.BILL_TO_PARTY_ID||
122 ' ,BILL_TO_PARTY_SITE_ID='|| p_rail_rec.BILL_TO_PARTY_SITE_ID||
123 ' ,BILL_TO_CUST_ACCT_ID='|| p_rail_rec.BILL_TO_CUST_ACCT_ID||
124 ' ,BILL_TO_CUST_ACCT_SITE_ID='|| p_rail_rec.BILL_TO_CUST_ACCT_SITE_ID||
125 ' ,BILL_TO_CUST_ACCT_SITE_USE_ID='|| p_rail_rec.BILL_TO_CUST_ACCT_SITE_USE_ID||
126 ' ,CONVERSION_TYPE='|| p_rail_rec.CONVERSION_TYPE||
127 ' ,CONVERSION_DATE='|| p_rail_rec.CONVERSION_DATE||
128 ' ,CONVERSION_RATE='|| p_rail_rec.CONVERSION_RATE||
129 ' ,PRODUCT_TYPE='|| p_rail_rec.PRODUCT_TYPE);
130
131 END IF;
132
133
134 EXCEPTION
135
136 WHEN OTHERS THEN
137 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
138 --first log the sqlerrm
139 l_error_text := substr (SQLERRM, 1, 512);
140 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
141 --then add it to the message api list
142 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
143 END IF;
144 RAISE;
145
146 END PRINT_G_RAIL_REC;
147
148
149 /*
150 Internal function to get set_of_books id for a given org.
151 */
152 FUNCTION GET_SET_OF_BOOKS_ID (p_org_id NUMBER) RETURN NUMBER IS
153
154 l_api_name CONSTANT VARCHAR2(30) := 'GET_SET_OF_BOOKS_ID';
155 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
156 l_error_text VARCHAR(512);
157
158 CURSOR l_org_csr IS
159 SELECT set_of_books_id
160 FROM ar_system_parameters_all
161 WHERE org_id = p_org_id;
162 l_set_of_books_id ar_system_parameters_all.set_of_books_id%TYPE;
163
164 BEGIN
165
166 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
167 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_org_id='||p_org_id);
168 END IF;
169
170 OPEN l_org_csr;
171 FETCH l_org_csr INTO l_set_of_books_id ;
172 CLOSE l_org_csr;
173
174 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
175 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', 'l_set_of_books_id='||l_set_of_books_id);
176 END IF;
177
178 RETURN (l_set_of_books_id);
179
180 EXCEPTION
181 WHEN OTHERS THEN
182
183 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
184 --first log the sqlerrm
185 l_error_text := substr (SQLERRM, 1, 512);
186 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
187 --then add it to the message api list
188 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
189 END IF;
190
191 IF l_org_csr%isopen THEN
192 CLOSE l_org_csr;
193 END IF;
194
195 RAISE;
196
197 END GET_SET_OF_BOOKS_ID;
198
199 /*
200 Internal procedure to set the following attributes in px_rail_rec from the
201 contract header
202 TAX_EXEMPT_FLAG
203 EXEMPT_CERTIFICATE_NUMBER
204 EXEMPT_REASON_CODE
205 TAX_CLASSIFICATION_CODE
206 */
207
208 PROCEDURE GET_HDR_TAX
209 (
210 p_oks_rec IN oks_hdr_csr%ROWTYPE,
211 p_okc_rec IN okc_hdr_csr%ROWTYPE,
212 p_okc_line_rec IN okc_line_csr%ROWTYPE,
213 px_rail_rec IN OUT NOCOPY ra_rec_type,
214 x_return_status OUT NOCOPY VARCHAR2
215 )
216 IS
217
218 l_api_name CONSTANT VARCHAR2(30) := 'GET_HDR_TAX';
219 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
220 l_error_text VARCHAR(512);
221
222 CURSOR tax_exempt_csr(cp_tax_exempt_id NUMBER) IS
223 SELECT EXEMPT_CERTIFICATE_NUMBER,
224 EXEMPT_REASON_CODE
225 FROM ZX_EXEMPTIONS
226 WHERE TAX_EXEMPTION_ID = cp_tax_exempt_id;
227 TAX_REC tax_exempt_csr%ROWTYPE;
228
229 --npalepu added on 12-JUL-2006 for bug # 5380878
230 CURSOR get_taxcode_from_taxexempt_csr(cp_tax_exempt_id NUMBER) IS
231 SELECT ex.TAX_CODE
232 FROM RA_TAX_EXEMPTIONS_ALL ex
233 WHERE ex.TAX_EXEMPTION_ID = cp_tax_exempt_id;
234 --end npalepu
235
236 CURSOR tax_code_csr(cp_tax_code VARCHAR2) IS
237 SELECT TAX_CLASSIFICATION_CODE
238 FROM ZX_ID_TCC_MAPPING
239 WHERE TAX_RATE_CODE_ID = cp_tax_code
240 AND source = 'AR';
241 l_tax_code ZX_ID_TCC_MAPPING.TAX_CLASSIFICATION_CODE%TYPE;
242
243
244 CURSOR cust_acct_csr(cp_site_use_id NUMBER) IS
245 SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID
246 FROM HZ_CUST_SITE_USES_ALL S_SHIP,
247 HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
248 WHERE S_SHIP.SITE_USE_ID = cp_site_use_id
249 AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id;
250
251 l_header_cust_acct_id NUMBER;
252 l_line_cust_acct_id NUMBER;
253
254
255 BEGIN
256
257 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
258 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_oks_rec.tax_status='||p_oks_rec.tax_status||' ,p_okc_rec.cust_acct_id='||p_okc_rec.cust_acct_id||' ,p_okc_line_rec.cust_acct_id='||p_okc_line_rec.cust_acct_id||
259 ' ,p_oks_rec.EXEMPT_CERTIFICATE_NUMBER='||p_oks_rec.EXEMPT_CERTIFICATE_NUMBER||' ,p_oks_rec.EXEMPT_REASON_CODE='||p_oks_rec.EXEMPT_REASON_CODE||' ,p_oks_rec.TAX_EXEMPTION_ID='||p_oks_rec.TAX_EXEMPTION_ID||
260 ' ,p_oks_rec.TAX_CLASSIFICATION_CODE='||p_oks_rec.TAX_CLASSIFICATION_CODE||' ,p_oks_rec.TAX_CODE='||p_oks_rec.TAX_CODE);
261 END IF;
262
263 x_return_status := OKC_API.G_RET_STS_SUCCESS;
264
265 --need to copy the tax_exempt_flag from oks_k_headers_b (p_oks_rec)
266 --to px_rail_rec
267 --the IF condition is so that we don't overwrite the tax information from the line
268 IF px_rail_rec.TAX_EXEMPT_FLAG IS NULL THEN
269 px_rail_rec.TAX_EXEMPT_FLAG := p_oks_rec.tax_status;
270 END IF;
271
272
273 --Get exemption information if tax_status is 'E'
274 IF NVL(p_oks_rec.tax_status, 'S') = 'E' THEN
275
276 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
277 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.tax_status','p_oks_rec.tax_status: Tax Status is E (Exempt)');
278 END IF;
279
280 --Check if the header and lines have the same cust_acct_id
281 --We only use the header level exemptions for the lines
282 --if the header and line customer accounts are the same
283 l_header_cust_acct_id := p_okc_rec.cust_acct_id;
284 l_line_cust_acct_id := p_okc_line_rec.cust_acct_id;
285
286 IF l_header_cust_acct_id IS NULL THEN
287 OPEN cust_acct_csr(p_okc_rec.bill_to_site_use_id);
288 FETCH cust_acct_csr INTO l_header_cust_acct_id;
289 CLOSE cust_acct_csr;
290 END IF;
291
292 IF l_line_cust_acct_id IS NULL THEN
293 OPEN cust_acct_csr(p_okc_line_rec.bill_to_site_use_id);
294 FETCH cust_acct_csr INTO l_line_cust_acct_id;
295 CLOSE cust_acct_csr;
296 END IF;
297
298 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
299 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cust_accts','l_header_cust_acct_id='||l_header_cust_acct_id||' ,l_line_cust_acct_id='||l_line_cust_acct_id);
300 END IF;
301
302 IF l_header_cust_acct_id = l_line_cust_acct_id THEN
303
304 px_rail_rec.TAX_EXEMPT_FLAG := p_oks_rec.TAX_STATUS;
305
306 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
307 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cust_accts','Header and line have same customer account ID');
308 END IF;
309
310 IF(p_oks_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL)THEN
311
312 --Contracts created after R12
313 px_rail_rec.EXEMPT_CERTIFICATE_NUMBER := p_oks_rec.EXEMPT_CERTIFICATE_NUMBER;
314 px_rail_rec.EXEMPT_REASON_CODE := p_oks_rec.EXEMPT_REASON_CODE;
315
316 ELSE
317
318 --Historical contracts
319 OPEN tax_exempt_csr(p_oks_rec.TAX_EXEMPTION_ID);
320 FETCH tax_exempt_csr INTO tax_rec;
321 CLOSE tax_exempt_csr;
322
323 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
324 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_tax_exempt_csr','old contracts - tax_rec.EXEMPT_CERTIFICATE_NUMBER='|| tax_rec.EXEMPT_CERTIFICATE_NUMBER||
325 ' ,tax_rec.EXEMPT_REASON_CODE='|| tax_rec.EXEMPT_REASON_CODE);
326 END IF;
327
328 px_rail_rec.TAX_EXEMPT_NUMBER := tax_rec.EXEMPT_CERTIFICATE_NUMBER;
329 px_rail_rec.TAX_EXEMPT_REASON := tax_rec.EXEMPT_REASON_CODE;
330
331 --npalepu added on 12-jul-2006 for bug # 5380878
332 OPEN get_taxcode_from_taxexempt_csr(p_oks_rec.TAX_EXEMPTION_ID);
333 FETCH get_taxcode_from_taxexempt_csr INTO l_tax_code;
334
335 IF get_taxcode_from_taxexempt_csr%FOUND THEN
336 px_rail_rec.TAX_CLASSIFICATION_CODE := l_tax_code;
337
338 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
339 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_taxcode_from_taxexempt_csr','old contracts - TAX_CLASSIFICATION_CODE='|| l_tax_code);
340 END IF;
341 END IF;
342 CLOSE get_taxcode_from_taxexempt_csr;
343 --end npalepu
344
345 END IF; --of IF(p_oks_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL)THEN
346
347 ELSE
348
349 --log the line level customer id and header level customer id
350 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
351 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cust_accts','header and line have different customers, cannot use header exemptions for line, setting TAX_EXEMPT_FLAG to S (Standard)');
352 END IF;
353
354 --since the exemption flag is 'E' taken from the header, and we cannot use it
355 --we should NOT set the exemption flag to 'E'
356 --set it to 'S' instead
357 px_rail_rec.TAX_EXEMPT_FLAG := 'S';
358
359 END IF; -- of IF l_header_cust_acct_id = l_line_cust_acct_id THEN
360
361 END IF; --of IF NVL(p_oks_rec.tax_status, 'S') = 'E' THEN
362
363 -- Populate Tax Classification Code
364 IF p_oks_rec.TAX_CLASSIFICATION_CODE IS NOT NULL THEN
365
366 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
367 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.classification_code_new','New contract, TAX_CLASSIFICATION_CODE='||p_oks_rec.TAX_CLASSIFICATION_CODE);
368 END IF;
369
370 --Contracts created after R12
371 px_rail_rec.TAX_CLASSIFICATION_CODE := p_oks_rec.TAX_CLASSIFICATION_CODE;
372
373 ELSIF p_oks_rec.TAX_CODE IS NOT NULL THEN
374
375 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
376 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.classification_code_old','Old contract, TAX_CODE='||p_oks_rec.TAX_CODE);
377 END IF;
378
379 --Historical contracts
380 OPEN tax_code_csr(p_oks_rec.TAX_CODE);
381 FETCH tax_code_csr INTO l_tax_code;
382
383 IF tax_code_csr%FOUND THEN
384
385 px_rail_rec.TAX_CLASSIFICATION_CODE := l_tax_code;
386
387 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
388 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.classification_code_old','classification code from ZX_ID_TCC_MAPPING, l_tax_code='|| l_tax_code);
389 END IF;
390
391 END IF; --of IF tax_code_csr%FOUND THEN
392 CLOSE tax_code_csr;
393
394 END IF; --of IF p_oks_rec.TAX_CLASSIFICATION_CODE IS NOT NULL THEN
395
396 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
397 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
398 END IF;
399
400
401 EXCEPTION
402
403 WHEN OTHERS THEN
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
405 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
406 --first log the sqlerrm
407 l_error_text := substr (SQLERRM, 1, 512);
408 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
409 --then add it to the message api list
410 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
411 END IF;
412
413 IF cust_acct_csr%isopen THEN
414 CLOSE cust_acct_csr;
415 END IF;
416 IF tax_exempt_csr%isopen THEN
417 CLOSE tax_exempt_csr;
418 END IF;
419 IF tax_code_csr%isopen THEN
420 CLOSE tax_code_csr;
421 END IF;
422
423 RAISE;
424
425 END GET_HDR_TAX;
426
427
428 /*
429 Internal procedure to set the following attributes in px_rail_rec from the
430 contract header
431 INVOICING_RULE_ID
432 TRX_DATE
433 CONVERSION_TYPE
434 CONVERSION_RATE
435 CONVERSION_RATE_DATE
436 CUST_TRX_TYPE_ID
437 PAYMENT_TERM_ID
438
439 BILL_TO_SITE_USE_ID
440 ORIG_SYSTEM_BILL_CUSTOMER_ID
441 ORIG_SYSTEM_BILL_ADDRESS_ID
442 ORIG_SYSTEM_SOLD_CUSTOMER_ID
443 BILL_TO_ORG_ID
444
445 ORIG_SYSTEM_SHIP_CUSTOMER_ID
446 ORIG_SYSTEM_SHIP_ADDRESS_ID
447 SHIP_TO_SITE_USE_ID
448 SHIP_TO_ORG_ID
449 */
450
451 -- Added for rule re-architecture.
452 PROCEDURE GET_HDR_RULES
453 (
454 p_oks_rec IN oks_hdr_csr%ROWTYPE,
455 p_okc_rec IN okc_hdr_csr%ROWTYPE,
456 px_rail_rec IN OUT NOCOPY ra_rec_type,
457 x_return_status OUT NOCOPY VARCHAR2
458 ) IS
459
460 l_api_name CONSTANT VARCHAR2(30) := 'GET_HDR_RULES';
461 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
462 l_error_text VARCHAR(512);
463 /* Added by sjanakir for Bug# 6972776 */
464 x_date DATE;
465 x_hook NUMBER;
466
467 CURSOR cur_address(cp_site_use_id IN VARCHAR2, cp_code VARCHAR2) IS
468 SELECT CS.SITE_USE_ID, CS.ORG_ID, CA.CUST_ACCOUNT_ID, CA.CUST_ACCT_SITE_ID
469 FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
470 WHERE CS.SITE_USE_ID = cp_site_use_id AND CS.SITE_USE_CODE = cp_code
471 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
472 ADDRESS_REC cur_address%ROWTYPE;
473
474
475 BEGIN
476
477 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
478 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_okc_rec.INV_RULE_ID='||p_okc_rec.INV_RULE_ID||' ,p_okc_rec.START_DATE='||p_okc_rec.START_DATE||' ,p_okc_rec.org_id='||p_okc_rec.org_id||
479 ' ,p_okc_rec.currency_code='||p_okc_rec.currency_code||' ,p_okc_rec.PAYMENT_TERM_ID='||p_okc_rec.PAYMENT_TERM_ID||' ,p_oks_rec.INV_TRX_TYPE='||p_oks_rec.INV_TRX_TYPE||
480 ' ,p_okc_rec.CONVERSION_TYPE='||p_okc_rec.CONVERSION_TYPE||' ,p_okc_rec.CONVERSION_RATE='||p_okc_rec.CONVERSION_RATE||' ,p_okc_rec.CONVERSION_RATE_DATE='||p_okc_rec.CONVERSION_RATE_DATE||
481 ' ,p_okc_rec.BILL_TO_SITE_USE_ID='||p_okc_rec.BILL_TO_SITE_USE_ID||' ,p_okc_rec.SHIP_TO_SITE_USE_ID='||p_okc_rec.SHIP_TO_SITE_USE_ID);
482 END IF;
483
484 x_return_status := OKC_API.G_RET_STS_SUCCESS;
485
486 px_rail_rec.INVOICING_RULE_ID := p_okc_rec.INV_RULE_ID; -- IRE
487
488 --Get Transaction Date
489 IF (px_rail_rec.TRX_DATE IS NULL) AND (p_okc_rec.START_DATE IS NOT NULL)
490 THEN
491
492 /* Added by sjanakir for Bug#6972776 */
493 oks_code_hook.tax_trx_date(p_chr_id => p_okc_rec.id,
494 p_cle_id => NULL,
495 p_hdr_start_date => p_okc_rec.START_DATE,
496 p_lin_start_date => NULL,
497 x_hook => x_hook,
498 x_date => x_date
499 );
500 IF x_hook = 1 AND x_date IS NOT NULL
501 THEN
502 px_rail_rec.TRX_DATE := x_date;
503 ELSE
504 px_rail_rec.TRX_DATE := p_okc_rec.START_DATE;
505 END IF;
506 END IF;
507 ----------------- CVN rule -------------------
508 px_rail_rec.CONVERSION_TYPE := p_okc_rec.CONVERSION_TYPE;
509 IF (px_rail_rec.CONVERSION_TYPE = 'User') THEN
510 px_rail_rec.CONVERSION_RATE := 1;
511 ELSE
512 px_rail_rec.CONVERSION_RATE := NULL;
513 END IF;
514
515 IF (okc_currency_api.get_ou_currency(p_okc_rec.org_id) <> p_okc_rec.currency_code ) THEN
516 IF (p_okc_rec.CONVERSION_TYPE = 'User') THEN
517 px_rail_rec.CONVERSION_RATE := nvl(p_okc_rec.CONVERSION_RATE, 1);
518 ELSE
519 px_rail_rec.CONVERSION_RATE := NULL;
520 END IF;
521 END IF;
522
523 -- conversion date is not getting used anywhere.
524 IF p_okc_rec.CONVERSION_RATE_DATE IS NULL THEN
525 px_rail_rec.CONVERSION_DATE := SYSDATE;
526 ELSE
527 px_rail_rec.CONVERSION_DATE := p_okc_rec.CONVERSION_RATE_DATE;
528 END IF;
529
530 -------------------- BTO rule ----------------------------
531 OPEN cur_address(p_okc_rec.BILL_TO_SITE_USE_ID, 'BILL_TO');
532 FETCH cur_address INTO address_rec;
533 IF cur_address%FOUND THEN
534 px_rail_rec.BILL_TO_SITE_USE_ID := address_rec.site_use_id;
535 px_rail_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID := address_rec.cust_account_id;
536 px_rail_rec.ORIG_SYSTEM_BILL_ADDRESS_ID := address_rec.cust_acct_site_id;
537 px_rail_rec.ORIG_SYSTEM_SOLD_CUSTOMER_ID := address_rec.cust_account_id;
538 px_rail_rec.BILL_TO_ORG_ID := address_rec.org_id;
539 END IF;
540 CLOSE cur_address;
541
542 ----------------- STO rule ------------------------------
543 OPEN cur_address(p_okc_rec.SHIP_TO_SITE_USE_ID, 'SHIP_TO');
544 FETCH cur_address INTO address_rec;
545 IF cur_address%FOUND THEN
546 px_rail_rec.ORIG_SYSTEM_SHIP_CUSTOMER_ID := address_rec.cust_account_id;
547 px_rail_rec.ORIG_SYSTEM_SHIP_ADDRESS_ID := address_rec.cust_acct_site_id;
548 px_rail_rec.SHIP_TO_SITE_USE_ID := address_rec.site_use_id;
549 px_rail_rec.SHIP_TO_ORG_ID := address_rec.org_id;
550 END IF;
551 CLOSE cur_address;
552
553 -------------------- PTR rule -----------------------------
554 px_rail_rec.PAYMENT_TERM_ID := p_okc_rec.PAYMENT_TERM_ID;
555
556 ------------------ SBG rule -------------------------------
557
558 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
559 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calling_get_cust_trx_type_id','p_oks_rec.INV_TRX_TYPE='||p_oks_rec.INV_TRX_TYPE||' ,p_org_id='||p_okc_rec.org_id);
560 END IF;
561
562
563 px_rail_rec.CUST_TRX_TYPE_ID := get_cust_trx_type_id(
564 p_org_id => p_okc_rec.org_id,
565 p_inv_trx_type => p_oks_rec.INV_TRX_TYPE);
566
567 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
568 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_cust_trx_type_id','px_rail_rec.CUST_TRX_TYPE_ID='||px_rail_rec.CUST_TRX_TYPE_ID);
569 END IF;
570
571 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
572 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
573 END IF;
574
575 EXCEPTION
576
577 WHEN OTHERS THEN
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
580 --first log the sqlerrm
581 l_error_text := substr (SQLERRM, 1, 512);
582 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
583 --then add it to the message api list
584 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
585 END IF;
586
587 IF cur_address%isopen THEN
588 CLOSE cur_address;
589 END IF;
590 RAISE;
591
592 END GET_HDR_RULES;
593
594 /*
595 Internal procedure to set the following attributes in px_rail_rec from the
596 contract line
597 INVOICING_RULE_ID
598 LINE_ID
599 PRICE_NEGOTIATED
600 TRX_DATE
601
602 TAX_EXEMPT_FLAG
603 EXEMPT_CERTIFICATE_NUMBER
604 EXEMPT_REASON_CODE
605 TAX_CLASSIFICATION_CODE
606
607
608 CONVERSION_TYPE
609 CONVERSION_RATE
610 CONVERSION_RATE_DATE
611 PAYMENT_TERM_ID
612
613 BILL_TO_SITE_USE_ID
614 ORIG_SYSTEM_BILL_CUSTOMER_ID
615 ORIG_SYSTEM_BILL_ADDRESS_ID
616 ORIG_SYSTEM_SOLD_CUSTOMER_ID
617 BILL_TO_ORG_ID
618
619 ORIG_SYSTEM_SHIP_CUSTOMER_ID
620 ORIG_SYSTEM_SHIP_ADDRESS_ID
621 SHIP_TO_SITE_USE_ID
622 SHIP_TO_ORG_ID
623 */
624
625 PROCEDURE GET_LINE_RULES
626 (
627 p_oks_line_rec IN oks_line_csr%ROWTYPE,
628 p_okc_line_rec IN okc_line_csr%ROWTYPE,
629 px_rail_rec IN OUT NOCOPY ra_rec_type,
630 x_return_status OUT NOCOPY VARCHAR2,
631 x_need_header_tax OUT NOCOPY VARCHAR2
632 )
633 IS
634 l_api_name CONSTANT VARCHAR2(30) := 'GET_LINE_RULES';
635 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
636 l_error_text VARCHAR(512);
637
638 /* Added by sjanakir for Bug# 6972776 */
639 x_date DATE;
640 x_hook NUMBER;
641
642
643 CURSOR tax_exempt_csr(cp_tax_exempt_id NUMBER) IS
644 SELECT EXEMPT_CERTIFICATE_NUMBER,
645 EXEMPT_REASON_CODE
646 FROM ZX_EXEMPTIONS
647 WHERE TAX_EXEMPTION_ID = cp_tax_exempt_id;
648 TAX_REC tax_exempt_csr%ROWTYPE;
649
650 --npalepu added on 12-JUL-2006 for bug # 5380878
651 CURSOR get_taxcode_from_taxexempt_csr(cp_tax_exempt_id NUMBER) IS
652 SELECT ex.TAX_CODE
653 FROM RA_TAX_EXEMPTIONS_ALL ex
654 WHERE ex.TAX_EXEMPTION_ID = cp_tax_exempt_id;
655 --end npalepu
656
657 CURSOR tax_code_csr(cp_tax_code VARCHAR2) IS
658 SELECT TAX_CLASSIFICATION_CODE
659 FROM ZX_ID_TCC_MAPPING
660 WHERE TAX_RATE_CODE_ID = cp_tax_code
661 AND source = 'AR';
662 l_tax_code ZX_ID_TCC_MAPPING.TAX_CLASSIFICATION_CODE%TYPE;
663
664 CURSOR cur_address(cp_site_use_id IN VARCHAR2, cp_code VARCHAR2) IS
665 SELECT CS.SITE_USE_ID, CS.ORG_ID, CA.CUST_ACCOUNT_ID, CA.CUST_ACCT_SITE_ID
666 FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
667 WHERE CS.SITE_USE_ID = cp_site_use_id AND CS.SITE_USE_CODE = cp_code
668 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
669 ADDRESS_REC cur_address%ROWTYPE;
670
671 l_need_header_tax VARCHAR2(1);
672
673 BEGIN
674
675 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
676 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'p_okc_line_rec.INV_RULE_ID='||p_okc_line_rec.INV_RULE_ID||' ,p_oks_line_rec.id='||p_oks_line_rec.id||' ,p_okc_line_rec.PRICE_NEGOTIATED='||p_okc_line_rec.PRICE_NEGOTIATED||
677 ' ,p_okc_line_rec.START_DATE='||p_okc_line_rec.START_DATE||' ,p_oks_line_rec.TAX_STATUS='||p_oks_line_rec.TAX_STATUS||' ,p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER='||p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER||
678 ' ,p_oks_line_rec.EXEMPT_REASON_CODE='||p_oks_line_rec.EXEMPT_REASON_CODE||' ,p_oks_line_rec.TAX_EXEMPTION_ID='||p_oks_line_rec.TAX_EXEMPTION_ID||' ,p_oks_line_rec.TAX_CLASSIFICATION_CODE='||p_oks_line_rec.TAX_CLASSIFICATION_CODE||
679 ' ,p_oks_line_rec.TAX_CODE='||p_oks_line_rec.TAX_CODE||' ,p_okc_line_rec.PAYMENT_TERM_ID='||p_okc_line_rec.PAYMENT_TERM_ID||
680 ' ,p_okc_line_rec.BILL_TO_SITE_USE_ID='||p_okc_line_rec.BILL_TO_SITE_USE_ID||' ,p_okc_line_rec.SHIP_TO_SITE_USE_ID='||p_okc_line_rec.SHIP_TO_SITE_USE_ID);
681 END IF;
682
683 x_return_status := OKC_API.G_RET_STS_SUCCESS;
684
685 IF p_okc_line_rec.INV_RULE_ID IS NOT NULL THEN
686 px_rail_rec.INVOICING_RULE_ID := p_okc_line_rec.INV_RULE_ID; -- IRE
687 END IF;
688
689 --Added in R12
690 IF p_oks_line_rec.id IS NOT NULL THEN
691 px_rail_rec.LINE_ID := p_oks_line_rec.id;
692 END IF;
693
694 --For sublines only
695 IF p_okc_line_rec.PRICE_NEGOTIATED IS NOT NULL THEN
696 px_rail_rec.PRICE_NEGOTIATED := p_okc_line_rec.price_negotiated;
697 END IF;
698
699 --Get Transaction Date
700 IF p_okc_line_rec.START_DATE IS NOT NULL THEN
701
702 /* Added by sjanakir for Bug#6972776 */
703 oks_code_hook.tax_trx_date(p_chr_id => NULL,
704 p_cle_id => p_okc_line_rec.id,
705 p_hdr_start_date => NULL,
706 p_lin_start_date => p_okc_line_rec.start_date,
707 x_hook => x_hook,
708 x_date => x_date
709 );
710
711 IF x_hook = 1 AND x_date IS NOT NULL
712 THEN
713 px_rail_rec.TRX_DATE := x_date;
714 ELSE
715 px_rail_rec.TRX_DATE := p_okc_line_rec.START_DATE;
716 END IF;
717 END IF;
718 --Populate Exemption Information if Tax Status = 'E'
719 IF p_oks_line_rec.TAX_STATUS IS NOT NULL THEN
720
721 px_rail_rec.TAX_EXEMPT_FLAG := p_oks_line_rec.TAX_STATUS;
722
723 IF(NVL(px_rail_rec.TAX_EXEMPT_FLAG, 'S') = 'E') THEN
724
725 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
726 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.tax_status','Tax status is E, p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER='|| p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER||
727 ' ,p_oks_line_rec.EXEMPT_REASON_CODE='|| p_oks_line_rec.EXEMPT_REASON_CODE||' ,p_oks_line_rec.TAX_EXEMPTION_ID='||p_oks_line_rec.TAX_EXEMPTION_ID);
728 END IF;
729
730 IF(p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL)THEN
731
732 --Contracts created after R12
733 IF p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL THEN
734 px_rail_rec.EXEMPT_CERTIFICATE_NUMBER := p_oks_line_rec.EXEMPT_CERTIFICATE_NUMBER;
735 END IF;
736
737 IF p_oks_line_rec.EXEMPT_REASON_CODE IS NOT NULL THEN
738 px_rail_rec.EXEMPT_REASON_CODE := p_oks_line_rec.EXEMPT_REASON_CODE;
739 END IF;
740
741 ELSE
742
743 --Historical contracts
744 OPEN tax_exempt_csr(p_oks_line_rec.TAX_EXEMPTION_ID);
745 FETCH tax_exempt_csr INTO tax_rec;
746 CLOSE tax_exempt_csr;
747
748 IF tax_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL THEN
749 px_rail_rec.EXEMPT_CERTIFICATE_NUMBER := tax_rec.EXEMPT_CERTIFICATE_NUMBER;
750 END IF;
751
752 IF tax_rec.EXEMPT_REASON_CODE IS NOT NULL THEN
753 px_rail_rec.EXEMPT_REASON_CODE := tax_rec.EXEMPT_REASON_CODE;
754 END IF;
755
756 --npalepu added on 12-jul-2006 for bug # 5380878
757 OPEN get_taxcode_from_taxexempt_csr(p_oks_line_rec.TAX_EXEMPTION_ID);
758 FETCH get_taxcode_from_taxexempt_csr INTO l_tax_code;
759
760 IF get_taxcode_from_taxexempt_csr%FOUND THEN
761 px_rail_rec.TAX_CLASSIFICATION_CODE := l_tax_code;
762
763 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
764 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_taxcode_from_taxexempt_csr','old contracts - TAX_CLASSIFICATION_CODE='|| l_tax_code);
765 END IF;
766 END IF;
767 CLOSE get_taxcode_from_taxexempt_csr;
768 --end npalepu
769
770 END IF; --of IF(p_oks_rec.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL)THEN
771
772 ELSE
773
774 NULL;
775
776 END IF; --of IF(NVL(px_rail_rec.TAX_EXEMPT_FLAG, 'S') = 'E') THEN
777
778 END IF; --of IF p_oks_line_rec.TAX_STATUS IS NOT NULL THEN
779
780
781 -- Populate Tax Classification Code
782 IF p_oks_line_rec.TAX_CLASSIFICATION_CODE IS NOT NULL THEN
783
784 --Contracts created after R12
785 px_rail_rec.TAX_CLASSIFICATION_CODE := p_oks_line_rec.TAX_CLASSIFICATION_CODE;
786 IF p_oks_line_rec.TAX_STATUS IS NULL THEN
787 --tax code is not null, but tax status is null
788 --default tax status to 'S'
789 px_rail_rec.TAX_EXEMPT_FLAG := 'S';
790 END IF;
791
792 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
793 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.tax_classification_code','New Contract - tax_classification_code='||p_oks_line_rec.TAX_CLASSIFICATION_CODE);
794 END IF;
795
796 ELSIF p_oks_line_rec.TAX_CODE IS NOT NULL THEN
797
798 --Historical contracts
799 OPEN tax_code_csr(p_oks_line_rec.TAX_CODE);
800 FETCH tax_code_csr INTO l_tax_code;
801 IF tax_code_csr%FOUND THEN
802 px_rail_rec.TAX_CLASSIFICATION_CODE := l_tax_code;
803 ELSE
804 --tax_status is 'S' or 'R', no tax code
805 px_rail_rec.TAX_CLASSIFICATION_CODE := NULL;
806 l_need_header_tax := 'Y';
807 END IF;
808 CLOSE tax_code_csr;
809
810 IF p_oks_line_rec.TAX_STATUS IS NULL THEN
811 --tax code is not null, but tax status is null
812 --default tax status to 'S'
813 px_rail_rec.TAX_EXEMPT_FLAG := 'S';
814 END IF;
815
816 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
817 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.tax_classification_code','Old unmigarted Contract - tax_code='||p_oks_line_rec.TAX_CODE||' ,corresponding classification_code='||l_tax_code);
818 END IF;
819
820 ELSE
821 --no tax code on line level, need header information
822 --This is to prevent the null subline information from overwriting top line
823 --IF px_rail_rec.TAX_CLASSIFICATION_CODE IS NULL AND px_rail_rec.TAX_EXEMPT_FLAG <> 'E' THEN --gbgupta
824 IF px_rail_rec.TAX_CLASSIFICATION_CODE IS NULL AND px_rail_rec.TAX_EXEMPT_FLAG IS NULL THEN --gbgupta
825 l_need_header_tax := 'Y';
826 END IF;
827
828 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
829 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.tax_classification_code','Both tax classication code and tax code are null');
830 END IF;
831
832 END IF; -- of IF p_oks_rec.TAX_CLASSIFICATION_CODE IS NOT NULL THEN
833
834 IF px_rail_rec.TAX_EXEMPT_FLAG IS NULL THEN
835 l_need_header_tax := 'Y';
836 END IF;
837
838 x_need_header_tax := l_need_header_tax;
839
840
841 -------------------- BTO rule ----------------------------
842 IF px_rail_rec.BILL_TO_CUST_ACCT_ID IS NULL AND p_okc_line_rec.CUST_ACCT_ID IS NOT NULL THEN
843 px_rail_rec.BILL_TO_CUST_ACCT_ID := p_okc_line_rec.CUST_ACCT_ID;
844 END IF;
845
846 OPEN cur_address(p_okc_line_rec.BILL_TO_SITE_USE_ID, 'BILL_TO');
847 FETCH cur_address INTO address_rec;
848 IF cur_address%FOUND THEN
849 px_rail_rec.BILL_TO_SITE_USE_ID := address_rec.site_use_id;
850 px_rail_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID := address_rec.cust_account_id;
851 px_rail_rec.ORIG_SYSTEM_BILL_ADDRESS_ID := address_rec.cust_acct_site_id;
852 px_rail_rec.ORIG_SYSTEM_SOLD_CUSTOMER_ID := address_rec.cust_account_id;
853 px_rail_rec.BILL_TO_ORG_ID := address_rec.org_id;
854 END IF;
855 CLOSE cur_address;
856
857 ----------------- STO rule ------------------------------
858 OPEN cur_address(p_okc_line_rec.SHIP_TO_SITE_USE_ID, 'SHIP_TO');
859 FETCH cur_address INTO address_rec;
860 IF cur_address%FOUND THEN
861 px_rail_rec.ORIG_SYSTEM_SHIP_CUSTOMER_ID := address_rec.cust_account_id;
862 px_rail_rec.ORIG_SYSTEM_SHIP_ADDRESS_ID := address_rec.cust_acct_site_id;
863 px_rail_rec.SHIP_TO_SITE_USE_ID := address_rec.site_use_id;
864 px_rail_rec.SHIP_TO_ORG_ID := address_rec.org_id;
865 END IF;
866 CLOSE cur_address;
867
868 -------------------- PTR rule -----------------------------
869 px_rail_rec.PAYMENT_TERM_ID := p_okc_line_rec.PAYMENT_TERM_ID;
870
871
872 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
873 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status||' ,x_need_header_tax='||x_need_header_tax);
874 END IF;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
880 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
881 --first log the sqlerrm
882 l_error_text := substr (SQLERRM, 1, 512);
883 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
884 --then add it to the message api list
885 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
886 END IF;
887
888 IF cur_address%isopen THEN
889 CLOSE cur_address;
890 END IF;
891 IF tax_exempt_csr%isopen THEN
892 CLOSE tax_exempt_csr;
893 END IF;
894 IF tax_code_csr%isopen THEN
895 CLOSE tax_code_csr;
896 END IF;
897
898 RAISE;
899
900 END GET_LINE_RULES;
901
902
903 /*
904 Internal procedure to set the following attributes in px_rail_rec from the
905 SHIP_TO_SITE_USE_ID and BILL_TO_SITE_USE_ID attributes
906 SHIP_TO_POSTAL_CODE
907 SHIP_TO_LOCATION_ID
908 FOB_POINT
909 SHIP_TO_PARTY_ID
910 SHIP_TO_PARTY_SITE_ID
911 SHIP_TO_CUST_ACCT_SITE_USE_ID
912 SHIP_TO_CUST_ACCT_ID
913 SHIP_TO_CUST_ACCT_SITE_ID
914
915 BILL_TO_POSTAL_CODE
916 BILL_TO_LOCATION_ID
917 BILL_TO_PARTY_ID
918 BILL_TO_PARTY_SITE_ID
919 BILL_TO_CUST_ACCT_ID
920 BILL_TO_CUST_ACCT_SITE_ID
921 */
922 PROCEDURE TAX_INTEGRATION
923 (
924 p_chr_id IN NUMBER,
925 px_rail_rec IN OUT NOCOPY RA_REC_TYPE,
926 x_return_status OUT NOCOPY VARCHAR2
927 )
928 IS
929 l_api_name CONSTANT VARCHAR2(30) := 'TAX_INTEGRATION';
930 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
931 l_error_text VARCHAR(512);
932
933 CURSOR cur_cust_info(cp_siteuseid NUMBER) IS
934 SELECT S_SHIP.CUST_ACCT_SITE_ID,
935 S_SHIP.fob_point,
936 S_SHIP.warehouse_id,
937 ACCT_SITE_SHIP.CUST_ACCOUNT_ID,
938 ACCT_SITE_SHIP.org_id,
939 LOC_SHIP.POSTAL_CODE,
940 --LOC_ASSIGN_SHIP.LOC_ID,
941 PARTY.PARTY_NAME,
942 PARTY.party_id,
943 CUST_ACCT.ACCOUNT_NUMBER,
944 CUST_ACCT.TAX_HEADER_LEVEL_FLAG,
945 CUST_ACCT.TAX_ROUNDING_RULE,
946 CUST_ACCT.PARTY_ID CUST_ACCT_PARTY_ID,
947 PARTY_SITE_SHIP.PARTY_SITE_ID,
948 PARTY_SITE_SHIP.LOCATION_ID,
949 LOC_SHIP.STATE
950 FROM
951 HZ_CUST_SITE_USES_ALL S_SHIP,
952 HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP,
953 HZ_PARTY_SITES PARTY_SITE_SHIP,
954 HZ_LOCATIONS LOC_SHIP,
955 --HZ_LOC_ASSIGNMENTS LOC_ASSIGN_SHIP,
956 HZ_PARTIES PARTY,
957 HZ_CUST_ACCOUNTS CUST_ACCT
958 WHERE S_SHIP.SITE_USE_ID = cp_siteuseid
959 AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id
960 AND acct_site_ship.cust_account_id = cust_acct.cust_account_id
961 AND cust_acct.party_id = party.party_id
962 AND acct_site_ship.party_site_id = party_site_ship.party_site_id
963 AND party_site_ship.location_id = loc_ship.location_id;
964 --and loc_ship.location_id = loc_assign_ship.location_id;
965 CUST_INFO_REC cur_cust_info%ROWTYPE;
966
967
968 BEGIN
969
970 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
971 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin', 'px_rail_rec.SHIP_TO_SITE_USE_ID='||px_rail_rec.SHIP_TO_SITE_USE_ID||' ,px_rail_rec.BILL_TO_SITE_USE_ID='||px_rail_rec.BILL_TO_SITE_USE_ID);
972 END IF;
973
974 x_return_status := OKC_API.G_RET_STS_SUCCESS;
975
976 OPEN cur_cust_info(px_rail_rec.SHIP_TO_SITE_USE_ID);
977 FETCH cur_cust_info INTO cust_info_rec;
978 IF cur_cust_info%FOUND THEN
979 px_rail_rec.SHIP_TO_POSTAL_CODE := cust_info_rec.POSTAL_CODE;
980 px_rail_rec.SHIP_TO_LOCATION_ID := cust_info_rec.LOCATION_ID;
981 px_rail_rec.FOB_POINT := cust_info_rec.FOB_POINT;
982 px_rail_rec.SHIP_TO_PARTY_ID := cust_info_rec.CUST_ACCT_PARTY_ID;
983 px_rail_rec.SHIP_TO_PARTY_SITE_ID := cust_info_rec.PARTY_SITE_ID;
984 px_rail_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID := px_rail_rec.SHIP_TO_SITE_USE_ID;
985 px_rail_rec.SHIP_TO_CUST_ACCT_ID := cust_info_rec.CUST_ACCOUNT_ID;
986 px_rail_rec.SHIP_TO_CUST_ACCT_SITE_ID := cust_info_rec.CUST_ACCT_SITE_ID;
987 END IF;
988 CLOSE cur_cust_info;
989
990 OPEN cur_cust_info(px_rail_rec.BILL_TO_SITE_USE_ID);
991 FETCH cur_cust_info INTO cust_info_rec;
992 IF cur_cust_info%FOUND THEN
993 px_rail_rec.BILL_TO_POSTAL_CODE := cust_info_rec.POSTAL_CODE;
994 px_rail_rec.BILL_TO_LOCATION_ID := cust_info_rec.LOCATION_ID;
995 px_rail_rec.BILL_TO_PARTY_ID := cust_info_rec.CUST_ACCT_PARTY_ID;
996 px_rail_rec.BILL_TO_PARTY_SITE_ID := cust_info_rec.PARTY_SITE_ID;
997 --bill_to_cust_acct_id is needed for getting legal_entity_id later
998 --in case user does not enter any party information on the line
999 --bill_to_cust_acct_id at this point will be null
1000 --If it's not null, then it's already populated from the lines
1001 --and we do not want to overwrite that
1002 IF px_rail_rec.BILL_TO_CUST_ACCT_ID IS NULL THEN
1003 px_rail_rec.BILL_TO_CUST_ACCT_ID := cust_info_rec.CUST_ACCOUNT_ID;
1004 END IF;
1005
1006 px_rail_rec.BILL_TO_CUST_ACCT_SITE_ID := cust_info_rec.CUST_ACCT_SITE_ID;
1007 END IF;
1008 CLOSE cur_cust_info;
1009
1010 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1011 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status);
1012 END IF;
1013
1014 EXCEPTION
1015
1016 WHEN OTHERS THEN
1017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1018 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1019 --first log the sqlerrm
1020 l_error_text := substr (SQLERRM, 1, 512);
1021 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1022 --then add it to the message api list
1023 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1024 END IF;
1025
1026 IF cur_cust_info%isopen THEN
1027 CLOSE cur_cust_info;
1028 END IF;
1029 RAISE;
1030
1031 END TAX_INTEGRATION;
1032
1033 /*
1034 Internal procedure that sets the EB Tax (ZX) record structure before calling the calculate tax
1035 api
1036 */
1037
1038 PROCEDURE INIT_ZX_TRXLINE_DIST_TBL
1039 (
1040 p_rail_rec IN RA_REC_TYPE
1041 )
1042 IS
1043
1044 l_api_name CONSTANT VARCHAR2(30) := 'INIT_ZX_TRXLINE_DIST_TBL';
1045 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1046 l_error_text VARCHAR(512);
1047
1048 l_legal_entity_id NUMBER;
1049 l_bill_from_location_id NUMBER; /* added for bug8323627 */
1050
1051 BEGIN
1052 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1053 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','begin');
1054 END IF;
1055
1056 --Initialize the global PL/SQL table
1057 ZX_GLOBAL_STRUCTURES_PKG.init_trx_line_dist_tbl(1);
1058
1059 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1) := p_rail_rec.org_id;
1060 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(1) := 515;
1061 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(1) := 'OKC_K_HEADERS_B';
1062 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(1) := 'SALES_TRANSACTION_TAX_QUOTE';
1063 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(1) := 'CREATE';
1064 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(1) := p_rail_rec.header_id;
1065 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(1) := p_rail_rec.line_id;
1066 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(1) := 'LINE';
1067 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(1) := 'CREATE';
1068
1069 /*Added by nchadala on 16-JUL-2007 for Bug#6164825*/
1070 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1071 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'Added the trxtypeid to the AR dist table='||p_rail_rec.cust_trx_type_id);
1072 END IF;
1073 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RECEIVABLES_TRX_TYPE_ID(1) := p_rail_rec.cust_trx_type_id;
1074
1075 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.QUOTE_FLAG(1) := 'Y';
1076 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(1) := p_rail_rec.trx_date;
1077 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(1) := p_rail_rec.set_of_books_id;
1078
1079 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(1) := p_rail_rec.currency_code;
1080 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(1) := p_rail_rec.conversion_date;
1081 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(1) := p_rail_rec.conversion_rate;
1082 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(1) := p_rail_rec.conversion_type;
1083 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(1) := p_rail_rec.minimum_accountable_unit;
1084 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(1) := p_rail_rec.PRECISION;
1085
1086 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1087 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calling_get_legal_entity_id','p_bill_to_cust_acct_id='||p_rail_rec.bill_to_cust_acct_id||' ,p_cust_trx_type_id='||p_rail_rec.cust_trx_type_id||' ,p_org_id='||p_rail_rec.org_id);
1088 END IF;
1089
1090 l_legal_entity_id := get_legal_entity_id(
1091 p_bill_to_cust_acct_id => p_rail_rec.bill_to_cust_acct_id,
1092 p_cust_trx_type_id => p_rail_rec.cust_trx_type_id,
1093 p_org_id => p_rail_rec.org_id);
1094
1095 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1096 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_legal_entity_id','l_legal_entity_id='||l_legal_entity_id);
1097 END IF;
1098
1099 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(1) := l_legal_entity_id;
1100
1101 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_TO_PARTY_ID(1) := nvl(p_rail_rec.ship_to_party_id, p_rail_rec.bill_to_party_id);
1102 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(1) := p_rail_rec.bill_to_party_id;
1103 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_TO_PARTY_SITE_ID(1) := nvl(p_rail_rec.ship_to_party_site_id, p_rail_rec.bill_to_party_site_id);
1104 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_TO_PARTY_SITE_ID(1) := p_rail_rec.bill_to_party_site_id;
1105
1106 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_TYPE(1) := 'ITEM';
1107 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.FOB_POINT(1) := p_rail_rec.fob_point;
1108 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(1) := p_rail_rec.inventory_item_id;
1109 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ORG_ID(1) := p_rail_rec.inventory_org_id;
1110 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UOM_CODE(1) := p_rail_rec.uom_code;
1111
1112 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(1) := p_rail_rec.product_type;
1113 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(1) := p_rail_rec.amount; --nvl(p_rail_rec.price_negotiated, p_rail_rec.amount);
1114
1115 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_QUANTITY(1) := p_rail_rec.quantity;
1116 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLIED_TO_TRX_LINE_ID(1) := p_rail_rec.line_id;
1117
1118 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_CERTIFICATE_NUMBER(1) := p_rail_rec.exempt_certificate_number;
1119 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_REASON_CODE(1) := p_rail_rec.exempt_reason_code;
1120 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(1) := nvl(p_rail_rec.tax_exempt_flag, 'S'); --fix bug 4766741
1121
1122 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(1) := nvl(p_rail_rec.ship_to_location_id, p_rail_rec.bill_to_location_id);
1123 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(1) := p_rail_rec.bill_to_location_id;
1124 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(1) := nvl(p_rail_rec.ship_to_party_id, p_rail_rec.bill_to_party_id);
1125
1126 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(1) := p_rail_rec.bill_to_party_id;
1127
1128 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(1) := nvl(p_rail_rec.ship_to_party_site_id, p_rail_rec.bill_to_party_site_id);
1129 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_SITE_ID(1) := p_rail_rec.bill_to_party_site_id;
1130
1131 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_CLASS(1) := 'STANDARD';
1132 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(1) := p_rail_rec.trx_date;
1133 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(1) := p_rail_rec.tax_classification_code;
1134
1135 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_CUST_ACCT_SITE_USE_ID(1) := nvl(p_rail_rec.ship_to_cust_acct_site_use_id, p_rail_rec.bill_to_site_use_id);
1136 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_CUST_ACCT_SITE_USE_ID(1) := p_rail_rec.bill_to_site_use_id;
1137 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_ID(1) := p_rail_rec.BILL_TO_CUST_ACCT_ID;
1138 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_ID(1) := nvl(p_rail_rec.SHIP_TO_CUST_ACCT_ID, p_rail_rec.BILL_TO_CUST_ACCT_ID);
1139 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_SITE_ID(1) := p_rail_rec.BILL_TO_CUST_ACCT_SITE_ID;
1140 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(1) := p_rail_rec.SHIP_TO_CUST_ACCT_SITE_ID;
1141 /* ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG (1) := 'N';*/
1142 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG (1) := 'S'; /*Modified for bug:8307724*/
1143 /* code added for bug8323627 -- start */
1144 BEGIN
1145 SELECT location_id
1146 INTO l_bill_from_location_id
1147 FROM HR_ALL_ORGANIZATION_UNITS
1148 WHERE organization_id = p_rail_rec.org_id ;
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 l_bill_from_location_id := NULL;
1152 END;
1153 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_LOCATION_ID(1) := l_bill_from_location_id;
1154 /* code added for bug8323627 -- end */
1155
1156 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1157 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','end');
1158 END IF;
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1163 --first log the sqlerrm
1164 l_error_text := substr (SQLERRM, 1, 512);
1165 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1166 --then add it to the message api list
1167 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1168 END IF;
1169 RAISE;
1170
1171 END INIT_ZX_TRXLINE_DIST_TBL;
1172
1173
1174 ------------------------ End Internal procedures ----------------------------------
1175
1176 /*
1177 External function to get Invoice Transaction id Type for given org and/or invoice
1178 transaction type name.
1179 */
1180
1181 FUNCTION GET_CUST_TRX_TYPE_ID
1182 (
1183 p_org_id IN NUMBER,
1184 p_inv_trx_type IN VARCHAR2
1185 ) RETURN NUMBER
1186 IS
1187
1188 CURSOR cur_custtrx_type_id(cp_book_id NUMBER, cp_object1id1 NUMBER, cp_org_id NUMBER) IS
1189 SELECT cust_trx_type_id
1190 FROM RA_CUST_TRX_TYPES_ALL
1191 WHERE SET_OF_BOOKS_ID = cp_book_id
1192 AND org_id = cp_org_id
1193 AND cust_trx_type_id = NVL(cp_object1id1, -99);
1194
1195
1196 CURSOR cur_default_custtrx_type_id(cp_book_id NUMBER, cp_org_id NUMBER) IS
1197 SELECT cust_trx_type_id
1198 FROM RA_CUST_TRX_TYPES_ALL
1199 WHERE SET_OF_BOOKS_ID = cp_book_id
1200 AND org_id = cp_org_id
1201 AND TYPE = 'INV' AND name = 'Invoice-OKS' AND SYSDATE <= nvl(end_date, SYSDATE);
1202
1203 l_api_name CONSTANT VARCHAR2(30) := 'GET_CUST_TRX_TYPE_ID';
1204 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1205 l_error_text VARCHAR(512);
1206
1207 l_cust_trx_type_id NUMBER;
1208 l_set_of_books_id NUMBER;
1209
1210 BEGIN
1211
1212 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1213 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_org_id='|| p_org_id||' ,p_inv_trx_type='|| p_inv_trx_type);
1214 END IF;
1215
1216 l_cust_trx_type_id := NULL;
1217
1218 l_set_of_books_id := get_set_of_books_id(p_org_id);
1219
1220 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1221 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_set_of_books_id','l_set_of_books_id='|| l_set_of_books_id);
1222 END IF;
1223
1224 IF p_inv_trx_type IS NOT NULL THEN
1225 OPEN cur_custtrx_type_id(l_set_of_books_id, p_inv_trx_type, p_org_id);
1226 FETCH cur_custtrx_type_id INTO l_cust_trx_type_id;
1227 CLOSE cur_custtrx_type_id;
1228
1229 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1230 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.cust_trx_type','p_inv_trx_type is not null, l_cust_trx_type_id='||l_cust_trx_type_id);
1231 END IF;
1232
1233 END IF;
1234
1235 IF l_cust_trx_type_id IS NULL THEN
1236 OPEN cur_default_custtrx_type_id(l_set_of_books_id,p_org_id);
1237 FETCH cur_default_custtrx_type_id INTO l_cust_trx_type_id;
1238 CLOSE cur_default_custtrx_type_id;
1239
1240 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1241 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.default_trx_type','l_cust_trx_type_id is null, default value - l_cust_trx_type_id=' || l_cust_trx_type_id);
1242 END IF;
1243
1244 END IF;
1245
1246 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1247 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','l_cust_trx_type_id='||l_cust_trx_type_id);
1248 END IF;
1249
1250 RETURN l_cust_trx_type_id;
1251
1252 EXCEPTION
1253
1254 WHEN OTHERS THEN
1255
1256 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1257 --first log the sqlerrm
1258 l_error_text := substr (SQLERRM, 1, 512);
1259 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1260 --then add it to the message api list
1261 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1262 END IF;
1263
1264 IF cur_custtrx_type_id%isopen THEN
1265 CLOSE cur_custtrx_type_id;
1266 END IF;
1267
1268 IF cur_default_custtrx_type_id%isopen THEN
1269 CLOSE cur_default_custtrx_type_id;
1270 END IF;
1271
1272 END GET_CUST_TRX_TYPE_ID;
1273
1274 /*
1275 External function to get legal entity for a given contract.
1276 */
1277 FUNCTION GET_LEGAL_ENTITY_ID (p_chr_id IN NUMBER) RETURN NUMBER IS
1278
1279 l_api_name CONSTANT VARCHAR2(30) := 'GET_LEGAL_ENTITY_ID';
1280 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1281 l_error_text VARCHAR(512);
1282
1283 CURSOR cur_batch_source_id(cp_org_id IN NUMBER) IS
1284 SELECT BATCH_SOURCE_ID
1285 FROM ra_batch_sources_all
1286 WHERE org_id = cp_org_id
1287 AND NAME = 'OKS_CONTRACTS';
1288
1289
1290 CURSOR cur_k_header(cp_chr_id IN NUMBER) IS
1291 SELECT a.ORG_ID, a.CUST_ACCT_ID, b.INV_TRX_TYPE
1292 FROM OKC_K_HEADERS_ALL_B a, OKS_K_HEADERS_B b
1293 WHERE a.id = cp_chr_id
1294 AND b.chr_id = a.id;
1295
1296 CURSOR cur_okc_lines(cp_chr_id IN NUMBER) IS
1297 SELECT CUST_ACCT_ID
1298 FROM OKC_K_LINES_B
1299 WHERE CHR_ID = cp_chr_id;
1300
1301 l_batch_source_id NUMBER;
1302 l_legal_entity_id NUMBER;
1303 l_bill_to_cust_acct_id NUMBER;
1304 l_cust_trx_type_id NUMBER;
1305 l_org_id NUMBER;
1306 l_inv_trx_type OKS_K_HEADERS_B.inv_trx_type%TYPE;
1307
1308 l_return_status VARCHAR2(1);
1309 l_msg_data VARCHAR2(2000);
1310
1311 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1312
1313 BEGIN
1314
1315 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1316 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id='||p_chr_id);
1317 END IF;
1318
1319 OPEN cur_k_header(p_chr_id);
1320 FETCH cur_k_header INTO l_org_id, l_bill_to_cust_acct_id, l_inv_trx_type;
1321 CLOSE cur_k_header;
1322
1323 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1324 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_cur_k_header', 'l_org_id='||l_org_id||' ,l_bill_to_cust_acct_id='||l_bill_to_cust_acct_id||' ,l_inv_trx_type='||l_inv_trx_type);
1325 END IF;
1326
1327 IF l_bill_to_cust_acct_id IS NULL THEN
1328 OPEN cur_okc_lines(p_chr_id);
1329 FETCH cur_okc_lines INTO l_bill_to_cust_acct_id;
1330 CLOSE cur_okc_lines;
1331
1332 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1333 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_cur_okc_lines', 'l_bill_to_cust_acct_id='||l_bill_to_cust_acct_id);
1334 END IF;
1335 END IF;
1336
1337
1338 l_cust_trx_type_id := get_cust_trx_type_id(
1339 p_org_id => l_org_id,
1340 p_inv_trx_type => l_inv_trx_type);
1341
1342 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1343 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_get_cust_trx_type_id', 'l_cust_trx_type_id='||l_cust_trx_type_id);
1344 END IF;
1345
1346 OPEN cur_batch_source_id(l_org_id);
1347 FETCH cur_batch_source_id INTO l_batch_source_id;
1348 CLOSE cur_batch_source_id;
1349
1350 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1351 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_cur_batch_source_id', 'l_batch_source_id='||l_batch_source_id);
1352 END IF;
1353
1354 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1355 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calling_ XLE_BUSINESSINFO_GRP.get_ordertocash_info','p_customer_type: BILL_TO'||' ,p_customer_id: '|| l_bill_to_cust_acct_id||
1356 ' ,P_transaction_type_id: '|| l_cust_trx_type_id||' ,p_batch_source_id: '|| l_batch_source_id||' ,p_operating_unit_id: '|| l_org_id);
1357 END IF;
1358
1359
1360 XLE_BUSINESSINFO_GRP.get_ordertocash_info(
1361 x_return_status => l_return_status,
1362 x_msg_data => l_msg_data,
1363 p_customer_type => 'BILL_TO',
1364 p_customer_id => l_bill_to_cust_acct_id,
1365 P_transaction_type_id => l_cust_trx_type_id,
1366 p_batch_source_id => l_batch_source_id,
1367 p_operating_unit_id => l_org_id,
1368 x_otoc_le_info => l_otoc_le_info);
1369
1370 l_legal_entity_id := l_otoc_le_info.LEGAL_ENTITY_ID;
1371
1372 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1373 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_XLE_BUSINESSINFO_GRP.get_ordertocash_info','x_return_status='||l_return_status||' ,x_otoc_le_info.LEGAL_ENTITY_ID='||l_otoc_le_info.LEGAL_ENTITY_ID);
1374 END IF;
1375
1376 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1377 RAISE FND_API.g_exc_unexpected_error;
1378 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1379 RAISE FND_API.g_exc_error;
1380 END IF;
1381
1382 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1383 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','end');
1384 END IF;
1385
1386 RETURN (l_legal_entity_id);
1387
1388 EXCEPTION
1389
1390 WHEN FND_API.g_exc_error THEN
1391
1392 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1393 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'end_error');
1394 END IF;
1395
1396 IF (cur_k_header%isopen) THEN
1397 CLOSE cur_k_header;
1398 END IF;
1399 IF (cur_okc_lines%isopen) THEN
1400 CLOSE cur_okc_lines;
1401 END IF;
1402 IF (cur_batch_source_id%isopen) THEN
1403 CLOSE cur_batch_source_id;
1404 END IF;
1405
1406 WHEN FND_API.g_exc_unexpected_error THEN
1407
1408 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1409 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_unexpected_error', 'end_unexpected_error');
1410 END IF;
1411
1412 IF (cur_k_header%isopen) THEN
1413 CLOSE cur_k_header;
1414 END IF;
1415 IF (cur_okc_lines%isopen) THEN
1416 CLOSE cur_okc_lines;
1417 END IF;
1418 IF (cur_batch_source_id%isopen) THEN
1419 CLOSE cur_batch_source_id;
1420 END IF;
1421
1422 WHEN OTHERS THEN
1423
1424 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1425 --first log the sqlerrm
1426 l_error_text := substr (SQLERRM, 1, 512);
1427 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1428 --then add it to the message api list
1429 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1430 END IF;
1431
1432 IF (cur_k_header%isopen) THEN
1433 CLOSE cur_k_header;
1434 END IF;
1435 IF (cur_okc_lines%isopen) THEN
1436 CLOSE cur_okc_lines;
1437 END IF;
1438 IF (cur_batch_source_id%isopen) THEN
1439 CLOSE cur_batch_source_id;
1440 END IF;
1441
1442 END GET_LEGAL_ENTITY_ID;
1443
1444
1445 /*
1446 External function to get legal entity for a given customer, transaction type and org.
1447 */
1448
1449 FUNCTION GET_LEGAL_ENTITY_ID
1450 (
1451 p_bill_to_cust_acct_id IN NUMBER,
1452 p_cust_trx_type_id IN NUMBER,
1453 p_org_id IN NUMBER
1454 ) RETURN NUMBER IS
1455
1456 l_api_name CONSTANT VARCHAR2(30) := 'GET_LEGAL_ENTITY_ID(2)';
1457 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1458 l_error_text VARCHAR(512);
1459
1460 CURSOR cur_batch_source_id IS
1461 SELECT BATCH_SOURCE_ID
1462 FROM ra_batch_sources_all
1463 WHERE org_id = p_org_id
1464 AND NAME = 'OKS_CONTRACTS';
1465
1466 l_batch_source_id NUMBER;
1467 l_legal_entity_id NUMBER;
1468
1469 l_return_status VARCHAR2(1);
1470 l_msg_data VARCHAR2(2000);
1471
1472 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1473
1474 BEGIN
1475
1476 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1477 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_bill_to_cust_acct_id='||p_bill_to_cust_acct_id||' ,p_cust_trx_type_id='||p_cust_trx_type_id||' ,p_org_id='||p_org_id);
1478 END IF;
1479
1480
1481 OPEN cur_batch_source_id;
1482 FETCH cur_batch_source_id INTO l_batch_source_id;
1483 CLOSE cur_batch_source_id;
1484
1485 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1486 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_cur_batch_source_id', 'l_batch_source_id='||l_batch_source_id);
1487 END IF;
1488
1489 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1490 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calling_ XLE_BUSINESSINFO_GRP.get_ordertocash_info','p_customer_type: BILL_TO'||' ,p_customer_id: '|| p_bill_to_cust_acct_id||
1491 ' ,P_transaction_type_id: '|| p_cust_trx_type_id||' ,p_batch_source_id: '|| l_batch_source_id||' ,p_operating_unit_id: '|| p_org_id);
1492 END IF;
1493
1494
1495 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info(
1496 x_return_status => l_return_status,
1497 x_msg_data => l_msg_data,
1498 p_customer_type => 'BILL_TO',
1499 p_customer_id => p_bill_to_cust_acct_id,
1500 P_transaction_type_id => p_cust_trx_type_id,
1501 p_batch_source_id => l_batch_source_id,
1502 p_operating_unit_id => p_org_id,
1503 x_otoc_Le_info => l_otoc_le_info);
1504
1505 l_legal_entity_id := l_otoc_le_info.LEGAL_ENTITY_ID;
1506
1507 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1508 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_XLE_BUSINESSINFO_GRP.get_ordertocash_info','x_return_status='||l_return_status||' ,x_otoc_le_info.LEGAL_ENTITY_ID='||l_otoc_le_info.LEGAL_ENTITY_ID);
1509 END IF;
1510
1511 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1512 RAISE FND_API.g_exc_unexpected_error;
1513 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1514 RAISE FND_API.g_exc_error;
1515 END IF;
1516
1517 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1518 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','end');
1519 END IF;
1520
1521 RETURN (l_legal_entity_id);
1522
1523 EXCEPTION
1524
1525 WHEN FND_API.g_exc_error THEN
1526
1527 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1528 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'end_error');
1529 END IF;
1530
1531 IF (cur_batch_source_id%isopen) THEN
1532 CLOSE cur_batch_source_id;
1533 END IF;
1534
1535 WHEN FND_API.g_exc_unexpected_error THEN
1536
1537 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1538 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_unexpected_error', 'end_unexpected_error');
1539 END IF;
1540
1541 IF (cur_batch_source_id%isopen) THEN
1542 CLOSE cur_batch_source_id;
1543 END IF;
1544
1545 WHEN OTHERS THEN
1546
1547 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1548 --first log the sqlerrm
1549 l_error_text := substr (SQLERRM, 1, 512);
1550 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1551 --then add it to the message api list
1552 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1553 END IF;
1554
1555 IF (cur_batch_source_id%isopen) THEN
1556 CLOSE cur_batch_source_id;
1557 END IF;
1558
1559
1560 END GET_LEGAL_ENTITY_ID;
1561
1562 /*
1563 Procedure that calciulates tax for a given contract line and pupulates the
1564 results in px_rail_rec
1565
1566 Parameters
1567 p_chr_id : contract id
1568 p_cle_id : top line or subline id
1569 px_rail_rec : empty tax record structure, that is populated with tax results
1570
1571 */
1572
1573
1574
1575 PROCEDURE GET_TAX
1576 (
1577 p_api_version IN NUMBER,
1578 p_init_msg_list IN VARCHAR2,
1579 p_chr_id IN NUMBER,
1580 p_cle_id IN NUMBER,
1581 px_rail_rec IN OUT NOCOPY RA_REC_TYPE,
1582 x_msg_count OUT NOCOPY NUMBER,
1583 x_msg_data OUT NOCOPY VARCHAR2,
1584 x_return_status OUT NOCOPY VARCHAR2
1585 )
1586 IS
1587 l_api_name CONSTANT VARCHAR2(30) := 'GET_TAX';
1588 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1589 l_error_text VARCHAR(512);
1590
1591 -- Gets subline details
1592 CURSOR cur_sub_lines(cp_sub_line_id NUMBER) IS
1593 SELECT
1594 CLE_ID,
1595 PRICE_UNIT,
1596 PRICE_NEGOTIATED,
1597 --npalepu added start_date on 28-jun-2006 for bug # 5223699
1598 START_DATE
1599 --end npalepu
1600 FROM okc_k_lines_b
1601 WHERE dnz_chr_id = p_chr_id
1602 AND lse_id IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
1603 AND id = cp_sub_line_id;
1604 SUB_LINE_REC cur_sub_lines%ROWTYPE;
1605
1606 CURSOR cur_item(cp_cle_id IN NUMBER) IS
1607 SELECT a.object1_id1, a.object1_id2, jtot_object1_code,
1608 a.Number_of_items,
1609 a.UOM_code
1610 FROM OKC_K_ITEMS a
1611 WHERE a.CLE_ID = cp_cle_id;
1612 ITEM_REC cur_item%ROWTYPE;
1613
1614 CURSOR cur_tax_info(cp_trx_id NUMBER) IS
1615 SELECT
1616 tax_rate,
1617 tax_amt,
1618 tax_amt_included_flag,
1619 tax_rate_code
1620 FROM zx_detail_tax_lines_gt
1621 WHERE application_id = 515
1622 AND entity_code = 'OKC_K_HEADERS_B'
1623 AND event_class_code = 'SALES_TRANSACTION_TAX_QUOTE'
1624 AND trx_id = cp_trx_id;
1625 TAX_REC cur_tax_info%ROWTYPE;
1626
1627 CURSOR cur_get_precision(cp_currency_code VARCHAR2) IS
1628 SELECT c.minimum_accountable_unit, c.precision
1629 FROM FND_CURRENCIES C
1630 WHERE c.currency_code = cp_currency_code;
1631 PRECISION_REC cur_get_precision%ROWTYPE;
1632
1633 CURSOR get_operating_unit(cp_org_id NUMBER) IS
1634 SELECT name
1635 FROM hr_all_organization_units_tl -- Bug 5036523 hr_operating_units
1636 WHERE organization_id = cp_org_id;
1637
1638 G_RAIL_REC OKS_TAX_UTIL_PVT.ra_rec_type;
1639 l_okc_hdr_rec okc_hdr_csr%ROWTYPE;
1640 l_oks_hdr_rec oks_hdr_csr%ROWTYPE;
1641 l_okc_line_rec okc_line_csr%ROWTYPE;
1642 l_oks_line_rec oks_line_csr%ROWTYPE;
1643
1644 l_parent_cle_id NUMBER;
1645 l_line_amount NUMBER := px_rail_rec.amount;
1646
1647 transaction_rec ZX_API_PUB.transaction_rec_type;
1648 l_doc_level_recalc_flag ZX_LINES_DET_FACTORS.threshold_indicator_flag%TYPE;
1649
1650 l_default_tax_code VARCHAR2(50); /* npalepu 12-jul-2006, changed the tax_code field length from 30 to 50 for bug # 5380870 */
1651 l_need_header_tax VARCHAR2(1);
1652 l_op_unit_name hr_all_organization_units_tl.name%TYPE; --Bug 5036523 hr_operating_units.name%TYPE;
1653
1654 --npalepu added on 28-jun-2006 for bug # 5223699
1655 l_subline_start_date okc_k_lines_b.start_date%TYPE;
1656 --end npalepu
1657
1658 --npalepu added on 11-jul-2006 for bug # 5380881
1659 CURSOR Cur_Batch_Source_Id(p_org_id IN NUMBER)
1660 IS
1661 SELECT BATCH_SOURCE_ID
1662 FROM ra_batch_sources_all
1663 WHERE org_id = p_org_id
1664 AND NAME = 'OKS_CONTRACTS';
1665
1666 l_msg_count NUMBER;
1667 l_msg_data VARCHAR2(2000);
1668 l_ret_stat VARCHAR2(20);
1669 l_valid_flag VARCHAR2(3) := 'N';
1670 l_batch_source_id NUMBER;
1671 --end npalepu
1672
1673 BEGIN
1674
1675 --npalepu removed the following code on 20-jun-2006 for bug # 5335312.
1676 --Reverting back the changes made for the bug # 5292938
1677 /*
1678 --npalepu added on 6/9/2006 for bug # 5292938
1679 --setting the context to contract context, only if no org context is set
1680 IF mo_global.get_current_org_id IS NULL THEN
1681 okc_context.set_okc_org_context(p_chr_id => p_chr_id);
1682 END IF;
1683 */
1684 --end npalepu
1685
1686 --0. log the input details
1687 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1688 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.begin','p_chr_id='||p_chr_id||' ,p_cle_id= '||p_cle_id||' ,line_amount='||l_line_amount);
1689 print_g_rail_rec(
1690 p_rail_rec => px_rail_rec,
1691 p_msg => 'Begin get_tax, PX_RAIL_REC details',
1692 p_level => FND_LOG.level_procedure);
1693 END IF;
1694
1695 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1696 IF(FND_API.G_TRUE = p_init_msg_list) THEN
1697 FND_MSG_PUB.initialize;
1698 END IF;
1699
1700 --1. get hdr details from okc table and do basic id validation
1701 OPEN okc_hdr_csr(p_chr_id);
1702 FETCH okc_hdr_csr INTO l_okc_hdr_rec;
1703 IF (okc_hdr_csr%notfound) THEN
1704 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_INV_CONTRACT');
1705 FND_MESSAGE.set_token('CONTRACT_ID', p_chr_id);
1706 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1707 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.basic_validation', FALSE);
1708 END IF;
1709 FND_MSG_PUB.ADD;
1710 CLOSE okc_hdr_csr;
1711 RAISE FND_API.g_exc_error;
1712 END IF;
1713 CLOSE okc_hdr_csr;
1714
1715 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1716 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_okc_hdr_csr', 'ok');
1717 END IF;
1718
1719 G_RAIL_REC.HEADER_ID := p_chr_id;
1720 G_RAIL_REC.LINE_ID := p_cle_id;
1721 G_RAIL_REC.ORG_ID := l_okc_hdr_rec.org_id;
1722 G_RAIL_REC.INVENTORY_ORG_ID := l_okc_hdr_rec.inv_organization_id;
1723 G_RAIL_REC.CURRENCY_CODE := l_okc_hdr_rec.currency_code;
1724 G_RAIL_REC.PURCHASE_ORDER := l_okc_hdr_rec.cust_po_number;
1725 G_RAIL_REC.SET_OF_BOOKS_ID := get_set_of_books_id(l_okc_hdr_rec.org_id);
1726 G_RAIL_REC.AMOUNT := nvl(l_okc_hdr_rec.estimated_amount, 0);
1727
1728 --2. get hdr details from oks table
1729 OPEN oks_hdr_csr(p_chr_id);
1730 FETCH oks_hdr_csr INTO l_oks_hdr_rec;
1731 CLOSE oks_hdr_csr;
1732
1733 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1734 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_oks_hdr_csr', 'ok');
1735 END IF;
1736
1737 --3. populate hdr rules in G_RAIL_REC
1738 get_hdr_rules(
1739 p_oks_rec => l_oks_hdr_rec,
1740 p_okc_rec => l_okc_hdr_rec,
1741 px_rail_rec => G_RAIL_REC,
1742 x_return_status => x_return_status);
1743
1744 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1745 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_hdr_rules', 'x_return_status='||x_return_status);
1746 print_g_rail_rec(
1747 p_rail_rec => G_RAIL_REC,
1748 p_msg => 'After calling get_hdr_rules, G_RAIL_REC details',
1749 p_level => FND_LOG.level_statement);
1750 END IF;
1751
1752
1753 --4. find out if p_cle_id is top line (for subscription) or sub line id
1754 OPEN cur_sub_lines(p_cle_id);
1755 FETCH cur_sub_lines INTO sub_line_rec;
1756 IF cur_sub_lines%FOUND THEN
1757 l_parent_cle_id := sub_line_rec.cle_id; -- This is when the given line is a subline
1758 --npalepu added on 28-jun-2006 for bug # 5223699
1759 l_subline_start_date := sub_line_rec.start_date;
1760 --end npalepu
1761 ELSE
1762 l_parent_cle_id := p_cle_id; -- This is when the given line is a top line
1763 --npalepu added on 28-jun-2006 for bug # 5223699
1764 l_subline_start_date := null;
1765 --end npalepu
1766 END IF;
1767 CLOSE cur_sub_lines;
1768
1769 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1770 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_cur_sub_lines', 'p_cle_id='||p_cle_id||' ,l_parent_cle_id='||l_parent_cle_id);
1771 END IF;
1772
1773 --5. All tax details except amount are at the topline level
1774 -- get the topline details from okc table
1775 OPEN okc_line_csr(p_chr_id, l_parent_cle_id);
1776 FETCH okc_line_csr INTO l_okc_line_rec;
1777 IF okc_line_csr%FOUND THEN
1778 G_RAIL_REC.UNIT_SELLING_PRICE := nvl(l_okc_line_rec.price_unit, l_okc_line_rec.price_negotiated);
1779 G_RAIL_REC.AMOUNT := nvl(l_okc_line_rec.price_negotiated, 0);
1780
1781 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1782 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_line_open_oks_line_csr', 'line id='||l_okc_line_rec.id);
1783 END IF;
1784
1785 --6. get the topline details from oks table
1786 OPEN oks_line_csr(p_chr_id, l_okc_line_rec.id);
1787 FETCH oks_line_csr INTO l_oks_line_rec;
1788 CLOSE oks_line_csr;
1789
1790 --npalepu added on 28-jun-2006 for bug # 5223699
1791 --Except for subscription, over riding the top line start_date with sub line start_date
1792 IF l_subline_start_date is not null then
1793 l_okc_line_rec.start_date := l_subline_start_date;
1794 end if;
1795 --end npalepu
1796
1797 --7. populate line rules in G_RAIL_REC, also figure out if some rules need to be
1798 --defaulted from header
1799 get_line_rules(
1800 p_oks_line_rec => l_oks_line_rec,
1801 p_okc_line_rec => l_okc_line_rec,
1802 px_rail_rec => G_RAIL_REC,
1803 x_return_status => x_return_status,
1804 x_need_header_tax => l_need_header_tax);
1805
1806 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1807 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_line_rules', 'x_return_status='||x_return_status||' ,l_need_header_tax='||l_need_header_tax);
1808 print_g_rail_rec(
1809 p_rail_rec => G_RAIL_REC,
1810 p_msg => 'After calling get_line_rules, G_RAIL_REC details',
1811 p_level => FND_LOG.level_statement);
1812 END IF;
1813
1814 --8. get the item details
1815 OPEN cur_item(l_okc_line_rec.id);
1816 FETCH cur_item INTO item_rec;
1817 IF cur_item%FOUND THEN
1818 G_RAIL_REC.INVENTORY_ITEM_id := item_rec.object1_id1;
1819 G_RAIL_REC.QUANTITY := item_rec.Number_of_items;
1820 G_RAIL_REC.UOM_CODE := item_rec.UOM_code;
1821
1822 --bug 5193041, commented after request from EB Tax team
1823 /*
1824 OPEN Cur_prod_type(G_RAIL_REC.INVENTORY_ITEM_ID, G_RAIL_REC.ORG_ID);
1825 FETCH Cur_prod_type INTO l_product_type;
1826 G_RAIL_REC.PRODUCT_TYPE := l_product_type;
1827 CLOSE Cur_prod_type;
1828 */
1829 END IF;
1830 CLOSE Cur_item;
1831
1832 IF l_need_header_tax = 'Y' THEN
1833
1834 --9. Get header tax info such as tax classification code,
1835 --exemption details, if not present at lines
1836 get_hdr_tax(
1837 p_oks_rec => l_oks_hdr_rec,
1838 p_okc_rec => l_okc_hdr_rec,
1839 p_okc_line_rec => l_okc_line_rec,
1840 px_rail_rec => G_RAIL_REC,
1841 x_return_status => x_return_status);
1842
1843 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1844 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_get_hdr_tax', 'x_return_status='||x_return_status);
1845 print_g_rail_rec(
1846 p_rail_rec => G_RAIL_REC,
1847 p_msg => 'After calling get_hdr_tax on top line, G_RAIL_REC details',
1848 p_level => FND_LOG.level_statement);
1849 END IF;
1850
1851 END IF; --of IF l_need_header_tax = 'Y' THEN
1852
1853 END IF; --of IF okc_line_csr%FOUND THEN - TOPLINE CSR
1854 CLOSE okc_line_csr;
1855
1856
1857 --10. if p_cle_id is that of a subline, get the subline amount and unit selling price,
1858 --else get the topline values
1859 IF (nvl(l_parent_cle_id, -99) = p_cle_id) THEN
1860
1861 --p_cle_id is id of topline, do nothing
1862 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1863 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.amount','p_cle_id='||p_cle_id||' is topline id');
1864 END IF;
1865
1866 ELSE
1867
1868 --p_cle_id is id of subline, get the subline amount
1869 G_RAIL_REC.UNIT_SELLING_PRICE := nvl(sub_line_rec.price_unit, sub_line_rec.price_negotiated);
1870 G_RAIL_REC.AMOUNT := nvl(sub_line_rec.price_negotiated, 0);
1871
1872 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1873 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.amount','p_cle_id='||p_cle_id||' is subline id, sub_line_rec.price_negotiated='||sub_line_rec.price_negotiated);
1874 END IF;
1875
1876 END IF;
1877
1878
1879 --11. set other misc attributes
1880 G_RAIL_REC.WAREHOUSE_ID := G_RAIL_REC.INVENTORY_ORG_ID; -- added for Vertex
1881
1882 OPEN cur_get_precision(G_RAIL_REC.CURRENCY_CODE);
1883 FETCH cur_get_precision INTO precision_rec;
1884 IF cur_get_precision%FOUND THEN
1885 G_RAIL_REC.MINIMUM_ACCOUNTABLE_UNIT := precision_rec.MINIMUM_ACCOUNTABLE_UNIT;
1886 G_RAIL_REC.PRECISION := precision_rec.PRECISION;
1887 END IF;
1888 CLOSE cur_get_precision;
1889
1890 --12. if the calling module specifies an amount, use that instead of the line amount from DB.
1891 IF l_line_amount IS NOT NULL THEN
1892 G_RAIL_REC.AMOUNT := l_line_amount;
1893 END IF;
1894
1895
1896
1897 --13. check to trx type
1898 --Added for tax bug # 4026206, trx type must be defined on contract or for org
1899 IF G_RAIL_REC.CUST_TRX_TYPE_ID IS NULL THEN
1900 OPEN get_operating_unit(G_RAIL_REC.ORG_ID);
1901 FETCH get_operating_unit INTO l_op_unit_name;
1902 CLOSE get_operating_unit;
1903
1904 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_NO_TRX_TYPE');
1905 FND_MESSAGE.set_token('NAME', l_op_unit_name);
1906 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1907 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.no_trx_type', FALSE);
1908 END IF;
1909 FND_MSG_PUB.ADD;
1910 RAISE FND_API.g_exc_error;
1911 END IF;
1912 -- End of added for tax bug # 4026206
1913
1914
1915 --14. populate transaction record
1916 transaction_rec.internal_organization_id := l_okc_hdr_rec.ORG_ID;
1917 transaction_rec.application_id := 515;
1918 transaction_rec.entity_code := 'OKC_K_HEADERS_B';
1919 transaction_rec.event_class_code := 'SALES_TRANSACTION_TAX_QUOTE';
1920 transaction_rec.event_type_code := 'CREATE';
1921 transaction_rec.trx_id := l_okc_hdr_rec.id;
1922
1923
1924 --15. populate hz-related information
1925 tax_integration(
1926 p_chr_id => p_chr_id,
1927 px_rail_rec => G_RAIL_REC,
1928 x_return_status => x_return_status);
1929
1930 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1931 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.after_tax_integration', 'x_return_status='||x_return_status);
1932 END IF;
1933
1934 --in case the exemption control is null, we default it to 'S'
1935 G_RAIL_REC.tax_exempt_flag := nvl(G_RAIL_REC.tax_exempt_flag, 'S');
1936
1937 --npalepu added on 11-jul-2006 for bug # 5380881
1938 --validating tax_exemptions
1939 IF G_RAIL_REC.EXEMPT_CERTIFICATE_NUMBER IS NOT NULL THEN
1940 BEGIN
1941 OPEN Cur_Batch_Source_Id(G_RAIL_REC.org_id);
1942 FETCH Cur_Batch_Source_Id INTO l_batch_source_id;
1943 CLOSE Cur_Batch_Source_Id;
1944
1945 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1946 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.validating_exemptions','calling ZX_TCM_VALIDATE_EXEMPT_PKG.VALIDATE_TAX_EXEMPTIONS to validate tax exemptions'||
1947 ' ,p_tax_exempt_number='|| G_RAIL_REC.EXEMPT_CERTIFICATE_NUMBER ||
1948 ' ,p_tax_exempt_reason_code='|| G_RAIL_REC.EXEMPT_REASON_CODE ||
1949 ' ,p_ship_to_org_id='|| G_RAIL_REC.ship_to_site_use_id ||
1950 ' ,p_invoice_to_org_id='|| G_RAIL_REC.bill_to_site_use_id ||
1951 ' ,p_bill_to_cust_account_id='|| G_RAIL_REC.BILL_TO_CUST_ACCT_ID ||
1952 ' ,p_ship_to_party_site_id='|| G_RAIL_REC.SHIP_TO_PARTY_SITE_ID ||
1953 ' ,p_bill_to_party_site_id='|| G_RAIL_REC.BILL_TO_PARTY_SITE_ID ||
1954 ' ,p_org_id='|| G_RAIL_REC.org_id ||
1955 ' ,p_bill_to_party_id='|| G_RAIL_REC.BILL_TO_PARTY_ID ||
1956 ' ,p_legal_entity_id='|| NULL ||
1957 ' ,p_trx_type_id='|| G_RAIL_REC.CUST_TRX_TYPE_ID ||
1958 ' ,p_batch_source_id='|| l_batch_source_id ||
1959 ' ,p_trx_date='|| G_RAIL_REC.trx_date ||
1960 ' ,p_exemption_status='|| 'PMU' );
1961 END IF;
1962
1963 ZX_TCM_VALIDATE_EXEMPT_PKG.VALIDATE_TAX_EXEMPTIONS
1964 (p_tax_exempt_number => G_RAIL_REC.EXEMPT_CERTIFICATE_NUMBER,
1965 p_tax_exempt_reason_code => G_RAIL_REC.EXEMPT_REASON_CODE,
1966 p_ship_to_org_id => G_RAIL_REC.ship_to_site_use_id,
1967 p_invoice_to_org_id => G_RAIL_REC.bill_to_site_use_id,
1968 p_bill_to_cust_account_id => G_RAIL_REC.BILL_TO_CUST_ACCT_ID,
1969 p_ship_to_party_site_id => G_RAIL_REC.SHIP_TO_PARTY_SITE_ID,
1970 p_bill_to_party_site_id => G_RAIL_REC.BILL_TO_PARTY_SITE_ID,
1971 p_org_id => G_RAIL_REC.org_id,
1972 p_bill_to_party_id => G_RAIL_REC.BILL_TO_PARTY_ID,
1973 p_legal_entity_id => NULL,
1974 p_trx_type_id => G_RAIL_REC.CUST_TRX_TYPE_ID,
1975 p_batch_source_id => l_batch_source_id,
1976 p_trx_date => G_RAIL_REC.trx_date,
1977 p_exemption_status => 'PMU',
1978 x_valid_flag => l_valid_flag,
1979 x_return_status => l_ret_stat,
1980 x_msg_count => l_msg_count,
1981 x_msg_data => l_msg_data);
1982
1983 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1984 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.After_VALIDATE_TAX_EXEMPTIONS', 'l_valid_flag='||l_valid_flag);
1985 End If;
1986
1987 IF l_valid_flag <> 'Y' THEN
1988 --reset the tax_exempt_flag to 'S'
1989 G_RAIL_REC.TAX_EXEMPT_FLAG := 'S';
1990 G_RAIL_REC.EXEMPT_CERTIFICATE_NUMBER := NULL;
1991 G_RAIL_REC.EXEMPT_REASON_CODE := NULL;
1992 IF l_oks_line_rec.TAX_EXEMPTION_ID IS NOT NULL THEN
1993 G_RAIL_REC.tax_classification_code := NULL;
1994 ELSIF l_need_header_tax = 'Y' AND l_oks_hdr_rec.TAX_EXEMPTION_ID IS NOT NULL THEN
1995 G_RAIL_REC.tax_classification_code := NULL;
1996 END IF;
1997 END IF;
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000
2001 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2002 --first log the sqlerrm
2003 l_error_text := substr (SQLERRM, 1, 512);
2004 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.exemption_validation_error', l_error_text);
2005 --then add it to the message api list
2006 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
2007 END IF;
2008 IF Cur_Batch_Source_Id%ISOPEN THEN
2009 CLOSE Cur_Batch_Source_Id;
2010 END IF;
2011 RAISE FND_API.g_exc_unexpected_error;
2012 END;
2013 END IF;
2014 --end npalepu
2015
2016 --16. get the default tcc if null
2017 --fix bug 4952080, need to call the defaulting logic if tax_classification_code is null
2018 IF G_RAIL_REC.tax_classification_code IS NULL THEN
2019 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2020 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.default_tcc','calling ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification to get default tcc'||
2021 ' ,p_ship_to_site_use_id='|| G_RAIL_REC.ship_to_site_use_id ||
2022 ' ,p_bill_to_site_use_id='|| G_RAIL_REC.bill_to_site_use_id ||
2023 ' ,p_inventory_item_id='|| G_RAIL_REC.inventory_item_id ||
2024 ' ,p_organization_id='|| G_RAIL_REC.org_id ||
2025 ' ,p_set_of_books_id='|| G_RAIL_REC.set_of_books_id ||
2026 ' ,p_trx_date='|| trunc(G_RAIL_REC.trx_date) ||
2027 ' ,p_trx_type_id='|| G_RAIL_REC.cust_trx_type_id ||
2028 ' ,p_internal_organization_id='|| G_RAIL_REC.org_id);
2029 END IF;
2030
2031 --as per zx documentation,
2032 --the api ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification
2033 --will either return a tcc or an exception
2034 BEGIN
2035
2036 ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification(
2037 p_ship_to_site_use_id => G_RAIL_REC.ship_to_site_use_id,
2038 p_bill_to_site_use_id => G_RAIL_REC.bill_to_site_use_id,
2039 p_inventory_item_id => G_RAIL_REC.inventory_item_id,
2040 p_organization_id => G_RAIL_REC.org_id,
2041 p_set_of_books_id => G_RAIL_REC.set_of_books_id,
2042 p_trx_date => trunc(G_RAIL_REC.trx_date),
2043 p_trx_type_id => G_RAIL_REC.cust_trx_type_id,
2044 p_tax_classification_code => l_default_tax_code,
2045 appl_short_name => 'OKS',
2046 p_entity_code => 'OKC_K_HEADERS_B',
2047 p_event_class_code => 'SALES_TRANSACTION_TAX_QUOTE',
2048 p_application_id => 515,
2049 p_internal_organization_id => G_RAIL_REC.org_id);
2050
2051 EXCEPTION
2052 WHEN OTHERS THEN
2053
2054 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2055 --first log the sqlerrm
2056 l_error_text := substr (SQLERRM, 1, 512);
2057 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.default_tcc_error', l_error_text);
2058 --then add it to the message api list
2059 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
2060 END IF;
2061 RAISE FND_API.g_exc_unexpected_error;
2062
2063 END; --of inner block
2064
2065 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2066 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.default_tcc','after call to ZX_AR_TAX_CLASSIFICATN_DEF_PKG.get_default_tax_classification, p_tax_classification_code='||l_default_tax_code);
2067 END IF;
2068
2069 G_RAIL_REC.tax_classification_code := l_default_tax_code;
2070
2071 END IF; --of IF G_RAIL_REC.tax_classification_code IS NULL
2072 --end of bug 4952080
2073
2074
2075 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2076 print_g_rail_rec(
2077 p_rail_rec => G_RAIL_REC,
2078 p_msg => 'Final state of G_RAIL_REC details, before calling ZX_API_PUB.calculate_tax',
2079 p_level => FND_LOG.level_statement);
2080 END IF;
2081
2082
2083 --17. populate pl/sql global table
2084 init_zx_trxline_dist_tbl(p_rail_rec => G_RAIL_REC);
2085
2086
2087 --18. finally call the zx api to calculate tax
2088 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2089 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calculate_tax','calling ZX_API_PUB.calculate_tax, p_quote_flag=Y, p_data_transfer_mode=PLS, p_validation_level=FND_API.G_VALID_LEVEL_FULL, transaction_rec_details.'||
2090 ' ,trec.internal_organization_id='||transaction_rec.internal_organization_id||
2091 ' ,trec.application_id='||transaction_rec.application_id||
2092 ' ,trec.entity_code='||transaction_rec.entity_code||
2093 ' ,trec.event_class_code='||transaction_rec.event_class_code||
2094 ' ,trec.event_type_code='||transaction_rec.event_type_code||
2095 ' ,trec.trx_id='||transaction_rec.trx_id);
2096 END IF;
2097
2098 ZX_API_PUB.calculate_tax(
2099 p_api_version => p_api_version,
2100 p_init_msg_list => p_init_msg_list,
2101 p_commit => FND_API.G_FALSE,
2102 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2103 x_return_status => x_return_status,
2104 x_msg_count => x_msg_count,
2105 x_msg_data => x_msg_data,
2106 p_transaction_rec => transaction_rec,
2107 p_quote_flag => 'Y',
2108 p_data_transfer_mode => 'PLS',
2109 x_doc_level_recalc_flag => l_doc_level_recalc_flag);
2110
2111 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2112 FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.calculate_tax','after call to ZX_API_PUB.calculate_tax, x_return_status='||x_return_status||' ,x_doc_level_recalc_flag='||l_doc_level_recalc_flag);
2113 END IF;
2114
2115 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2116 RAISE FND_API.g_exc_unexpected_error;
2117 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
2118 RAISE FND_API.g_exc_error;
2119 END IF;
2120
2121 --19. get the calculated tax value
2122 --query to get the calculated tax from the global temp table
2123 G_RAIL_REC.TAX_VALUE := 0;
2124
2125 OPEN cur_tax_info(l_okc_hdr_rec.id);
2126 LOOP
2127 FETCH cur_tax_info INTO tax_rec;
2128 EXIT WHEN cur_tax_info%NOTFOUND;
2129
2130 G_RAIL_REC.TAX_VALUE := G_RAIL_REC.TAX_VALUE + nvl(tax_rec.tax_amt,0);
2131 G_RAIL_REC.TAX_RATE := tax_rec.tax_rate;
2132 G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG := tax_rec.tax_amt_included_flag;
2133
2134 END LOOP;
2135 CLOSE cur_tax_info;
2136
2137 IF NVL(G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG, 'N') = 'N' THEN
2138 G_RAIL_REC.TOTAL_PLUS_TAX := NVL(G_RAIL_REC.AMOUNT, 0) + NVL(G_RAIL_REC.TAX_VALUE, 0);
2139 G_RAIL_REC.AMOUNT_INCLUDES_TAX_FLAG := 'N';
2140 ELSE
2141 G_RAIL_REC.TOTAL_PLUS_TAX := NVL(G_RAIL_REC.AMOUNT, 0);
2142 END IF;
2143 px_rail_rec := G_RAIL_REC;
2144
2145
2146 --20. log the tax details and exit
2147 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2148 FND_LOG.string(FND_LOG.level_procedure, l_mod_name||'.end','x_return_status='||x_return_status||' ,px_rail_rec(key details). '||
2149 ' .AMOUNT=' || px_rail_rec.AMOUNT ||
2150 ' .TAX_VALUE=' || px_rail_rec.TAX_VALUE ||
2151 ' .TAX_RATE=' || px_rail_rec.TAX_RATE ||
2152 ' .AMOUNT_INCLUDES_TAX_FLAG=' || px_rail_rec.AMOUNT_INCLUDES_TAX_FLAG ||
2153 ' .TOTAL_PLUS_TAX=' || px_rail_rec.TOTAL_PLUS_TAX);
2154
2155 print_g_rail_rec(
2156 p_rail_rec => px_rail_rec,
2157 p_msg => 'End get_tax, PX_RAIL_REC details',
2158 p_level => FND_LOG.level_procedure);
2159 END IF;
2160 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2161
2162
2163 EXCEPTION
2164 WHEN FND_API.g_exc_error THEN
2165 x_return_status := FND_API.G_RET_STS_ERROR ;
2166
2167 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
2168 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
2169 END IF;
2170 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2171
2172 IF okc_hdr_csr%ISOPEN THEN
2173 CLOSE okc_hdr_csr;
2174 END IF;
2175
2176 IF oks_hdr_csr%ISOPEN THEN
2177 CLOSE oks_hdr_csr;
2178 END IF;
2179
2180 IF okc_line_csr%ISOPEN THEN
2181 CLOSE okc_line_csr;
2182 END IF;
2183
2184 IF oks_line_csr%ISOPEN THEN
2185 CLOSE oks_line_csr;
2186 END IF;
2187
2188 IF cur_sub_lines%ISOPEN THEN
2189 CLOSE cur_sub_lines;
2190 END IF;
2191
2192 IF cur_item%ISOPEN THEN
2193 CLOSE cur_item;
2194 END IF;
2195
2196 IF cur_get_precision%ISOPEN THEN
2197 CLOSE cur_get_precision;
2198 END IF;
2199
2200 IF get_operating_unit%ISOPEN THEN
2201 CLOSE get_operating_unit;
2202 END IF;
2203
2204 IF cur_tax_info%ISOPEN THEN
2205 CLOSE cur_tax_info;
2206 END IF;
2207
2208 --npalepu added on 11-jul-2006 for bug # 5380881
2209 IF Cur_Batch_Source_Id%ISOPEN THEN
2210 CLOSE Cur_Batch_Source_Id;
2211 END IF;
2212 --end npalepu
2213
2214 WHEN FND_API.g_exc_unexpected_error THEN
2215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2216
2217 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2218 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
2219 END IF;
2220 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2221
2222 IF okc_hdr_csr%ISOPEN THEN
2223 CLOSE okc_hdr_csr;
2224 END IF;
2225
2226 IF oks_hdr_csr%ISOPEN THEN
2227 CLOSE oks_hdr_csr;
2228 END IF;
2229
2230 IF okc_line_csr%ISOPEN THEN
2231 CLOSE okc_line_csr;
2232 END IF;
2233
2234 IF oks_line_csr%ISOPEN THEN
2235 CLOSE oks_line_csr;
2236 END IF;
2237
2238 IF cur_sub_lines%ISOPEN THEN
2239 CLOSE cur_sub_lines;
2240 END IF;
2241
2242 IF cur_item%ISOPEN THEN
2243 CLOSE cur_item;
2244 END IF;
2245
2246 IF cur_get_precision%ISOPEN THEN
2247 CLOSE cur_get_precision;
2248 END IF;
2249
2250 IF get_operating_unit%ISOPEN THEN
2251 CLOSE get_operating_unit;
2252 END IF;
2253
2254 IF cur_tax_info%ISOPEN THEN
2255 CLOSE cur_tax_info;
2256 END IF;
2257
2258 --npalepu added on 11-jul-2006 for bug # 5380881
2259 IF Cur_Batch_Source_Id%ISOPEN THEN
2260 CLOSE Cur_Batch_Source_Id;
2261 END IF;
2262 --end npalepu
2263
2264 WHEN OTHERS THEN
2265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2266
2267 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
2268 --first log the sqlerrm
2269 l_error_text := substr (SQLERRM, 1, 240);
2270 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
2271 --then add it to the message api list
2272 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
2273 END IF;
2274 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
2275
2276 IF okc_hdr_csr%ISOPEN THEN
2277 CLOSE okc_hdr_csr;
2278 END IF;
2279
2280 IF oks_hdr_csr%ISOPEN THEN
2281 CLOSE oks_hdr_csr;
2282 END IF;
2283
2284 IF okc_line_csr%ISOPEN THEN
2285 CLOSE okc_line_csr;
2286 END IF;
2287
2288 IF oks_line_csr%ISOPEN THEN
2289 CLOSE oks_line_csr;
2290 END IF;
2291
2292 IF cur_sub_lines%ISOPEN THEN
2293 CLOSE cur_sub_lines;
2294 END IF;
2295
2296 IF cur_item%ISOPEN THEN
2297 CLOSE cur_item;
2298 END IF;
2299
2300 IF cur_get_precision%ISOPEN THEN
2301 CLOSE cur_get_precision;
2302 END IF;
2303
2304 IF get_operating_unit%ISOPEN THEN
2305 CLOSE get_operating_unit;
2306 END IF;
2307
2308 IF cur_tax_info%ISOPEN THEN
2309 CLOSE cur_tax_info;
2310 END IF;
2311
2312 --npalepu added on 11-jul-2006 for bug # 5380881
2313 IF Cur_Batch_Source_Id%ISOPEN THEN
2314 CLOSE Cur_Batch_Source_Id;
2315 END IF;
2316 --end npalepu
2317
2318 END GET_TAX;
2319
2320
2321
2322 /*
2323 This is a concurrent program to migrate the pre-R12 tax data
2324 to R12 eBTax. We do not remove the old values.
2325 OKS_K_HEADERS_B:
2326 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
2327
2328 OKS_K_HEADERS_BH:
2329 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
2330 */
2331 PROCEDURE TAX_MIGRATION
2332 (
2333 ERRBUF OUT NOCOPY VARCHAR2,
2334 RETCODE OUT NOCOPY NUMBER
2335 ) IS
2336
2337 l_module_name CONSTANT VARCHAR2(30) := 'TAX_MIGRATION';
2338
2339 BEGIN
2340 RETCODE := 0; --0 for success, 1 for warning, 2 for error
2341 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting Concurrent Program: '|| G_PKG_NAME || '.' || l_module_name);
2342 FND_FILE.PUT_LINE(FND_FILE.LOG,'time: '|| to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2343
2344 --migrate the header level tax_exemption_id to exempt_certificate_number and exempt_reason_code
2345 --npalepu updating tax_code and tax_exemption_id to null for bug # 4908543
2346 UPDATE /*+ parallel(oks) */ OKS_K_HEADERS_B oks
2347 SET (EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE)
2348 = (SELECT zx.EXEMPT_CERTIFICATE_NUMBER, zx.EXEMPT_REASON_CODE
2349 FROM ZX_EXEMPTIONS zx
2350 WHERE zx.TAX_EXEMPTION_ID = oks.tax_exemption_id)
2351 ,TAX_EXEMPTION_ID = NULL
2352 ,TAX_CODE = NULL
2353 WHERE oks.tax_exemption_id IS NOT NULL
2354 AND (oks.EXEMPT_CERTIFICATE_NUMBER IS NULL OR oks.EXEMPT_REASON_CODE IS NULL);
2355 --end npalepu
2356
2357 FND_FILE.PUT_LINE(FND_FILE.LOG,'Finished migrating tax_exemption_id in OKS_K_HEADERS_B');
2358 FND_FILE.PUT_LINE(FND_FILE.LOG,'time: '|| to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2359
2360 /*npalepu added migration of tax_exemption_id to exempt_certificate_number and exempt_reason_code in oks_k_headers_bh table.
2361 updating tax_code and tax_exemption_id to null for bug # 4908543 */
2362 UPDATE /*+ parallel(oks) */ OKS_K_HEADERS_BH oks
2363 SET (EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE)
2364 = (SELECT zx.EXEMPT_CERTIFICATE_NUMBER, zx.EXEMPT_REASON_CODE
2365 FROM ZX_EXEMPTIONS zx
2366 WHERE zx.TAX_EXEMPTION_ID = oks.tax_exemption_id)
2367 ,TAX_EXEMPTION_ID = NULL
2368 ,TAX_CODE = NULL
2369 WHERE oks.tax_exemption_id IS NOT NULL
2370 AND (oks.EXEMPT_CERTIFICATE_NUMBER IS NULL OR oks.EXEMPT_REASON_CODE IS NULL);
2371 --end npalepu
2372
2373 FND_FILE.PUT_LINE(FND_FILE.LOG,'Finished migrating tax_exemption_id in OKS_K_HEADERS_BH');
2374 FND_FILE.PUT_LINE(FND_FILE.LOG,'time: '|| to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2375 --end npalepu
2376
2377 COMMIT;
2378
2379 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Concurrent Program - Success, time: '|| to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||' ,retcode='|| retcode);
2380
2381 EXCEPTION
2382 WHEN OTHERS THEN
2383 retcode := 2;
2384 errbuf := SQLCODE || SQLERRM;
2385 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Concurrent Program - Error, time: '|| to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||' ,retcode='|| retcode);
2386
2387 END TAX_MIGRATION;
2388
2389
2390 --npalepu added on 19-jun-2006 for bug # 4908543.
2391 PROCEDURE Update_Tax_BMGR(X_errbuf out NOCOPY varchar2,
2392 X_retcode out NOCOPY varchar2,
2393 P_batch_size in number,
2394 P_Num_Workers in number)
2395 IS
2396 BEGIN
2397 --
2398 -- Manager processing for OKS_K_LINES_B table
2399 --
2400 fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Tax_BMGR ');
2401 fnd_file.put_line(FND_FILE.LOG, ' P_batch_size : '||P_batch_size);
2402 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
2403
2404 fnd_file.put_line(FND_FILE.LOG, 'starting oks_k_lines_b update worker ');
2405
2406 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
2407 X_retcode,
2408 'OKS',
2409 'OKSTAXBWKR',
2410 P_batch_size,
2411 P_Num_Workers);
2412
2413 fnd_file.put_line(FND_FILE.LOG, 'X_errbuf : '||X_errbuf);
2414 fnd_file.put_line(FND_FILE.LOG, 'X_retcode : '||X_retcode);
2415
2416 END Update_Tax_BMGR;
2417
2418 PROCEDURE Update_Tax_HMGR(X_errbuf out NOCOPY varchar2,
2419 X_retcode out NOCOPY varchar2,
2420 P_batch_size in number,
2421 P_Num_Workers in number)
2422 IS
2423 BEGIN
2424 --
2425 -- Manager processing for OKS_K_LINES_BH table
2426 --
2427 fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Tax_HMGR ');
2428 fnd_file.put_line(FND_FILE.LOG, ' P_batch_size : '||P_batch_size);
2429 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
2430
2431 fnd_file.put_line(FND_FILE.LOG, 'starting oks_k_lines_bh update worker ');
2432
2433 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
2434 X_retcode,
2435 'OKS',
2436 'OKSTAXHWKR',
2437 P_batch_size,
2438 P_Num_Workers);
2439
2440 fnd_file.put_line(FND_FILE.LOG, 'X_errbuf : '||X_errbuf);
2441 fnd_file.put_line(FND_FILE.LOG, 'X_retcode : '||X_retcode);
2442
2443 END Update_Tax_HMGR;
2444
2445 /*
2446 This is a concurrent program to migrate the pre-R12 tax data
2447 to R12 eBTax. We do not remove the old values.
2448 OKS_K_LINES_B:
2449 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE and TAX_CLASSIFICATION_CODE
2450 TAX_CODE --> TAX_CLASSIFICATION_CODE
2451 */
2452 PROCEDURE Update_Tax_BWKR(X_errbuf out NOCOPY varchar2,
2453 X_retcode out NOCOPY varchar2,
2454 P_batch_size in number,
2455 P_Worker_Id in number,
2456 P_Num_Workers in number)
2457 IS
2458 l_worker_id number;
2459 l_product varchar2(30) := 'OKS';
2460 l_table_name varchar2(30) := 'OKS_K_LINES_B';
2461 l_update_name varchar2(30) := 'OKSTAXUPG_CP';
2462 l_status varchar2(30);
2463 l_industry varchar2(30);
2464 l_retstatus boolean;
2465 l_table_owner varchar2(30);
2466 l_any_rows_to_process boolean;
2467 l_start_rowid rowid;
2468 l_end_rowid rowid;
2469 l_rows_processed number;
2470 BEGIN
2471 --
2472 -- get schema name of the table for ROWID range processing
2473 --
2474 l_retstatus := fnd_installation.get_app_info(l_product,
2475 l_status,
2476 l_industry,
2477 l_table_owner);
2478 if ((l_retstatus = FALSE) OR (l_table_owner is null))
2479 then
2480 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
2481 end if;
2482
2483 fnd_file.put_line(FND_FILE.LOG, 'Start of upgrade script for OKS_K_LINES_B table ');
2484 fnd_file.put_line(FND_FILE.LOG, ' P_Worker_Id : '||P_Worker_Id);
2485 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
2486
2487 --
2488 -- Worker processing
2489 --
2490 BEGIN
2491 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
2492 l_table_owner,
2493 l_table_name,
2494 l_update_name,
2495 P_worker_id,
2496 P_num_workers,
2497 P_batch_size,
2498 0);
2499 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
2500 l_end_rowid,
2501 l_any_rows_to_process,
2502 P_batch_size,
2503 TRUE);
2504 while (l_any_rows_to_process = TRUE)
2505 loop
2506
2507 UPDATE (SELECT /*+ ROWID(oks) LEADING(oks) */ oks.id,
2508 oks.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE,
2509 oks.TAX_CODE TAX_CODE,
2510 oks.EXEMPT_CERTIFICATE_NUMBER EXEMPT_CERTIFICATE_NUMBER,
2511 oks.EXEMPT_REASON_CODE EXEMPT_REASON_CODE,
2512 oks.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
2513 FROM OKS_K_LINES_B oks
2514 WHERE oks.rowid BETWEEN l_start_rowid and l_end_rowid
2515 --npalepu added on 07-jul-2006 for bug # 4908543
2516 AND (oks.TAX_CODE IS NOT NULL OR TAX_EXEMPTION_ID IS NOT NULL)
2517 --end npalepu
2518 ) oks1
2519 set TAX_CLASSIFICATION_CODE = (CASE WHEN oks1.TAX_CODE IS NOT NULL
2520 AND oks1.TAX_CLASSIFICATION_CODE IS NULL
2521 THEN
2522 (SELECT zx.TAX_CLASSIFICATION_CODE
2523 FROM ZX_ID_TCC_MAPPING_ALL zx /* npalepu replaced ZX_ID_TCC_MAPPING with ZX_ID_TCC_MAPPING_ALL on 29-jun-2006 for bug # 4908543 */
2524 WHERE zx.TAX_RATE_CODE_ID = oks1.TAX_CODE
2525 AND zx.SOURCE = 'AR')
2526 --npalepu added on 29-jun-2006 for bug # 4908543
2527 WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL)
2528 THEN
2529 (SELECT zx.TAX_CODE
2530 FROM RA_TAX_EXEMPTIONS_ALL zx
2531 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2532 --end npalepu
2533 ELSE
2534 oks1.TAX_CLASSIFICATION_CODE
2535 END)
2536 ,EXEMPT_CERTIFICATE_NUMBER = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
2537 AND
2538 (oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
2539 OR oks1.EXEMPT_REASON_CODE IS NULL))
2540 THEN
2541 (SELECT zx.CUSTOMER_EXEMPTION_NUMBER /* npalepu replaced zx.EXEMPT_CERTIFICATE_NUMBER with zx.CUSTOMER_EXEMPTION_NUMBER for bug # 4908543. */
2542 FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
2543 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2544 ELSE
2545 oks1.EXEMPT_CERTIFICATE_NUMBER
2546 END)
2547 ,EXEMPT_REASON_CODE = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
2548 AND
2549 (oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
2550 OR oks1.EXEMPT_REASON_CODE IS NULL))
2551 THEN
2552 (SELECT zx.REASON_CODE /* npalepu replaced zx.EXEMPT_REASON_CODE with zx.REASON_CODE for bug # 4908543. */
2553 FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL by bug # 4908543. */
2554 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2555 ELSE
2556 oks1.EXEMPT_REASON_CODE
2557 END)
2558 --npalepu added on 29-jun-2006 for bug # 4908543
2559 ,TAX_EXEMPTION_ID = NULL
2560 ,TAX_CODE = NULL;
2561 --end npalepu
2562
2563 l_rows_processed := SQL%ROWCOUNT;
2564 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
2565 l_end_rowid);
2566 commit;
2567 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
2568 l_end_rowid,
2569 l_any_rows_to_process,
2570 P_batch_size,
2571 FALSE);
2572 end loop;
2573 fnd_file.put_line(FND_FILE.LOG,'Upgrade for tax columns in OKS_K_LINES_B table completed successfully');
2574 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
2575 X_errbuf := ' ';
2576 EXCEPTION
2577 WHEN OTHERS THEN
2578 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
2579 X_errbuf := SQLERRM;
2580 fnd_file.put_line(FND_FILE.LOG,'X_errbuf : '||X_errbuf);
2581 fnd_file.put_line(FND_FILE.LOG,' ');
2582 raise;
2583 END;
2584 END Update_Tax_BWKR;
2585
2586 /*
2587 This is a concurrent program to migrate the pre-R12 tax data
2588 to R12 eBTax. We do not remove the old values.
2589 OKS_K_LINES_BH:
2590 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE and TAX_CLASSIFICATION_CODE
2591 TAX_CODE --> TAX_CLASSIFICATION_CODE
2592 */
2593 PROCEDURE Update_Tax_HWKR(X_errbuf out NOCOPY varchar2,
2594 X_retcode out NOCOPY varchar2,
2595 P_batch_size in number,
2596 P_Worker_Id in number,
2597 P_Num_Workers in number)
2598 IS
2599 l_worker_id number;
2600 l_product varchar2(30) := 'OKS';
2601 l_table_name varchar2(30) := 'OKS_K_LINES_BH';
2602 l_update_name varchar2(30) := 'OKSTAXUPH_CP';
2603 l_status varchar2(30);
2604 l_industry varchar2(30);
2605 l_retstatus boolean;
2606 l_table_owner varchar2(30);
2607 l_any_rows_to_process boolean;
2608 l_start_rowid rowid;
2609 l_end_rowid rowid;
2610 l_rows_processed number;
2611 BEGIN
2612 --
2613 -- get schema name of the table for ROWID range processing
2614 --
2615 l_retstatus := fnd_installation.get_app_info(l_product,
2616 l_status,
2617 l_industry,
2618 l_table_owner);
2619 if ((l_retstatus = FALSE) OR (l_table_owner is null))
2620 then
2621 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
2622 end if;
2623
2624 fnd_file.put_line(FND_FILE.LOG, 'Start of upgrade script for OKS_K_LINES_BH table ');
2625 fnd_file.put_line(FND_FILE.LOG, ' P_Worker_Id : '||P_Worker_Id);
2626 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
2627
2628 --
2629 -- Worker processing
2630 --
2631 BEGIN
2632 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
2633 l_table_owner,
2634 l_table_name,
2635 l_update_name,
2636 P_worker_id,
2637 P_num_workers,
2638 P_batch_size,
2639 0);
2640 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
2641 l_end_rowid,
2642 l_any_rows_to_process,
2643 P_batch_size,
2644 TRUE);
2645 while (l_any_rows_to_process = TRUE)
2646 loop
2647 UPDATE (SELECT /*+ ROWID(oks) LEADING(oks) */ oks.id,
2648 oks.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE,
2649 oks.TAX_CODE TAX_CODE,
2650 oks.EXEMPT_CERTIFICATE_NUMBER EXEMPT_CERTIFICATE_NUMBER,
2651 oks.EXEMPT_REASON_CODE EXEMPT_REASON_CODE,
2652 oks.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
2653 FROM OKS_K_LINES_BH oks
2654 WHERE oks.rowid BETWEEN l_start_rowid and l_end_rowid
2655 --npalepu added on 07-jul-2006 for bug # 4908543
2656 AND (oks.TAX_CODE IS NOT NULL OR TAX_EXEMPTION_ID IS NOT NULL)
2657 --end npalepu
2658 ) oks1
2659 set TAX_CLASSIFICATION_CODE = (CASE WHEN oks1.TAX_CODE IS NOT NULL
2660 AND oks1.TAX_CLASSIFICATION_CODE IS NULL
2661 THEN
2662 (SELECT zx.TAX_CLASSIFICATION_CODE
2663 FROM ZX_ID_TCC_MAPPING_ALL zx /* npalepu replaced ZX_ID_TCC_MAPPING with ZX_ID_TCC_MAPPING_ALL on 29-jun-2006 for bug # 4908543 */
2664 WHERE zx.TAX_RATE_CODE_ID = oks1.TAX_CODE
2665 AND zx.SOURCE = 'AR')
2666 --npalepu added on 29-jun-2006 for bug # 4908543
2667 WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL)
2668 THEN
2669 (SELECT zx.TAX_CODE
2670 FROM RA_TAX_EXEMPTIONS_ALL zx
2671 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2672 --end npalepu
2673 ELSE
2674 oks1.TAX_CLASSIFICATION_CODE
2675 END)
2676 ,EXEMPT_CERTIFICATE_NUMBER = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
2677 AND
2678 (oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
2679 OR oks1.EXEMPT_REASON_CODE IS NULL))
2680 THEN
2681 (SELECT zx.CUSTOMER_EXEMPTION_NUMBER /* npalepu replaced zx.EXEMPT_CERTIFICATE_NUMBER with zx.CUSTOMER_EXEMPTION_NUMBER for bug # 4908543. */
2682 FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
2683 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2684 ELSE
2685 oks1.EXEMPT_CERTIFICATE_NUMBER
2686 END)
2687 ,EXEMPT_REASON_CODE = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
2688 AND
2689 (oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
2690 OR oks1.EXEMPT_REASON_CODE IS NULL))
2691 THEN
2692 (SELECT zx.REASON_CODE /* npalepu replaced zx.EXEMPT_REASON_CODE with zx.REASON_CODE for bug # 4908543. */
2693 FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
2694 WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
2695 ELSE
2696 oks1.EXEMPT_REASON_CODE
2697 END)
2698 --npalepu added tax_exemption_code on 29-jun-2006 for bug # 4908543
2699 ,TAX_EXEMPTION_ID = NULL
2700 ,TAX_CODE = NULL;
2701 --end npalepu
2702
2703 l_rows_processed := SQL%ROWCOUNT;
2704 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
2705 l_end_rowid);
2706 commit;
2707 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
2708 l_end_rowid,
2709 l_any_rows_to_process,
2710 P_batch_size,
2711 FALSE);
2712 end loop;
2713 fnd_file.put_line(FND_FILE.LOG,'Upgrade for tax columns in OKS_K_LINES_BH table completed successfully');
2714 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
2715 X_errbuf := ' ';
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
2719 X_errbuf := SQLERRM;
2720 fnd_file.put_line(FND_FILE.LOG,'X_errbuf : '||X_errbuf);
2721 fnd_file.put_line(FND_FILE.LOG,' ');
2722 raise;
2723 END;
2724 END Update_Tax_HWKR;
2725 --end npalepu
2726
2727
2728 END OKS_TAX_UTIL_PVT;
2729
2730
2731