[Home] [Help]
PACKAGE BODY: APPS.ASO_TAX_INT
Source
1 PACKAGE BODY ASO_TAX_INT as
2 /* $Header: asoitaxb.pls 120.23.12010000.2 2008/11/11 07:36:48 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_TAX_INT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_TAX_INT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoitaxb.pls';
12 l_sys_date date := SYSDATE;
13
14
15 /*
16 *
17 *
18 PROCEDURE Calculate_Tax(
19 P_Api_Version_Number IN NUMBER,
20 P_Tax_Control_Rec IN Tax_Control_Rec_Type
21 := G_Miss_Tax_Control_Rec,
22 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
23 := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
24 P_Qte_Line_Rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type
25 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Rec,
26 P_Shipment_Rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
27 := ASO_QUOTE_PUB.G_MISS_SHIPMENT_REC,
28 p_tax_detail_rec IN ASO_QUOTE_PUB.Tax_Detail_Rec_Type
29 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_REC,
30 x_tax_amount OUT NOCOPY NUMBER,
31 x_tax_detail_tbl OUT NOCOPY ASO_QUOTE_PUB.Tax_Detail_Tbl_Type,
32 X_Return_Status OUT NOCOPY VARCHAR2,
33 X_Msg_Count OUT NOCOPY NUMBER,
34 X_Msg_Data OUT NOCOPY VARCHAR2)
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Tax';
37 l_trx_id NUMBER := NULL;
38 l_trx_line_id NUMBER := NULL;
39 l_charge_line_id NUMBER := NULL;
40 l_arp_tax_tbl ARP_TAX.tax_rec_tbl_type;
41
42 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
43
44 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
45 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
46 CURSOR C_Tax_Code (c_tax_id NUMBER) IS
47 SELECT tax_code from AR_VAT_TAX
48 WHERE vat_tax_id = c_tax_id;
49 BEGIN
50 SAVEPOINT CALCULATE_TAX_INT;
51
52 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 IF p_tax_control_rec.tax_level = 'HEADER' THEN
57 l_trx_id := p_tax_detail_rec.quote_header_id;
58 ELSIF p_tax_control_rec.tax_level = 'SHIPPING' THEN
59 l_trx_line_id := p_tax_detail_rec.quote_shipment_id;
60 l_trx_id := p_tax_detail_rec.quote_header_id;
61 -- l_charge_line_id := P_Shipment_Rec.shipment_id;
62 END IF;
63 BEGIN
64 ARP_PROCESS_TAX.Summary(
65 p_trx_id => l_trx_id,
66 p_trx_line_id => l_trx_line_id,
67 p_charge_line_id => l_charge_line_id,
68 p_viewname => 'ASO_I_TAX_LINES_SUMMARY_V',
69 p_new_tax_amount => x_tax_amount,
70 p_tax_rec_tbl => l_arp_tax_tbl);
71 EXCEPTION
72 WHEN OTHERS THEN
73 FND_MESSAGE.Set_Name('ASO', 'ASO_API_TAX_EXCEPTION');
74 FND_MSG_PUB.Add;
75 -- x_return_status := FND_API.G_RET_STS_ERROR;
76 END;
77 FOR i IN 1.. l_arp_tax_tbl.count LOOP
78 OPEN c_tax_code(l_arp_tax_tbl(i).vat_tax_id);
79 FETCH c_tax_code INTO l_tax_detail_rec.TAX_CODE;
80 CLOSE c_tax_code;
81 l_tax_detail_rec.QUOTE_HEADER_ID := p_tax_detail_rec.quote_header_id;
82 l_tax_detail_rec.QUOTE_LINE_ID := p_tax_detail_rec.quote_line_id;
83 l_tax_detail_rec.QUOTE_SHIPMENT_ID := p_tax_detail_rec.quote_shipment_id;
84 l_tax_detail_rec.TAX_RATE := l_arp_tax_tbl(i).TAX_RATE;
85 l_tax_detail_rec.TAX_DATE := sysdate;
86 l_tax_detail_rec.TAX_AMOUNT := l_arp_tax_tbl(i).EXTENDED_AMOUNT;
87 l_tax_detail_rec.TAX_EXEMPT_FLAG := l_arp_tax_tbl(i).TAX_EXEMPT_FLAG;
88 l_tax_detail_rec.TAX_EXEMPT_NUMBER := l_arp_tax_tbl(i).TAX_EXEMPT_NUMBER;
89 l_tax_detail_rec.TAX_EXEMPT_REASON_CODE := l_arp_tax_tbl(i).TAX_EXEMPT_REASON_CODE;
90 x_tax_detail_tbl(x_tax_detail_tbl.count+1) := l_tax_detail_rec;
91 END LOOP;
92
93 IF p_tax_control_rec.update_DB = 'Y' THEN
94
95 IF p_tax_control_rec.tax_level = 'SHIPPING' THEN
96 DELETE FROM aso_tax_details
97 WHERE quote_shipment_id = p_tax_detail_rec.quote_shipment_id and
98 quote_line_id = p_tax_detail_rec.quote_line_id and
99 quote_header_id = p_tax_detail_rec.quote_header_id;
100 END IF;
101
102 IF p_tax_control_rec.tax_level = 'HEADER' THEN
103 DELETE FROM aso_tax_details
104 WHERE quote_header_id = p_tax_detail_rec.quote_header_id AND
105 quote_line_id = p_tax_detail_rec.quote_line_id;
106 END IF;
107 -- AND orig_tax_code IS NOT NULL;
108 FOR i IN 1..x_tax_detail_Tbl.count LOOP
109 l_tax_detail_rec := x_tax_detail_tbl(i);
110
111 ASO_TAX_DETAILS_PKG.Insert_Row(
112 px_TAX_DETAIL_ID => x_tax_detail_tbl(i).TAX_DETAIL_ID,
113 p_CREATION_DATE => SYSDATE,
114 p_CREATED_BY => G_USER_ID,
115 p_LAST_UPDATE_DATE => SYSDATE,
116 p_LAST_UPDATED_BY => G_USER_ID,
117 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
118 p_REQUEST_ID => l_tax_detail_rec.REQUEST_ID,
119 p_PROGRAM_APPLICATION_ID => l_tax_detail_rec.PROGRAM_APPLICATION_ID,
120 p_PROGRAM_ID => l_tax_detail_rec.PROGRAM_ID,
121 p_PROGRAM_UPDATE_DATE => l_tax_detail_rec.PROGRAM_UPDATE_DATE,
122 p_QUOTE_HEADER_ID => p_tax_detail_rec.quote_header_id,
123 p_QUOTE_LINE_ID => l_tax_detail_rec.QUOTE_LINE_ID,
124 p_QUOTE_SHIPMENT_ID => l_tax_detail_rec.QUOTE_SHIPMENT_ID,
125 p_ORIG_TAX_CODE => l_tax_detail_rec.ORIG_TAX_CODE,
126 p_TAX_CODE => l_tax_detail_rec.TAX_CODE,
127 p_TAX_RATE => l_tax_detail_rec.TAX_RATE,
128 p_TAX_DATE => l_tax_detail_rec.TAX_DATE,
129 p_TAX_AMOUNT => l_tax_detail_rec.TAX_AMOUNT,
130 p_TAX_EXEMPT_FLAG => l_tax_detail_rec.TAX_EXEMPT_FLAG,
131 p_TAX_EXEMPT_NUMBER => l_tax_detail_rec.TAX_EXEMPT_NUMBER,
132 p_TAX_EXEMPT_REASON_CODE => l_tax_detail_rec.TAX_EXEMPT_REASON_CODE,
133 p_ATTRIBUTE_CATEGORY => l_tax_detail_rec.ATTRIBUTE_CATEGORY,
134 p_ATTRIBUTE1 => l_tax_detail_rec.ATTRIBUTE1,
135 p_ATTRIBUTE2 => l_tax_detail_rec.ATTRIBUTE2,
136 p_ATTRIBUTE3 => l_tax_detail_rec.ATTRIBUTE3,
137 p_ATTRIBUTE4 => l_tax_detail_rec.ATTRIBUTE4,
138 p_ATTRIBUTE5 => l_tax_detail_rec.ATTRIBUTE5,
139 p_ATTRIBUTE6 => l_tax_detail_rec.ATTRIBUTE6,
140 p_ATTRIBUTE7 => l_tax_detail_rec.ATTRIBUTE7,
141 p_ATTRIBUTE8 => l_tax_detail_rec.ATTRIBUTE8,
142 p_ATTRIBUTE9 => l_tax_detail_rec.ATTRIBUTE9,
143 p_ATTRIBUTE10 => l_tax_detail_rec.ATTRIBUTE10,
144 p_ATTRIBUTE11 => l_tax_detail_rec.ATTRIBUTE11,
145 p_ATTRIBUTE12 => l_tax_detail_rec.ATTRIBUTE12,
146 p_ATTRIBUTE13 => l_tax_detail_rec.ATTRIBUTE13,
147 p_ATTRIBUTE14 => l_tax_detail_rec.ATTRIBUTE14,
148 p_ATTRIBUTE15 => l_tax_detail_rec.ATTRIBUTE15,
149 p_ATTRIBUTE16 => l_tax_detail_rec.ATTRIBUTE16,
150 p_ATTRIBUTE17 => l_tax_detail_rec.ATTRIBUTE17,
151 p_ATTRIBUTE18 => l_tax_detail_rec.ATTRIBUTE18,
152 p_ATTRIBUTE19 => l_tax_detail_rec.ATTRIBUTE19,
153 p_ATTRIBUTE20 => l_tax_detail_rec.ATTRIBUTE20,
154 p_TAX_INCLUSIVE_FLAG => l_tax_detail_rec.TAX_INCLUSIVE_FLAG,
155 p_OBJECT_VERSION_NUMBER => l_tax_detail_rec.OBJECT_VERSION_NUMBER,
156 p_TAX_RATE_ID => l_tax_detail_rec.TAX_RATE_ID
157 );
158 END LOOP;
159
160 END IF;
161
162 -- Standard call to get message count and if count is 1, get message info.
163 FND_MSG_PUB.Count_And_Get
164 ( p_count => x_msg_count,
165 p_data => x_msg_data
166 );
167
168 EXCEPTION
169 WHEN FND_API.G_EXC_ERROR THEN
170 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
171 P_API_NAME => L_API_NAME
172 ,P_PKG_NAME => G_PKG_NAME
173 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
174 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
175 ,X_MSG_COUNT => X_MSG_COUNT
176 ,X_MSG_DATA => X_MSG_DATA
177 ,X_RETURN_STATUS => X_RETURN_STATUS);
178
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
181 P_API_NAME => L_API_NAME
182 ,P_PKG_NAME => G_PKG_NAME
183 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
184 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
185 ,X_MSG_COUNT => X_MSG_COUNT
186 ,X_MSG_DATA => X_MSG_DATA
187 ,X_RETURN_STATUS => X_RETURN_STATUS);
188
189 WHEN OTHERS THEN
190 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
191 P_API_NAME => L_API_NAME
192 ,P_PKG_NAME => G_PKG_NAME
193 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
194 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
195 ,P_SQLCODE => SQLCODE
196 ,P_SQLERRM => SQLERRM
197 ,X_MSG_COUNT => X_MSG_COUNT
198 ,X_MSG_DATA => X_MSG_DATA
199 ,X_RETURN_STATUS => X_RETURN_STATUS);
200 END Calculate_Tax;
201
202
203 PROCEDURE Calculate_Tax(
204 P_Api_Version_Number IN NUMBER,
205 p_quote_header_id IN NUMBER,
206 p_qte_line_id IN NUMBER :=NULL,
207 P_Tax_Control_Rec IN Tax_Control_Rec_Type
208 := G_Miss_Tax_Control_Rec,
209 x_tax_amount OUT NOCOPY NUMBER,
210 x_tax_detail_tbl OUT NOCOPY ASO_QUOTE_PUB.Tax_Detail_Tbl_Type,
211 X_Return_Status OUT NOCOPY VARCHAR2,
212 X_Msg_Count OUT NOCOPY NUMBER,
213 X_Msg_Data OUT NOCOPY VARCHAR2)
214 IS
215 Cursor C_shipment(l_quote_header_id NUMBER) IS
216 select quote_line_id, shipment_id
217 from aso_shipments
218 where quote_header_id = l_quote_header_id;
219
220
221 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Tax';
222 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
223 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
224 lx_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
225 l_count NUMBER;
226
227 c_header_id NUMBER;
228 c_line_id NUMBER;
229 c_shipment_id NUMBER;
230 l_hd_exempt_flag VARCHAR2(1);
231 l_hd_exempt_number VARCHAR2(80);
232 l_hd_exempt_reason_code VARCHAR2(30);
233 l_exempt_flag VARCHAR2(1);
234 l_exempt_number VARCHAR2(80);
235 l_exempt_reason_code VARCHAR2(30);
236 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
237 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
238 CURSOR c_hd_tax(qt_hdr_id NUMBER,q_ship_id NUMBER) IS SELECT tax_exempt_flag,tax_exempt_number,tax_exempt_reason_code
239 FROM
240 aso_tax_details WHERE quote_header_id= qt_hdr_id
241 and quote_shipment_id= q_ship_id and quote_line_id IS NULL;
242
243 CURSOR c_tax_line(qt_hdr_id NUMBER,q_line_id NUMBER,q_ship_id NUMBER) IS SELECT tax_exempt_flag,tax_exempt_number,tax_exempt_reason_code FROM
244 aso_tax_details WHERE quote_header_id= qt_hdr_id
245 and quote_shipment_id= q_ship_id and quote_line_id = q_line_id;
246
247 BEGIN
248 SAVEPOINT CALCULATE_TAX_PUB;
249
250 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
251
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 l_tax_detail_rec.quote_header_id := p_quote_header_id;
255 FOR i in C_shipment(p_quote_header_id) LOOP
256 --l_tax_detail_rec.quote_header_id := p_quote_header_id;
257 l_tax_detail_rec.quote_line_id := i.quote_line_id;
258 l_tax_detail_rec.quote_shipment_id := i.shipment_id;
259 l_tax_detail_tbl(l_tax_detail_tbl.COUNT+1) := l_tax_detail_rec;
260 END LOOP;
261
262 IF aso_debug_pub.g_debug_flag = 'Y' THEN
263
264 aso_debug_pub.add('After shipment loop',1,'Y');
265 aso_debug_pub.add('After shipment loop'||l_tax_detail_tbl.count,1,'Y');
266
267 END IF;
268
269 FOR j IN 1..l_tax_detail_tbl.count LOOP
270
271 IF l_tax_detail_tbl(j).quote_line_id IS NULL THEN
272 c_header_id := l_tax_detail_tbl(j).quote_header_id;
273 c_shipment_id := l_tax_detail_tbl(j).quote_shipment_id;
274 OPEN c_hd_tax(c_header_id,c_shipment_id);
275 FETCH c_hd_tax into l_hd_exempt_flag,l_hd_exempt_number,l_hd_exempt_reason_code;
276 IF c_hd_tax%NOTFOUND or l_hd_exempt_flag is null or l_hd_exempt_flag = FND_API.G_MISS_CHAR THEN
277 l_hd_exempt_flag := null;
278 l_hd_exempt_number := null;
279 l_hd_exempt_reason_code := null;
280 END IF;
281 CLOSE c_hd_tax;
282 END IF;
283
284 END LOOP;
285
286 IF aso_debug_pub.g_debug_flag = 'Y' THEN
287 aso_debug_pub.add('After header tax detail query ',1,'Y');
288 END IF;
289
290 FOR j IN 1..l_tax_detail_tbl.count LOOP
291
292 c_header_id := l_tax_detail_tbl(j).quote_header_id;
293 c_line_id := l_tax_detail_tbl(j).quote_line_id;
294 c_shipment_id := l_tax_detail_tbl(j).quote_shipment_id;
295
296 IF l_tax_detail_tbl(j).quote_line_id IS NOT NULL and l_tax_detail_tbl(j).quote_line_id <> FND_API.G_MISS_NUM THEN
297
298 IF aso_debug_pub.g_debug_flag = 'Y' THEN
299
300 aso_debug_pub.add('Inside line tax c_header_id '||c_header_id ,1,'Y');
301 aso_debug_pub.add('Inside line tax c_line_id '||c_line_id ,1,'Y');
302 aso_debug_pub.add('Inside line tax c_shp_id '||c_shipment_id ,1,'Y');
303
304 END IF;
305 OPEN c_tax_line(c_header_id,c_line_id,c_shipment_id);
306 FETCH c_tax_line into l_exempt_flag,l_exempt_number,l_exempt_reason_code;
307 IF c_tax_line%NOTFOUND THEN
308 -- Insert into tax details
309 ASO_TAX_DETAILS_PKG.Insert_Row(
310 px_TAX_DETAIL_ID => l_tax_detail_tbl(j).TAX_DETAIL_ID,
311 p_CREATION_DATE => SYSDATE,
312 p_CREATED_BY => G_USER_ID,
313 p_LAST_UPDATE_DATE => SYSDATE,
314 p_LAST_UPDATED_BY => G_USER_ID,
315 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
316 p_REQUEST_ID => l_tax_detail_tbl(j).REQUEST_ID,
317 p_PROGRAM_APPLICATION_ID => l_tax_detail_tbl(j).PROGRAM_APPLICATION_ID,
318 p_PROGRAM_ID => l_tax_detail_tbl(j).PROGRAM_ID,
319 p_PROGRAM_UPDATE_DATE => l_tax_detail_tbl(j).PROGRAM_UPDATE_DATE,
320 p_QUOTE_HEADER_ID => l_tax_detail_tbl(j).quote_header_id,
321 p_QUOTE_LINE_ID => l_tax_detail_tbl(j).QUOTE_LINE_ID,
322 p_QUOTE_SHIPMENT_ID => l_tax_detail_tbl(j).QUOTE_SHIPMENT_ID,
323 p_ORIG_TAX_CODE => l_tax_detail_tbl(j).ORIG_TAX_CODE,
324 p_TAX_CODE => l_tax_detail_tbl(j).TAX_CODE,
325 p_TAX_RATE => l_tax_detail_tbl(j).TAX_RATE,
326 p_TAX_DATE => l_sys_date,--l_tax_detail_tbl(j).TAX_DATE,
327 p_TAX_AMOUNT => l_tax_detail_tbl(j).TAX_AMOUNT,
328 p_TAX_EXEMPT_FLAG => l_hd_EXEMPT_FLAG,
329 p_TAX_EXEMPT_NUMBER => l_hd_exempt_number ,
330 p_TAX_EXEMPT_REASON_CODE => l_hd_exempt_reason_code ,
331 p_ATTRIBUTE_CATEGORY => l_tax_detail_tbl(j).ATTRIBUTE_CATEGORY,
332 p_ATTRIBUTE1 => l_tax_detail_tbl(j).ATTRIBUTE1,
333 p_ATTRIBUTE2 => l_tax_detail_tbl(j).ATTRIBUTE2,
334 p_ATTRIBUTE3 => l_tax_detail_tbl(j).ATTRIBUTE3,
335 p_ATTRIBUTE4 => l_tax_detail_tbl(j).ATTRIBUTE4,
336 p_ATTRIBUTE5 => l_tax_detail_tbl(j).ATTRIBUTE5,
337 p_ATTRIBUTE6 => l_tax_detail_tbl(j).ATTRIBUTE6,
338 p_ATTRIBUTE7 => l_tax_detail_tbl(j).ATTRIBUTE7,
339 p_ATTRIBUTE8 => l_tax_detail_tbl(j).ATTRIBUTE8,
340 p_ATTRIBUTE9 => l_tax_detail_tbl(j).ATTRIBUTE9,
341 p_ATTRIBUTE10 => l_tax_detail_tbl(j).ATTRIBUTE10,
342 p_ATTRIBUTE11 => l_tax_detail_tbl(j).ATTRIBUTE11,
343 p_ATTRIBUTE12 => l_tax_detail_tbl(j).ATTRIBUTE12,
344 p_ATTRIBUTE13 => l_tax_detail_tbl(j).ATTRIBUTE13,
345 p_ATTRIBUTE14 => l_tax_detail_tbl(j).ATTRIBUTE14,
346 p_ATTRIBUTE15 => l_tax_detail_tbl(j).ATTRIBUTE15,
347 p_ATTRIBUTE16 => l_tax_detail_tbl(j).ATTRIBUTE16,
348 p_ATTRIBUTE17 => l_tax_detail_tbl(j).ATTRIBUTE17,
349 p_ATTRIBUTE18 => l_tax_detail_tbl(j).ATTRIBUTE18,
350 p_ATTRIBUTE19 => l_tax_detail_tbl(j).ATTRIBUTE19,
351 p_ATTRIBUTE20 => l_tax_detail_tbl(j).ATTRIBUTE20,
352 p_TAX_INCLUSIVE_FLAG => l_tax_detail_tbl(j).TAX_INCLUSIVE_FLAG,
353 p_OBJECT_VERSION_NUMBER => l_tax_detail_tbl(j).OBJECT_VERSION_NUMBER,
354 p_TAX_RATE_ID => l_tax_detail_tbl(j).TAX_RATE_ID
355 );
356
357 IF aso_debug_pub.g_debug_flag = 'Y' THEN
358 aso_debug_pub.add('Inside line tax detail after insert ',1,'Y');
359 END IF;
360
361 -- ELSIF l_exempt_flag is null or l_exempt_flag = FND_API.G_MISS_CHAR or l_exempt_flag <> 'R' THEN
362 ELSIF l_hd_exempt_flag IS NOT NULL AND l_hd_exempt_flag <> FND_API.G_MISS_CHAR THEN
363
364 IF aso_debug_pub.g_debug_flag = 'Y' THEN
365 aso_debug_pub.add('Inside line tax detail before update ',1,'Y');
366 END IF;
367
368 UPDATE ASO_TAX_DETAILS
369 SET tax_exempt_flag = l_hd_exempt_flag ,
370 tax_exempt_number = l_hd_exempt_number,
371 tax_exempt_reason_code = l_hd_exempt_reason_code,
372 last_update_date = sysdate,
373 last_updated_by = fnd_global.user_id,
374 last_update_login = fnd_global.conc_login_id,
375 tax_date = l_sys_date
376 WHERE quote_header_id = c_header_id
377 and quote_line_id = c_line_id
378 and quote_shipment_id = c_shipment_id;
379
380 END IF;
381
382 IF aso_debug_pub.g_debug_flag = 'Y' THEN
383 aso_debug_pub.add('Inside line tax detail after update ',1,'Y');
384 END IF;
385
386 --END IF;
387 CLOSE c_tax_line;
388 END IF;
389
390 END LOOP;
391
392
393 BEGIN
394
395 IF aso_debug_pub.g_debug_flag = 'Y' THEN
396
397 aso_debug_pub.add('Before calling tax engine: FND_PROFILE.Value(ASO_USE_TAX_VIEW)'||FND_PROFILE.Value('ASO_USE_TAX_VIEW'),1,'Y');
398 aso_debug_pub.add('Before new tax call : ' || x_tax_amount, 1, 'Y');
399
400 END IF;
401
402 aso_tax_line( p_api_version_number => p_api_version_number,
403 p_qte_header_id => c_header_id,
404 p_qte_line_id => p_qte_line_id,
405 p_tax_control_rec => p_tax_control_rec,
406 x_tax_value => x_tax_amount,
407 x_tax_detail_tbl => x_tax_detail_tbl,
408 x_return_status => x_return_status-- Tax engine is not returning msg_count and msg_data to OM. Do we need these?
409 );
410
411 EXCEPTION
412
413 WHEN OTHERS THEN
414
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417 IF aso_debug_pub.g_debug_flag = 'Y' THEN
418 aso_debug_pub.add('after new tax call in when others: ' || x_tax_amount, 1, 'Y');
419 END IF;
420
421 END;
422
423 IF aso_debug_pub.g_debug_flag = 'Y' THEN
424 aso_debug_pub.add('after new tax call : ' || x_tax_amount, 1, 'Y');
425 END IF;
426
427
428 -- Standard call to get message count and if count is 1, get message info.
429 FND_MSG_PUB.Count_And_Get
430 ( p_count => x_msg_count,
431 p_data => x_msg_data
432 );
433
434 EXCEPTION
435 WHEN FND_API.G_EXC_ERROR THEN
436 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
437 P_API_NAME => L_API_NAME
438 ,P_PKG_NAME => G_PKG_NAME
439 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
440 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
441 ,X_MSG_COUNT => X_MSG_COUNT
442 ,X_MSG_DATA => X_MSG_DATA
443 ,X_RETURN_STATUS => X_RETURN_STATUS);
444
445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
446 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
447 P_API_NAME => L_API_NAME
448 ,P_PKG_NAME => G_PKG_NAME
449 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
450 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
451 ,X_MSG_COUNT => X_MSG_COUNT
452 ,X_MSG_DATA => X_MSG_DATA
453 ,X_RETURN_STATUS => X_RETURN_STATUS);
454
455 WHEN OTHERS THEN
456 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
457 P_API_NAME => L_API_NAME
458 ,P_PKG_NAME => G_PKG_NAME
459 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
460 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
461 ,P_SQLCODE => SQLCODE
462 ,P_SQLERRM => SQLERRM
463 ,X_MSG_COUNT => X_MSG_COUNT
464 ,X_MSG_DATA => X_MSG_DATA
465 ,X_RETURN_STATUS => X_RETURN_STATUS);
466 END Calculate_Tax;
467
468
469
470 PROCEDURE Calculate_Tax(
471 p_trx_id IN NUMBER,
472 p_trx_line_id IN NUMBER,
473 p_charge_line_id IN NUMBER,
474 p_viewname IN VARCHAR2,
475 x_tax_amount OUT NOCOPY NUMBER,
476 x_tax_rec_tbl OUT NOCOPY ARP_TAX.tax_rec_tbl_type)
477 IS
478 BEGIN
479 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
480 ARP_PROCESS_TAX.Summary(
481 p_trx_id => p_trx_id,
482 p_trx_line_id => p_trx_line_id,
483 p_charge_line_id => p_charge_line_id,
484 p_viewname => p_viewname,
485 p_new_tax_amount => x_tax_amount,
486 p_tax_rec_tbl => x_tax_rec_tbl);
487 END Calculate_Tax;
488
489 -- New tax api
490 Procedure initialize_Tax_info_rec
491 IS
492 Begin
493 arp_tax.tax_info_rec.ship_to_cust_id := to_number(null);
494 arp_tax.tax_info_rec.bill_to_cust_id := to_number(null);
495 arp_tax.tax_info_rec.customer_trx_charge_line_id := to_number(null);
496 arp_tax.tax_info_rec.customer_trx_line_id := to_number(null);
497 arp_tax.tax_info_rec.customer_trx_id := to_number(null);
498 arp_tax.tax_info_rec.link_to_cust_trx_line_id := to_number(null);
499 arp_tax.tax_info_rec.trx_date := null;
500 arp_tax.tax_info_rec.gl_date := NULL;
501 arp_tax.tax_info_rec.tax_code := NULL;
502 arp_tax.tax_info_rec.tax_rate := NULL;
503 arp_tax.tax_info_rec.tax_amount := NULL;
504 arp_tax.tax_info_rec.ship_to_site_use_id := to_number(null);
505 arp_tax.tax_info_rec.bill_to_site_use_id := to_number(null);
506 arp_tax.tax_info_rec.ship_to_postal_code := null;
507 arp_tax.tax_info_rec.bill_to_postal_code := null;
508 arp_tax.tax_info_rec.inventory_item_id := to_number(null);
509 arp_tax.tax_info_rec.memo_line_id := to_number(NULL);
510 arp_tax.tax_info_rec.tax_control := null;
511 arp_tax.tax_info_rec.xmpt_cert_no := null;
512 arp_tax.tax_info_rec.xmpt_reason := null;
513 arp_tax.tax_info_rec.ship_to_location_id := to_number(null);
514 arp_tax.tax_info_rec.bill_to_location_id := to_number(null);
515 arp_tax.tax_info_rec.invoicing_rule_id := to_number(null);
516 arp_tax.tax_info_rec.extended_amount := null;
517 arp_tax.tax_info_rec.trx_exchange_rate := null;
518 arp_tax.tax_info_rec.trx_currency_code := null;
519 arp_tax.tax_info_rec.minimum_accountable_unit := null;
520 arp_tax.tax_info_rec.precision := null;
521 arp_tax.tax_info_rec.default_ussgl_transaction_code := NULL;
522 arp_tax.tax_info_rec.default_ussgl_trx_code_context := NULL;
523 arp_tax.tax_info_rec.poo_code := null;
524 arp_tax.tax_info_rec.poa_code := null;
525 arp_tax.tax_info_rec.ship_from_code := null;
526 arp_tax.tax_info_rec.ship_to_code := null;
527 arp_tax.tax_info_rec.fob_point := null;
528 arp_tax.tax_info_rec.taxed_quantity := null;
529 arp_tax.tax_info_rec.part_no := null;
530 arp_tax.tax_info_rec.tax_line_number := TO_NUMBER(NULL);
531 arp_tax.tax_info_rec.qualifier := null;
532 arp_tax.tax_info_rec.calculate_tax := null;
533 arp_tax.tax_info_rec.tax_precedence := NULL;
534 arp_tax.tax_info_rec.tax_exemption_id := TO_NUMBER(NULL);
535 arp_tax.tax_info_rec.item_exception_rate_id := TO_NUMBER(NULL);
536 arp_tax.tax_info_rec.vdrctrl_exempt := NULL;
537 arp_tax.tax_info_rec.userf1 := null;
538 arp_tax.tax_info_rec.userf2 := null;
539 arp_tax.tax_info_rec.userf3 := NULL;
540 arp_tax.tax_info_rec.userf4 := NULL;
541 arp_tax.tax_info_rec.userf5 := NULL;
542 arp_tax.tax_info_rec.usern1 := null;
543 arp_tax.tax_info_rec.usern2 := null;
544 arp_tax.tax_info_rec.usern3 := null;
545 arp_tax.tax_info_rec.usern4 := null;
546 arp_tax.tax_info_rec.usern5 := TO_NUMBER(NULL);
547 arp_tax.tax_info_rec.trx_number := NULL;
548 arp_tax.tax_info_rec.ship_to_customer_number := NULL;
549 arp_tax.tax_info_rec.ship_to_customer_name := NULL;
550 arp_tax.tax_info_rec.bill_to_customer_number := NULL;
551 arp_tax.tax_info_rec.bill_to_customer_name := NULL;
552 arp_tax.tax_info_rec.previous_customer_trx_line_id := to_number(NULL);
553 arp_tax.tax_info_rec.previous_customer_trx_id := to_number(null);
554 arp_tax.tax_info_rec.previous_trx_number := NULL;
555 arp_tax.tax_info_rec.audit_flag := null;
556 arp_tax.tax_info_rec.trx_line_type := NULL;
557 arp_tax.tax_info_rec.division_code := null;
558 arp_tax.tax_info_rec.company_code := null;
559 arp_tax.tax_info_rec.tax_header_level_flag := null;
560 arp_tax.tax_info_rec.tax_rounding_rule := null;
561 arp_tax.tax_info_rec.vat_tax_id := TO_NUMBER(NULL);
562 arp_tax.tax_info_rec.trx_type_id := TO_NUMBER(NULL);
563 arp_tax.tax_info_rec.amount_includes_tax_flag := null;
564 arp_tax.tax_info_rec.ship_from_warehouse_id := null;
565 arp_tax.tax_info_rec.poo_id := to_number(null);
566 arp_tax.tax_info_rec.poa_id := to_number(null);
567 arp_tax.tax_info_rec.payment_term_id := to_number(null);
568 arp_tax.tax_info_rec.payment_terms_discount_percent := NULL;
569 arp_tax.tax_info_rec.taxable_basis := null;
570 arp_tax.tax_info_rec.tax_calculation_plsql_block := null;
571 arp_tax.tax_info_rec.userf6 := NULL;
572 arp_tax.tax_info_rec.userf7 := NULL;
573 arp_tax.tax_info_rec.userf8 := NULL;
574 arp_tax.tax_info_rec.userf9 := NULL;
575 arp_tax.tax_info_rec.userf10 := NULL;
576 arp_tax.tax_info_rec.usern6 := TO_NUMBER(NULL);
577 arp_tax.tax_info_rec.usern7 := TO_NUMBER(NULL);
578 arp_tax.tax_info_rec.usern8 := TO_NUMBER(NULL);
579 arp_tax.tax_info_rec.usern9 := TO_NUMBER(NULL);
580 arp_tax.tax_info_rec.usern10 := TO_NUMBER(NULL);
581 End initialize_Tax_info_rec;
582
583
584 Procedure aso_tax_line( p_api_version_number IN NUMBER,
585 p_qte_header_id IN NUMBER,
586 p_tax_control_rec IN Tax_Control_Rec_Type := G_Miss_Tax_Control_Rec,
587 p_qte_line_id IN NUMBER := NULL,
588 x_tax_value OUT NOCOPY NUMBER,
589 x_tax_detail_tbl OUT NOCOPY ASO_QUOTE_PUB.Tax_Detail_Tbl_Type,
590 x_return_status OUT NOCOPY VARCHAR2
591 ) is
592
593 -- Declare local variables
594 x_tax_out_tbl ARP_TAX.om_tax_out_tab_type;
595
596 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
597 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type;
598 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_tbl_Type;
599 l_Shipment_Rec ASO_QUOTE_PUB.Shipment_Rec_Type;
600 l_Shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
601 l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
602 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
603 l_hdr_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
604
605 l_qte_header_id NUMBER;
606 l_tax_method VARCHAR2(15) := NULL;
607 l_vendor_installed VARCHAR2(1) := NULL;
608
609 l_tax_rounding_allow_override VARCHAR2(1);
610 l_tax_header_level_flag VARCHAR2(1);
611 l_tax_rounding_rule VARCHAR2(30);
612 l_set_of_books_id NUMBER;
613 l_site_use_id NUMBER;
614 l_site_use_id_ship NUMBER;
615 l_site_use_id_bill NUMBER;
616 l_resource_id NUMBER;
617 l_poo_id NUMBER;
618 l_asgn_org_id NUMBER;
619
620 --Ship to Info
621
622 l_ship_to_site_use_id NUMBER;
623 l_ship_to_address_id NUMBER;
624 l_ship_to_customer_id NUMBER;
625 l_ship_to_postal_code VARCHAR2(60);
626 l_ship_to_location_ccid NUMBER;
627 l_ship_to_customer_name VARCHAR2(360);
628 l_ship_to_customer_number VARCHAR2(30);
629 l_ship_to_state VARCHAR2(60);
630 l_ship_tax_header_level_flag VARCHAR2(1);
631 l_ship_tax_rounding_rule VARCHAR2(30);
632
633 --Bill to Info
634
635 l_bill_to_site_use_id NUMBER;
636 l_bill_to_address_id NUMBER;
637 l_bill_to_customer_id NUMBER;
638 l_bill_to_postal_code VARCHAR2(60);
639 l_bill_to_location_ccid NUMBER;
640 l_bill_to_customer_name VARCHAR2(360);
641 l_bill_to_customer_number VARCHAR2(30);
642 l_bill_to_state VARCHAR2(60);
643 l_bill_tax_header_level_flag VARCHAR2(1);
644 l_bill_tax_rounding_rule VARCHAR2(30);
645 l_party_site_id NUMBER;
646 l_party_site_id_ship NUMBER;
647 l_party_site_id_bill NUMBER;
648
649 -- Others bill to ,ship to
650 l_bc_tax_header_level_flag VARCHAR2(1);
651 l_bc_tax_rounding_rule VARCHAR2(30);
652
653 -- Currency info
654 l_minimum_accountable_unit NUMBER;
655 l_precision NUMBER;
656 l_currency_code VARCHAR2(15);
657
658 --sales rep info
659 l_person_id NUMBER;
660 l_sales_tax_geocode VARCHAR2(30);
661 l_sales_tax_inside_city_limits VARCHAR2(1);
662 l_poa_id NUMBER;
663
664 --Order type line type
665 l_in_line_type NUMBER;
666 l_out_line_type NUMBER;
667 l_line_type_id NUMBER;
668 l_cust_trx_type_id NUMBER;
669 l_trx_type_id NUMBER;
670 l_om_trx_type_id NUMBER;
671 l_tax_code VARCHAR2(50);
672 l_tax_rate NUMBER;
673 l_amount_includes_tax_flag VARCHAR2(1);
674 l_taxable_basis VARCHAR2(30);
675 l_tax_calculation_plsql_block VARCHAR2(2000);
676 l_ra_cust_trx_type_id NUMBER;
677
678 --Vertex and Taxware related
679 l_poo_address_code VARCHAR2(4000) := NULL;
680 l_poa_address_code VARCHAR2(4000) := NULL;
681 l_salesrep_id NUMBER;
682 l_ship_from_address_code VARCHAR2(4000) := NULL;
683 l_ship_to_address_code VARCHAR2(4000) := NULL;
684 l_part_number VARCHAR2(4000) := NULL;
685 l_vendor_control_exemptions VARCHAR2(4000) := NULL;
686 l_attribute1 VARCHAR2(4000) := NULL;
687 l_attribute2 VARCHAR2(4000) := NULL;
688 l_division_code VARCHAR2(30) := NULL;
689 l_company_code VARCHAR2(30) := NULL;
690 l_numeric_attribute1 NUMBER := NULL;
691 l_numeric_attribute2 NUMBER := NULL;
692 l_numeric_attribute3 NUMBER := NULL;
693 l_numeric_attribute4 NUMBER := NULL;
694
695 --Payment term info
696 l_payment_term_id NUMBER;
697
698 -- Default Tax code
699 l_vat_tax_id NUMBER;
700 l_amt_incl_tax_flag VARCHAR2(1);
701 l_amt_incl_tax_override VARCHAR2(1);
702 l_fiscal_classification VARCHAR2(150);
703 l_transaction_cond_class VARCHAR2(150);
704
705 l_hdr_tax_date DATE;
706 l_hdr_tax_exempt_flag VARCHAR2(1);
707 l_hdr_tax_exempt_number VARCHAR2(80);
708 l_hdr_tax_exempt_reason_code VARCHAR2(30);
709
710 l_count NUMBER;
711 l_reason VARCHAR2(4000);
712 l_ship_from_org_id NUMBER;
713 l_party_id NUMBER;
714
715 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
716 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
717 l_ship_loc_asgn_id NUMBER;
718 l_bill_loc_asgn_id NUMBER;
719
720 l_tax_start_time number;
721 l_tax_end_time number;
722 l_tax_total_time number := 0;
723
724 cursor getlocinfo is
725 select s_ship.site_use_id,
726 s_ship.cust_acct_site_id,
727 acct_site_ship.cust_account_id,
728 loc_ship.postal_code,
729 loc_assign_ship.loc_id,
730 cust_acct.party_id,
731 cust_acct.account_number,
732 cust_acct.tax_header_level_flag,
733 cust_acct.tax_rounding_rule,
734 loc_ship.state,
735 s_ship.tax_header_level_flag,
736 s_ship.tax_rounding_rule
737 FROM
738 hz_cust_site_uses_all s_ship ,
739 hz_cust_acct_sites acct_site_ship,
740 hz_party_sites party_site_ship,
741 hz_locations loc_ship,
742 hz_loc_assignments loc_assign_ship,
743 hz_cust_accounts cust_acct
744 WHERE s_ship.site_use_id = l_site_use_id
745 and s_ship.cust_acct_site_id = acct_site_ship.cust_acct_site_id
746 and acct_site_ship.cust_account_id = cust_acct.cust_account_id
747 and acct_site_ship.party_site_id = party_site_ship.party_site_id
748 and party_site_ship.location_id = loc_ship.location_id
749 and acct_site_ship.org_id = loc_assign_ship.org_id ; -- New code Yogeshwar (MOAC)
750 --Commented Code Yogeshwar Start (MOAC)
751 -- and NVL(acct_site_ship.org_id,
752 -- NVL(to_number(decode(substrb(userenv('client_info'),1 ,1), ' ',null,
753 -- substrb(userenv('client_info'), 1,10))),-99)) =
754 -- NVL(loc_assign_ship.org_id,
755 -- NVL(to_number(decode( substrb(userenv('client_info'),1,1), ' ',null,
756 -- substrb(userenv('client_info'),1,10))), -99));
757 --End of comments Yogeshwar (MOAC)
758 --Need ORG striped synonym for HZ_LOC_ASSIGNMENTS
759 cursor getpartyinfo is
760 select sl.postal_code, sla.loc_id, sl.location_id
761 from hz_party_sites sps,
762 hz_locations sl,
763 hz_loc_assignments sla
764 where sps.party_site_id = l_party_site_id
765 and SPS.location_id = SL.location_id
766 and sl.location_id = sla.location_id ;
767 --Commented Code Yogeshwar (MOAC)
768 -- and sla.org_id = nvl(to_number(decode(substrb( userenv('CLIENT_INFO'),1,1),' ', null,
769 -- substrb(userenv('CLIENT_INFO'),1,10))),-99);
770 --End of commented Code -- Yogeshwar (MOAC)
771 --Need ORG striped synonym for HZ_LOC_ASSIGNMENTS
772 cursor c_currency is
773 select minimum_accountable_unit,precision
774 from fnd_currencies
775 where currency_code = l_currency_code;
776
777 cursor c_person(c_resource_id number) is
778 select person_id,sales_tax_geocode,sales_tax_inside_city_limits, salesrep_id
779 -- from jtf_rs_srp_vl Commented code Yogeshwar (MOAC)
780 from JTF_RS_SALESREPS_MO_V --New Code Yogeshwar (MOAC)
781 where resource_id=c_resource_id ;
782 --Commented code start yogeshwar (MOAC)
783 -- and nvl(org_id,nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1), ' ',
784 -- null, substrb(userenv('CLIENT_INFO'),1,10))),-99)) = nvl(to_number(decode(substrb(
785 -- userenv('CLIENT_INFO'),1,1), ' ', null, substrb(userenv('CLIENT_INFO'),1,10))),-99);
786 --Commented code end yogeshwar (MOAC)
787
788 cursor c_asgn(c_person_id number) is
789 select organization_id
790 from per_all_assignments_f
791 where person_id = c_person_id
792 and nvl(primary_flag, 'Y') = 'Y'
793 and sysdate between nvl(effective_start_date,to_date( '01011900', 'DDMMYYYY'))
794 and nvl(effective_end_date,to_date( '31122199', 'DDMMYYYY'));
795
796
797 cursor c_tax_code( c_tax_id number ) is
798 select tax_code
799 from ar_vat_tax
800 where vat_tax_id = c_tax_id;
801
802 cursor getpartyname (p_party_id number) is
803 select party_name
804 from hz_parties
805 where party_id = p_party_id;
806
807 cursor c_global_attributes( p_inventory_item_id number, p_organization_id number) is
808 select global_attribute1, global_attribute2
809 from mtl_system_items_b
810 where inventory_item_id = p_inventory_item_id
811 and organization_id = p_organization_id;
812
813 Begin
814
815 x_return_status := FND_API.G_RET_STS_SUCCESS;
816
817 IF aso_debug_pub.g_debug_flag = 'Y' THEN
818
819 aso_debug_pub.add('ASO_TAX_INT: Begin ASO_TAX_LINE', 1, 'Y');
820
821 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: p_qte_header_id: '|| p_qte_header_id, 1, 'Y');
822 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: p_qte_line_id: '|| p_qte_line_id, 1, 'Y');
823
824 END IF;
825
826 -- Retrieve Quote header and line information from database
827 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_id);
828
829 IF aso_debug_pub.g_debug_flag = 'Y' THEN
830 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: After call to ASO_UTILITY_PVT.Query_Header_Row', 1, 'Y');
831 END IF;
832
833 If p_qte_line_id is null or p_qte_line_id = FND_API.G_MISS_NUM then
834
835 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows(p_qte_header_id);
836
837 IF aso_debug_pub.g_debug_flag = 'Y' THEN
838 aso_debug_pub.add('ASO_TAX_LINE: After call to ASO_UTILITY_PVT.Query_Qte_Line_Rows', 1, 'Y');
839 END IF;
840
841 else
842
843 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_id);
844 l_qte_line_tbl(1) := l_qte_line_rec;
845
846 IF aso_debug_pub.g_debug_flag = 'Y' THEN
847 aso_debug_pub.add('ASO_TAX_LINE: After call to ASO_UTILITY_PVT.Query_Qte_Line_Row', 1, 'Y');
848 END IF;
849
850 end if;
851
852 -- Get the tax method
853
854 Begin
855
856 IF aso_debug_pub.g_debug_flag = 'Y' THEN
857 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: Before call to ARP_TAX_CRM_INTEGRATION_PKG.tax_method', 1, 'Y');
858 END IF;
859
860 arp_tax_crm_integration_pkg.tax_method (l_tax_method,l_vendor_installed);
861
862 IF aso_debug_pub.g_debug_flag = 'Y' THEN
863 aso_debug_pub.add('ASO_TAX_LINE: After Call to tax_method', 1, 'Y');
864 aso_debug_pub.add('ASO_TAX_LINE: l_tax_method: '|| l_tax_method, 1, 'Y');
865 aso_debug_pub.add('ASO_TAX_LINE: l_vendor_installed: '|| l_vendor_installed, 1, 'Y');
866 END IF;
867
868 EXCEPTION
869
870 WHEN OTHERS THEN
871
872 IF aso_debug_pub.g_debug_flag = 'Y' THEN
873
874 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: Exception raised in ARP_TAX_CRM_INTEGRATION_PKG.tax_method', 1, 'Y');
875
876 END IF;
877
878 l_reason := 'ARP_TAX_CRM_INTEGRATION_PKG.tax_method is raising an exception.';
879
880 aso_quote_misc_pvt.debug_tax_info_notification(l_qte_header_rec, l_Shipment_rec, l_reason);
881 End;
882
883 IF p_qte_header_id is not null and p_qte_header_id <> FND_API.G_MISS_NUM then
884
885 Begin
886
887 -- Get the AR system parameters
888
889 select tax_rounding_allow_override,
890 tax_header_level_flag,
891 tax_rounding_rule,
892 set_of_books_id
893 into l_tax_rounding_allow_override,
894 l_tax_header_level_flag,
895 l_tax_rounding_rule,
896 l_set_of_books_id
897 from ar_system_parameters;
898
899 EXCEPTION
900
901 WHEN NO_DATA_FOUND THEN
902
903 IF aso_debug_pub.g_debug_flag = 'Y' THEN
904 aso_debug_pub.add('ASO_TAX_LINE: NO_DATA_FOUND from AR_SYSTEM_PARAMETERS table', 1, 'Y');
905 END IF;
906
907 l_reason := 'No Data Found while selecting tax_rounding_rule, set_of_books_id';
908 l_reason := l_reason || 'from ar_system_parameters table.';
909
910 aso_quote_misc_pvt.debug_tax_info_notification( l_qte_header_rec,
911 l_Shipment_rec, l_reason);
912
913 End;
914
915 IF aso_debug_pub.g_debug_flag = 'Y' THEN
916
917 aso_debug_pub.add('ASO_TAX_LINE: After selecting from AR_SYSTEM_PARAMETERS table.', 1, 'Y');
918 aso_debug_pub.add('l_tax_rounding_allow_override: '|| l_tax_rounding_allow_override, 1, 'Y');
919 aso_debug_pub.add('l_tax_header_level_flag: '|| l_tax_header_level_flag, 1, 'Y');
920 aso_debug_pub.add('l_tax_rounding_rule: '|| l_tax_rounding_rule, 1, 'Y');
921 aso_debug_pub.add('l_set_of_books_id: '|| l_set_of_books_id, 1, 'Y');
922
923 aso_debug_pub.add('ASO_TAX_LINE: Before beginng of the Quote line loop', 1, 'Y');
924 aso_debug_pub.add('ASO_TAX_LINE: l_qte_line_tbl.count: '||l_qte_line_tbl.count, 1, 'Y');
925
926 END IF;
927
928
929 FOR i IN 1..l_qte_line_tbl.count LOOP
930
931
932 l_shipment_tbl := aso_utility_pvt.query_shipment_rows( p_qte_header_id,
933 l_qte_line_tbl(i).quote_line_id);
934
935 IF aso_debug_pub.g_debug_flag = 'Y' THEN
936 aso_debug_pub.add('ASO_TAX_LINE: l_shipment_tbl.count: '|| l_shipment_tbl.count, 1, 'Y');
937 END IF;
938
939 l_shipment_rec := l_shipment_tbl(1);
940 l_qte_line_rec := l_qte_line_tbl(i);
941
942 -- Get ship_from_org_id
943 l_ship_from_org_id := ASO_SHIPMENT_PVT.get_ship_from_org_id(p_qte_header_id,
944 l_qte_line_tbl(i).quote_line_id);
945
946 IF aso_debug_pub.g_debug_flag = 'Y' THEN
947 aso_debug_pub.add('ASO_TAX_LINE: After call to Get_ship_from_org_id: l_ship_from_org_id: '||l_ship_from_org_id, 1, 'Y');
948 END IF;
949
950 IF l_ship_from_org_id IS NULL OR l_ship_from_org_id = FND_API.G_MISS_NUM THEN
951
952 l_ship_from_org_id := fnd_profile.value( 'ASO_SHIP_FROM_ORG_ID' );
953
954 IF aso_debug_pub.g_debug_flag = 'Y' THEN
955 aso_debug_pub.add('ASO_TAX_LINE: Profile ASO_SHIP_FROM_ORG_ID value', 1, 'Y');
956 aso_debug_pub.add('ASO_TAX_LINE: l_ship_from_org_id: '|| l_ship_from_org_id, 1, 'Y');
957 END IF;
958
959 END IF;
960
961 --Get Line type info
962 IF aso_debug_pub.g_debug_flag = 'Y' THEN
963 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: Before call to get_ra_trx_type_id', 1, 'Y');
964 END IF;
965
966 l_trx_type_id := get_ra_trx_type_id(l_qte_header_rec.order_type_id,l_qte_line_rec);
967
968 IF aso_debug_pub.g_debug_flag = 'Y' THEN
969 aso_debug_pub.add('After call to get_ra_trx_type_id: l_trx_type_id: '|| l_trx_type_id, 1, 'Y');
970 END IF;
971
972 --Get the currency info
973 IF l_qte_line_tbl(i).currency_code is not null THEN
974 l_currency_code := l_qte_line_tbl(i).currency_code;
975 ELSE
976 l_currency_code := l_qte_header_rec.currency_code;
977 END IF;
978
979 IF aso_debug_pub.g_debug_flag = 'Y' THEN
980 aso_debug_pub.add('ASO_TAX_LINE: l_currency_code: '|| l_currency_code, 1, 'Y');
981 END IF;
982
983
984 --Get site use information
985 IF aso_debug_pub.g_debug_flag = 'Y' THEN
986 aso_debug_pub.add('ASO_TAX_LINE: Before call to aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
987 END IF;
988
989 l_site_use_id_ship := aso_shipment_pvt.get_ship_to_site_id( l_shipment_rec.quote_header_id,
990 l_shipment_rec.quote_line_id,
991 l_shipment_rec.shipment_id );
992
993 IF aso_debug_pub.g_debug_flag = 'Y' THEN
994 aso_debug_pub.add('After call: l_site_use_id_ship: ' || l_site_use_id_ship, 1, 'Y');
995 aso_debug_pub.add('Before call to aso_shipment_pvt.get_cust_to_party_site_id', 1, 'Y');
996 END IF;
997
998 l_site_use_id_bill := aso_shipment_pvt.get_cust_to_party_site_id
999 ( l_qte_line_tbl(i).quote_header_id,
1000 l_qte_line_tbl(i).quote_line_id );
1001
1002 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1003 aso_debug_pub.add('After call: l_site_use_id_bill: '|| l_site_use_id_bill, 1, 'Y');
1004 aso_debug_pub.add('Before call to aso_payment_int.get_payment_term_id', 1, 'Y');
1005 END IF;
1006
1007
1008 l_payment_term_id := aso_payment_int.get_payment_term_id( p_qte_header_id,
1009 l_qte_line_tbl(i).quote_line_id);
1010
1011 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1012 aso_debug_pub.add('After call: l_payment_term_id: '|| l_payment_term_id, 1, 'Y');
1013 END IF;
1014
1015 l_tax_detail_tbl := aso_utility_pvt.query_tax_detail_Rows( p_qte_header_id,
1016 l_qte_line_tbl(i).quote_line_id,
1017 l_shipment_tbl);
1018
1019 l_hdr_tax_detail_tbl := aso_utility_pvt.query_tax_detail_rows( p_qte_header_id, null,
1020 l_shipment_tbl);
1021
1022 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1023 aso_debug_pub.add('ASO_TAX_LINE: l_tax_detail_tbl.count: '||l_tax_detail_tbl.count, 1, 'Y');
1024 aso_debug_pub.add('ASO_TAX_LINE: l_hdr_tax_detail_tbl.count : '||l_hdr_tax_detail_tbl.count, 1, 'Y');
1025 END IF;
1026
1027 IF l_hdr_tax_detail_tbl.count > 0 THEN
1028
1029 IF l_hdr_tax_detail_tbl(1).tax_exempt_flag is null THEN
1030
1031 l_hdr_tax_exempt_flag := 'S';
1032 ELSE
1033 l_hdr_tax_exempt_flag := l_hdr_tax_detail_tbl(1).tax_exempt_flag;
1034 END IF;
1035
1036 IF l_hdr_tax_detail_tbl(1).tax_exempt_number is null THEN
1037
1038 l_hdr_tax_exempt_number := null;
1039 ELSE
1040 l_hdr_tax_exempt_number := l_hdr_tax_detail_tbl(1).tax_exempt_number;
1041 END IF;
1042
1043 IF l_hdr_tax_detail_tbl(1).tax_exempt_reason_code is null THEN
1044
1045 l_hdr_tax_exempt_reason_code := null;
1046 ELSE
1047 l_hdr_tax_exempt_reason_code := l_hdr_tax_detail_tbl(1).tax_exempt_reason_code;
1048 END IF;
1049
1050 IF l_hdr_tax_detail_tbl(1).tax_date is null THEN
1051
1052 l_hdr_tax_date := l_sys_date;--sysdate;
1053 ELSE
1054 l_hdr_tax_date := l_hdr_tax_detail_tbl(1).tax_date;
1055 END IF;
1056
1057 ELSE
1058
1059 l_hdr_tax_date := l_sys_date;--sysdate;
1060 l_hdr_tax_exempt_number := null;
1061 l_hdr_tax_exempt_reason_code := null;
1062 l_hdr_tax_exempt_flag := 'S';
1063
1064 END IF;
1065
1066 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1067
1068 aso_debug_pub.add('ASO_TAX_LINE: Header level exemption information', 1, 'Y');
1069 aso_debug_pub.add('l_hdr_tax_date: '|| l_hdr_tax_date, 1, 'Y');
1070 aso_debug_pub.add('l_hdr_tax_exempt_number: '|| l_hdr_tax_exempt_number, 1, 'Y');
1071 aso_debug_pub.add('l_hdr_tax_exempt_reason_code: '|| l_hdr_tax_exempt_reason_code, 1, 'Y');
1072 aso_debug_pub.add('l_hdr_tax_exempt_flag: '|| l_hdr_tax_exempt_flag, 1, 'Y');
1073
1074 aso_debug_pub.add('Before selecting cust_trx_type_id from ra_cust_trx_types_all table', 1, 'Y');
1075 aso_debug_pub.add('ASO_TAX_LINE: l_trx_type_id: '|| l_trx_type_id, 1, 'Y');
1076
1077 END IF;
1078
1079
1080 IF l_tax_method <> 'LATIN' THEN
1081
1082 Begin
1083
1084 select cust_trx_type_id
1085 into l_ra_cust_trx_type_id
1086 --from ra_cust_trx_types_all Commented Code yogeshwar (MOAC)
1087 from ra_cust_trx_types --New Code Yogeshwar (MOAC)
1088 where cust_trx_type_id = l_trx_type_id
1089 --Commented Code Start Yogeshwar (MOAC)
1090 -- and nvl(org_id,
1091 -- nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1 ,1), ' ',null,
1092 -- substrb(userenv('CLIENT_INFO'), 1,10))),-99)) =
1093 -- nvl(l_qte_header_rec.org_id,
1094 -- nvl(to_number(decode( substrb(userenv('CLIENT_INFO'),1,1), ' ',null,
1095 -- substrb(userenv('CLIENT_INFO'),1,10))), -99))
1096 --Commented Code End Yogeshwar (MOAC)
1097 and ((tax_calculation_flag = 'Y')
1098 or (l_hdr_tax_exempt_flag='R' ) );
1099
1100 EXCEPTION
1101
1102 WHEN NO_DATA_FOUND THEN
1103
1104 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1105 aso_debug_pub.add('ASO_TAX_LINE: NO_DATA_FOUND when selecting cust_trx_type_id', 1, 'Y');
1106 END IF;
1107
1108 l_reason := 'No Data Found Exception raised while selecting cust_trx_type_id';
1109 l_reason := l_reason || 'from ra_cust_trx_types_all. ';
1110 l_reason := l_reason || fnd_global.newline();
1111 l_reason := l_reason || 'Please check Default order type id profile is correctly';
1112 l_reason := l_reason || 'set. Also pl verify the';
1113 l_reason := l_reason || fnd_global.newline();
1114 l_reason := l_reason || 'the value the profile is returning. No tax call being';
1115 l_reason := l_reason || 'being made to tax engine.';
1116
1117 aso_quote_misc_pvt.debug_tax_info_notification(l_qte_header_rec,
1118 l_Shipment_rec, l_reason);
1119
1120 if aso_debug_pub.g_debug_flag = 'Y' then
1121 aso_debug_pub.add('ASO_TAX_LINE: Before deleting all tax records for the quote line', 1, 'Y');
1122 end if;
1123
1124 if l_tax_detail_tbl.count > 0 then
1125
1126 delete from aso_tax_details
1127 where quote_header_id = l_tax_detail_tbl(1).quote_header_id
1128 and quote_line_id = l_tax_detail_tbl(1).quote_line_id ;
1129
1130 end if;
1131
1132 if aso_debug_pub.g_debug_flag = 'Y' then
1133 aso_debug_pub.add('ASO_TAX_LINE: After deleting all tax records for the quote line', 1, 'Y');
1134 end if;
1135
1136 End;
1137
1138 open c_currency;
1139 fetch c_currency into l_minimum_accountable_unit, l_precision;
1140 close c_currency;
1141
1142 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1143 aso_debug_pub.add('ASO_TAX_LINE: l_ra_cust_trx_type_id: '|| l_ra_cust_trx_type_id, 1, 'Y');
1144 aso_debug_pub.add('ASO_TAX_LINE: After c_currency cursor fetch', 1, 'Y');
1145 aso_debug_pub.add('ASO_TAX_LINE: l_minimum_accountable_unit: '|| l_minimum_accountable_unit, 1, 'Y');
1146 aso_debug_pub.add('ASO_TAX_LINE: l_precision: '|| l_precision, 1, 'Y');
1147 END IF;
1148
1149 IF l_qte_header_rec.resource_id is NOT NULL THEN
1150
1151 open c_person(l_qte_header_rec.resource_id);
1152 fetch c_person into l_person_id, l_sales_tax_geocode,
1153 l_sales_tax_inside_city_limits, l_salesrep_id;
1154
1155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1156 aso_debug_pub.add('ASO_TAX_LINE: After c_person cursor fetch', 1, 'Y');
1157 aso_debug_pub.add('l_person_id: '|| l_person_id, 1, 'Y');
1158 aso_debug_pub.add('l_sales_tax_geocode: '|| l_sales_tax_geocode, 1, 'Y');
1159 aso_debug_pub.add('l_sales_tax_inside_city_limits: '|| l_sales_tax_inside_city_limits, 1, 'Y');
1160 aso_debug_pub.add('l_salesrep_id: '|| l_salesrep_id, 1, 'Y');
1161 END IF;
1162
1163
1164 IF C_PERSON%NOTFOUND THEN
1165 CLOSE C_PERSON;
1166 l_POO_ID := l_qte_header_rec.org_id;
1167 l_person_id :=NULL;
1168 L_SALES_TAX_GEOCODE :=NULL;
1169 L_SALES_TAX_INSIDE_CITY_LIMITS := NULL;
1170
1171 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1172 aso_debug_pub.add('ASO_TAX_LINE: Inside C_PERSON%NOTFOUND: l_POO_ID: '||l_POO_ID, 1, 'Y');
1173 END IF;
1174
1175 ELSE
1176 CLOSE C_PERSON;
1177 OPEN C_ASGN(l_person_id);
1178 FETCH C_ASGN INTO l_asgn_org_id;
1179 IF C_ASGN%NOTFOUND THEN
1180 l_asgn_org_id := NULL;
1181 ELSE
1182 l_poo_id := l_asgn_org_id;
1183
1184 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1185 aso_debug_pub.add('Inside c_person%found and c_asgn%found: l_poo_id: '|| l_poo_id, 1, 'Y');
1186 END IF;
1187
1188 END IF;
1189 CLOSE C_ASGN;
1190
1191 END IF;
1192
1193 ELSE
1194
1195 L_POO_ID := l_qte_header_rec.org_id;
1196
1197 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1198 aso_debug_pub.add('ASO_TAX_LINE: IF l_qte_header_rec.resource_id is NULL then: l_POO_ID: '||l_POO_ID, 1,'Y');
1199 END IF;
1200
1201 END IF;
1202
1203
1204 IF l_site_use_id_ship is not null AND l_site_use_id_ship <> FND_API.G_MISS_NUM THEN
1205
1206 l_site_use_id := l_site_use_id_ship;
1207
1208 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1209 aso_debug_pub.add('ASO_TAX_LINE: Inside l_site_use_id_ship IF Cond: l_site_use_id: ' || l_site_use_id, 1, 'Y');
1210 END IF;
1211
1212 OPEN getlocinfo;
1213 FETCH getlocinfo
1214 INTO L_SHIP_TO_SITE_USE_ID,
1215 L_SHIP_TO_ADDRESS_ID,
1216 L_SHIP_TO_CUSTOMER_ID,
1217 L_SHIP_TO_POSTAL_CODE,
1218 L_SHIP_TO_LOCATION_CCID,
1219 L_PARTY_ID,
1220 L_SHIP_TO_CUSTOMER_NUMBER,
1221 L_BC_TAX_HEADER_LEVEL_FLAG,
1222 L_BC_TAX_ROUNDING_RULE,
1223 L_SHIP_TO_STATE,
1224 L_SHIP_TAX_HEADER_LEVEL_FLAG,
1225 L_SHIP_TAX_ROUNDING_RULE;
1226
1227 IF getlocinfo%NOTFOUND THEN
1228
1229 L_SHIP_TO_ADDRESS_ID := -1;
1230 L_SHIP_TO_CUSTOMER_ID := NULL;
1231 L_SHIP_TO_POSTAL_CODE := NULL;
1232 L_SHIP_TO_LOCATION_CCID := NULL;
1233 L_SHIP_TO_CUSTOMER_NAME := NULL;
1234 L_SHIP_TO_CUSTOMER_NUMBER := NULL;
1235 L_BC_TAX_HEADER_LEVEL_FLAG := NULL;
1236 L_BC_TAX_ROUNDING_RULE := NULL;
1237
1238 ELSE
1239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1240
1241 aso_debug_pub.add('ASO_TAX_LINE: Inside else cond of getlocinfo cursor for SHIP_TO', 1, 'Y');
1242 aso_debug_pub.add('ASO_TAX_LINE: l_party_id: '|| l_party_id, 1, 'Y');
1243
1244 END IF;
1245
1246 open getpartyname (l_party_id);
1247 fetch getpartyname into l_ship_to_customer_name;
1248 close getpartyname;
1249
1250 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1251 aso_debug_pub.add('ASO_TAX_LINE: l_ship_to_customer_name: '|| l_ship_to_customer_name, 1, 'Y');
1252 END IF;
1253
1254 END IF;
1255 CLOSE getlocinfo;
1256
1257 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1258
1259 aso_debug_pub.add('ASO_TAX_LINE: Inside l_site_use_id_ship IF Cond: After fetching getlocinfo cursor.', 1, 'Y');
1260 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_SITE_USE_ID: '|| L_SHIP_TO_SITE_USE_ID, 1, 'Y');
1261 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_ADDRESS_ID: '|| L_SHIP_TO_ADDRESS_ID, 1, 'Y');
1262 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_CUSTOMER_ID: '|| L_SHIP_TO_CUSTOMER_ID, 1, 'Y');
1263 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_POSTAL_CODE: '|| L_SHIP_TO_POSTAL_CODE, 1, 'Y');
1264 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_LOCATION_CCID: '|| L_SHIP_TO_LOCATION_CCID, 1, 'Y');
1265 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_CUSTOMER_NAME: '|| L_SHIP_TO_CUSTOMER_NAME, 1, 'Y');
1266 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_CUSTOMER_NUMBER: '|| L_SHIP_TO_CUSTOMER_NUMBER, 1, 'Y');
1267 aso_debug_pub.add('ASO_TAX_LINE: L_BC_TAX_HEADER_LEVEL_FLAG: '|| L_BC_TAX_HEADER_LEVEL_FLAG, 1, 'Y');
1268 aso_debug_pub.add('ASO_TAX_LINE: L_BC_TAX_ROUNDING_RULE: '|| L_BC_TAX_ROUNDING_RULE, 1, 'Y');
1269 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_STATE: '|| L_SHIP_TO_STATE, 1, 'Y');
1270 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TAX_ROUNDING_RULE: '|| L_SHIP_TAX_ROUNDING_RULE, 1, 'Y');
1271 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TAX_HEADER_LEVEL_FLAG: ' || L_SHIP_TAX_HEADER_LEVEL_FLAG, 1, 'Y');
1272 END IF;
1273
1274 END IF; -- l_site_use_id_ship;
1275
1276
1277 IF l_site_use_id_bill is not null and l_site_use_id_bill <> FND_API.G_MISS_NUM THEN
1278
1279 l_site_use_id := l_site_use_id_bill;
1280
1281 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1282 aso_debug_pub.add('ASO_TAX_LINE: Inside l_site_use_id_bill IF Cond: l_site_use_id: ' || l_site_use_id, 1, 'Y');
1283 END IF;
1284
1285 OPEN getlocinfo;
1286 FETCH getlocinfo
1287 INTO L_BILL_TO_SITE_USE_ID,
1288 L_BILL_TO_ADDRESS_ID,
1289 L_BILL_TO_CUSTOMER_ID,
1290 L_BILL_TO_POSTAL_CODE,
1291 L_BILL_TO_LOCATION_CCID,
1292 L_PARTY_ID,
1293 L_BILL_TO_CUSTOMER_NUMBER,
1294 L_BC_TAX_HEADER_LEVEL_FLAG,
1295 L_BC_TAX_ROUNDING_RULE,
1296 L_BILL_TO_STATE,
1297 L_BILL_TAX_HEADER_LEVEL_FLAG,
1298 L_BILL_TAX_ROUNDING_RULE;
1299
1300 IF getlocinfo%NOTFOUND THEN
1301
1302 L_BILL_TO_ADDRESS_ID := -1;
1303 L_BILL_TO_CUSTOMER_ID := NULL;
1304 L_BILL_TO_POSTAL_CODE := NULL;
1305 L_BILL_TO_LOCATION_CCID := NULL;
1306 L_BILL_TO_CUSTOMER_NAME := NULL;
1307 L_BILL_TO_CUSTOMER_NUMBER := NULL;
1308 L_BC_TAX_HEADER_LEVEL_FLAG := NULL;
1309 L_BC_TAX_ROUNDING_RULE := NULL;
1310
1311 ELSE
1312 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1313 aso_debug_pub.add('ASO_TAX_LINE: Inside else cond of getlocinfo cursor for BILL_TO', 1, 'Y');
1314 aso_debug_pub.add('ASO_TAX_LINE: l_party_id: '|| l_party_id, 1, 'Y');
1315 END IF;
1316
1317 OPEN getpartyname (l_party_id);
1318 FETCH getpartyname INTO l_bill_to_customer_name;
1319 CLOSE getpartyname;
1320
1321 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1322 aso_debug_pub.add('ASO_TAX_LINE: l_bill_to_customer_name: '|| l_bill_to_customer_name, 1, 'Y');
1323 END IF;
1324
1325 END IF;
1326 CLOSE getlocinfo;
1327
1328 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1329
1330 aso_debug_pub.add('ASO_TAX_LINE: Inside l_site_use_id_bill IF Cond: After fetching getlocinfo cursor.', 1, 'Y');
1331 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_SITE_USE_ID: '|| L_BILL_TO_SITE_USE_ID, 1, 'Y');
1332 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_ADDRESS_ID: '|| L_BILL_TO_ADDRESS_ID, 1, 'Y');
1333 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_CUSTOMER_ID: '|| L_BILL_TO_CUSTOMER_ID, 1, 'Y');
1334 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_POSTAL_CODE: '|| L_BILL_TO_POSTAL_CODE, 1, 'Y');
1335 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_LOCATION_CCID: '|| L_BILL_TO_LOCATION_CCID, 1, 'Y');
1336 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_CUSTOMER_NAME: '|| L_BILL_TO_CUSTOMER_NAME, 1, 'Y');
1337 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_CUSTOMER_NUMBER: '|| L_BILL_TO_CUSTOMER_NUMBER, 1, 'Y');
1338 aso_debug_pub.add('ASO_TAX_LINE: L_BC_TAX_HEADER_LEVEL_FLAG: '|| L_BC_TAX_HEADER_LEVEL_FLAG, 1, 'Y');
1339 aso_debug_pub.add('ASO_TAX_LINE: L_BC_TAX_ROUNDING_RULE: '|| L_BC_TAX_ROUNDING_RULE, 1, 'Y');
1340 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_STATE: '|| L_BILL_TO_STATE, 1, 'Y');
1341 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TAX_ROUNDING_RULE: '|| L_BILL_TAX_ROUNDING_RULE, 1, 'Y');
1342 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TAX_HEADER_LEVEL_FLAG: '|| L_BILL_TAX_HEADER_LEVEL_FLAG, 1, 'Y');
1343
1344 END IF;
1345
1346 END IF; -- l_site_use_id_bill
1347
1348 IF (l_site_use_id_ship is null OR l_site_use_id_ship = FND_API.G_MISS_NUM) AND
1349 (l_site_use_id_bill is null OR l_site_use_id_bill = FND_API.G_MISS_NUM) THEN
1350
1351 -- Ship to party_site information
1352 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1353 aso_debug_pub.add('ASO_TAX_LINE: Before call to ASO_SHIPMENT_PVT.Get_ship_to_party_site_id', 1, 'Y');
1354 END IF;
1355
1356 l_party_site_id_ship := ASO_SHIPMENT_PVT.Get_ship_to_party_site_id(l_shipment_rec.quote_header_id, l_shipment_rec.quote_line_id, l_shipment_rec.shipment_id);
1357
1358 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1359 aso_debug_pub.add('ASO_TAX_LINE: After call to Get_ship_to_party_site_id: l_party_site_id_ship: '|| l_party_site_id_ship, 1, 'Y');
1360 END IF;
1361
1362 IF l_party_site_id_ship IS NOT NULL AND l_party_site_id_ship <> FND_API.G_MISS_NUM THEN
1363
1364 l_party_site_id := l_party_site_id_ship;
1365
1366 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1367 aso_debug_pub.add('ASO_TAX_LINE: Inside l_party_site_id_ship IF Cond: l_party_site_id: '|| l_party_site_id, 1, 'Y');
1368 END IF;
1369
1370 OPEN getpartyinfo;
1371 FETCH getpartyinfo INTO l_ship_to_postal_code, l_ship_to_location_ccid,l_ship_loc_asgn_id;
1372 IF getpartyinfo%NOTFOUND THEN
1373 L_SHIP_TO_POSTAL_CODE := NULL;
1374 L_SHIP_TO_LOCATION_CCID := NULL;
1375 L_SHIP_LOC_ASGN_ID := NULL;
1376 END IF;
1377 CLOSE getpartyinfo;
1378
1379 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1380
1381 aso_debug_pub.add('ASO_TAX_LINE: Inside l_party_site_id_ship IF Cond: After fetching getpartyinfo cursor.', 1, 'Y');
1382 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_POSTAL_CODE: ' || L_SHIP_TO_POSTAL_CODE, 1, 'Y');
1383 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_TO_LOCATION_CCID: ' || L_SHIP_TO_LOCATION_CCID, 1, 'Y');
1384 aso_debug_pub.add('ASO_TAX_LINE: L_SHIP_LOC_ASGN_ID: ' || L_SHIP_LOC_ASGN_ID, 1, 'Y');
1385
1386 END IF;
1387
1388 END IF; --l_party_site_id_ship
1389
1390 -- Bill to party_site information
1391 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1392 aso_debug_pub.add('ASO_TAX_LINE: Before call to ASO_SHIPMENT_PVT.Get_invoice_to_party_site_id', 1,'Y');
1393 END IF;
1394
1395 l_party_site_id_bill := ASO_SHIPMENT_PVT.Get_invoice_to_party_site_id( l_shipment_rec.quote_header_id, l_shipment_rec.quote_line_id);
1396
1397 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1398 aso_debug_pub.add('ASO_TAX_LINE: After call to Get_invoice_to_party_site_id: l_party_site_id_bill: '|| l_party_site_id_bill,1,'Y');
1399 END IF;
1400
1401 IF l_party_site_id_bill IS NOT NULL AND l_party_site_id_bill <> FND_API.G_MISS_NUM THEN
1402
1403 l_party_site_id := l_party_site_id_bill;
1404
1405 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1406 aso_debug_pub.add('ASO_TAX_LINE: Inside l_party_site_id_bill IF Cond: l_party_site_id: '|| l_party_site_id,1,'Y');
1407 END IF;
1408
1409 OPEN getpartyinfo;
1410 FETCH getpartyinfo INTO l_bill_to_postal_code, l_bill_to_location_ccid,l_bill_loc_asgn_id;
1411
1412 IF getpartyinfo%NOTFOUND THEN
1413
1414 l_bill_to_postal_code := null;
1415 l_bill_to_location_ccid := null;
1416 l_bill_loc_asgn_id := null;
1417
1418 END IF;
1419
1420 CLOSE getpartyinfo;
1421
1422 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1423
1424 aso_debug_pub.add('ASO_TAX_LINE: Inside l_party_site_id_bill IF Cond: After fetching getpartyinfo cursor.', 1, 'Y');
1425 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_POSTAL_CODE: ' || L_BILL_TO_POSTAL_CODE, 1, 'Y');
1426 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_TO_LOCATION_CCID: ' || L_BILL_TO_LOCATION_CCID, 1, 'Y');
1427 aso_debug_pub.add('ASO_TAX_LINE: L_BILL_LOC_ASGN_ID: ' || L_BILL_LOC_ASGN_ID, 1, 'Y');
1428
1429 END IF;
1430
1431 END IF;--l_party_site_id
1432
1433 END IF; --l_site_use_id bill and l_site_use_id_ship;
1434
1435
1436 IF nvl(l_tax_rounding_allow_override, 'N') = 'Y' THEN
1437
1438 l_tax_header_level_flag := nvl( l_bill_tax_header_level_flag,
1439 nvl( l_bc_tax_header_level_flag,
1440 nvl( l_tax_header_level_flag, 'N' )));
1441 ELSE
1442
1443 l_tax_header_level_flag := nvl( l_tax_header_level_flag, 'N' );
1444
1445 END IF;
1446
1447 IF nvl(l_tax_rounding_allow_override, 'N') = 'Y' THEN
1448
1449 l_tax_rounding_rule := nvl( l_bill_tax_rounding_rule, nvl( l_bc_tax_rounding_rule,
1450 nvl( l_tax_rounding_rule, 'NEAREST')));
1451 ELSE
1452
1453 l_tax_rounding_rule := nvl(l_tax_rounding_rule, 'NEAREST' );
1454
1455 END IF;
1456
1457 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1458 aso_debug_pub.add('ASO_TAX_LINE: l_tax_header_level_flag: '|| l_tax_header_level_flag, 1, 'Y');
1459 aso_debug_pub.add('ASO_TAX_LINE: l_tax_rounding_rule: '|| l_tax_rounding_rule, 1, 'Y');
1460 END IF;
1461
1462
1463 IF l_tax_method = 'VERTEX' then
1464
1465 Begin
1466
1467 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1468 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: Inside IF cond l_tax_method = VERTEX ', 1, 'Y');
1469 END IF;
1470
1471 L_POO_ADDRESS_CODE := ARP_TAX_VIEW_VERTEX.POO_ADDRESS_CODE('ASO_TAX_LINES_SUMMARY_V_V',
1472 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1473
1474 L_POA_ADDRESS_CODE := ARP_TAX_VIEW_VERTEX.POA_ADDRESS_CODE('ASO_TAX_LINES_SUMMARY_V_V',
1475 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID,L_SALESREP_ID);
1476
1477 L_SHIP_FROM_ADDRESS_CODE := ARP_TAX_VIEW_VERTEX.SHIP_FROM_ADDRESS_CODE
1478 ('ASO_TAX_LINES_SUMMARY_V_V',P_QTE_HEADER_ID,
1479 L_SHIPMENT_REC.SHIPMENT_ID,l_ship_from_org_id);
1480
1481 L_SHIP_TO_ADDRESS_CODE := ARP_TAX_VIEW_VERTEX.SHIP_TO_ADDRESS_CODE
1482 ('ASO_TAX_LINES_SUMMARY_V_V',P_QTE_HEADER_ID,
1483 L_SHIPMENT_REC.SHIPMENT_ID,
1484 nvl(L_SHIP_TO_ADDRESS_ID, L_BILL_TO_ADDRESS_ID),
1485 nvl(L_SHIP_TO_LOCATION_CCID, L_BILL_TO_LOCATION_CCID),
1486 SYSDATE,nvl(L_SHIP_TO_STATE, L_BILL_TO_STATE),
1487 nvl(L_SHIP_TO_postal_code, L_BILL_TO_postal_code));
1488
1489 L_PART_NUMBER := ARP_TAX_VIEW_VERTEX.PRODUCT_CODE('ASO_TAX_LINES_SUMMARY_V_V',
1490 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID,
1491 l_qte_line_tbl(i).INVENTORY_ITEM_ID,null);
1492
1493 L_VENDOR_CONTROL_EXEMPTIONS := ARP_TAX_VIEW_VERTEX.VENDOR_CONTROL_EXEMPTIONS('ASO_TAX_LINES_SUMMARY_V_V', P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID, l_trx_type_id);
1494
1495 L_ATTRIBUTE1 := ARP_TAX_VIEW_VERTEX.TRX_LINE_TYPE('ASO_TAX_LINES_SUMMARY_V_V',
1496 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1497
1498 L_ATTRIBUTE2 := ARP_TAX_VIEW_VERTEX.CUSTOMER_CLASS('ASO_TAX_LINES_SUMMARY_V_V',
1499 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID,
1500 nvl(L_SHIP_TO_CUSTOMER_ID,L_BILL_TO_CUSTOMER_ID));
1501
1502 L_DIVISION_CODE := ARP_TAX_VIEW_VERTEX.DIVISION_CODE('ASO_TAX_LINES_SUMMARY_V_V',
1503 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1504
1505 L_COMPANY_CODE := ARP_TAX_VIEW_VERTEX.COMPANY_CODE('ASO_TAX_LINES_SUMMARY_V_V',
1506 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1507
1508 L_NUMERIC_ATTRIBUTE1 := ARP_TAX_VIEW_VERTEX.USE_SECONDARY ('ASO_TAX_LINES_SUMMARY_V_V',
1509 P_QTE_HEADER_ID,L_SHIPMENT_REC.SHIPMENT_ID);
1510
1511 L_NUMERIC_ATTRIBUTE2 := ARP_TAX_VIEW_VERTEX.STATE_TYPE ('ASO_TAX_LINES_SUMMARY_V_V',
1512 P_QTE_HEADER_ID,L_SHIPMENT_REC.SHIPMENT_ID);
1513
1514 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1515
1516 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_POO_ADDRESS_CODE'||L_POO_ADDRESS_CODE, 1, 'Y');
1517 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_POA_ADDRESS_CODE'||L_POA_ADDRESS_CODE, 1, 'Y');
1518 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_SHIP_FROM_ADDRESS_CODE'||L_SHIP_FROM_ADDRESS_CODE, 1, 'Y');
1519 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_SHIP_TO_ADDRESS_CODE'||L_SHIP_TO_ADDRESS_CODE, 1, 'Y');
1520 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_PART_NUMBER'||L_PART_NUMBER, 1, 'Y');
1521 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_VENDOR_CONTROL_EXEMPTIONS'||L_VENDOR_CONTROL_EXEMPTIONS, 1, 'Y');
1522 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_ATTRIBUTE1'||L_ATTRIBUTE1, 1, 'Y');
1523 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_ATTRIBUTE2'||L_ATTRIBUTE2, 1, 'Y');
1524 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_DIVISION_CODE'||L_DIVISION_CODE, 1, 'Y');
1525 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_COMPANY_CODE'||L_COMPANY_CODE, 1, 'Y');
1526 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_NUMERIC_ATTRIBUTE1'||L_NUMERIC_ATTRIBUTE1, 1, 'Y');
1527 aso_debug_pub.add('ASO_TAX_LINE: Inside vertex: L_NUMERIC_ATTRIBUTE2'||L_NUMERIC_ATTRIBUTE2, 1, 'Y');
1528
1529 END IF;
1530
1531 End; -- VERTEX
1532
1533 ELSIF l_tax_method = 'TAXWARE' then
1534
1535 Begin
1536
1537 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1538 aso_debug_pub.add('ASO_TAX_INT: ASO_TAX_LINE: Inside IF cond l_tax_method = TAXWARE ', 1, 'Y');
1539 END IF;
1540
1541 L_POO_ADDRESS_CODE := ARP_TAX_VIEW_TAXWARE.POO_ADDRESS_CODE('ASO_TAX_LINES_SUMMARY_V_A',
1542 P_QTE_HEADER_ID,L_SHIPMENT_REC.SHIPMENT_ID,L_SALESREP_ID);
1543
1544 L_POA_ADDRESS_CODE := ARP_TAX_VIEW_TAXWARE.POA_ADDRESS_CODE('ASO_TAX_LINES_SUMMARY_V_A',
1545 P_QTE_HEADER_ID,L_SHIPMENT_REC.SHIPMENT_ID);
1546
1547 L_SHIP_FROM_ADDRESS_CODE := ARP_TAX_VIEW_TAXWARE.SHIP_FROM_ADDRESS_CODE
1548 ('ASO_TAX_LINES_SUMMARY_V_A',P_QTE_HEADER_ID,
1549 L_SHIPMENT_REC.SHIPMENT_ID, l_ship_from_org_id);
1550
1551 L_SHIP_TO_ADDRESS_CODE := ARP_TAX_VIEW_TAXWARE.SHIP_TO_ADDRESS_CODE
1552 ('ASO_TAX_LINES_SUMMARY_V_A',P_QTE_HEADER_ID,
1553 L_SHIPMENT_REC.SHIPMENT_ID,
1554 nvl(L_SHIP_TO_ADDRESS_ID, L_BILL_TO_ADDRESS_ID),
1555 nvl(L_SHIP_TO_LOCATION_CCID, L_BILL_TO_LOCATION_CCID),
1556 sysdate,nvl(L_SHIP_TO_STATE, L_BILL_TO_STATE),
1557 nvl(L_SHIP_TO_postal_code, L_BILL_TO_postal_code));
1558
1559 L_PART_NUMBER := ARP_TAX_VIEW_TAXWARE.PRODUCT_CODE('ASO_TAX_LINES_SUMMARY_V_A',
1560 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID,
1561 l_qte_line_tbl(i).INVENTORY_ITEM_ID,null);
1562
1563 L_VENDOR_CONTROL_EXEMPTIONS := ARP_TAX_VIEW_TAXWARE.VENDOR_CONTROL_EXEMPTIONS('ASO_TAX_LINES_SUMMARY_V_A', P_QTE_HEADER_ID,L_SHIPMENT_REC.SHIPMENT_ID, L_TRX_TYPE_ID);
1564
1565 L_ATTRIBUTE1 := ARP_TAX_VIEW_TAXWARE.Calculation_Flag('ASO_TAX_LINES_SUMMARY_V_A',
1566 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1567
1568 L_ATTRIBUTE2 := ARP_TAX_VIEW_TAXWARE.USE_NEXPRO('ASO_TAX_LINES_SUMMARY_V_A',
1569 P_QTE_HEADER_ID, L_SHIPMENT_REC.SHIPMENT_ID);
1570
1571 l_numeric_attribute1 := arp_tax_view_taxware.use_secondary('ASO_TAX_LINES_SUMMARY_V_A',
1572 p_qte_header_id ,l_shipment_rec.shipment_id);
1573
1574 l_numeric_attribute2 := arp_tax_view_taxware.tax_sel_parm('ASO_TAX_LINES_SUMMARY_V_A',
1575 p_qte_header_id, l_shipment_rec.shipment_id);
1576
1577 l_numeric_attribute3 := arp_tax_view_taxware.tax_type('ASO_TAX_LINES_SUMMARY_V_A',
1578 p_qte_header_id, l_shipment_rec.shipment_id);
1579
1580 l_numeric_attribute4 := arp_tax_view_taxware.service_indicator('ASO_TAX_LINES_SUMMARY_V_A' , p_qte_header_id, l_shipment_rec.shipment_id);
1581
1582 l_division_code := arp_tax_view_taxware.division_code('ASO_TAX_LINES_SUMMARY_V_A',
1583 p_qte_header_id, l_shipment_rec.shipment_id);
1584
1585 l_company_code := arp_tax_view_taxware.company_code('ASO_TAX_LINES_SUMMARY_V_A',
1586 p_qte_header_id, l_shipment_rec.shipment_id);
1587
1588 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1589
1590 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_POO_ADDRESS_CODE'||L_POO_ADDRESS_CODE, 1, 'Y');
1591 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_POA_ADDRESS_CODE'||L_POA_ADDRESS_CODE, 1, 'Y');
1592 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_SHIP_FROM_ADDRESS_CODE'||L_SHIP_FROM_ADDRESS_CODE, 1, 'Y');
1593 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_SHIP_TO_ADDRESS_CODE'||L_SHIP_TO_ADDRESS_CODE, 1, 'Y');
1594 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_PART_NUMBER'||L_PART_NUMBER, 1, 'Y');
1595 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_VENDOR_CONTROL_EXEMPTIONS'||L_VENDOR_CONTROL_EXEMPTIONS, 1, 'Y');
1596 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_ATTRIBUTE1'||L_ATTRIBUTE1, 1, 'Y');
1597 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_ATTRIBUTE2'||L_ATTRIBUTE2, 1, 'Y');
1598 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_NUMERIC_ATTRIBUTE1'||L_NUMERIC_ATTRIBUTE1, 1, 'Y');
1599 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_NUMERIC_ATTRIBUTE2'||L_NUMERIC_ATTRIBUTE2, 1, 'Y');
1600 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_NUMERIC_ATTRIBUTE3'||L_NUMERIC_ATTRIBUTE3, 1, 'Y');
1601 aso_debug_pub.add('ASO_TAX_LINE: Inside TAXWARE: L_NUMERIC_ATTRIBUTE4'||L_NUMERIC_ATTRIBUTE4, 1, 'Y');
1602
1603 END IF;
1604
1605 End; -- TAXWARE
1606
1607 END IF; -- TAXWARE
1608
1609
1610 arp_tax.tax_info_rec.ship_to_cust_id := l_ship_to_customer_id;
1611 arp_tax.tax_info_rec.bill_to_cust_id := l_bill_to_customer_id;
1612 arp_tax.tax_info_rec.customer_trx_charge_line_id := null;
1613 arp_tax.tax_info_rec.customer_trx_line_id := l_qte_line_tbl(i).quote_line_id;
1614 arp_tax.tax_info_rec.customer_trx_id := p_qte_header_id;
1615 arp_tax.tax_info_rec.link_to_cust_trx_line_id := null;
1616 arp_tax.tax_info_rec.trx_date := l_hdr_tax_date;
1617 arp_tax.tax_info_rec.gl_date := null;
1618 arp_tax.tax_info_rec.tax_code := null;
1619 arp_tax.tax_info_rec.tax_rate := null;
1620 arp_tax.tax_info_rec.tax_amount := null;
1621
1622 if l_ship_to_site_use_id is null and l_bill_to_site_use_id is null then
1623 arp_tax.tax_info_rec.ship_to_site_use_id := l_party_site_id_ship;
1624 arp_tax.tax_info_rec.bill_to_site_use_id := l_party_site_id_bill;
1625 else
1626 arp_tax.tax_info_rec.ship_to_site_use_id := l_ship_to_site_use_id;
1627 arp_tax.tax_info_rec.bill_to_site_use_id := l_bill_to_site_use_id;
1628 end if;
1629
1630 arp_tax.tax_info_rec.ship_to_postal_code := l_ship_to_postal_code;
1631 arp_tax.tax_info_rec.bill_to_postal_code := l_bill_to_postal_code;
1632 arp_tax.tax_info_rec.inventory_item_id := l_qte_line_tbl(i).inventory_item_id;
1633 arp_tax.tax_info_rec.memo_line_id := null;
1634 arp_tax.tax_info_rec.tax_control := l_hdr_tax_exempt_flag;
1635 arp_tax.tax_info_rec.xmpt_cert_no := l_hdr_tax_exempt_number;
1636 arp_tax.tax_info_rec.xmpt_reason := l_hdr_tax_exempt_reason_code;
1637 arp_tax.tax_info_rec.ship_to_location_id := l_ship_to_location_ccid;
1638 arp_tax.tax_info_rec.bill_to_location_id := l_bill_to_location_ccid;
1639 arp_tax.tax_info_rec.invoicing_rule_id := l_qte_line_tbl(i).invoicing_rule_id;
1640 arp_tax.tax_info_rec.extended_amount := nvl(l_qte_line_tbl(i).line_quote_price,
1641 0) * nvl(l_qte_line_tbl(i).quantity,0);
1642 arp_tax.tax_info_rec.trx_exchange_rate := l_qte_header_rec.exchange_rate;
1643 arp_tax.tax_info_rec.trx_currency_code := l_currency_code;
1644 arp_tax.tax_info_rec.minimum_accountable_unit := l_minimum_accountable_unit;
1645 arp_tax.tax_info_rec.precision := l_precision;
1646 arp_tax.tax_info_rec.default_ussgl_transaction_code := null;
1647 arp_tax.tax_info_rec.default_ussgl_trx_code_context := null;
1648 arp_tax.tax_info_rec.poo_code := l_poo_address_code;
1649 arp_tax.tax_info_rec.poa_code := l_poa_address_code;
1650 arp_tax.tax_info_rec.ship_from_code := l_ship_from_address_code;
1651 arp_tax.tax_info_rec.ship_to_code := l_ship_to_address_code;
1652 arp_tax.tax_info_rec.fob_point := l_shipment_rec.fob_code;
1653 arp_tax.tax_info_rec.taxed_quantity := l_qte_line_tbl(i).quantity;
1654 arp_tax.tax_info_rec.part_no := l_part_number;
1655 arp_tax.tax_info_rec.tax_line_number := to_number(null);
1656 arp_tax.tax_info_rec.qualifier := 'ALL';
1657 arp_tax.tax_info_rec.calculate_tax := 'Y';
1658 arp_tax.tax_info_rec.tax_precedence := null;
1659 arp_tax.tax_info_rec.tax_exemption_id := to_number(null);
1660 arp_tax.tax_info_rec.item_exception_rate_id := to_number(null);
1661 arp_tax.tax_info_rec.vdrctrl_exempt := l_vendor_control_exemptions;
1662 arp_tax.tax_info_rec.userf1 := l_attribute1;
1663 arp_tax.tax_info_rec.userf2 := l_attribute2;
1664 arp_tax.tax_info_rec.userf3 := null;
1665 arp_tax.tax_info_rec.userf4 := null;
1666 arp_tax.tax_info_rec.userf5 := null;
1667 arp_tax.tax_info_rec.usern1 := l_numeric_attribute1;
1668 arp_tax.tax_info_rec.usern2 := l_numeric_attribute2;
1669 arp_tax.tax_info_rec.usern3 := l_numeric_attribute3;
1670 arp_tax.tax_info_rec.usern4 := l_numeric_attribute4;
1671 arp_tax.tax_info_rec.usern5 := to_number(null);
1672 arp_tax.tax_info_rec.trx_number := null;
1673 arp_tax.tax_info_rec.ship_to_customer_number := l_ship_to_customer_number;
1674 arp_tax.tax_info_rec.ship_to_customer_name := l_ship_to_customer_name;
1675 arp_tax.tax_info_rec.bill_to_customer_number := l_bill_to_customer_number;
1676 arp_tax.tax_info_rec.bill_to_customer_name := l_bill_to_customer_name;
1677 arp_tax.tax_info_rec.previous_customer_trx_line_id := null;
1678 arp_tax.tax_info_rec.previous_customer_trx_id := null;
1679 arp_tax.tax_info_rec.previous_trx_number := null;
1680 arp_tax.tax_info_rec.audit_flag := 'N';
1681 arp_tax.tax_info_rec.trx_line_type := null;
1682 arp_tax.tax_info_rec.division_code := l_division_code;
1683 arp_tax.tax_info_rec.company_code := l_company_code;
1684 arp_tax.tax_info_rec.tax_header_level_flag := l_tax_header_level_flag;
1685 arp_tax.tax_info_rec.tax_rounding_rule := l_tax_rounding_rule;
1686 arp_tax.tax_info_rec.vat_tax_id := to_number(null);
1687 arp_tax.tax_info_rec.trx_type_id := l_ra_cust_trx_type_id;
1688 arp_tax.tax_info_rec.amount_includes_tax_flag := null;
1689 arp_tax.tax_info_rec.ship_from_warehouse_id := l_ship_from_org_id;
1690 arp_tax.tax_info_rec.poo_id := l_poo_id;
1691 arp_tax.tax_info_rec.poa_id := l_qte_header_rec.org_id;
1692 arp_tax.tax_info_rec.payment_term_id := l_payment_term_id;
1693
1694 if l_ship_to_customer_id is null and l_bill_to_customer_id is null then
1695 arp_tax.tax_info_rec.party_flag := 'Y';
1696 end if;
1697
1698 arp_tax.tax_info_rec.payment_terms_discount_percent := null;
1699 arp_tax.tax_info_rec.taxable_basis := null;
1700 arp_tax.tax_info_rec.tax_calculation_plsql_block := null;
1701 arp_tax.tax_info_rec.userf6 := null;
1702 arp_tax.tax_info_rec.userf7 := null;
1703 arp_tax.tax_info_rec.userf8 := null;
1704 arp_tax.tax_info_rec.userf9 := null;
1705 arp_tax.tax_info_rec.userf10 := null;
1706 arp_tax.tax_info_rec.usern6 := to_number(null);
1707 arp_tax.tax_info_rec.usern7 := to_number(null);
1708 arp_tax.tax_info_rec.usern8 := to_number(null);
1709 arp_tax.tax_info_rec.usern9 := to_number(null);
1710 arp_tax.tax_info_rec.usern10 := to_number(null);
1711
1712
1713 ELSE -- LATIN
1714
1715 Begin
1716
1717 if aso_debug_pub.g_debug_flag = 'Y' then
1718 aso_debug_pub.add('ASO_TAX_LINE: l_trx_type_id: '|| l_trx_type_id, 1, 'Y');
1719 end if;
1720
1721 select oe.transaction_type_id
1722 into l_om_trx_type_id
1723 --from ra_cust_trx_types_all ra Commented Code Yogeshwar (MOAC)
1724 from ra_cust_trx_types ra, --New Code Yogeshwar (MOAC)
1725 oe_transaction_types_vl oe
1726 where ra.cust_trx_type_id = l_trx_type_id
1727 and ra.cust_trx_type_id = oe.cust_trx_type_id
1728 and oe.transaction_type_id = nvl(l_qte_line_tbl(i).order_line_type_id, l_qte_header_rec.order_type_id)
1729 --Commented code start Yogeshwar(MOAC)
1730 -- and nvl(ra.org_id,
1731 -- nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1 ,1), ' ',null,
1732 -- substrb(userenv('CLIENT_INFO'), 1,10))),-99)) =
1733 -- nvl(l_qte_header_rec.org_id,
1734 -- nvl(to_number(decode( substrb(userenv('CLIENT_INFO'),1,1), ' ',null,
1735 -- substrb(userenv('CLIENT_INFO'),1,10))), -99))
1736 --End of commented code Yogeshwar (MOAC)
1737
1738 and (( tax_calculation_flag = 'Y' ) or ( l_hdr_tax_exempt_flag='R' ))
1739 and ra.org_id = l_qte_header_rec.org_id ; --New Code Yogeshwar (MOAC)
1740
1741 --Need to find org striped synonym for OE_TRANSACTION_TYPES_ALL Yogeshwar
1742
1743 EXCEPTION
1744
1745 WHEN NO_DATA_FOUND THEN
1746
1747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1748 aso_debug_pub.add('ASO_TAX_LINE: NO_DATA_FOUND when selecting transaction_type_id', 1, 'Y');
1749 END IF;
1750
1751 l_reason := 'No Data Found Exception when selecting transaction_type_id from ';
1752 l_reason := l_reason || 'ra_cust_trx_types_all and oe_transaction_types_vl';
1753 l_reason := l_reason || fnd_global.newline();
1754 l_reason := l_reason || 'Please check Default order type id profile is ';
1755 l_reason := l_reason || 'correctly set. Also pl verify the';
1756 l_reason := l_reason || fnd_global.newline();
1757 l_reason := l_reason || 'the value the profile is returning. No tax call ';
1758 l_reason := l_reason || 'being made to tax engine.';
1759
1760 aso_quote_misc_pvt.debug_tax_info_notification(l_qte_header_rec,
1761 l_shipment_rec, l_reason);
1762
1763 if aso_debug_pub.g_debug_flag = 'Y' then
1764 aso_debug_pub.add('ASO_TAX_LINE: Before deleting all tax records for the quote line', 1, 'Y');
1765 end if;
1766
1767 if l_tax_detail_tbl.count > 0 then
1768
1769 delete from aso_tax_details
1770 where quote_header_id = l_tax_detail_tbl(1).quote_header_id
1771 and quote_line_id = l_tax_detail_tbl(1).quote_line_id ;
1772
1773 end if;
1774
1775 if aso_debug_pub.g_debug_flag = 'Y' then
1776 aso_debug_pub.add('ASO_TAX_LINE: After deleting all tax records for the quote line', 1, 'Y');
1777 end if;
1778
1779 End;
1780
1781
1782 Begin
1783
1784 open c_global_attributes( l_qte_line_tbl(i).inventory_item_id,
1785 l_qte_line_tbl(i).organization_id );
1786 fetch c_global_attributes into l_fiscal_classification, l_transaction_cond_class;
1787 close c_global_attributes;
1788
1789 if aso_debug_pub.g_debug_flag = 'Y' then
1790 aso_debug_pub.add('l_fiscal_classification: '|| l_fiscal_classification, 1, 'Y');
1791 aso_debug_pub.add('l_transaction_cond_class: '|| l_transaction_cond_class, 1, 'Y');
1792 aso_debug_pub.add('l_site_use_id_ship: '|| l_site_use_id_ship, 1, 'Y');
1793 aso_debug_pub.add('l_site_use_id_bill: '|| l_site_use_id_bill, 1, 'Y');
1794 aso_debug_pub.add('inventory_item_id: '|| l_qte_line_tbl(i).inventory_item_id, 1, 'Y');
1795 aso_debug_pub.add('organization_id: '|| l_qte_line_tbl(i).organization_id, 1, 'Y');
1796 aso_debug_pub.add('l_ship_from_org_id: '|| l_ship_from_org_id, 1, 'Y');
1797 aso_debug_pub.add('l_set_of_books_id: '|| l_set_of_books_id, 1, 'Y');
1798 aso_debug_pub.add('l_hdr_tax_date: '|| l_hdr_tax_date, 1, 'Y');
1799 aso_debug_pub.add('l_trx_type_id: '|| l_trx_type_id, 1, 'Y');
1800 aso_debug_pub.add('ASO_TAX_LINE: Before call to get_crm_default_tax_code', 1, 'Y');
1801 end if;
1802
1803 arp_tax_crm_integration_pkg.get_crm_default_tax_code(
1804 p_ship_to_site_use_id => l_site_use_id_ship,
1805 p_bill_to_site_use_id => l_site_use_id_bill,
1806 p_inventory_item_id => l_qte_line_tbl(i).inventory_item_id,
1807 p_organization_id => l_qte_line_tbl(i).organization_id,
1808 p_warehouse_id => l_ship_from_org_id,
1809 p_set_of_books_id => l_set_of_books_id,
1810 p_trx_date => l_hdr_tax_date,
1811 p_trx_type_id => l_trx_type_id,
1812 p_tax_code => l_tax_code,
1813 p_vat_tax_id => l_vat_tax_id,
1814 p_amt_incl_tax_flag => l_amt_incl_tax_flag,
1815 p_amt_incl_tax_override => l_amt_incl_tax_override );
1816
1817 if aso_debug_pub.g_debug_flag = 'Y' then
1818 aso_debug_pub.add('ASO_TAX_LINE: After call to get_crm_default_tax_code', 1, 'Y');
1819 aso_debug_pub.add('l_tax_code: '|| l_tax_code, 1, 'Y');
1820 aso_debug_pub.add('l_vat_tax_id: '|| l_vat_tax_id, 1, 'Y');
1821 aso_debug_pub.add('l_amt_incl_tax_flag: '|| l_amt_incl_tax_flag, 1, 'Y');
1822 aso_debug_pub.add('l_amt_incl_tax_override: '|| l_amt_incl_tax_override, 1, 'Y');
1823 end if;
1824
1825
1826 EXCEPTION
1827
1828 when others then
1829
1830 if aso_debug_pub.g_debug_flag = 'Y' then
1831 aso_debug_pub.add('ASO_TAX_LINE: Exception in call to get_crm_default_tax_code', 1, 'Y');
1832 end if;
1833
1834
1835 End;
1836
1837
1838 Begin
1839
1840 if aso_debug_pub.g_debug_flag = 'Y' then
1841
1842 aso_debug_pub.add('ASO_TAX_LINE: Before call to populate_om_ar_tax_struct', 1, 'Y');
1843 aso_debug_pub.add('p_qte_header_id: '|| p_qte_header_id, 1, 'Y');
1844 aso_debug_pub.add('l_currency_code: '|| l_currency_code, 1, 'Y');
1845 aso_debug_pub.add('l_shipment_rec.fob_code: '|| l_shipment_rec.fob_code, 1, 'Y');
1846 aso_debug_pub.add('l_fiscal_classification: '|| l_fiscal_classification, 1, 'Y');
1847 aso_debug_pub.add('l_site_use_id_bill: '|| l_site_use_id_bill, 1, 'Y');
1848 aso_debug_pub.add('l_om_trx_type_id: '|| l_om_trx_type_id, 1, 'Y');
1849 aso_debug_pub.add('l_payment_term_id: '|| l_payment_term_id, 1, 'Y');
1850 aso_debug_pub.add('l_transaction_cond_class: '|| l_transaction_cond_class, 1, 'Y');
1851 aso_debug_pub.add('l_ship_from_org_id: '|| l_ship_from_org_id, 1, 'Y');
1852 aso_debug_pub.add('l_site_use_id_ship: '|| l_site_use_id_ship, 1, 'Y');
1853 aso_debug_pub.add('l_tax_code: '|| l_tax_code, 1, 'Y');
1854 aso_debug_pub.add('l_hdr_tax_date: '|| l_hdr_tax_date, 1, 'Y');
1855 aso_debug_pub.add('l_hdr_tax_exempt_flag: '|| l_hdr_tax_exempt_flag, 1, 'Y');
1856 aso_debug_pub.add('l_hdr_tax_exempt_number: '|| l_hdr_tax_exempt_number, 1, 'Y');
1857 aso_debug_pub.add('l_hdr_tax_exempt_reason_code: '|| l_hdr_tax_exempt_reason_code, 1, 'Y');
1858
1859 aso_debug_pub.add('exchange_rate: '|| l_qte_header_rec.exchange_rate, 1, 'Y');
1860 aso_debug_pub.add('quote_line_id: '|| l_qte_line_tbl(i).quote_line_id, 1, 'Y');
1861 aso_debug_pub.add('inventory_item_id: '|| l_qte_line_tbl(i).inventory_item_id, 1, 'Y');
1862 aso_debug_pub.add('invoicing_rule_id: '|| l_qte_line_tbl(i).invoicing_rule_id, 1, 'Y');
1863 aso_debug_pub.add('quantity: '|| l_qte_line_tbl(i).quantity, 1, 'Y');
1864 aso_debug_pub.add('line_quote_price: '|| l_qte_line_tbl(i).line_quote_price, 1, 'Y');
1865
1866 end if;
1867 -- JL_ZZ_TAX_INTEGRATION_PKG.populate_om_ar_tax_struct(
1868 -- p_conversion_rate => l_qte_header_rec.exchange_rate,
1869 -- p_currency_code => l_currency_code,
1870 -- p_fob_point_code => l_shipment_rec.fob_code,
1871 -- p_global_attribute5 => l_fiscal_classification,
1872 -- p_line_id => l_qte_line_tbl(i).quote_line_id,
1873 -- p_header_id => p_qte_header_id,
1874 -- p_inventory_item_id => l_qte_line_tbl(i).inventory_item_id,
1875 -- p_invoice_to_org_id => l_site_use_id_bill,
1876 -- p_invoicing_rule_id => l_qte_line_tbl(i).invoicing_rule_id,
1877 -- p_line_type_id => l_om_trx_type_id,
1878 -- p_pricing_quantity => l_qte_line_tbl(i).quantity,
1879 -- p_payment_term_id => l_payment_term_id,
1880 -- p_global_attribute6 => l_transaction_cond_class,
1881 -- p_ship_from_org_id => l_ship_from_org_id,
1882 -- p_ship_to_org_id => l_site_use_id_ship,
1883 -- p_tax_code => l_tax_code,
1884 -- p_tax_date => l_hdr_tax_date,
1885 -- p_tax_exempt_flag => l_hdr_tax_exempt_flag,
1886 -- p_tax_exempt_number => l_hdr_tax_exempt_number,
1887 -- p_tax_exempt_reason => l_hdr_tax_exempt_reason_code,
1888 -- p_unit_selling_price => l_qte_line_tbl(i).line_quote_price );
1889
1890 if aso_debug_pub.g_debug_flag = 'Y' then
1891 aso_debug_pub.add('ASO_TAX_LINE: After call to populate_om_ar_tax_struct', 1, 'Y');
1892 end if;
1893
1894 EXCEPTION
1895
1896 when others then
1897
1898 if aso_debug_pub.g_debug_flag = 'Y' then
1899 aso_debug_pub.add('ASO_TAX_LINE: Exception in call to populate_om_ar_tax_struct', 1, 'Y');
1900 end if;
1901
1902
1903 End;
1904
1905 END IF; --tax_method <> 'LATIN'
1906
1907 if aso_debug_pub.g_debug_flag = 'Y' then
1908 aso_debug_pub.add('ASO_TAX_LINE: Before call to print_tax_info_rec', 1, 'Y');
1909 end if;
1910
1911 print_tax_info_rec(p_debug_level => 5);
1912
1913 if aso_debug_pub.g_debug_flag = 'Y' then
1914 aso_debug_pub.add('ASO_TAX_LINE: After call to print_tax_info_rec', 1, 'Y');
1915 end if;
1916
1917
1918 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1919 aso_debug_pub.add('arp_tax.tax_info_rec.trx_type_id: '|| arp_tax.tax_info_rec.trx_type_id, 1, 'Y');
1920 END IF;
1921
1922
1923 IF arp_tax.tax_info_rec.trx_type_id is not null THEN
1924
1925 Begin
1926
1927 l_tax_start_time := dbms_utility.get_time;
1928
1929 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1930 aso_debug_pub.add('ASO_TAX_LINE: Before call to arp_tax_crm_integration_pkg.summary', 1, 'Y');
1931 aso_debug_pub.add('ASO_TAX_LINE: l_tax_start_time: '|| l_tax_start_time, 1, 'Y');
1932 END IF;
1933
1934 arp_tax_crm_integration_pkg.summary( p_set_of_books_id => l_set_of_books_id,
1935 x_crm_tax_out_tbl => x_tax_out_tbl,
1936 p_new_tax_amount => x_tax_value);
1937
1938 l_tax_end_time := dbms_utility.get_time;
1939 l_tax_total_time := l_tax_total_time + (l_tax_end_time - l_tax_start_time);
1940
1941 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1942 aso_debug_pub.add('ASO_TAX_LINE: After call to arp_tax_crm_integration_pkg.summary', 1, 'Y');
1943 aso_debug_pub.add('ASO_TAX_LINE: l_tax_end_time: '|| l_tax_end_time, 1, 'Y');
1944 aso_debug_pub.add('ASO_TAX_LINE: l_tax_total_time: '|| l_tax_total_time, 1, 'Y');
1945 END IF;
1946
1947
1948 EXCEPTION
1949
1950 WHEN OTHERS THEN
1951
1952 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1953
1954 aso_debug_pub.add('ASO_TAX_LINE: Exception raised after call to ARP_TAX_CRM_INTEGRATION_PKG.summary', 1, 'Y');
1955 aso_debug_pub.add('After call to ARP_TAX_CRM_INTEGRATION_PKG.summary: x_tax_out_tbl.count: '||x_tax_out_tbl.count, 1, 'Y');
1956
1957 END IF;
1958
1959 l_reason := 'Exception in call to tax engine arp_tax_crm_integration_pkg.summary';
1960 l_reason := l_reason || fnd_global.newline();
1961 l_reason := l_reason || 'Table count returned from tax engine x_tax_out_tbl.count :';
1962 l_reason := l_reason || fnd_global.newline();
1963 l_reason := l_reason || x_tax_out_tbl.count;
1964
1965 FOR i IN 1.. x_tax_out_tbl.count LOOP
1966
1967 l_reason := l_reason || 'x_tax_out_tbl('||i||').vat_tax_id : ';
1968 l_reason := l_reason || x_tax_out_tbl(i).vat_tax_id;
1969 l_reason := l_reason || fnd_global.newline();
1970 l_reason := l_reason || 'x_tax_out_tbl('||i||').extended_amount: ';
1971 l_reason := l_reason || x_tax_out_tbl(i).extended_amount;
1972 l_reason := l_reason || fnd_global.newline();
1973 l_reason := l_reason || 'x_tax_out_tbl('||i||').tax_rate: ';
1974 l_reason := l_reason || x_tax_out_tbl(i).tax_rate;
1975 l_reason := l_reason || fnd_global.newline();
1976 l_reason := l_reason || 'x_tax_out_tbl('||i||').tax_amount: ';
1977 l_reason := l_reason || x_tax_out_tbl(1).tax_amount;
1978 l_reason := l_reason || fnd_global.newline();
1979 l_reason := l_reason || 'x_tax_out_tbl('||i||').tax_control: ';
1980 l_reason := l_reason || x_tax_out_tbl(1).tax_control;
1981
1982 End Loop;
1983
1984 aso_quote_misc_pvt.debug_tax_info_notification( l_qte_header_rec,
1985 l_shipment_rec, l_reason);
1986 End;
1987
1988 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1989
1990 aso_debug_pub.add('ASO_TAX_LINE: After call to ARP_TAX_CRM_INTEGRATION_PKG.summary', 1, 'Y');
1991 aso_debug_pub.add('ASO_TAX_LINE: After call x_tax_out_tbl.count: '|| x_tax_out_tbl.count, 1, 'Y');
1992
1993 FOR i IN 1.. x_tax_out_tbl.count LOOP
1994
1995 aso_debug_pub.add('******Out put from ARP_TAX_CRM_INTEGRATION_PKG.summary******');
1996 aso_debug_pub.add('x_tax_out_tbl('||i||').vat_tax_id : '|| x_tax_out_tbl(i).vat_tax_id, 1, 'Y');
1997 aso_debug_pub.add('x_tax_out_tbl('||i||').extended_amount: '|| x_tax_out_tbl(i).extended_amount, 1, 'Y');
1998 aso_debug_pub.add('x_tax_out_tbl('||i||').tax_rate: '|| x_tax_out_tbl(i).tax_rate, 1, 'Y');
1999 aso_debug_pub.add('x_tax_out_tbl('||i||').tax_amount: '|| x_tax_out_tbl(i).tax_amount, 1, 'Y');
2000 aso_debug_pub.add('x_tax_out_tbl('||i||').tax_control: '|| x_tax_out_tbl(i).tax_control, 1, 'Y');
2001 aso_debug_pub.add('x_tax_out_tbl('||i||').amount_includes_tax_flag: '|| x_tax_out_tbl(i).amount_includes_tax_flag, 1, 'Y');
2002
2003 End Loop;
2004
2005 aso_debug_pub.add('ASO_TAX_LINE: OUTput from ARP_TAX_CRM_INTEGRATION_PKG.summary: x_tax_value: ' || x_tax_value, 1, 'Y');
2006
2007 END IF;
2008
2009 FOR i IN 1.. x_tax_out_tbl.count LOOP
2010
2011 open c_tax_code(x_tax_out_tbl(i).vat_tax_id);
2012 fetch c_tax_code into l_tax_detail_tbl(1).tax_code;
2013 close c_tax_code;
2014
2015 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2016
2017 aso_debug_pub.add('l_tax_detail_tbl(1).tax_code: '||l_tax_detail_tbl(1).tax_code, 1, 'Y');
2018 aso_debug_pub.add('x_tax_out_tbl.tax_rate: '||x_tax_out_tbl(i).tax_rate, 1, 'Y');
2019 aso_debug_pub.add('x_tax_out_tbl.tax_amount: '||x_tax_out_tbl(i).tax_amount, 1, 'Y');
2020 aso_debug_pub.add('x_tax_out_tbl.extended_amount: '||x_tax_out_tbl(i).extended_amount, 1, 'Y');
2021 aso_debug_pub.add('x_tax_out_tbl.vat_tax_id: '||x_tax_out_tbl(i).vat_tax_id, 1, 'Y');
2022 aso_debug_pub.add('x_tax_out_tbl.tax_control: '||x_tax_out_tbl(i).tax_control, 1, 'Y');
2023 aso_debug_pub.add('x_tax_out_tbl.xmpt_cert_no: '||x_tax_out_tbl(i).xmpt_cert_no, 1, 'Y');
2024 aso_debug_pub.add('x_tax_out_tbl.xmpt_reason: '||x_tax_out_tbl(i).xmpt_reason, 1, 'Y');
2025
2026 END IF;
2027
2028 l_tax_detail_tbl(1).tax_rate := x_tax_out_tbl(i).tax_rate;
2029 l_tax_detail_tbl(1).tax_date := l_sys_date;
2030 l_tax_detail_tbl(1).tax_amount := x_tax_out_tbl(i).tax_amount;
2031 l_tax_detail_tbl(1).tax_exempt_flag := x_tax_out_tbl(i).tax_control;
2032 l_tax_detail_tbl(1).tax_exempt_number := x_tax_out_tbl(i).xmpt_cert_no;
2033 l_tax_detail_tbl(1).tax_exempt_reason_code := x_tax_out_tbl(i).xmpt_reason;
2034 l_tax_detail_tbl(1).tax_inclusive_flag := x_tax_out_tbl(i).amount_includes_tax_flag;
2035
2036 x_tax_detail_tbl(x_tax_detail_tbl.count+1) := l_tax_detail_tbl(1);
2037
2038 END LOOP;
2039
2040 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2041 aso_debug_pub.add('After populating: x_tax_detail_tbl.count: '|| x_tax_detail_tbl.count, 1, 'Y');
2042 aso_debug_pub.add('ASO_TAX_LINE: Deleting tax records Before First IF', 1, 'Y');
2043 END IF;
2044
2045 IF p_tax_control_rec.update_db = 'Y' THEN
2046
2047 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2048 aso_debug_pub.add('ASO_TAX_LINE: Deleting tax records inside first IF before 2nd IF', 1, 'Y');
2049 END IF;
2050
2051 IF p_tax_control_rec.tax_level = 'SHIPPING' THEN
2052
2053 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2054 aso_debug_pub.add('ASO_TAX_LINE: Deleting tax records inside 2nd IF');
2055 aso_debug_pub.add('ASO_TAX_LINE: l_tax_detail_tbl(1).quote_shipment_id'||l_tax_detail_tbl(1).quote_shipment_id, 1, 'Y');
2056 aso_debug_pub.add('ASO_TAX_LINE: l_tax_detail_tbl(1).quote_line_id'||l_tax_detail_tbl(1).quote_line_id, 1, 'Y');
2057 aso_debug_pub.add('ASO_TAX_LINE: l_tax_detail_tbl(1).quote_header_id'||l_tax_detail_tbl(1).quote_header_id, 1, 'Y');
2058 END IF;
2059
2060 DELETE FROM aso_tax_details
2061 WHERE quote_shipment_id = l_tax_detail_tbl(1).quote_shipment_id
2062 and quote_line_id = l_qte_line_tbl(i).quote_line_id
2063 and quote_header_id = l_tax_detail_tbl(1).quote_header_id;
2064
2065 END IF;
2066
2067 IF p_tax_control_rec.tax_level = 'HEADER' THEN
2068
2069 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2070 aso_debug_pub.add('ASO_TAX_LINE: Deleting HEADER level tax records inside IF', 1, 'Y');
2071 END IF;
2072
2073 DELETE FROM aso_tax_details
2074 WHERE quote_header_id = l_qte_line_tbl(i).quote_header_id
2075 and quote_line_id = l_qte_line_tbl(i).quote_line_id;
2076
2077 END IF;
2078
2079 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2080 aso_debug_pub.add('Before calling aso_tax_details_pkg.insert_row in loop', 1, 'Y');
2081 aso_debug_pub.add('x_tax_detail_Tbl.count: ' || x_tax_detail_Tbl.count, 1, 'Y');
2082 END IF;
2083
2084 FOR i IN 1..x_tax_detail_Tbl.count LOOP
2085
2086 x_tax_detail_tbl(i).tax_detail_id := null;
2087
2088 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2089 aso_debug_pub.add('ASO_TAX_LINE: value of loop index i: ' || i, 1, 'Y');
2090 aso_debug_pub.add('tax_detail_id: ' || x_tax_detail_tbl(i).tax_detail_id, 1, 'Y');
2091 aso_debug_pub.add('tax_code: ' || x_tax_detail_tbl(i).tax_code, 1, 'Y');
2092 aso_debug_pub.add('tax_rate: ' || x_tax_detail_tbl(i).tax_rate, 1, 'Y');
2093 aso_debug_pub.add('tax_amount: ' || x_tax_detail_tbl(i).tax_amount, 1, 'Y');
2094 END IF;
2095
2096 ASO_TAX_DETAILS_PKG.Insert_Row(
2097 px_TAX_DETAIL_ID => x_tax_detail_tbl(i).TAX_DETAIL_ID,
2098 p_CREATION_DATE => SYSDATE,
2099 p_CREATED_BY => G_USER_ID,
2100 p_LAST_UPDATE_DATE => SYSDATE,
2101 p_LAST_UPDATED_BY => G_USER_ID,
2102 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
2103 p_REQUEST_ID => x_tax_detail_tbl(i).REQUEST_ID,
2104 p_PROGRAM_APPLICATION_ID => x_tax_detail_tbl(i).PROGRAM_APPLICATION_ID,
2105 p_PROGRAM_ID => x_tax_detail_tbl(i).PROGRAM_ID,
2106 p_PROGRAM_UPDATE_DATE => x_tax_detail_tbl(i).PROGRAM_UPDATE_DATE,
2107 p_QUOTE_HEADER_ID => x_tax_detail_tbl(i).quote_header_id,
2108 p_QUOTE_LINE_ID => x_tax_detail_tbl(i).QUOTE_LINE_ID,
2109 p_QUOTE_SHIPMENT_ID => x_tax_detail_tbl(i).QUOTE_SHIPMENT_ID,
2110 p_ORIG_TAX_CODE => x_tax_detail_tbl(i).ORIG_TAX_CODE,
2111 p_TAX_CODE => x_tax_detail_tbl(i).TAX_CODE,
2112 p_TAX_RATE => x_tax_detail_tbl(i).TAX_RATE,
2113 p_TAX_DATE => l_sys_date,--x_tax_detail_tbl(i).TAX_DATE,
2114 p_TAX_AMOUNT => x_tax_detail_tbl(i).TAX_AMOUNT,
2115 p_TAX_EXEMPT_FLAG => x_tax_detail_tbl(i).TAX_EXEMPT_FLAG,
2116 p_TAX_EXEMPT_NUMBER => x_tax_detail_tbl(i).TAX_EXEMPT_NUMBER,
2117 p_TAX_EXEMPT_REASON_CODE => x_tax_detail_tbl(i).TAX_EXEMPT_REASON_CODE,
2118 p_ATTRIBUTE_CATEGORY => x_tax_detail_tbl(i).ATTRIBUTE_CATEGORY,
2119 p_ATTRIBUTE1 => x_tax_detail_tbl(i).ATTRIBUTE1,
2120 p_ATTRIBUTE2 => x_tax_detail_tbl(i).ATTRIBUTE2,
2121 p_ATTRIBUTE3 => x_tax_detail_tbl(i).ATTRIBUTE3,
2122 p_ATTRIBUTE4 => x_tax_detail_tbl(i).ATTRIBUTE4,
2123 p_ATTRIBUTE5 => x_tax_detail_tbl(i).ATTRIBUTE5,
2124 p_ATTRIBUTE6 => x_tax_detail_tbl(i).ATTRIBUTE6,
2125 p_ATTRIBUTE7 => x_tax_detail_tbl(i).ATTRIBUTE7,
2126 p_ATTRIBUTE8 => x_tax_detail_tbl(i).ATTRIBUTE8,
2127 p_ATTRIBUTE9 => x_tax_detail_tbl(i).ATTRIBUTE9,
2128 p_ATTRIBUTE10 => x_tax_detail_tbl(i).ATTRIBUTE10,
2129 p_ATTRIBUTE11 => x_tax_detail_tbl(i).ATTRIBUTE11,
2130 p_ATTRIBUTE12 => x_tax_detail_tbl(i).ATTRIBUTE12,
2131 p_ATTRIBUTE13 => x_tax_detail_tbl(i).ATTRIBUTE13,
2132 p_ATTRIBUTE14 => x_tax_detail_tbl(i).ATTRIBUTE14,
2133 p_ATTRIBUTE15 => x_tax_detail_tbl(i).ATTRIBUTE15,
2134 p_ATTRIBUTE16 => l_tax_detail_tbl(i).ATTRIBUTE16,
2135 p_ATTRIBUTE17 => l_tax_detail_tbl(i).ATTRIBUTE17,
2136 p_ATTRIBUTE18 => l_tax_detail_tbl(i).ATTRIBUTE18,
2137 p_ATTRIBUTE19 => l_tax_detail_tbl(i).ATTRIBUTE19,
2138 p_ATTRIBUTE20 => l_tax_detail_tbl(i).ATTRIBUTE20,
2139 p_TAX_INCLUSIVE_FLAG => x_tax_detail_tbl(i).TAX_INCLUSIVE_FLAG,
2140 p_OBJECT_VERSION_NUMBER => x_tax_detail_tbl(i).OBJECT_VERSION_NUMBER,
2141 p_TAX_RATE_ID => l_tax_detail_tbl(i).TAX_RATE_ID
2142 );
2143 END LOOP;--x_tax_detail_tbl(i)
2144
2145 END IF;-- p_tax_control_rec.update_db
2146
2147 -- Call to initialize the AR Global Tax info record.
2148
2149 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2150 aso_debug_pub.add('ASO_TAX_LINE: Before call to initialize_tax_info_rec.', 1, 'Y');
2151 END IF;
2152
2153 initialize_tax_info_rec;
2154
2155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2156 aso_debug_pub.add('ASO_TAX_LINE: After call to initialize_tax_info_rec.', 1, 'Y');
2157 END IF;
2158
2159 x_tax_detail_Tbl := aso_quote_pub.g_miss_tax_detail_tbl;
2160 l_tax_detail_tbl := aso_quote_pub.g_miss_tax_detail_tbl;
2161 l_hdr_tax_detail_tbl := aso_quote_pub.g_miss_tax_detail_tbl;
2162
2163 END IF; --arp_tax.tax_info_rec.trx_type_id
2164
2165 End Loop; --quote line loop
2166
2167 END IF; --qte_header_id;
2168
2169 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2170 aso_debug_pub.add('ASO_TAX_LINE: Total time Tax Engine took: l_tax_total_time: '|| l_tax_total_time, 1,'Y');
2171 END IF;
2172
2173 l_tax_total_time := l_tax_total_time/100;
2174
2175 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2176 aso_debug_pub.add('ASO_TAX_LINE: Total time Tax Engine took after dividing by 100: l_tax_total_time: '|| l_tax_total_time, 1,'Y');
2177 aso_debug_pub.add('ASO_TAX_INT: End ASO_TAX_LINE', 1, 'Y');
2178 END IF;
2179
2180 End aso_tax_line;
2181 *
2182 *
2183 */
2184
2185 --Calculate Tax with GTT added as a part of etax By Anoop Rajan om 9 August 2005
2186 --Modified on 11 August with NOCOPY Hint added
2187
2188 Procedure CALCULATE_TAX_WITH_GTT
2189 (
2190 p_API_VERSION_NUMBER IN NUMBER,
2191 p_qte_header_id IN NUMBER,
2192 p_qte_line_id IN NUMBER:=NULL,
2193 x_return_status OUT NOCOPY VARCHAR2,
2194 X_Msg_Count OUT NOCOPY NUMBER,
2195 X_Msg_Data OUT NOCOPY VARCHAR2
2196
2197 )
2198 is
2199 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_TAX_INT';
2200 L_API_NAME CONSTANT VARCHAR2(50):='CALCULATE_TAX_WITH_GTT';
2201 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
2202 l_currency_code VARCHAR2(15);
2203 l_minimum_accountable_unit NUMBER;
2204 l_precision NUMBER;
2205 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_tbl_Type;
2206 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type;
2207 l_set_of_books_id NUMBER;
2208 l_site_use_id_ship_header NUMBER;
2209 l_site_use_id_bill_header NUMBER;
2210 l_site_use_id_ship_lines NUMBER;
2211 l_site_use_id_bill_lines NUMBER;
2212 l_site_use_id NUMBER;
2213 l_ra_cust_trx_type_id NUMBER;
2214 l_trx_type_id NUMBER;
2215 l_acct_site_id_ship NUMBER;
2216 l_acct_site_id_bill NUMBER;
2217 l_acct_site_id_bill_lines NUMBER;
2218 l_acct_site_id_ship_lines NUMBER;
2219 l_ship_cust_account_id_header NUMBER;
2220 l_ship_cust_acct_id_lines NUMBER;
2221 l_bill_cust_acct_id_lines NUMBER;
2222 l_Shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
2223 l_Shipment_header_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
2224 l_Shipment_Rec ASO_QUOTE_PUB.Shipment_Rec_Type;
2225 l_Shipment_header_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
2226 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
2227 l_hdr_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_tbl_Type;
2228 l_hdr_tax_exempt_flag VARCHAR2(1);
2229 l_hdr_tax_exempt_number VARCHAR2(80);
2230 l_hdr_tax_exempt_reason_code VARCHAR2(30);
2231 l_product_type VARCHAR2(15);
2232 l_fiscal_classification VARCHAR2(150);
2233 l_transaction_cond_class VARCHAR2(150);
2234 l_SHIP_FROM_LOCATION_ID NUMBER;
2235 l_ship_to_location NUMBER;
2236 l_bill_to_location NUMBER;
2237 l_party_site_id NUMBER;
2238 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
2239 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
2240 x_legal_entity XLE_BUSINESSINFO_GRP.otoc_le_rec;
2241 l_batch_source NUMBER;
2242 l_init_msg_list VARCHAR2(1);
2243 l_commit VARCHAR2(1) :=NULL;
2244 l_validation_level NUMBER;
2245 l_int_org_location NUMBER;
2246 l_legal_entity_id NUMBER;
2247 l_tax_start_time NUMBER;
2248 l_tax_end_time NUMBER;
2249 l_tax_total_time NUMBER;
2250 l_msg_cnt1 NUMBER;
2251 l_msg_cnt2 NUMBER;
2252 l_tax_classification_code varchar2(50);
2253 Cursor c_currency is
2254 select minimum_accountable_unit,precision
2255 from fnd_currencies
2256 where currency_code = l_currency_code;
2257
2258 Cursor c_get_acct_site(l_site_use_id NUMBER) is
2259 select cust_acct_site_id
2260 from hz_cust_site_uses
2261 where site_use_id = l_site_use_id;
2262
2263 cursor c_getlocinfo(l_site_use_id NUMBER) is
2264 select site_use.cust_acct_site_id,site.CUST_ACCOUNT_ID
2265 from HZ_cust_site_uses site_use,hz_cust_acct_sites site
2266 where site.CUST_ACCT_SITE_ID=site_use.CUST_ACCT_SITE_ID
2267 and site_use.SITE_USE_ID=l_site_use_id;
2268
2269 Cursor c_ship_to_cust_account_id is
2270 select ship_to_cust_account_id
2271 from aso_shipments
2272 where quote_header_id = l_qte_header_rec.quote_header_id
2273 and quote_line_id is null;
2274
2275 Cursor c_location_id is
2276 SELECT LOCATION_ID
2277 from HR_ORGANIZATION_UNITS
2278 WHERE ORGANIZATION_ID = l_qte_line_rec.organization_id;
2279
2280 Cursor c_int_org_location is
2281 SELECT LOCATION_ID
2282 FROM HR_ORGANIZATION_UNITS
2283 WHERE ORGANIZATION_ID=l_qte_header_rec.ORG_ID;
2284
2285 Cursor c_product_type is
2286 Select CLASSIFICATION_CODE
2287 FROM ZX_PRODUCT_TYPES_DEF_V
2288 WHERE INVENTORY_ITEM_ID = l_qte_line_rec.INVENTORY_ITEM_ID
2289 AND ORG_ID= l_qte_line_rec.organization_id;
2290
2291 Cursor c_shiplocation(l_party_site_id NUMBER) is
2292 select LOCATION_ID
2293 FROM hz_party_sites
2294 WHERE party_site_id=l_party_site_id;
2295
2296 Cursor c_tax is
2297 select TAX_EXEMPT_FLAG,TAX_EXEMPT_NUMBER,TAX_EXEMPT_REASON_CODE
2298 from aso_tax_details
2299 WHERE QUOTE_LINE_ID is null
2300 AND quote_header_id=p_qte_header_id;
2301
2302 Cursor c_cust_trx_type_id(l_trx_type_id NUMBER) is
2303 select cust_trx_type_id
2304 from ra_cust_trx_types
2305 where cust_trx_type_id = l_trx_type_id
2306 and (tax_calculation_flag = 'Y');
2307
2308 Cursor c_set_of_books_id is
2309 select set_of_books_id
2310 from ar_system_parameters;
2311
2312 Cursor c_INVOICE_SOURCE_ID is
2313 select INVOICE_SOURCE_ID
2314 FROM OE_TRANSACTION_TYPES
2315 WHERE TRANSACTION_TYPE_ID=l_qte_header_rec.ORDER_TYPE_ID;
2316
2317 -- new cursors added by suyog bug 5061912
2318
2319 Cursor c_get_resource_id (l_qte_hdr_id NUMBER) is
2320 SELECT resource_id
2321 FROM aso_quote_headers_all trx
2322 WHERE trx.quote_header_id = l_qte_hdr_id;
2323
2324 Cursor c_get_org_id ( l_source_id NUMBER) is
2325 SELECT per.organization_id
2326 FROM jtf_rs_srp_vl sales, per_all_assignments_f per
2327 WHERE sales.resource_id = l_source_id
2328 AND per.person_id = sales.person_id
2329 AND nvl(per.primary_flag,'Y') = 'Y'
2330 AND sysdate BETWEEN nvl(per.effective_start_date,sysdate) AND nvl(per.effective_end_date,sysdate);
2331
2332 Cursor c_get_location_id ( l_party_id NUMBER) is
2333 SELECT hr.location_id
2334 FROM hr_organization_units hr
2335 WHERE hr.organization_id = l_party_id;
2336
2337
2338 -- new variables
2339 l_resource_id number;
2340 l_poo_party_id number;
2341 l_poo_location_id number;
2342 l_bill_from_location_id NUMBER; -- bug 7408162
2343
2344 Begin
2345
2346 Savepoint CALCULATE_TAX_WITH_GTT;
2347 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2348 x_return_status := FND_API.G_RET_STS_SUCCESS;
2349
2350 IF aso_debug_pub.g_debug_flag = 'Y'
2351 THEN
2352 aso_debug_pub.add('ASO_TAX_INT: Begin CALCULATE_TAX_WITH_GTT', 1, 'Y');
2353 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: p_qte_header_id: '|| p_qte_header_id, 1, 'Y');
2354 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: p_qte_line_id: '|| p_qte_line_id, 1, 'Y');
2355 END IF;
2356 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_id);
2357 IF aso_debug_pub.g_debug_flag = 'Y'
2358 THEN
2359 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Header_Row ', 1, 'Y');
2360 END IF;
2361
2362 l_currency_code := l_qte_header_rec.currency_code;
2363 IF aso_debug_pub.g_debug_flag = 'Y'
2364 THEN
2365 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_currency_code: '|| l_currency_code, 1, 'Y');
2366 END IF;
2367
2368 l_Shipment_header_tbl:=aso_utility_pvt.query_shipment_rows( p_qte_header_id,null);
2369 IF aso_debug_pub.g_debug_flag = 'Y'
2370 THEN
2371 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.query_shipment_rows ', 1, 'Y');
2372 END IF;
2373
2374 --Condition added on 20/09/05 by anrajan
2375 IF l_Shipment_header_tbl.count > 0
2376 THEN
2377 l_Shipment_header_rec:=l_Shipment_header_tbl(1);
2378 END IF;
2379
2380 OPEN c_currency;
2381 FETCH c_currency into l_minimum_accountable_unit,l_precision;
2382 CLOSE c_currency;
2383
2384 IF aso_debug_pub.g_debug_flag = 'Y'
2385 THEN
2386 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_currency cursor ', 1, 'Y');
2387 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_minimum_accountable_unit: '||l_minimum_accountable_unit, 1, 'Y');
2388 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_precision: '||l_precision,1,'Y');
2389 END IF;
2390
2391 open c_ship_to_cust_account_id;
2392 fetch c_ship_to_cust_account_id into l_ship_cust_account_id_header;
2393 close c_ship_to_cust_account_id;
2394
2395 IF aso_debug_pub.g_debug_flag = 'Y'
2396 THEN
2397 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_ship_to_cust_account_id ', 1, 'Y');
2398 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_ship_cust_account_id_header: '||l_ship_cust_account_id_header, 1, 'Y');
2399 END IF;
2400
2401 OPEN c_int_org_location;
2402 Fetch c_int_org_location into l_int_org_location;
2403 close c_int_org_location;
2404
2405 IF aso_debug_pub.g_debug_flag = 'Y'
2406 THEN
2407 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_int_org_location ', 1, 'Y');
2408 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_int_org_location : '||l_int_org_location, 1, 'Y');
2409 END IF;
2410
2411 IF p_qte_line_id is null or p_qte_line_id = FND_API.G_MISS_NUM
2412 THEN
2413 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows(p_qte_header_id);
2414 IF
2415 aso_debug_pub.g_debug_flag = 'Y'
2416 THEN
2417 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Qte_Line_Rows', 1, 'Y');
2418 END IF;
2419 ELSE
2420 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_id);
2421 IF
2422 aso_debug_pub.g_debug_flag = 'Y'
2423 THEN
2424 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Qte_Line_Row', 1, 'Y');
2425 END IF;
2426 l_qte_line_tbl(1) := l_qte_line_rec;
2427 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: Quote Line Id : '||p_qte_line_id, 1, 'Y');
2428 END IF;
2429
2430 IF l_qte_line_tbl.count>0
2431 THEN
2432 l_qte_line_rec:= l_qte_line_tbl(1);
2433 IF
2434 aso_debug_pub.g_debug_flag = 'Y'
2435 THEN
2436 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT:l_qte_line_tbl.count>0 ', 1, 'Y');
2437 END IF;
2438 ELSE
2439 IF
2440 aso_debug_pub.g_debug_flag = 'Y'
2441 THEN
2442 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT:l_qte_line_tbl.count=0 ', 1, 'Y');
2443 END IF;
2444 END IF;
2445
2446 --bug7408162
2447 begin
2448 select location_id
2449 into l_bill_from_location_id
2450 from HR_ALL_ORGANIZATION_UNITS
2451 where organization_id = l_qte_line_tbl(1).organization_id;
2452 Exception
2453 when others then
2454 l_bill_from_location_id := NULL;
2455 End;
2456
2457 IF aso_debug_pub.g_debug_flag = 'Y'
2458 THEN
2459 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: value for l_bill_from_location_id'||l_bill_from_location_id, 1, 'Y');
2460 END IF;
2461
2462 Open c_set_of_books_id;
2463 FETCH c_set_of_books_id into l_set_of_books_id;
2464 close c_set_of_books_id;
2465
2466 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2467 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After selecting from AR_SYSTEM_PARAMETERS table', 1, 'Y');
2468 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_set_of_books_id : '||l_set_of_books_id, 1, 'Y');
2469 END IF;
2470
2471
2472 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2473 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: Before call to aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
2474 END IF;
2475 l_site_use_id_ship_header := aso_shipment_pvt.get_ship_to_site_id
2476 (l_qte_header_rec.quote_header_id,null,null);
2477 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2478 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
2479 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_site_use_id_ship_header : '||l_site_use_id_ship_header, 1,'Y');
2480 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: Before call to aso_shipment_pvt.get_cust_to_party_site_id', 1, 'Y');
2481 END IF;
2482 l_site_use_id_bill_header := aso_shipment_pvt.get_cust_to_party_site_id
2483 (l_qte_header_rec.quote_header_id, null);
2484 IF aso_debug_pub.g_debug_flag ='Y' THEN
2485 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to aso_shipment_pvt.get_cust_to_party_site_id', 1, 'Y');
2486 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_site_use_id_bill_header : '||l_site_use_id_bill_header, 1,'Y');
2487 END IF;
2488 IF l_site_use_id_ship_header is not null THEN
2489 l_site_use_id:=l_site_use_id_ship_header;
2490 IF aso_debug_pub.g_debug_flag ='Y' THEN
2491 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO c_get_acct_site ', 1, 'Y');
2492 END IF;
2493
2494 Open c_get_acct_site(l_site_use_id);
2495 Fetch c_get_acct_site into l_acct_site_id_ship;
2496 Close c_get_acct_site;
2497
2498 IF aso_debug_pub.g_debug_flag ='Y' THEN
2499 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO c_get_acct_site ', 1, 'Y');
2500 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_acct_site_id_ship : '||l_acct_site_id_ship, 1, 'Y');
2501 END IF;
2502 ELSE
2503 IF aso_debug_pub.g_debug_flag ='Y' THEN
2504 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_site_use_id is null ', 1, 'Y');
2505 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: So l_acct_site_id_ship is also null ', 1, 'Y');
2506 END IF;
2507 l_acct_site_id_ship:=NULL;
2508 END IF;
2509
2510 IF l_site_use_id_bill_header is not null THEN
2511 l_site_use_id:=l_site_use_id_bill_header;
2512 IF aso_debug_pub.g_debug_flag ='Y' THEN
2513 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO c_get_acct_site ', 1, 'Y');
2514 END IF;
2515
2516 Open c_get_acct_site(l_site_use_id);
2517 Fetch c_get_acct_site into l_acct_site_id_bill;
2518 Close c_get_acct_site;
2519
2520 IF aso_debug_pub.g_debug_flag ='Y' THEN
2521 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO c_get_acct_site ', 1, 'Y');
2522 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_acct_site_id_bill : '||l_acct_site_id_bill, 1, 'Y');
2523 END IF;
2524 ELSE
2525 IF aso_debug_pub.g_debug_flag ='Y' THEN
2526 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_site_use_id is null ', 1, 'Y');
2527 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: So l_acct_site_id_ship is also null ', 1, 'Y');
2528 END IF;
2529 l_acct_site_id_bill:=NULL;
2530 END IF;
2531
2532 OPEN c_invoice_source_id;
2533 fetch c_invoice_source_id into l_batch_source;
2534 CLOSE c_invoice_source_id;
2535
2536 IF aso_debug_pub.g_debug_flag ='Y' THEN
2537 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: After CALL TO the c_invoice_source_id ', 1, 'Y');
2538 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: After CALL TO the c_invoice_source_id :l_batch_source '||l_batch_source, 1, 'Y');
2539 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the l_trx_type_id ', 1, 'Y');
2540 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the l_trx_type_id : x_return_status : '||x_return_status, 1, 'Y');
2541 END IF;
2542
2543 l_trx_type_id := ASO_TAX_INT.get_ra_trx_type_id(l_qte_header_rec.order_type_id,l_qte_line_rec);
2544
2545 IF aso_debug_pub.g_debug_flag ='Y' THEN
2546 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: AFTER THE CALL TO ASO_TAX_INT.get_ra_trx_type_id ', 1, 'Y');
2547 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_trx_type_id : '||l_trx_type_id , 1, 'Y');
2548 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the Legal Entity API :BEFORE THE CALL TO c_cust_trx_type_id ', 1, 'Y');
2549 END IF;
2550
2551 Open c_cust_trx_type_id(l_trx_type_id);
2552 fetch c_cust_trx_type_id into l_ra_cust_trx_type_id;
2553 close c_cust_trx_type_id;
2554
2555 IF aso_debug_pub.g_debug_flag ='Y' THEN
2556 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: AFTER THE CALL TO c_cust_trx_type_id ' , 1, 'Y');
2557 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_ra_cust_trx_type_id : '||l_ra_cust_trx_type_id , 1, 'Y');
2558 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the Legal Entity API ', 1, 'Y');
2559 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the Legal Entity API : x_return_status : '||x_return_status, 1, 'Y');
2560 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the Legal Entity API : x_msg_data : '||x_msg_data, 1, 'Y');
2561 END IF;
2562
2563 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info(
2564 x_return_status=>x_return_status,
2565 x_msg_data=>X_Msg_Data,
2566 P_customer_type=>'SOLD_TO',
2567 P_customer_id =>l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID,
2568 P_transaction_type_id =>l_ra_cust_trx_type_id,
2569 P_batch_source_id => l_batch_source,
2570 P_operating_unit_id => l_qte_header_rec.ORG_ID,
2571 x_otoc_Le_info =>x_legal_entity);
2572
2573 l_legal_entity_id:= x_legal_entity.legal_entity_id;
2574
2575
2576 IF aso_debug_pub.g_debug_flag ='Y' THEN
2577 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO the Legal Entity API ', 1, 'Y');
2578 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: LEGAL ENTITY : '||l_legal_entity_id, 1, 'Y');
2579 --Added by anrajan on 05/10/2005
2580 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER LEGAL ENTITY API : RETURN_STATUS : '||x_return_status , 1, 'Y');
2581 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER LEGAL ENTITY API : MESSAGE DATA : '||X_Msg_Data , 1, 'Y');
2582 END IF;
2583
2584 --Insertion into the Header Temporary Table.
2585
2586 IF aso_debug_pub.g_debug_flag ='Y' THEN
2587 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE INSERTION INTO ZX_TRX_HEADERS_GT temporary table ', 1, 'Y');
2588 END IF;
2589
2590 DELETE FROM ZX_TRX_HEADERS_GT where
2591 APPLICATION_ID=697 and
2592 ENTITY_CODE= 'ASO_QUOTE_HEADERS_ALL' and
2593 EVENT_CLASS_CODE= 'SALES_TRANSACTION_TAX_QUOTE' and
2594 TRX_ID= p_qte_header_id;
2595
2596 insert into ZX_TRX_HEADERS_GT
2597 (
2598 INTERNAL_ORGANIZATION_ID,
2599 INTERNAL_ORG_LOCATION_ID,
2600 APPLICATION_ID,
2601 ENTITY_CODE,
2602 EVENT_CLASS_CODE,
2603 EVENT_TYPE_CODE,
2604 TRX_ID,
2605 TRX_DATE,
2606 LEDGER_ID,
2607 TRX_CURRENCY_CODE,
2608 CURRENCY_CONVERSION_DATE,
2609 CURRENCY_CONVERSION_RATE,
2610 CURRENCY_CONVERSION_TYPE,
2611 MINIMUM_ACCOUNTABLE_UNIT,
2612 PRECISION,
2613 LEGAL_ENTITY_ID,
2614 QUOTE_FLAG,
2615 TRX_NUMBER,
2616 FIRST_PTY_ORG_ID,
2617 TAX_EVENT_TYPE_CODE,
2618 VALIDATION_CHECK_FLAG,
2619 TAX_REPORTING_FLAG,
2620 SHIP_TO_CUST_ACCT_SITE_USE_ID,
2621 BILL_TO_CUST_ACCT_SITE_USE_ID,
2622 DOC_LEVEL_RECALC_FLAG,
2623 SHIP_THIRD_PTY_ACCT_SITE_ID,
2624 BILL_THIRD_PTY_ACCT_SITE_ID,
2625 SHIP_THIRD_PTY_ACCT_ID,
2626 BILL_THIRD_PTY_ACCT_ID,
2627 ROUNDING_BILL_TO_PARTY_ID
2628 )
2629 values
2630 (
2631 l_qte_header_rec.ORG_ID,
2632 l_int_org_location,
2633 697,
2634 'ASO_QUOTE_HEADERS_ALL',
2635 'SALES_TRANSACTION_TAX_QUOTE',
2636 'CREATE',
2637 p_qte_header_id,
2638 sysdate,
2639 l_set_of_books_id,
2640 l_qte_header_rec.CURRENCY_CODE,
2641 l_qte_header_rec.EXCHANGE_RATE_DATE,
2642 l_qte_header_rec.EXCHANGE_RATE,
2643 l_qte_header_rec.EXCHANGE_TYPE_CODE,
2644 l_minimum_accountable_unit,
2645 l_precision,
2646 l_legal_entity_id,
2647 'Y',
2648 l_qte_header_rec.QUOTE_NUMBER,
2649 null,
2650 'CREATE',
2651 null,
2652 'N',
2653 l_site_use_id_ship_header,
2654 l_site_use_id_bill_header,
2655 'N',
2656 l_acct_site_id_ship,
2657 l_acct_site_id_bill,
2658 l_ship_cust_account_id_header,
2659 l_qte_header_rec.INVOICE_TO_CUST_ACCOUNT_ID,
2660 l_qte_header_rec.INVOICE_TO_CUST_PARTY_ID
2661 );
2662
2663 print_tax_info(1,p_qte_header_id);
2664 IF aso_debug_pub.g_debug_flag ='Y' THEN
2665 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: BEFORE THE QUOTE LINES LOOP ', 1, 'Y');
2666 END IF;
2667
2668 DELETE FROM Zx_transaction_lines_gt
2669 WHERE APPLICATION_ID= 697
2670 AND ENTITY_CODE= 'ASO_QUOTE_HEADERS_ALL'
2671 AND EVENT_CLASS_CODE = 'SALES_TRANSACTION_TAX_QUOTE'
2672 AND TRX_ID = p_qte_header_id
2673 AND TRX_LEVEL_TYPE= 'LINE';
2674
2675 FOR i in 1..l_qte_line_tbl.count
2676 LOOP
2677 l_qte_line_rec:=l_qte_line_tbl(i);
2678 IF aso_debug_pub.g_debug_flag ='Y' THEN
2679 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP : QUOTE LINE ID :'||l_qte_line_rec.QUOTE_LINE_ID, 1, 'Y');
2680 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO aso_utility_pvt.query_shipment_rows', 1, 'Y');
2681 END IF;
2682 l_Shipment_tbl:=aso_utility_pvt.query_shipment_rows( p_qte_header_id,l_qte_line_rec.quote_line_id);
2683
2684 --Condition added by anrajan on 06/10/2005 for Bug Number :4656728
2685 IF l_Shipment_tbl.count>0
2686 THEN
2687 l_Shipment_Rec:=l_shipment_tbl(1);
2688 END IF;
2689
2690 IF aso_debug_pub.g_debug_flag ='Y' THEN
2691 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO aso_utility_pvt.query_shipment_rows', 1, 'Y');
2692 END IF;
2693
2694 l_site_use_id_bill_lines:=aso_shipment_pvt.get_cust_to_party_site_id
2695 (p_qte_header_id,
2696 l_qte_line_rec.quote_line_id);
2697 IF aso_debug_pub.g_debug_flag ='Y' THEN
2698 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO aso_shipment_pvt.get_cust_to_party_site_id', 1, 'Y');
2699 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_site_use_id_bill_lines : '||l_site_use_id_bill_lines , 1, 'Y');
2700 END IF;
2701
2702 l_site_use_id_ship_lines:= aso_shipment_pvt.get_ship_to_site_id
2703 (p_qte_header_id,
2704 l_qte_line_rec.quote_line_id,
2705 l_Shipment_Rec.SHIPMENT_ID);
2706 IF aso_debug_pub.g_debug_flag ='Y' THEN
2707 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
2708 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_site_use_id_ship_lines '||l_site_use_id_ship_lines , 1, 'Y');
2709 END IF;
2710
2711 Open c_getlocinfo(l_site_use_id_bill_lines);
2712 Fetch c_getlocinfo into l_acct_site_id_bill_lines,l_bill_cust_acct_id_lines;
2713 Close c_getlocinfo;
2714
2715 Open c_getlocinfo(l_site_use_id_ship_lines);
2716 Fetch c_getlocinfo into l_acct_site_id_ship_lines,l_ship_cust_acct_id_lines;
2717 Close c_getlocinfo;
2718
2719 IF aso_debug_pub.g_debug_flag ='Y' THEN
2720 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO c_getlocinfo', 1, 'Y');
2721 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_acct_site_id_bill_lines '||l_acct_site_id_bill_lines , 1, 'Y');
2722 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_bill_cust_acct_id_lines '||l_bill_cust_acct_id_lines , 1, 'Y');
2723 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_acct_site_id_ship_lines '||l_acct_site_id_ship_lines , 1, 'Y');
2724 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_ship_cust_acct_id_lines '||l_ship_cust_acct_id_lines , 1, 'Y');
2725 END IF;
2726
2727 IF aso_debug_pub.g_debug_flag ='Y' THEN
2728 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_shiplocation', 1, 'Y');
2729 END IF;
2730
2731 IF
2732 l_Shipment_Rec.ship_to_party_site_id is not null
2733 THEN
2734 OPEN c_shiplocation(l_Shipment_Rec.ship_to_party_site_id);
2735 ELSE
2736 OPEN c_shiplocation(l_Shipment_header_rec.ship_to_party_site_id);
2737 END IF;
2738 FETCH c_shiplocation into l_ship_to_location;
2739 close c_shiplocation;
2740
2741 IF aso_debug_pub.g_debug_flag ='Y' THEN
2742 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO c_shiplocation', 1, 'Y');
2743 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_ship_to_location '||l_ship_to_location , 1, 'Y');
2744 END IF;
2745
2746 IF
2747 l_qte_line_rec.invoice_to_party_site_id is not null
2748 THEN
2749 OPEN c_shiplocation(l_qte_line_rec.invoice_to_party_site_id);
2750 ELSE
2751 OPEN c_shiplocation(l_qte_header_rec.invoice_to_party_site_id);
2752 END IF;
2753
2754 FETCH c_shiplocation into l_bill_to_location;
2755 close c_shiplocation;
2756
2757 IF aso_debug_pub.g_debug_flag ='Y' THEN
2758 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO c_shiplocation', 1, 'Y');
2759 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_bill_to_location '||l_bill_to_location , 1, 'Y');
2760 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO ASO_TAX_INT.get_ra_trx_type_id ', 1, 'Y');
2761 END IF;
2762
2763 l_trx_type_id := ASO_TAX_INT.get_ra_trx_type_id(l_qte_header_rec.order_type_id,l_qte_line_rec);
2764 IF aso_debug_pub.g_debug_flag ='Y' THEN
2765 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO ASO_TAX_INT.get_ra_trx_type_id ', 1, 'Y');
2766 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_trx_type_id '||l_trx_type_id , 1, 'Y');
2767 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_product_type ', 1, 'Y');
2768 END IF;
2769
2770 Open c_product_type;
2771 Fetch c_product_type into l_product_type;
2772 close c_product_type;
2773
2774 IF aso_debug_pub.g_debug_flag ='Y' THEN
2775 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_product_type ' , 1, 'Y');
2776 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_product_type: '||l_product_type, 1, 'Y');
2777 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_tax ', 1, 'Y');
2778 END IF;
2779
2780
2781 Open c_tax;
2782 fetch c_tax into l_hdr_tax_exempt_flag,l_hdr_tax_exempt_number,l_hdr_tax_exempt_reason_code;
2783 close c_tax;
2784
2785 IF aso_debug_pub.g_debug_flag ='Y' THEN
2786 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_tax ' , 1, 'Y');
2787 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_HDR_TAX_EXEMPT_FLAG: '||l_hdr_tax_exempt_flag , 1, 'Y');
2788 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_HDR_TAX_EXEMPT_NUMBER : '||l_hdr_tax_exempt_number , 1, 'Y');
2789 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_HDR_TAX_EXEMPT_REASON_CODE : '||l_hdr_tax_exempt_reason_code , 1, 'Y');
2790 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_cust_trx_type_id ', 1, 'Y');
2791 END IF;
2792
2793
2794 Open c_cust_trx_type_id(l_trx_type_id);
2795 fetch c_cust_trx_type_id into l_ra_cust_trx_type_id;
2796 close c_cust_trx_type_id;
2797
2798 IF aso_debug_pub.g_debug_flag ='Y' THEN
2799 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_cust_trx_type_id ' , 1, 'Y');
2800 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_ra_cust_trx_type_id : '||l_ra_cust_trx_type_id , 1, 'Y');
2801 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_LOCATION_ID ', 1, 'Y');
2802 END IF;
2803
2804
2805 OPEN c_LOCATION_ID;
2806 Fetch c_LOCATION_ID into l_SHIP_FROM_LOCATION_ID;
2807 close c_LOCATION_ID;
2808
2809 IF aso_debug_pub.g_debug_flag ='Y' THEN
2810 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_LOCATION_ID ', 1, 'Y');
2811 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_SHIP_FROM_LOCATION_ID : '||l_SHIP_FROM_LOCATION_ID , 1, 'Y');
2812 --aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE INSERTING INTO Zx_transaction_lines_gt ', 1, 'Y');
2813 END IF;
2814
2815
2816 -- new code added by suyog bug 5061912
2817
2818 -- get the resource id and the trxn date
2819 OPEN c_get_resource_id(p_qte_header_id);
2820 FETCH c_get_resource_id into l_resource_id;
2821 CLOSE c_get_resource_id;
2822
2823 IF aso_debug_pub.g_debug_flag ='Y' THEN
2824 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_get_resource_id ', 1, 'Y');
2825 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_resource_id: '|| l_resource_id , 1, 'Y');
2826 END IF;
2827
2828 -- get the org id based upon the resource id and the trxn date
2829 OPEN c_get_org_id(l_resource_id);
2830 FETCH c_get_org_id into l_poo_party_id;
2831 CLOSE c_get_org_id;
2832
2833 IF aso_debug_pub.g_debug_flag ='Y' THEN
2834 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_get_org_id ', 1, 'Y');
2835 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_poo_party_id: '|| l_poo_party_id , 1, 'Y');
2836 END IF;
2837
2838 -- get the location based upon the org id
2839 OPEN c_get_location_id(l_poo_party_id);
2840 FETCH c_get_location_id into l_poo_location_id;
2841 CLOSE c_get_location_id;
2842
2843 IF aso_debug_pub.g_debug_flag ='Y' THEN
2844 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_get_location_id ', 1, 'Y');
2845 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_poo_location_id: '|| l_poo_location_id , 1, 'Y');
2846 END IF;
2847
2848 -- POA PARTY ID is same as the internal_organization_id
2849 -- POA LOCATION ID is same as the l_int_org_location
2850
2851 IF aso_debug_pub.g_debug_flag ='Y' THEN
2852 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :Value for poa_party_id: '|| l_qte_header_rec.ORG_ID , 1, 'Y');
2853 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :Value for poa_location_id: '|| l_int_org_location , 1, 'Y');
2854 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE INSERTING INTO Zx_transaction_lines_gt ', 1, 'Y');
2855 END IF;
2856
2857 -- end of new code added by suyog
2858
2859
2860 -- Code changes for Default tax Classification Code Bug 5177854 BEGIN
2861
2862 ZX_AR_TAX_CLASSIFICATN_DEF_PKG.GET_DEFAULT_TAX_CLASSIFICATION
2863 (
2864 p_ship_to_site_use_id => l_site_use_id_ship_lines,
2865 p_bill_to_site_use_id => l_site_use_id_bill_lines,
2866 p_inventory_item_id => l_qte_line_rec.Inventory_item_id,
2867 p_organization_id => l_qte_line_rec.organization_id,
2868 p_set_of_books_id => l_set_of_books_id,
2869 p_trx_date => sysdate,
2870 p_trx_type_id => l_trx_type_id,
2871 p_tax_classification_code => l_Tax_Classification_Code,
2872 p_cust_trx_id => l_ra_cust_trx_type_id,
2873 p_customer_id => nvl(l_shipment_rec.ship_to_cust_party_id,l_shipment_header_rec.ship_to_cust_party_id),
2874 appl_short_name => 'ASO',
2875 p_entity_code => 'ASO_QUOTE_HEADERS_ALL',
2876 p_event_class_code => 'SALES_TRANSACTION_TAX_QUOTE',
2877 p_application_id => 697,
2878 p_internal_organization_id => l_qte_header_rec.org_id
2879 );
2880
2881 -- Code Changes for Default tax classification code Bug 5177854 END
2882
2883
2884 --Insertion into the Lines Temporary Table
2885 insert into Zx_transaction_lines_gt
2886 (
2887 APPLICATION_ID,
2888 ENTITY_CODE,
2889 EVENT_CLASS_CODE,
2890 TRX_ID,
2891 TRX_LEVEL_TYPE,
2892 TRX_LINE_ID,
2893 LINE_LEVEL_ACTION,
2894 LINE_CLASS,
2895 TRX_SHIPPING_DATE,
2896 TRX_LINE_TYPE,
2897 TRX_LINE_DATE,
2898 TRX_BUSINESS_CATEGORY,
2899 LINE_AMT,
2900 TRX_LINE_QUANTITY,
2901 EXEMPT_CERTIFICATE_NUMBER,
2902 EXEMPT_REASON_CODE,
2903 PRODUCT_ID,
2904 PRODUCT_ORG_ID,
2905 UOM_CODE,
2906 --PRODUCT_TYPE,
2907 FOB_POINT,
2908 SHIP_TO_PARTY_ID,
2909 SHIP_FROM_PARTY_ID,
2910 BILL_TO_PARTY_ID,
2911 SHIP_TO_PARTY_SITE_ID,
2912 BILL_TO_PARTY_SITE_ID,
2913 SHIP_TO_LOCATION_ID,
2914 BILL_TO_LOCATION_ID,
2915 SHIP_FROM_LOCATION_ID,
2916 HISTORICAL_FLAG,
2917 LINE_AMT_INCLUDES_TAX_FLAG,
2918 EXEMPTION_CONTROL_FLAG,UNIT_PRICE,
2919 TRX_LINE_GL_DATE,
2920 RECEIVABLES_TRX_TYPE_ID,
2921 BILL_TO_CUST_ACCT_SITE_USE_ID,
2922 SHIP_TO_CUST_ACCT_SITE_USE_ID,
2923 SHIP_THIRD_PTY_ACCT_SITE_ID,
2924 BILL_THIRD_PTY_ACCT_SITE_ID,
2925 SHIP_THIRD_PTY_ACCT_ID,
2926 BILL_THIRD_PTY_ACCT_ID,
2927 CTRL_HDR_TX_APPL_FLAG,
2928 TRX_LINE_NUMBER,
2929 POO_LOCATION_ID,
2930 POO_PARTY_ID,
2931 POA_PARTY_ID,
2932 POA_LOCATION_ID,
2933 OUTPUT_TAX_CLASSIFICATION_CODE,
2934 BILL_FROM_LOCATION_ID --bug7408162
2935 )
2936 values
2937 (
2938 697,
2939 'ASO_QUOTE_HEADERS_ALL',
2940 'SALES_TRANSACTION_TAX_QUOTE',
2941 p_qte_header_id,
2942 'LINE',
2943 l_qte_line_rec.QUOTE_LINE_ID,
2944 'CREATE',
2945 'INVOICE',
2946 nvl(l_Shipment_Rec.REQUEST_DATE,l_Shipment_header_rec.request_date),
2947 'ITEM',
2948 SYSDATE,
2949 null,
2950 l_qte_line_rec.LINE_QUOTE_PRICE*l_qte_line_rec.QUANTITY,
2951 l_qte_line_rec.QUANTITY,
2952 l_HDR_TAX_EXEMPT_NUMBER,
2953 l_HDR_TAX_EXEMPT_REASON_CODE,
2954 l_qte_line_rec.INVENTORY_ITEM_ID,
2955 l_qte_line_rec.organization_id,
2956 l_qte_line_rec.UOM_CODE,
2957 -- l_product_type,
2958 nvl(l_Shipment_Rec.fob_code,l_Shipment_header_rec.fob_code),
2959 nvl(l_Shipment_Rec.ship_to_cust_party_id,l_Shipment_header_rec.ship_to_cust_party_id),
2960 nvl(l_Shipment_Rec.ship_from_org_id,l_Shipment_header_rec.ship_from_org_id),
2961 nvl(l_qte_line_rec.INVOICE_TO_CUST_PARTY_ID,l_qte_header_rec.INVOICE_TO_CUST_PARTY_ID),
2962 nvl(l_Shipment_Rec.SHIP_TO_PARTY_SITE_ID,l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID),
2963 nvl(l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID,l_qte_header_rec.INVOICE_TO_PARTY_SITE_ID),
2964 l_ship_to_location,
2965 l_bill_to_location,
2966 l_SHIP_FROM_LOCATION_ID,
2967 'N',
2968 'S',
2969 nvl(l_HDR_TAX_EXEMPT_FLAG,'S'),
2970 l_qte_line_rec.LINE_QUOTE_PRICE,
2971 sysdate,
2972 l_ra_cust_trx_type_id,
2973 l_site_use_id_bill_lines,
2974 l_site_use_id_ship_lines,
2975 l_acct_site_id_ship_lines,
2976 l_acct_site_id_bill_lines,
2977 l_ship_cust_acct_id_lines,
2978 l_bill_cust_acct_id_lines,
2979 'N',
2980 l_qte_line_rec.LINE_NUMBER,
2981 l_poo_location_id,
2982 l_poo_party_id,
2983 l_qte_header_rec.ORG_ID,
2984 l_int_org_location,
2985 l_Tax_Classification_Code,
2986 l_bill_from_location_id -- bug 7408162
2987 );
2988
2989 END LOOP;
2990 print_tax_info(2,p_qte_header_id);
2991 IF aso_debug_pub.g_debug_flag ='Y' THEN
2992 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE QUOTE LINES LOOP ', 1, 'Y');
2993 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO TAX ENGINE ', 1, 'Y');
2994 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: p_api_version_number : '||p_api_version_number , 1, 'Y');
2995 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_init_msg_list : '||l_init_msg_list , 1, 'Y');
2996 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_commit : '||l_commit , 1, 'Y');
2997 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_validation_level : '||l_validation_level , 1, 'Y');
2998 l_tax_start_time := dbms_utility.get_time;
2999 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: CALLED TAX ENGINE AT : '|| l_tax_start_time, 1, 'Y');
3000 END IF;
3001
3002 --Changes done by anrajan on 20/09/05
3003 l_msg_cnt1:= FND_MSG_PUB.Count_Msg;
3004 IF aso_debug_pub.g_debug_flag ='Y' THEN
3005 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: Message Count Before Tax call : '|| l_msg_cnt1, 1, 'Y');
3006 END IF;
3007
3008 ZX_API_PUB.calculate_tax(p_api_version => p_api_version_number,
3009 p_init_msg_list => l_init_msg_list,
3010 p_commit => l_commit,
3011 P_VALIDATION_LEVEL =>l_validation_level,
3012 X_RETURN_STATUS =>x_return_status,
3013 X_MSG_COUNT =>X_Msg_Count,
3014 X_MSG_DATA =>X_Msg_Data);
3015
3016 --Changes done by anrajan on 20/09/05
3017 l_msg_cnt2:=FND_MSG_PUB.Count_Msg;
3018 IF aso_debug_pub.g_debug_flag ='Y' THEN
3019 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: Message Count After Tax call : '|| l_msg_cnt2, 1, 'Y');
3020 END IF;
3021
3022 IF aso_debug_pub.g_debug_flag ='Y' THEN
3023 l_tax_end_time := dbms_utility.get_time;
3024 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO TAX ENGINE ', 1, 'Y');
3025 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO TAX ENGINE : X_RETURN_STATUS : '||x_return_status , 1, 'Y');
3026 l_tax_total_time := (l_tax_end_time - l_tax_start_time)/100;
3027 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: TAX CALL ENDED AT : '|| l_tax_end_time, 1, 'Y');
3028 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: TIME TAKEN BY TAX ENGINE IN SECONDS: '|| l_tax_total_time, 1, 'Y');
3029 END IF;
3030
3031 print_tax_info(3,p_qte_header_id);
3032
3033 IF x_return_status='S' THEN
3034
3035 if p_qte_line_id is not null THEN
3036 IF aso_debug_pub.g_debug_flag ='Y' THEN
3037 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN SUCCESSFUL ', 1, 'Y');
3038 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :DELETING FROM ASO_TAX_DETAILS :QUOTE HEADER_ID : '||p_qte_header_id||'; QUOTE LINE ID : '||p_qte_line_id , 1, 'Y');
3039 END IF;
3040 Delete from
3041 ASO_TAX_DETAILS
3042 where
3043 QUOTE_HEADER_ID=p_qte_header_id
3044 and
3045 QUOTE_LINE_ID=p_qte_line_id;
3046 ELSE
3047 IF aso_debug_pub.g_debug_flag ='Y' THEN
3048 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN SUCCESSFUL ', 1, 'Y');
3049 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :DELETING FROM ASO_TAX_DETAILS :QUOTE HEADER_ID : '||p_qte_header_id,1, 'Y');
3050 END IF;
3051 Delete from ASO_TAX_DETAILS
3052 where QUOTE_HEADER_ID=p_qte_header_id
3053 and QUOTE_LINE_ID is not null;
3054 END IF;
3055
3056
3057 --Inserting the values from output temporary table into the ASO_TAX_DETAILS table.
3058
3059
3060 insert into Aso_tax_details
3061 (
3062 TAX_DETAIL_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
3063 LAST_UPDATE_LOGIN,PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE,
3064 QUOTE_HEADER_ID,QUOTE_LINE_ID,QUOTE_SHIPMENT_ID,TAX_CODE,TAX_RATE,
3065 TAX_DATE,TAX_AMOUNT,TAX_EXEMPT_NUMBER,TAX_EXEMPT_REASON_CODE,
3066 TAX_INCLUSIVE_FLAG,OBJECT_VERSION_NUMBER,TAX_RATE_ID,
3067 TAX_EXEMPT_FLAG --Added by anrajan on 05/10/2005
3068 )
3069 select
3070 ASO_TAX_DETAILS_S.nextval,SYSDATE,G_USER_ID,SYSDATE,G_USER_ID,
3071 G_LOGIN_ID,APPLICATION_ID,sysdate,
3072 a.TRX_ID,a.TRX_LINE_ID,b.SHIPMENT_ID,a.TAX_RATE_CODE,a.TAX_RATE,
3073 a.TAX_DETERMINE_DATE,a.TAX_AMT,a.EXEMPT_CERTIFICATE_NUMBER,a.EXEMPT_REASON_CODE,
3074 a.TAX_AMT_INCLUDED_FLAG,a.OBJECT_VERSION_NUMBER,a.TAX_RATE_ID,
3075 nvl(l_HDR_TAX_EXEMPT_FLAG,'S') --Added by anrajan on 05/10/2005
3076 FROM
3077 Zx_detail_tax_lines_gt a,aso_shipments b
3078 WHERE
3079 a.TRX_ID=b.QUOTE_HEADER_ID
3080 AND
3081 a.APPLICATION_ID=697
3082 AND
3083 a.ENTITY_CODE='ASO_QUOTE_HEADERS_ALL'
3084 AND
3085 a.EVENT_CLASS_CODE='SALES_TRANSACTION_TAX_QUOTE'
3086 AND
3087 a.TRX_ID=p_qte_header_id
3088 AND
3089 (
3090 a.TRX_LINE_ID=b.QUOTE_LINE_ID
3091 OR
3092 (a.TRX_LINE_ID is null AND b.QUOTE_LINE_ID is null)
3093 )
3094
3095 --AND
3096 --a.TRX_LEVEL_TYPE='LINE'
3097 ;
3098
3099 --Changed by Anoop on 14 Sep 2005.
3100 IF aso_debug_pub.g_debug_flag ='Y' THEN
3101 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :Number of rows inserted : '||sql%rowcount, 1, 'Y');
3102 END IF;
3103
3104 ELSE
3105 IF aso_debug_pub.g_debug_flag ='Y' THEN
3106 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN NOT SUCCESSFUL ', 1, 'Y');
3107 END IF;
3108 --Changes done by anrajan on 20/09/05
3109 for i in 1..(l_msg_cnt2-l_msg_cnt1)
3110 loop
3111 IF aso_debug_pub.g_debug_flag ='Y' THEN
3112 FND_MSG_PUB.GET(p_msg_index => l_msg_cnt1+1,
3113 p_data =>X_Msg_Data,
3114 p_encoded =>'F',
3115 p_msg_index_out =>X_Msg_Count);
3116 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :X_Msg_Data : '||X_Msg_Data, 1, 'Y');
3117 END IF;
3118 FND_MSG_PUB.Delete_Msg(l_msg_cnt1+1);
3119 END LOOP;
3120 x_return_status := FND_API.G_RET_STS_SUCCESS;
3121 END IF;
3122 FND_MSG_PUB.Count_And_Get
3123 (
3124 p_count => x_msg_count,
3125 p_data => x_msg_data
3126 );
3127
3128 EXCEPTION
3129 WHEN FND_API.G_EXC_ERROR THEN
3130 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3131 P_API_NAME => L_API_NAME
3132 ,P_PKG_NAME => G_PKG_NAME
3133 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3134 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3135 ,X_MSG_COUNT => X_MSG_COUNT
3136 ,X_MSG_DATA => X_MSG_DATA
3137 ,X_RETURN_STATUS => X_RETURN_STATUS);
3138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3139 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3140 P_API_NAME => L_API_NAME ,P_PKG_NAME => G_PKG_NAME
3141 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3142 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3143 ,X_MSG_COUNT => X_MSG_COUNT
3144 ,X_MSG_DATA => X_MSG_DATA
3145 ,X_RETURN_STATUS => X_RETURN_STATUS);
3146
3147 WHEN OTHERS THEN
3148 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3149 P_API_NAME => L_API_NAME
3150 ,P_PKG_NAME => G_PKG_NAME
3151 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3152 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3153 ,P_SQLCODE => SQLCODE
3154 ,P_SQLERRM => SQLERRM
3155 ,X_MSG_COUNT => X_MSG_COUNT
3156 ,X_MSG_DATA => X_MSG_DATA
3157 ,X_RETURN_STATUS => X_RETURN_STATUS);
3158
3159 end CALCULATE_TAX_WITH_GTT;
3160 -- End Calculate Tax with GTT added as a part of etax By Anoop Rajan om 9 August 2005
3161
3162
3163
3164
3165 FUNCTION Get_Tax_Detail_Id (
3166 p_qte_header_id NUMBER,
3167 p_qte_line_id NUMBER,
3168 p_shipment_id NUMBER) RETURN NUMBER
3169 IS
3170 CURSOR c_tax1 IS
3171 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3172 WHERE quote_shipment_id = p_shipment_id;
3173 CURSOR c_tax2 IS
3174 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3175 WHERE quote_header_id = p_qte_header_id and quote_line_id is NULL
3176 and quote_shipment_id = p_shipment_id;
3177 CURSOR c_tax3 IS
3178 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3179 WHERE quote_header_id = p_qte_header_id and quote_shipment_id = p_shipment_id
3180 and quote_line_id = p_qte_line_id ;
3181 l_tax_detail_id NUMBER;
3182 BEGIN
3183 OPEN c_tax3;
3184 FETCH c_tax3 INTO l_tax_detail_id;
3185 IF c_tax3%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3186 CLOSE c_tax3;
3187 return l_tax_detail_id;
3188 END IF;
3189 CLOSE c_tax3;
3190 OPEN c_tax2;
3191 FETCH c_tax2 INTO l_tax_detail_id ;
3192 IF c_tax2%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3193 CLOSE c_tax2;
3194 return l_tax_detail_id;
3195 END IF;
3196 CLOSE c_tax2;
3197 /*OPEN c_tax1;
3198 FETCH c_tax1 INTO l_tax_detail_id;
3199 IF c_tax1%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3200 CLOSE c_tax1;
3201 return l_tax_detail_id;
3202 END IF;
3203 CLOSE c_tax1;*/
3204 return l_tax_detail_id;
3205 END Get_Tax_Detail_Id;
3206
3207
3208
3209 FUNCTION Get_Tax_Code (
3210 p_qte_header_id NUMBER,
3211 p_qte_line_id NUMBER,
3212 p_shipment_id NUMBER) RETURN VARCHAR2
3213 IS
3214 CURSOR c_tax1 IS
3215 SELECT tax_code FROM ASO_TAX_DETAILS
3216 WHERE quote_shipment_id = p_shipment_id ;
3217 CURSOR c_tax2 IS
3218 SELECT tax_code FROM ASO_TAX_DETAILS
3219 WHERE quote_line_id = p_qte_line_id ;
3220 CURSOR c_tax3 IS
3221 SELECT tax_code FROM ASO_TAX_DETAILS
3222 WHERE quote_header_id = p_qte_header_id ;
3223 l_orig_tax_code VARCHAR2(240);
3224 BEGIN
3225 OPEN c_tax1;
3226 FETCH c_tax1 INTO l_orig_tax_code;
3227 IF c_tax1%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3228 CLOSE c_tax1;
3229 return l_orig_tax_code;
3230 END IF;
3231 CLOSE c_tax1;
3232 OPEN c_tax2;
3233 FETCH c_tax2 INTO l_orig_tax_code;
3234 IF c_tax2%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3235 CLOSE c_tax2;
3236 return l_orig_tax_code;
3237 END IF;
3238 CLOSE c_tax2;
3239 OPEN c_tax3;
3240 FETCH c_tax3 INTO l_orig_tax_code;
3241 IF c_tax3%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3242 CLOSE c_tax3;
3243 return l_orig_tax_code;
3244 END IF;
3245 CLOSE c_tax3;
3246 return l_orig_tax_code;
3247 END Get_Tax_Code;
3248
3249 FUNCTION Get_Tax_exempt_flag (
3250 p_qte_header_id NUMBER,
3251 p_qte_line_id NUMBER,
3252 p_shipment_id NUMBER) RETURN VARCHAR2
3253 IS
3254 CURSOR c_tax1 IS
3255 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3256 WHERE quote_shipment_id = p_shipment_id ;
3257 CURSOR c_tax2 IS
3258 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3259 WHERE quote_line_id = p_qte_line_id ;
3260 CURSOR c_tax3 IS
3261 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3262 WHERE quote_header_id = p_qte_header_id ;
3263 l_tax_exempt_flag VARCHAR2(1);
3264 BEGIN
3265 OPEN c_tax1;
3266 FETCH c_tax1 INTO l_tax_exempt_flag;
3267 IF c_tax1%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3268 CLOSE c_tax1;
3269 return l_tax_exempt_flag;
3270 END IF;
3271 CLOSE c_tax1;
3272 OPEN c_tax2;
3273 FETCH c_tax2 INTO l_tax_exempt_flag;
3274 IF c_tax2%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3275 CLOSE c_tax2;
3276 return l_tax_exempt_flag;
3277 END IF;
3278 CLOSE c_tax2;
3279 OPEN c_tax3;
3280 FETCH c_tax3 INTO l_tax_exempt_flag;
3281 IF c_tax3%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3282 CLOSE c_tax3;
3283 return l_tax_exempt_flag;
3284 END IF;
3285 CLOSE c_tax3;
3286 return l_tax_exempt_flag;
3287 END Get_Tax_Exempt_Flag;
3288
3289 FUNCTION Get_Tax_exempt_number (
3290 p_qte_header_id NUMBER,
3291 p_qte_line_id NUMBER,
3292 p_shipment_id NUMBER) RETURN VARCHAR2
3293 IS
3294 CURSOR c_tax1 IS
3295 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3296 WHERE quote_shipment_id = p_shipment_id ;
3297 CURSOR c_tax2 IS
3298 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3299 WHERE quote_line_id = p_qte_line_id ;
3300 CURSOR c_tax3 IS
3301 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3302 WHERE quote_header_id = p_qte_header_id ;
3303 l_tax_exempt_number VARCHAR2(80);
3304 BEGIN
3305 OPEN c_tax1;
3306 FETCH c_tax1 INTO l_tax_exempt_number;
3307 IF c_tax1%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3308 CLOSE c_tax1;
3309 return l_tax_exempt_number;
3310 END IF;
3311 CLOSE c_tax1;
3312 OPEN c_tax2;
3313 FETCH c_tax2 INTO l_tax_exempt_number;
3314 IF c_tax2%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3315 CLOSE c_tax2;
3316 return l_tax_exempt_number;
3317 END IF;
3318 CLOSE c_tax2;
3319 OPEN c_tax3;
3320 FETCH c_tax3 INTO l_tax_exempt_number;
3321 IF c_tax3%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3322 CLOSE c_tax3;
3323 return l_tax_exempt_number;
3324 END IF;
3325 CLOSE c_tax3;
3326 return l_tax_exempt_number;
3327 END Get_Tax_Exempt_number;
3328
3329 FUNCTION Get_Tax_exempt_REASON_CODE (
3330 p_qte_header_id NUMBER,
3331 p_qte_line_id NUMBER,
3332 p_shipment_id NUMBER) RETURN VARCHAR2
3333 IS
3334 CURSOR c_tax1 IS
3335 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3336 WHERE quote_shipment_id = p_shipment_id ;
3337 CURSOR c_tax2 IS
3338 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3339 WHERE quote_line_id = p_qte_line_id ;
3340 CURSOR c_tax3 IS
3341 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3342 WHERE quote_header_id = p_qte_header_id ;
3343 l_tax_exempt_reason_code VARCHAR2(80);
3344 BEGIN
3345 OPEN c_tax1;
3346 FETCH c_tax1 INTO l_tax_exempt_reason_code;
3347 IF c_tax1%FOUND and l_tax_exempt_reason_code is not null and l_tax_exempt_reason_code <> FND_API.G_MISS_CHAR THEN
3348 CLOSE c_tax1;
3349 return l_tax_exempt_reason_code;
3350 END IF;
3351 CLOSE c_tax1;
3352 OPEN c_tax2;
3353 FETCH c_tax2 INTO l_tax_exempt_reason_code;
3354 IF c_tax2%FOUND and l_tax_exempt_reason_code is not null and l_tax_exempt_reason_code <> FND_API.G_MISS_CHAR THEN
3355 CLOSE c_tax2;
3356 return l_tax_exempt_reason_code;
3357 END IF;
3358 CLOSE c_tax2;
3359 OPEN c_tax3;
3360 FETCH c_tax3 INTO l_tax_exempt_reason_code;
3361 IF c_tax3%FOUND and l_tax_exempt_reason_code is not null and l_tax_exempt_reason_code <> FND_API.G_MISS_CHAR THEN
3362 CLOSE c_tax3;
3363 return l_tax_exempt_reason_code;
3364 END IF;
3365 CLOSE c_tax3;
3366 return l_tax_exempt_reason_code;
3367 END Get_Tax_Exempt_reason_code;
3368
3369 FUNCTION Get_Tax_Invoice_To (
3370 p_ln_invoice_id NUMBER,
3371 p_hd_invoice_id NUMBER) RETURN NUMBER
3372 IS
3373 BEGIN
3374 return NVL(p_ln_invoice_id, p_hd_invoice_id);
3375 END Get_Tax_Invoice_To;
3376
3377 FUNCTION GET_ra_trx_type_ID (p_order_type_id NUMBER,p_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type) RETURN NUMBER
3378 IS
3379 CURSOR C_OE_trns(l_order_type_Id NUMBER) IS
3380 SELECT default_inbound_line_type_id, default_outbound_line_type_id, cust_trx_type_id
3381 FROM OE_TRANSACTION_TYPES_VL
3382 WHERE transaction_type_id=l_order_type_Id;
3383 l_in_line_type NUMBER;
3384 l_out_line_type NUMBER;
3385 l_cust_trx_type_id NUMBER;
3386 l_inv_cust_trx_type_id NUMBER;
3387 l_line_type_id NUMBER;
3388 l_order_type_id NUMBER := p_order_type_id;
3389 BEGIN
3390
3391 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3392 aso_debug_pub.add(' ASO_TAX_INT :p_order_type_id'||p_order_type_id , 1, 'N');
3393 END IF;
3394
3395 IF p_order_type_id is NULL OR p_order_type_id = FND_API.G_MISS_NUM THEN
3396
3397 -- Change START
3398 -- Release 12 MOAC Changes : Bug 4500739
3399 -- Changes Done by : Girish
3400 -- Comments : Changed to use HR EIT in place of org striped profile.
3401
3402 --l_order_type_id := to_number(fnd_profile.value('ASO_ORDER_TYPE_ID'));
3403 l_order_type_id := to_number(ASO_UTILITY_PVT.get_ou_attribute_value(ASO_UTILITY_PVT.G_DEFAULT_ORDER_TYPE));
3404
3405 -- Change END
3406
3407 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3408 aso_debug_pub.add(' ASO_TAX_INT Porder_type id is null:l_order_type_id'||l_order_type_id , 1, 'N');
3409 END IF;
3410
3411 END IF;
3412 OPEN C_OE_trns(l_order_type_id);
3413 FETCH C_OE_trns INTO l_in_line_type, l_out_line_type, l_cust_trx_type_id;
3414
3415 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3416
3417 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:l_in_line_type,l_out_line_type,l_cust_trx_type_id'||l_in_line_type , 1, 'N');
3418 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:l_out_line_type'||l_out_line_type , 1, 'N');
3419 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:l_cust_trx_type_id'||l_cust_trx_type_id , 1, 'N');
3420
3421 END IF;
3422
3423
3424 IF C_OE_trns%NOTFOUND THEN
3425 NULL;
3426
3427 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3428 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:NOT FOUND' , 1, 'N');
3429 END IF;
3430
3431 END IF;
3432 CLOSE C_OE_trns;
3433 IF p_qte_line_rec.order_line_type_id is NULL or p_qte_line_rec.order_line_type_id = FND_API.G_MISS_NUM THEN
3434
3435 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3436 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.order_line_type_id is NULL' , 1, 'N');
3437 END IF;
3438
3439 IF p_qte_line_rec.line_category_code = 'ORDER' THEN
3440 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3441 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.line_category_code '|| p_qte_line_rec.line_category_code , 1, 'N');
3442 END IF;
3443 l_line_type_id := l_out_line_type;
3444
3445 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3446 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.l_out_line_typ '|| l_out_line_type , 1, 'N');
3447 END IF;
3448
3449 ELSE
3450 l_line_type_id := l_in_line_type;
3451 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3452 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.li_n_line_typ '|| l_in_line_type , 1, 'N');
3453 END IF;
3454
3455 END IF;
3456 ELSE
3457 l_line_type_id := p_qte_line_rec.order_line_type_id ;
3458 END IF;
3459
3460 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3461 aso_debug_pub.add(' ASO_TAX_INT l_line_type_id '|| l_line_type_id , 1, 'N');
3462 END IF;
3463
3464 IF p_qte_line_rec.line_category_code <> 'RETURN' THEN -- Standard Order Line
3465
3466 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3467 aso_debug_pub.add(' ASO_TAX_INT Line Category return ' , 1, 'N');
3468 END IF;
3469
3470 SELECT NVL(lt.cust_trx_type_id, 0)
3471 INTO l_cust_trx_type_id
3472 FROM oe_line_types_v lt
3473 WHERE lt.line_type_id = l_line_type_id;
3474
3475 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3476 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id Category return '||l_cust_trx_type_id , 1, 'N');
3477 END IF;
3478
3479 IF l_cust_trx_type_id = 0 THEN
3480
3481 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3482 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id =0before '||l_cust_trx_type_id , 1, 'N');
3483 END IF;
3484
3485 SELECT NVL(ot.cust_trx_type_id, 0)
3486 INTO l_cust_trx_type_id
3487 FROM oe_order_types_v ot
3488 WHERE ot.order_type_id =l_order_type_id;
3489
3490 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3491 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id =0after '||l_cust_trx_type_id , 1, 'N');
3492 END IF;
3493
3494 IF l_cust_trx_type_id = 0 THEN
3495
3496 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3497 aso_debug_pub.add(' ASO_TAX_INT default from oe_invoice_trans profile ' , 1, 'N');
3498 END IF;
3499
3500 SELECT NVL(FND_PROFILE.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID'), 0)
3501 INTO l_cust_trx_type_id
3502 FROM DUAL;
3503 END IF;
3504 END IF;
3505 RETURN(l_cust_trx_type_id);
3506 ELSE -- Non Referenced Return Line
3507
3508 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3509 aso_debug_pub.add(' ASO_TAX_INT Non Referenced Return Line ' , 1, 'N');
3510 END IF;
3511
3512 SELECT NVL(lt.cust_trx_type_id, 0)
3513 INTO l_inv_cust_trx_type_id
3514 FROM oe_line_types_v lt
3515 WHERE lt.line_type_id = l_line_type_id;
3516
3517
3518 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3519 aso_debug_pub.add(' ASO_TAX_INT Non Referenced Return Line l_inv_cust_trx_type_id '||l_inv_cust_trx_type_id , 1, 'N');
3520 END IF;
3521
3522 IF l_inv_cust_trx_type_id = 0 THEN
3523 SELECT NVL(DECODE(ot.order_category_code, 'RETURN',ot.cust_trx_type_id, 0), 0)
3524 INTO l_inv_cust_trx_type_id
3525 FROM oe_order_types_v ot
3526 WHERE ot.order_type_id = l_order_type_id;
3527 END IF;
3528 IF l_inv_cust_trx_type_id <> 0 THEN
3529
3530 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3531 aso_debug_pub.add(' ASO_TAX_INT Non Referenced Return Line credit memo ' , 1, 'N');
3532 END IF;
3533
3534 SELECT nvl(ctt.credit_memo_type_id, 0)
3535 INTO l_cust_trx_type_id
3536 --FROM ra_cust_trx_types_all ctt Commented Code Yogeshwar (MOAC)
3537 FROM ra_cust_trx_types ctt --New Code Yogeshwar (MOAC)
3538 WHERE ctt.cust_trx_type_id = l_inv_cust_trx_type_id
3539 AND NVL(ctt.org_id, -3114) = DECODE(ctt.cust_trx_type_id,
3540 1, -3113,
3541 2, -3113,
3542 7, -3113,
3543 8, -3113,
3544 NVL(p_qte_line_rec.org_id, -3114));
3545
3546 END IF;
3547 IF l_inv_cust_trx_type_id = 0 OR l_cust_trx_type_id = 0 THEN
3548 SELECT NVL(FND_PROFILE.VALUE('OE_CREDIT_TRANSACTION_TYPE_ID'), 0)
3549 INTO l_cust_trx_type_id
3550 FROM DUAL;
3551
3552 END IF;
3553 RETURN(l_cust_trx_type_id);
3554 END IF;
3555
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558
3559 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3560 aso_debug_pub.add(' ASO_TAX_INT GET_RA_TRX In WHEN others ' , 1, 'N');
3561 END IF;
3562
3563 return(0);
3564 END GET_ra_trx_type_ID;
3565
3566
3567 -- Commenting the following routine as part of release 12. Bug 5044986
3568 /*
3569 *
3570 *
3571 PROCEDURE print_tax_info_rec( p_debug_level in number := 5 ) IS
3572 -- l_IO_flag CHAR(1);
3573 dummy varchar2(80) := NULL;
3574 BEGIN
3575
3576 IF ( p_debug_level <= aso_debug_pub.G_Debug_Level) THEN
3577
3578
3579 --
3580 -- Dump tax_info_rec
3581 --
3582
3583 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3584
3585 aso_debug_pub.add( '************************************',1, 'Y' );
3586 aso_debug_pub.add( '** Begining of Tax Info Record **',1, 'Y' );
3587 aso_debug_pub.add( '************************************',1, 'Y' );
3588 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_cust_id = '||arp_tax.tax_info_rec.Bill_to_cust_id);
3589 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_cust_id = '||arp_tax.tax_info_rec.Ship_to_cust_id);
3590 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_id = '||arp_tax.tax_info_rec.Customer_trx_id);
3591 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_date = '||arp_tax.tax_info_rec.Trx_date);
3592 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': GL_date = '||arp_tax.tax_info_rec.gl_date);
3593 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_site_use_id = '||arp_tax.tax_info_rec.Ship_to_site_use_id);
3594 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_site_use_id = '||arp_tax.tax_info_rec.Bill_to_site_use_id);
3595 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_postal_code = '||arp_tax.tax_info_rec.Ship_to_postal_code);
3596 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_postal_code = '||arp_tax.tax_info_rec.Bill_to_postal_code);
3597 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_location_id = '||arp_tax.tax_info_rec.Ship_to_location_id);
3598 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_location_id = '||arp_tax.tax_info_rec.Bill_to_location_id);
3599 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Invoicing_rule_id = '||arp_tax.tax_info_rec.Invoicing_rule_id);
3600 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': FOB_point = '||arp_tax.tax_info_rec.FOB_point);
3601 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_currency_code = '||arp_tax.tax_info_rec.Trx_currency_code);
3602 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_exchange_rate = '||arp_tax.tax_info_rec.Trx_exchange_rate);
3603 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Minimum_accountable_unit = '||arp_tax.tax_info_rec.Minimum_accountable_unit);
3604 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Precision = '||arp_tax.tax_info_rec.Precision);
3605 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_line_id = '||arp_tax.tax_info_rec.Customer_trx_line_id);
3606 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': link_to_cust_trx_line_id = '||arp_tax.tax_info_rec.link_to_cust_trx_line_id);
3607 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Memo_line_id = '||arp_tax.tax_info_rec.Memo_line_id);
3608 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxed_quantity = '||arp_tax.tax_info_rec.Taxed_quantity);
3609 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Inventory_item_id = '||arp_tax.tax_info_rec.Inventory_item_id);
3610 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Extended_amount = '||arp_tax.tax_info_rec.Extended_amount);
3611 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_code = '||arp_tax.tax_info_rec.Tax_code);
3612 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Vat_tax_id = '||arp_tax.tax_info_rec.Vat_tax_id);
3613 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_exemption_id = '||arp_tax.tax_info_rec.Tax_exemption_id);
3614 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Item_exception_rate_id = '||arp_tax.tax_info_rec.Item_exception_rate_id);
3615 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_rate = '||arp_tax.tax_info_rec.Tax_rate);
3616 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Default_ussgl_transaction_code = '||arp_tax.tax_info_rec.Default_ussgl_transaction_code);
3617 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Default_ussgl_trx_code_context = '||arp_tax.tax_info_rec.Default_ussgl_trx_code_context);
3618 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_control = '||arp_tax.tax_info_rec.Tax_control);
3619 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Qualifier = '||arp_tax.tax_info_rec.Qualifier);
3620 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_from_code = '||arp_tax.tax_info_rec.Ship_from_code);
3621 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_code = '||arp_tax.tax_info_rec.Ship_to_code);
3622 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poo_code = '||arp_tax.tax_info_rec.Poo_code);
3623 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poa_code = '||arp_tax.tax_info_rec.Poa_code);
3624 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Vdrctrl_exempt = '||arp_tax.tax_info_rec.Vdrctrl_exempt);
3625 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_cert_no = '||arp_tax.tax_info_rec.Xmpt_cert_no);
3626 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_reason = '||arp_tax.tax_info_rec.Xmpt_reason);
3627 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_percent = '||arp_tax.tax_info_rec.Xmpt_percent);
3628 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Part_no = '||arp_tax.tax_info_rec.Part_no);
3629 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf1 = '||arp_tax.tax_info_rec.Userf1);
3630 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf2 = '||arp_tax.tax_info_rec.Userf2);
3631 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf3 = '||arp_tax.tax_info_rec.Userf3);
3632 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf4 = '||arp_tax.tax_info_rec.Userf4);
3633 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf5 = '||arp_tax.tax_info_rec.Userf5);
3634 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf6 = '||arp_tax.tax_info_rec.Userf6);
3635 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf7 = '||arp_tax.tax_info_rec.Userf7);
3636 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf8 = '||arp_tax.tax_info_rec.Userf8);
3637 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf9 = '||arp_tax.tax_info_rec.Userf9);
3638 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf10 = '||arp_tax.tax_info_rec.Userf10);
3639 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern1 = '||arp_tax.tax_info_rec.Usern1);
3640 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern2 = '||arp_tax.tax_info_rec.Usern2);
3641 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern3 = '||arp_tax.tax_info_rec.Usern3);
3642 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern4 = '||arp_tax.tax_info_rec.Usern4);
3643 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern5 = '||arp_tax.tax_info_rec.Usern5);
3644 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern6 = '||arp_tax.tax_info_rec.Usern6);
3645 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern7 = '||arp_tax.tax_info_rec.Usern7);
3646 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern8 = '||arp_tax.tax_info_rec.Usern8);
3647 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern9 = '||arp_tax.tax_info_rec.Usern9);
3648 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern10 = '||arp_tax.tax_info_rec.Usern10);
3649
3650 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': trx_number = '||arp_tax.tax_info_rec.trx_number);
3651 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_customer_trx_line_id = '||arp_tax.tax_info_rec.previous_customer_trx_line_id);
3652 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_customer_trx_id = '||arp_tax.tax_info_rec.previous_customer_trx_id);
3653 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_trx_number = '||arp_tax.tax_info_rec.previous_trx_number);
3654 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': bill_to_customer_number = '||arp_tax.tax_info_rec.bill_to_customer_number);
3655 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': ship_to_customer_number = '||arp_tax.tax_info_rec.ship_to_customer_number);
3656 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': bill_to_customer_name = '||arp_tax.tax_info_rec.bill_to_customer_name);
3657 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': ship_to_customer_name = '||arp_tax.tax_info_rec.ship_to_customer_name);
3658 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Audit_Flag = ' || arp_tax.tax_info_rec.audit_flag);
3659 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_Line_Type = ' || arp_tax.tax_info_rec.trx_line_type);
3660 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Division Code = ' || arp_tax.tax_info_rec.division_code);
3661 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Company Code = '|| arp_tax.tax_info_rec.company_code);
3662 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Calculate_tax = '||arp_tax.tax_info_rec.Calculate_tax);
3663 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Status = '||arp_tax.tax_info_rec.Status);
3664 END IF;
3665
3666 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3667 IF ( arp_tax.tax_info_rec.tax_type = 0 ) THEN
3668 dummy := 'TAX_TYPE_INACTIVE';
3669 ELSIF ( arp_tax.tax_info_rec.tax_type = 1 ) THEN
3670 dummy := 'TAX_TYPE_LOCATION';
3671 ELSIF ( arp_tax.tax_info_rec.tax_type = 2 ) THEN
3672 dummy := 'TAX_TYPE_SALES';
3673 ELSIF ( arp_tax.tax_info_rec.tax_type = 3 ) THEN
3674 dummy := 'TAX_TYPE_VAT';
3675 ELSE
3676 dummy := null;
3677 END IF;
3678 END IF;
3679
3680
3681 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3682 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_type = '||arp_tax.tax_info_rec.tax_type||' : '||dummy);
3683 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Sales_tax_id = '||arp_tax.tax_info_rec.Sales_tax_id);
3684 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Location_segment_id = '||arp_tax.tax_info_rec.Location_segment_id);
3685 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_line_number = '||arp_tax.tax_info_rec.Tax_line_number);
3686 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_amount = '||arp_tax.tax_info_rec.Tax_amount);
3687 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_vendor_return_code = '||dummy);
3688 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_precedence = '||arp_tax.tax_info_rec.Tax_precedence);
3689 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Compound_amount = '||arp_tax.tax_info_rec.Compound_amount);
3690 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_header_level_flag = '||arp_tax.tax_info_rec.Tax_header_level_flag);
3691 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_rounding_rule = '||arp_tax.tax_info_rec.Tax_rounding_rule);
3692 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_type_id = '||arp_tax.tax_info_rec.Trx_type_id);
3693 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_From_Warehouse_id = '||arp_tax.tax_info_rec.Ship_From_Warehouse_id);
3694 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Amount_includes_tax_flag = '||arp_tax.tax_info_rec.Amount_includes_tax_flag);
3695 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_charge_line_id = '||arp_tax.tax_info_rec.customer_trx_charge_line_id);
3696 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poo_id = '||arp_tax.tax_info_rec.poo_id);
3697 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poa_id = '||arp_tax.tax_info_rec.poa_id);
3698 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxable_amount = '||arp_tax.tax_info_rec.taxable_amount);
3699 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxable_basis = '||arp_tax.tax_info_rec.taxable_basis);
3700 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Override Tax Rate = '||arp_tax.tax_info_rec.override_tax_rate);
3701 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Party Flag = '||arp_tax.tax_info_rec.party_flag);
3702 aso_debug_pub.add( '*******************************');
3703 aso_debug_pub.add( '** End of Tax Info Record **');
3704 aso_debug_pub.add( '*******************************');
3705 aso_debug_pub.add( 'print_tax_info_rec()-' ,1, 'N');
3706 END IF;
3707
3708 END IF;
3709 END print_tax_info_rec;
3710 *
3711 *
3712 */
3713
3714 --Procedure added by Anoop on 14 Sep 2005 to print TAX GTT details
3715
3716 Procedure print_tax_info(
3717 rec in number,
3718 qte_header_id in number)
3719 IS
3720 cursor c1 is select
3721 INTERNAL_ORGANIZATION_ID,
3722 INTERNAL_ORG_LOCATION_ID,
3723 APPLICATION_ID,
3724 ENTITY_CODE,
3725 EVENT_CLASS_CODE,
3726 EVENT_TYPE_CODE,
3727 TRX_ID,
3728 TRX_DATE,
3729 LEDGER_ID,
3730 TRX_CURRENCY_CODE,
3731 CURRENCY_CONVERSION_DATE,
3732 CURRENCY_CONVERSION_RATE,
3733 CURRENCY_CONVERSION_TYPE,
3734 MINIMUM_ACCOUNTABLE_UNIT,
3735 PRECISION,
3736 LEGAL_ENTITY_ID,
3737 QUOTE_FLAG,
3738 TRX_NUMBER,
3739 TAX_EVENT_TYPE_CODE,
3740 TAX_REPORTING_FLAG,
3741 SHIP_TO_CUST_ACCT_SITE_USE_ID,
3742 BILL_TO_CUST_ACCT_SITE_USE_ID,
3743 DOC_LEVEL_RECALC_FLAG,
3744 SHIP_THIRD_PTY_ACCT_SITE_ID,
3745 BILL_THIRD_PTY_ACCT_SITE_ID,
3746 SHIP_THIRD_PTY_ACCT_ID,
3747 BILL_THIRD_PTY_ACCT_ID,
3748 ROUNDING_BILL_TO_PARTY_ID
3749 from
3750 ZX_TRX_HEADERS_GT
3751 where
3752 TRX_ID=qte_header_id;
3753
3754 Cursor c2 is select
3755 APPLICATION_ID,
3756 ENTITY_CODE,
3757 EVENT_CLASS_CODE,
3758 TRX_ID,
3759 TRX_LEVEL_TYPE,
3760 TRX_LINE_ID,
3761 LINE_LEVEL_ACTION,
3762 LINE_CLASS,
3763 TRX_SHIPPING_DATE,
3764 TRX_LINE_TYPE,
3765 TRX_LINE_DATE,
3766 TRX_BUSINESS_CATEGORY,
3767 LINE_AMT,
3768 TRX_LINE_QUANTITY,
3769 EXEMPT_CERTIFICATE_NUMBER,
3770 EXEMPT_REASON_CODE,
3771 PRODUCT_ID,
3772 PRODUCT_ORG_ID,
3773 UOM_CODE,
3774 PRODUCT_TYPE,
3775 FOB_POINT,
3776 SHIP_TO_PARTY_ID,
3777 SHIP_FROM_PARTY_ID,
3778 BILL_TO_PARTY_ID,
3779 SHIP_TO_PARTY_SITE_ID,
3780 BILL_TO_PARTY_SITE_ID,
3781 SHIP_TO_LOCATION_ID,
3782 BILL_TO_LOCATION_ID,
3783 SHIP_FROM_LOCATION_ID,
3784 HISTORICAL_FLAG,
3785 LINE_AMT_INCLUDES_TAX_FLAG,
3786 EXEMPTION_CONTROL_FLAG,
3787 UNIT_PRICE,
3788 TRX_LINE_GL_DATE,
3789 RECEIVABLES_TRX_TYPE_ID,
3790 BILL_TO_CUST_ACCT_SITE_USE_ID,
3791 SHIP_TO_CUST_ACCT_SITE_USE_ID,
3792 SHIP_THIRD_PTY_ACCT_SITE_ID,
3793 BILL_THIRD_PTY_ACCT_SITE_ID,
3794 SHIP_THIRD_PTY_ACCT_ID,
3795 BILL_THIRD_PTY_ACCT_ID,
3796 CTRL_HDR_TX_APPL_FLAG,
3797 TRX_LINE_NUMBER,
3798 BILL_FROM_LOCATION_ID --bug7408162
3799 from
3800 Zx_transaction_lines_gt
3801 where
3802 TRX_ID=qte_header_id;
3803
3804 Cursor c3 is select
3805 TRX_ID,
3806 TRX_LINE_ID,
3807 TAX_RATE_CODE,
3808 TAX_RATE,
3809 TAX_DETERMINE_DATE,
3810 TAX_AMT,
3811 EXEMPT_CERTIFICATE_NUMBER,
3812 EXEMPT_REASON_CODE,
3813 TAX_AMT_INCLUDED_FLAG,
3814 OBJECT_VERSION_NUMBER,
3815 TAX_RATE_ID
3816 from
3817 Zx_detail_tax_lines_gt
3818 where
3819 TRX_ID=qte_header_id;
3820
3821 BEGIN
3822 IF (aso_debug_pub.g_debug_flag = 'Y' and rec=1) THEN
3823 for i in c1 LOOP
3824 aso_debug_pub.add( '******************************************',1, 'Y' );
3825 aso_debug_pub.add( '***** After insertion into ZX_TRX_HEADERS_GTT *****',1, 'Y' );
3826 aso_debug_pub.add( '******************************************',1, 'Y' );
3827 aso_debug_pub.add(rpad('INTERNAL_ORGANIZATION_ID',50,'------')||'------>'||i.INTERNAL_ORGANIZATION_ID , 1, 'Y');
3828 aso_debug_pub.add(rpad('INTERNAL_ORG_LOCATION_ID',50,'------')||'------>'||i.INTERNAL_ORG_LOCATION_ID , 1, 'Y');
3829 aso_debug_pub.add(rpad('APPLICATION_ID',50,'------')||'------>'||i.APPLICATION_ID , 1, 'Y');
3830 aso_debug_pub.add(rpad('ENTITY_CODE',50,'------')||'------>'||i.ENTITY_CODE , 1, 'Y');
3831 aso_debug_pub.add(rpad('EVENT_CLASS_CODE',50,'------')||'------>'||i.EVENT_CLASS_CODE , 1, 'Y');
3832 aso_debug_pub.add(rpad('EVENT_TYPE_CODE',50,'------')||'------>'||i.EVENT_TYPE_CODE , 1, 'Y');
3833 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
3834 aso_debug_pub.add(rpad('TRX_DATE',50,'------')||'------>'||i.TRX_DATE , 1, 'Y');
3835 aso_debug_pub.add(rpad('LEDGER_ID',50,'------')||'------>'||i.LEDGER_ID , 1, 'Y');
3836 aso_debug_pub.add(rpad('TRX_CURRENCY_CODE',50,'------')||'------>'||i.TRX_CURRENCY_CODE , 1, 'Y');
3837 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_DATE',50,'------')||'------>'||i.CURRENCY_CONVERSION_DATE , 1, 'Y');
3838 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_RATE',50,'------')||'------>'||i.CURRENCY_CONVERSION_RATE , 1, 'Y');
3839 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_TYPE',50,'------')||'------>'||i.CURRENCY_CONVERSION_TYPE , 1, 'Y');
3840 aso_debug_pub.add(rpad('MINIMUM_ACCOUNTABLE_UNIT',50,'------')||'------>'||i.MINIMUM_ACCOUNTABLE_UNIT , 1, 'Y');
3841 aso_debug_pub.add(rpad('PRECISION',50,'------')||'------>'||i.PRECISION , 1, 'Y');
3842 aso_debug_pub.add(rpad('LEGAL_ENTITY_ID',50,'------')||'------>'||i.LEGAL_ENTITY_ID , 1, 'Y');
3843 aso_debug_pub.add(rpad('QUOTE_FLAG',50,'------')||'------>'||i.QUOTE_FLAG , 1, 'Y');
3844 aso_debug_pub.add(rpad('TRX_NUMBER',50,'------')||'------>'||i.TRX_NUMBER , 1, 'Y');
3845 aso_debug_pub.add(rpad('TAX_EVENT_TYPE_CODE',50,'------')||'------>'||i.TAX_EVENT_TYPE_CODE , 1, 'Y');
3846 aso_debug_pub.add(rpad('TAX_REPORTING_FLAG',50,'------')||'------>'||i.TAX_REPORTING_FLAG , 1, 'Y');
3847 aso_debug_pub.add(rpad('SHIP_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.SHIP_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3848 aso_debug_pub.add(rpad('BILL_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.BILL_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3849 aso_debug_pub.add(rpad('DOC_LEVEL_RECALC_FLAG',50,'------')||'------>'||i.DOC_LEVEL_RECALC_FLAG , 1, 'Y');
3850 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3851 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3852 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_ID , 1, 'Y');
3853 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_ID , 1, 'Y');
3854 aso_debug_pub.add(rpad('ROUNDING_BILL_TO_PARTY_ID',50,'------')||'------>'||i.ROUNDING_BILL_TO_PARTY_ID , 1, 'Y');
3855 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
3856 --aso_debug_pub.add('*****INSERTED INTO ZX_TRX_HEADERS_GTT*****',1, 'Y' );
3857 --aso_debug_pub.add('******************************************',1, 'Y' );
3858 end loop;
3859 ELSIF (aso_debug_pub.g_debug_flag = 'Y' and rec=2) THEN
3860 for i in c2 loop
3861 aso_debug_pub.add( '******************************************',1, 'Y' );
3862 aso_debug_pub.add( '***** After insertion into Zx_transaction_lines_gt *****',1, 'Y' );
3863 aso_debug_pub.add( '******************************************',1, 'Y' );
3864 aso_debug_pub.add(rpad('APPLICATION_ID',50,'------')||'------>'||i.APPLICATION_ID , 1, 'Y');
3865 aso_debug_pub.add(rpad('ENTITY_CODE',50,'------')||'------>'||i.ENTITY_CODE , 1, 'Y');
3866 aso_debug_pub.add(rpad('EVENT_CLASS_CODE',50,'------')||'------>'||i.EVENT_CLASS_CODE , 1, 'Y');
3867 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
3868 aso_debug_pub.add(rpad('TRX_LEVEL_TYPE',50,'------')||'------>'||i.TRX_LEVEL_TYPE , 1, 'Y');
3869 aso_debug_pub.add(rpad('TRX_LINE_ID',50,'------')||'------>'||i.TRX_LINE_ID , 1, 'Y');
3870 aso_debug_pub.add(rpad('LINE_LEVEL_ACTION',50,'------')||'------>'||i.LINE_LEVEL_ACTION , 1, 'Y');
3871 aso_debug_pub.add(rpad('LINE_CLASS',50,'------')||'------>'||i.LINE_CLASS , 1, 'Y');
3872 aso_debug_pub.add(rpad('TRX_SHIPPING_DATE',50,'------')||'------>'||i.TRX_SHIPPING_DATE , 1, 'Y');
3873 aso_debug_pub.add(rpad('TRX_LINE_TYPE',50,'------')||'------>'||i.TRX_LINE_TYPE , 1, 'Y');
3874 aso_debug_pub.add(rpad('TRX_LINE_DATE',50,'------')||'------>'||i.TRX_LINE_DATE , 1, 'Y');
3875 aso_debug_pub.add(rpad('TRX_BUSINESS_CATEGORY',50,'------')||'------>'||i.TRX_BUSINESS_CATEGORY , 1, 'Y');
3876 aso_debug_pub.add(rpad('LINE_AMT',50,'------')||'------>'||i.LINE_AMT, 1, 'Y');
3877 aso_debug_pub.add(rpad('TRX_LINE_QUANTITY',50,'------')||'------>'||i.TRX_LINE_QUANTITY , 1, 'Y');
3878 aso_debug_pub.add(rpad('EXEMPT_CERTIFICATE_NUMBER',50,'------')||'------>'||i.EXEMPT_CERTIFICATE_NUMBER , 1, 'Y');
3879 aso_debug_pub.add(rpad('EXEMPT_REASON_CODE',50,'------')||'------>'||i.EXEMPT_REASON_CODE , 1, 'Y');
3880 aso_debug_pub.add(rpad('PRODUCT_ID',50,'------')||'------>'||i.PRODUCT_ID , 1, 'Y');
3881 aso_debug_pub.add(rpad('PRODUCT_ORG_ID',50,'------')||'------>'||i.PRODUCT_ORG_ID , 1, 'Y');
3882 aso_debug_pub.add(rpad('UOM_CODE',50,'------')||'------>'||i.UOM_CODE , 1, 'Y');
3883 aso_debug_pub.add(rpad('PRODUCT_TYPE',50,'------')||'------>'||i.PRODUCT_TYPE , 1, 'Y');
3884 aso_debug_pub.add(rpad('FOB_POINT',50,'------')||'------>'||i.FOB_POINT , 1, 'Y');
3885 aso_debug_pub.add(rpad('SHIP_TO_PARTY_ID',50,'------')||'------>'||i.SHIP_TO_PARTY_ID , 1, 'Y');
3886 aso_debug_pub.add(rpad('SHIP_FROM_PARTY_ID',50,'------')||'------>'||i.SHIP_FROM_PARTY_ID , 1, 'Y');
3887 aso_debug_pub.add(rpad('BILL_TO_PARTY_ID',50,'------')||'------>'||i.BILL_TO_PARTY_ID , 1, 'Y');
3888 aso_debug_pub.add(rpad('SHIP_TO_PARTY_SITE_ID',50,'------')||'------>'||i.SHIP_TO_PARTY_SITE_ID , 1, 'Y');
3889 aso_debug_pub.add(rpad('BILL_TO_PARTY_SITE_ID',50,'------')||'------>'||i.BILL_TO_PARTY_SITE_ID , 1, 'Y');
3890 aso_debug_pub.add(rpad('SHIP_TO_LOCATION_ID',50,'------')||'------>'||i.SHIP_TO_LOCATION_ID , 1, 'Y');
3891 aso_debug_pub.add(rpad('BILL_TO_LOCATION_ID',50,'------')||'------>'||i.BILL_TO_LOCATION_ID , 1, 'Y');
3892 aso_debug_pub.add(rpad('SHIP_FROM_LOCATION_ID',50,'------')||'------>'||i.SHIP_FROM_LOCATION_ID , 1, 'Y');
3893 aso_debug_pub.add(rpad('HISTORICAL_FLAG',50,'------')||'------>'||i.HISTORICAL_FLAG , 1, 'Y');
3894 aso_debug_pub.add(rpad('LINE_AMT_INCLUDES_TAX_FLAG',50,'------')||'------>'||i.LINE_AMT_INCLUDES_TAX_FLAG , 1, 'Y');
3895 aso_debug_pub.add(rpad('EXEMPTION_CONTROL_FLAG',50,'------')||'------>'||i.EXEMPTION_CONTROL_FLAG , 1, 'Y');
3896 aso_debug_pub.add(rpad('UNIT_PRICE',50,'------')||'------>'||i.UNIT_PRICE , 1, 'Y');
3897 aso_debug_pub.add(rpad('TRX_LINE_GL_DATE',50,'------')||'------>'||i.TRX_LINE_GL_DATE , 1, 'Y');
3898 aso_debug_pub.add(rpad('RECEIVABLES_TRX_TYPE_ID',50,'------')||'------>'||i.RECEIVABLES_TRX_TYPE_ID , 1, 'Y');
3899 aso_debug_pub.add(rpad('BILL_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.BILL_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3900 aso_debug_pub.add(rpad('SHIP_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.SHIP_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3901 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3902 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3903 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_ID , 1, 'Y');
3904 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_ID , 1, 'Y');
3905 aso_debug_pub.add(rpad('CTRL_HDR_TX_APPL_FLAG ',50,'------')||'------>'||i.CTRL_HDR_TX_APPL_FLAG , 1, 'Y');
3906 aso_debug_pub.add(rpad('TRX_LINE_NUMBER ',50,'------')||'------>'||i.TRX_LINE_NUMBER , 1, 'Y');
3907 aso_debug_pub.add(rpad('BILL_FROM_LOCATION_ID',50,'------')||'------>'||i.BILL_FROM_LOCATION_ID , 1, 'Y'); -- bug 7408162
3908 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
3909 end loop;
3910 ELSIF (aso_debug_pub.g_debug_flag = 'Y' and rec=3) THEN
3911 for i in c3 loop
3912 aso_debug_pub.add( '******************************************',1, 'Y' );
3913 aso_debug_pub.add( '***** After insertion into Zx_detail_tax_lines_gt *****',1, 'Y' );
3914 aso_debug_pub.add( '******************************************',1, 'Y' );
3915 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
3916 aso_debug_pub.add(rpad('TRX_LINE_ID',50,'------')||'------>'||i.TRX_LINE_ID , 1, 'Y');
3917 aso_debug_pub.add(rpad('TAX_RATE_CODE',50,'------')||'------>'||i.TAX_RATE_CODE , 1, 'Y');
3918 aso_debug_pub.add(rpad('TAX_RATE',50,'------')||'------>'||i.TAX_RATE , 1, 'Y');
3919 aso_debug_pub.add(rpad('TAX_DETERMINE_DATE',50,'------')||'------>'||i.TAX_DETERMINE_DATE , 1, 'Y');
3920 aso_debug_pub.add(rpad('TAX_AMT',50,'------')||'------>'||i.TAX_AMT , 1, 'Y');
3921 aso_debug_pub.add(rpad('EXEMPT_CERTIFICATE_NUMBER',50,'------')||'------>'||i.EXEMPT_CERTIFICATE_NUMBER , 1, 'Y');
3922 aso_debug_pub.add(rpad('EXEMPT_REASON_CODE',50,'------')||'------>'||i.EXEMPT_REASON_CODE , 1, 'Y');
3923 aso_debug_pub.add(rpad('TAX_AMT_INCLUDED_FLAG',50,'------')||'------>'||i.TAX_AMT_INCLUDED_FLAG , 1, 'Y');
3924 aso_debug_pub.add(rpad('OBJECT_VERSION_NUMBER',50,'------')||'------>'||i.OBJECT_VERSION_NUMBER , 1, 'Y');
3925 aso_debug_pub.add(rpad('TAX_RATE_ID',50,'------')||'------>'||i.TAX_RATE_ID , 1, 'Y');
3926 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
3927 end loop;
3928 END IF;
3929
3930 END print_tax_info;
3931
3932 End ASO_TAX_INT;