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