[Home] [Help]
PACKAGE BODY: APPS.ASO_TAX_INT
Source
1 PACKAGE BODY ASO_TAX_INT as
2 /* $Header: asoitaxb.pls 120.29.12020000.2 2012/09/25 05:35:03 akushwah 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_Shipment_Rec.ship_from_org_id;
2279 -- WHERE ORGANIZATION_ID = l_qte_line_rec.organization_id; commented As per Bug 12830088
2280
2281 Cursor c_int_org_location is
2282 SELECT LOCATION_ID
2283 FROM HR_ORGANIZATION_UNITS
2284 WHERE ORGANIZATION_ID=l_qte_header_rec.ORG_ID;
2285
2286 Cursor c_product_type is
2287 Select CLASSIFICATION_CODE
2288 FROM ZX_PRODUCT_TYPES_DEF_V
2289 WHERE INVENTORY_ITEM_ID = l_qte_line_rec.INVENTORY_ITEM_ID
2290 AND ORG_ID= l_qte_line_rec.organization_id;
2291
2292 Cursor c_shiplocation(l_party_site_id NUMBER) is
2293 select LOCATION_ID
2294 FROM hz_party_sites
2295 WHERE party_site_id=l_party_site_id;
2296
2297 Cursor c_tax is
2298 select TAX_EXEMPT_FLAG,TAX_EXEMPT_NUMBER,TAX_EXEMPT_REASON_CODE
2299 from aso_tax_details
2300 WHERE QUOTE_LINE_ID is null
2301 AND quote_header_id=p_qte_header_id;
2302
2303 Cursor c_cust_trx_type_id(l_trx_type_id NUMBER) is
2304 select cust_trx_type_id
2305 from ra_cust_trx_types
2306 where cust_trx_type_id = l_trx_type_id
2307 and (tax_calculation_flag = 'Y');
2308
2309 Cursor c_set_of_books_id is
2310 select set_of_books_id
2311 from ar_system_parameters;
2312
2313 Cursor c_INVOICE_SOURCE_ID is
2314 select INVOICE_SOURCE_ID
2315 FROM OE_TRANSACTION_TYPES
2316 WHERE TRANSACTION_TYPE_ID=l_qte_header_rec.ORDER_TYPE_ID;
2317
2318 -- new cursors added by suyog bug 5061912
2319
2320 Cursor c_get_resource_id (l_qte_hdr_id NUMBER) is
2321 SELECT resource_id
2322 FROM aso_quote_headers_all trx
2323 WHERE trx.quote_header_id = l_qte_hdr_id;
2324
2325 Cursor c_get_org_id ( l_source_id NUMBER) is
2326 SELECT per.organization_id
2327 FROM jtf_rs_srp_vl sales, per_all_assignments_f per
2328 WHERE sales.resource_id = l_source_id
2329 AND per.person_id = sales.person_id
2330 AND nvl(per.primary_flag,'Y') = 'Y'
2331 AND sysdate BETWEEN nvl(per.effective_start_date,sysdate) AND nvl(per.effective_end_date,sysdate);
2332
2333 Cursor c_get_location_id ( l_party_id NUMBER) is
2334 SELECT hr.location_id
2335 FROM hr_organization_units hr
2336 WHERE hr.organization_id = l_party_id;
2337
2338
2339 -- new variables
2340 l_resource_id number;
2341 l_poo_party_id number;
2342 l_poo_location_id number;
2343 l_bill_from_location_id NUMBER; /*** Added for Bug 8474803 and 7408162 ***/
2344
2345 /* Added for Bug 9558210 */
2346
2347 CURSOR C_SHIP(p_party_site_id NUMBER) IS
2348 SELECT a.party_id
2349 from
2350 HZ_PARTIES a, HZ_PARTY_SITES b
2351 WHERE a.status = 'A'
2352 and b.status = 'A'
2353 and b.party_site_id = p_party_site_id
2354 and b.party_id = a.party_id;
2355
2356 Cursor C_ACC(p_quote_header_id Number) Is
2357 SELECT CUST_PARTY_ID
2358 FROM ASO_QUOTE_HEADERS_ALL
2359 WHERE QUOTE_HEADER_ID = p_quote_header_id;
2360
2361 /* End for Bug 9558210 */
2362
2363 Begin
2364
2365 Savepoint CALCULATE_TAX_WITH_GTT;
2366 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2367 x_return_status := FND_API.G_RET_STS_SUCCESS;
2368
2369 IF aso_debug_pub.g_debug_flag = 'Y'
2370 THEN
2371 aso_debug_pub.add('ASO_TAX_INT: Begin CALCULATE_TAX_WITH_GTT', 1, 'Y');
2372 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: p_qte_header_id: '|| p_qte_header_id, 1, 'Y');
2373 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: p_qte_line_id: '|| p_qte_line_id, 1, 'Y');
2374 END IF;
2375 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_id);
2376 IF aso_debug_pub.g_debug_flag = 'Y'
2377 THEN
2378 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Header_Row ', 1, 'Y');
2379 END IF;
2380
2381 l_currency_code := l_qte_header_rec.currency_code;
2382 IF aso_debug_pub.g_debug_flag = 'Y'
2383 THEN
2384 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_currency_code: '|| l_currency_code, 1, 'Y');
2385 END IF;
2386
2387 l_Shipment_header_tbl:=aso_utility_pvt.query_shipment_rows( p_qte_header_id,null);
2388 IF aso_debug_pub.g_debug_flag = 'Y'
2389 THEN
2390 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.query_shipment_rows ', 1, 'Y');
2391 END IF;
2392
2393 --Condition added on 20/09/05 by anrajan
2394 IF l_Shipment_header_tbl.count > 0
2395 THEN
2396 l_Shipment_header_rec:=l_Shipment_header_tbl(1);
2397 END IF;
2398
2399 OPEN c_currency;
2400 FETCH c_currency into l_minimum_accountable_unit,l_precision;
2401 CLOSE c_currency;
2402
2403 IF aso_debug_pub.g_debug_flag = 'Y'
2404 THEN
2405 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_currency cursor ', 1, 'Y');
2406 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_minimum_accountable_unit: '||l_minimum_accountable_unit, 1, 'Y');
2407 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_precision: '||l_precision,1,'Y');
2408 END IF;
2409
2410 open c_ship_to_cust_account_id;
2411 fetch c_ship_to_cust_account_id into l_ship_cust_account_id_header;
2412 close c_ship_to_cust_account_id;
2413
2414 IF aso_debug_pub.g_debug_flag = 'Y'
2415 THEN
2416 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_ship_to_cust_account_id ', 1, 'Y');
2417 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');
2418 END IF;
2419
2420 OPEN c_int_org_location;
2421 Fetch c_int_org_location into l_int_org_location;
2422 close c_int_org_location;
2423
2424 IF aso_debug_pub.g_debug_flag = 'Y'
2425 THEN
2426 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to c_int_org_location ', 1, 'Y');
2427 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_int_org_location : '||l_int_org_location, 1, 'Y');
2428 END IF;
2429
2430 IF p_qte_line_id is null or p_qte_line_id = FND_API.G_MISS_NUM
2431 THEN
2432 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows(p_qte_header_id);
2433 IF
2434 aso_debug_pub.g_debug_flag = 'Y'
2435 THEN
2436 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Qte_Line_Rows', 1, 'Y');
2437 END IF;
2438 ELSE
2439 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_id);
2440 IF
2441 aso_debug_pub.g_debug_flag = 'Y'
2442 THEN
2443 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to ASO_UTILITY_PVT.Query_Qte_Line_Row', 1, 'Y');
2444 END IF;
2445 l_qte_line_tbl(1) := l_qte_line_rec;
2446 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: Quote Line Id : '||p_qte_line_id, 1, 'Y');
2447 END IF;
2448
2449 IF l_qte_line_tbl.count>0
2450 THEN
2451 l_qte_line_rec:= l_qte_line_tbl(1);
2452 IF
2453 aso_debug_pub.g_debug_flag = 'Y'
2454 THEN
2455 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT:l_qte_line_tbl.count>0 ', 1, 'Y');
2456 END IF;
2457 ELSE
2458 IF
2459 aso_debug_pub.g_debug_flag = 'Y'
2460 THEN
2461 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT:l_qte_line_tbl.count=0 ', 1, 'Y');
2462 END IF;
2463 END IF;
2464
2465 /*** Added this SQL for Bug 8474803 and 7408162 ***/
2466 begin
2467 select location_id
2468 into l_bill_from_location_id
2469 from HR_ALL_ORGANIZATION_UNITS
2470 where organization_id = l_qte_header_rec.ORG_ID; -- l_qte_line_tbl(1).organization_id;
2471 Exception
2472 when others then
2473 l_bill_from_location_id := NULL;
2474 End;
2475
2476 IF aso_debug_pub.g_debug_flag = 'Y'
2477 THEN
2478 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');
2479 END IF;
2480
2481 Open c_set_of_books_id;
2482 FETCH c_set_of_books_id into l_set_of_books_id;
2483 close c_set_of_books_id;
2484
2485 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2486 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After selecting from AR_SYSTEM_PARAMETERS table', 1, 'Y');
2487 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: l_set_of_books_id : '||l_set_of_books_id, 1, 'Y');
2488 END IF;
2489
2490
2491 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2492 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: Before call to aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
2493 END IF;
2494 l_site_use_id_ship_header := aso_shipment_pvt.get_ship_to_site_id
2495 (l_qte_header_rec.quote_header_id,null,l_Shipment_header_rec.shipment_id); -- bug 8228519 passing shipment id for quote header
2496 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2497 aso_debug_pub.add('ASO_TAX_INT: CALCULATE_TAX_WITH_GTT: After call to aso_shipment_pvt.get_ship_to_site_id', 1, 'Y');
2498 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');
2499 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');
2500 END IF;
2501 l_site_use_id_bill_header := aso_shipment_pvt.get_cust_to_party_site_id
2502 (l_qte_header_rec.quote_header_id, null);
2503 IF aso_debug_pub.g_debug_flag ='Y' THEN
2504 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');
2505 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');
2506 END IF;
2507 IF l_site_use_id_ship_header is not null THEN
2508 l_site_use_id:=l_site_use_id_ship_header;
2509 IF aso_debug_pub.g_debug_flag ='Y' THEN
2510 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO c_get_acct_site ', 1, 'Y');
2511 END IF;
2512
2513 Open c_get_acct_site(l_site_use_id);
2514 Fetch c_get_acct_site into l_acct_site_id_ship;
2515 Close c_get_acct_site;
2516
2517 IF aso_debug_pub.g_debug_flag ='Y' THEN
2518 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO c_get_acct_site ', 1, 'Y');
2519 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_acct_site_id_ship : '||l_acct_site_id_ship, 1, 'Y');
2520 END IF;
2521 ELSE
2522 IF aso_debug_pub.g_debug_flag ='Y' THEN
2523 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_site_use_id is null ', 1, 'Y');
2524 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: So l_acct_site_id_ship is also null ', 1, 'Y');
2525 END IF;
2526 l_acct_site_id_ship:=NULL;
2527 END IF;
2528
2529 IF l_site_use_id_bill_header is not null THEN
2530 l_site_use_id:=l_site_use_id_bill_header;
2531 IF aso_debug_pub.g_debug_flag ='Y' THEN
2532 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO c_get_acct_site ', 1, 'Y');
2533 END IF;
2534
2535 Open c_get_acct_site(l_site_use_id);
2536 Fetch c_get_acct_site into l_acct_site_id_bill;
2537 Close c_get_acct_site;
2538
2539 IF aso_debug_pub.g_debug_flag ='Y' THEN
2540 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO c_get_acct_site ', 1, 'Y');
2541 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_acct_site_id_bill : '||l_acct_site_id_bill, 1, 'Y');
2542 END IF;
2543 ELSE
2544 IF aso_debug_pub.g_debug_flag ='Y' THEN
2545 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_site_use_id is null ', 1, 'Y');
2546 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: So l_acct_site_id_ship is also null ', 1, 'Y');
2547 END IF;
2548 l_acct_site_id_bill:=NULL;
2549 END IF;
2550
2551 OPEN c_invoice_source_id;
2552 fetch c_invoice_source_id into l_batch_source;
2553 CLOSE c_invoice_source_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 CALL TO the c_invoice_source_id ', 1, 'Y');
2557 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');
2558 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the l_trx_type_id ', 1, 'Y');
2559 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');
2560 END IF;
2561
2562 l_trx_type_id := ASO_TAX_INT.get_ra_trx_type_id(l_qte_header_rec.order_type_id,l_qte_line_rec);
2563
2564 IF aso_debug_pub.g_debug_flag ='Y' THEN
2565 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');
2566 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_trx_type_id : '||l_trx_type_id , 1, 'Y');
2567 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');
2568 END IF;
2569
2570 Open c_cust_trx_type_id(l_trx_type_id);
2571 fetch c_cust_trx_type_id into l_ra_cust_trx_type_id;
2572 close c_cust_trx_type_id;
2573
2574 IF aso_debug_pub.g_debug_flag ='Y' THEN
2575 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: AFTER THE CALL TO c_cust_trx_type_id ' , 1, 'Y');
2576 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');
2577 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: BEFORE CALL TO the Legal Entity API ', 1, 'Y');
2578 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');
2579 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');
2580 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID :'||l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID, 1, 'Y');
2581 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID :'||l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID , 1, 'Y');
2582 END IF;
2583
2584 /* Added for bug 9558210 */
2585
2586 If (l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID Is Null OR
2587 l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID = FND_API.G_MISS_NUM) THEN
2588
2589 IF aso_debug_pub.g_debug_flag ='Y' THEN
2590 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID Is Null ', 1, 'Y');
2591 End if;
2592
2593 If (l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID Is Null OR
2594 l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID = FND_API.G_MISS_NUM) THEN
2595
2596 IF aso_debug_pub.g_debug_flag ='Y' THEN
2597 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID Is Null ', 1, 'Y');
2598 End if;
2599
2600 Open C_ACC(p_qte_header_id);
2601 Fetch C_ACC Into l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID;
2602 Close C_ACC;
2603 Else
2604 IF aso_debug_pub.g_debug_flag ='Y' THEN
2605 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID Is Not Null ', 1, 'Y');
2606 End if;
2607
2608 Open C_SHIP(l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID);
2609 Fetch C_SHIP Into l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID;
2610 Close C_SHIP;
2611 End If;
2612
2613 IF aso_debug_pub.g_debug_flag ='Y' THEN
2614 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: 3 l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID: '||l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID, 1, 'Y');
2615 End if;
2616
2617 End If;
2618
2619 /* End for bug 9558210 */
2620
2621 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info(
2622 x_return_status=>x_return_status,
2623 x_msg_data=>X_Msg_Data,
2624 P_customer_type=>'SOLD_TO',
2625 P_customer_id =>l_Shipment_header_rec.SHIP_TO_CUST_PARTY_ID,
2626 P_transaction_type_id =>l_ra_cust_trx_type_id,
2627 P_batch_source_id => l_batch_source,
2628 P_operating_unit_id => l_qte_header_rec.ORG_ID,
2629 x_otoc_Le_info =>x_legal_entity);
2630
2631 l_legal_entity_id:= x_legal_entity.legal_entity_id;
2632
2633
2634 IF aso_debug_pub.g_debug_flag ='Y' THEN
2635 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO the Legal Entity API ', 1, 'Y');
2636 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: LEGAL ENTITY : '||l_legal_entity_id, 1, 'Y');
2637 --Added by anrajan on 05/10/2005
2638 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER LEGAL ENTITY API : RETURN_STATUS : '||x_return_status , 1, 'Y');
2639 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER LEGAL ENTITY API : MESSAGE DATA : '||X_Msg_Data , 1, 'Y');
2640 END IF;
2641
2642
2643 /* Added for Bug 9558210*/
2644
2645 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2646 IF aso_debug_pub.g_debug_flag ='Y' THEN
2647 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: RAISING FND_API.G_EXC_ERROR ', 1, 'Y');
2648 End if;
2649 raise FND_API.G_EXC_ERROR;
2650 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2651 IF aso_debug_pub.g_debug_flag ='Y' THEN
2652 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: RAISING FND_API.G_EXC_UNEXPECTED_ERROR ', 1, 'Y');
2653 End if;
2654 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2655 END IF;
2656
2657 If l_legal_entity_id = -1 Then
2658 IF aso_debug_pub.g_debug_flag ='Y' THEN
2659 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: RAISING FND_API.G_EXC_ERROR for l_legal_entity_id = -1', 1, 'Y');
2660 End if;
2661 raise FND_API.G_EXC_ERROR;
2662 End If;
2663
2664 /* End for Bug 9558210*/
2665
2666 --Insertion into the Header Temporary Table.
2667
2668 IF aso_debug_pub.g_debug_flag ='Y' THEN
2669 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE INSERTION INTO ZX_TRX_HEADERS_GT temporary table ', 1, 'Y');
2670 END IF;
2671
2672 DELETE FROM ZX_TRX_HEADERS_GT where
2673 APPLICATION_ID=697 and
2674 ENTITY_CODE= 'ASO_QUOTE_HEADERS_ALL' and
2675 EVENT_CLASS_CODE= 'SALES_TRANSACTION_TAX_QUOTE' and
2676 TRX_ID= p_qte_header_id;
2677
2678 insert into ZX_TRX_HEADERS_GT
2679 (
2680 INTERNAL_ORGANIZATION_ID,
2681 INTERNAL_ORG_LOCATION_ID,
2682 APPLICATION_ID,
2683 ENTITY_CODE,
2684 EVENT_CLASS_CODE,
2685 EVENT_TYPE_CODE,
2686 TRX_ID,
2687 TRX_DATE,
2688 LEDGER_ID,
2689 TRX_CURRENCY_CODE,
2690 CURRENCY_CONVERSION_DATE,
2691 CURRENCY_CONVERSION_RATE,
2692 CURRENCY_CONVERSION_TYPE,
2693 MINIMUM_ACCOUNTABLE_UNIT,
2694 PRECISION,
2695 LEGAL_ENTITY_ID,
2696 QUOTE_FLAG,
2697 TRX_NUMBER,
2698 FIRST_PTY_ORG_ID,
2699 TAX_EVENT_TYPE_CODE,
2700 VALIDATION_CHECK_FLAG,
2701 TAX_REPORTING_FLAG,
2702 SHIP_TO_CUST_ACCT_SITE_USE_ID,
2703 BILL_TO_CUST_ACCT_SITE_USE_ID,
2704 DOC_LEVEL_RECALC_FLAG,
2705 SHIP_THIRD_PTY_ACCT_SITE_ID,
2706 BILL_THIRD_PTY_ACCT_SITE_ID,
2707 SHIP_THIRD_PTY_ACCT_ID,
2708 BILL_THIRD_PTY_ACCT_ID,
2709 ROUNDING_BILL_TO_PARTY_ID
2710 )
2711 values
2712 (
2713 l_qte_header_rec.ORG_ID,
2714 l_int_org_location,
2715 697,
2716 'ASO_QUOTE_HEADERS_ALL',
2717 'SALES_TRANSACTION_TAX_QUOTE',
2718 'CREATE',
2719 p_qte_header_id,
2720 sysdate,
2721 l_set_of_books_id,
2722 l_qte_header_rec.CURRENCY_CODE,
2723 l_qte_header_rec.EXCHANGE_RATE_DATE,
2724 l_qte_header_rec.EXCHANGE_RATE,
2725 l_qte_header_rec.EXCHANGE_TYPE_CODE,
2726 l_minimum_accountable_unit,
2727 l_precision,
2728 l_legal_entity_id,
2729 'Y',
2730 l_qte_header_rec.QUOTE_NUMBER,
2731 null,
2732 'CREATE',
2733 null,
2734 'N',
2735 l_site_use_id_ship_header,
2736 l_site_use_id_bill_header,
2737 'N',
2738 l_acct_site_id_ship,
2739 l_acct_site_id_bill,
2740 l_ship_cust_account_id_header,
2741 l_qte_header_rec.INVOICE_TO_CUST_ACCOUNT_ID,
2742 l_qte_header_rec.INVOICE_TO_CUST_PARTY_ID
2743 );
2744
2745 print_tax_info(1,p_qte_header_id);
2746 IF aso_debug_pub.g_debug_flag ='Y' THEN
2747 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: BEFORE THE QUOTE LINES LOOP ', 1, 'Y');
2748 END IF;
2749
2750 DELETE FROM Zx_transaction_lines_gt
2751 WHERE APPLICATION_ID= 697
2752 AND ENTITY_CODE= 'ASO_QUOTE_HEADERS_ALL'
2753 AND EVENT_CLASS_CODE = 'SALES_TRANSACTION_TAX_QUOTE'
2754 AND TRX_ID = p_qte_header_id
2755 AND TRX_LEVEL_TYPE= 'LINE';
2756
2757 FOR i in 1..l_qte_line_tbl.count
2758 LOOP
2759 l_qte_line_rec:=l_qte_line_tbl(i);
2760 IF aso_debug_pub.g_debug_flag ='Y' THEN
2761 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');
2762 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');
2763 END IF;
2764 l_Shipment_tbl:=aso_utility_pvt.query_shipment_rows( p_qte_header_id,l_qte_line_rec.quote_line_id);
2765
2766 --Condition added by anrajan on 06/10/2005 for Bug Number :4656728
2767 IF l_Shipment_tbl.count>0
2768 THEN
2769 l_Shipment_Rec:=l_shipment_tbl(1);
2770 END IF;
2771
2772 IF aso_debug_pub.g_debug_flag ='Y' THEN
2773 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');
2774 END IF;
2775
2776 l_site_use_id_bill_lines:=aso_shipment_pvt.get_cust_to_party_site_id
2777 (p_qte_header_id,
2778 l_qte_line_rec.quote_line_id);
2779 IF aso_debug_pub.g_debug_flag ='Y' THEN
2780 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');
2781 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');
2782 END IF;
2783
2784 l_site_use_id_ship_lines:= aso_shipment_pvt.get_ship_to_site_id
2785 (p_qte_header_id,
2786 l_qte_line_rec.quote_line_id,
2787 l_Shipment_Rec.SHIPMENT_ID);
2788 IF aso_debug_pub.g_debug_flag ='Y' THEN
2789 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');
2790 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');
2791 END IF;
2792
2793 Open c_getlocinfo(l_site_use_id_bill_lines);
2794 Fetch c_getlocinfo into l_acct_site_id_bill_lines,l_bill_cust_acct_id_lines;
2795 Close c_getlocinfo;
2796
2797 Open c_getlocinfo(l_site_use_id_ship_lines);
2798 Fetch c_getlocinfo into l_acct_site_id_ship_lines,l_ship_cust_acct_id_lines;
2799 Close c_getlocinfo;
2800
2801 IF aso_debug_pub.g_debug_flag ='Y' THEN
2802 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO c_getlocinfo', 1, 'Y');
2803 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');
2804 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');
2805 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');
2806 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');
2807 END IF;
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 :BEFORE THE CALL TO c_shiplocation', 1, 'Y');
2811 END IF;
2812
2813 IF
2814 l_Shipment_Rec.ship_to_party_site_id is not null
2815 THEN
2816 OPEN c_shiplocation(l_Shipment_Rec.ship_to_party_site_id);
2817 ELSE
2818 OPEN c_shiplocation(l_Shipment_header_rec.ship_to_party_site_id);
2819 END IF;
2820 FETCH c_shiplocation into l_ship_to_location;
2821 close c_shiplocation;
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_shiplocation', 1, 'Y');
2825 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');
2826 END IF;
2827
2828 IF
2829 l_qte_line_rec.invoice_to_party_site_id is not null
2830 THEN
2831 OPEN c_shiplocation(l_qte_line_rec.invoice_to_party_site_id);
2832 ELSE
2833 OPEN c_shiplocation(l_qte_header_rec.invoice_to_party_site_id);
2834 END IF;
2835
2836 FETCH c_shiplocation into l_bill_to_location;
2837 close c_shiplocation;
2838
2839 IF aso_debug_pub.g_debug_flag ='Y' THEN
2840 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :AFTER THE CALL TO c_shiplocation', 1, 'Y');
2841 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');
2842 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');
2843 END IF;
2844
2845 l_trx_type_id := ASO_TAX_INT.get_ra_trx_type_id(l_qte_header_rec.order_type_id,l_qte_line_rec);
2846 IF aso_debug_pub.g_debug_flag ='Y' THEN
2847 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');
2848 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');
2849 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');
2850 END IF;
2851
2852 Open c_product_type;
2853 Fetch c_product_type into l_product_type;
2854 close c_product_type;
2855
2856 IF aso_debug_pub.g_debug_flag ='Y' THEN
2857 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');
2858 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_product_type: '||l_product_type, 1, 'Y');
2859 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :BEFORE THE CALL TO c_tax ', 1, 'Y');
2860 END IF;
2861
2862
2863 Open c_tax;
2864 fetch c_tax into l_hdr_tax_exempt_flag,l_hdr_tax_exempt_number,l_hdr_tax_exempt_reason_code;
2865 close c_tax;
2866
2867 IF aso_debug_pub.g_debug_flag ='Y' THEN
2868 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :AFTER THE CALL TO c_tax ' , 1, 'Y');
2869 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');
2870 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');
2871 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');
2872 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');
2873 END IF;
2874
2875
2876 Open c_cust_trx_type_id(l_trx_type_id);
2877 fetch c_cust_trx_type_id into l_ra_cust_trx_type_id;
2878 close c_cust_trx_type_id;
2879
2880 IF aso_debug_pub.g_debug_flag ='Y' THEN
2881 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');
2882 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');
2883 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_Shipment_Rec.ship_from_org_id : '||l_Shipment_Rec.ship_from_org_id, 1, 'Y');
2884 -- 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');
2885 END IF;
2886
2887 If (l_ra_cust_trx_type_id Is Not Null And l_ra_cust_trx_type_id <> FND_API.G_MISS_NUM ) Then -- Code change done for Bug 14340122
2888
2889 IF aso_debug_pub.g_debug_flag ='Y' THEN
2890 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_ra_cust_trx_type_id Is Not Null ', 1, 'Y');
2891 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');
2892 END IF;
2893
2894 OPEN c_LOCATION_ID;
2895 Fetch c_LOCATION_ID into l_SHIP_FROM_LOCATION_ID;
2896 close c_LOCATION_ID;
2897
2898 IF aso_debug_pub.g_debug_flag ='Y' THEN
2899 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');
2900 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');
2901 --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');
2902 END IF;
2903
2904
2905 -- new code added by suyog bug 5061912
2906
2907 -- get the resource id and the trxn date
2908 OPEN c_get_resource_id(p_qte_header_id);
2909 FETCH c_get_resource_id into l_resource_id;
2910 CLOSE c_get_resource_id;
2911
2912 IF aso_debug_pub.g_debug_flag ='Y' THEN
2913 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');
2914 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE_LINES_LOOP :l_resource_id: '|| l_resource_id , 1, 'Y');
2915 END IF;
2916
2917 -- get the org id based upon the resource id and the trxn date
2918 OPEN c_get_org_id(l_resource_id);
2919 FETCH c_get_org_id into l_poo_party_id;
2920 CLOSE c_get_org_id;
2921
2922 IF aso_debug_pub.g_debug_flag ='Y' THEN
2923 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');
2924 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');
2925 END IF;
2926
2927 -- get the location based upon the org id
2928 OPEN c_get_location_id(l_poo_party_id);
2929 FETCH c_get_location_id into l_poo_location_id;
2930 CLOSE c_get_location_id;
2931
2932 IF aso_debug_pub.g_debug_flag ='Y' THEN
2933 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');
2934 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');
2935 END IF;
2936
2937 -- POA PARTY ID is same as the internal_organization_id
2938 -- POA LOCATION ID is same as the l_int_org_location
2939
2940 IF aso_debug_pub.g_debug_flag ='Y' THEN
2941 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');
2942 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');
2943 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');
2944 aso_debug_pub.add('8936101 ASO_TAX_INT :CALCULATE_TAX_WITH_GTT:l_qte_line_rec.LINE_QUOTE_PRICE '|| l_qte_line_rec.LINE_QUOTE_PRICE , 1, 'Y');
2945 aso_debug_pub.add('8936101 ASO_TAX_INT :CALCULATE_TAX_WITH_GTT:l_qte_line_rec.QUANTITY '||l_qte_line_rec.QUANTITY, 1, 'Y');
2946 END IF;
2947
2948 -- end of new code added by suyog
2949
2950 -- If (l_ra_cust_trx_type_id Is Not Null And l_ra_cust_trx_type_id <> FND_API.G_MISS_NUM ) Then -- Code change done for Bug 12587408
2951
2952 -- Code changes for Default tax Classification Code Bug 5177854 BEGIN
2953
2954 ZX_AR_TAX_CLASSIFICATN_DEF_PKG.GET_DEFAULT_TAX_CLASSIFICATION
2955 (
2956 p_ship_to_site_use_id => l_site_use_id_ship_lines,
2957 p_bill_to_site_use_id => l_site_use_id_bill_lines,
2958 p_inventory_item_id => l_qte_line_rec.Inventory_item_id,
2959 p_organization_id => l_qte_line_rec.organization_id,
2960 p_set_of_books_id => l_set_of_books_id,
2961 p_trx_date => sysdate,
2962 p_trx_type_id => l_trx_type_id,
2963 p_tax_classification_code => l_Tax_Classification_Code,
2964 p_cust_trx_id => l_ra_cust_trx_type_id,
2965 p_customer_id => nvl(l_shipment_rec.ship_to_cust_party_id,l_shipment_header_rec.ship_to_cust_party_id),
2966 appl_short_name => 'ASO',
2967 p_entity_code => 'ASO_QUOTE_HEADERS_ALL',
2968 p_event_class_code => 'SALES_TRANSACTION_TAX_QUOTE',
2969 p_application_id => 697,
2970 p_internal_organization_id => l_qte_header_rec.org_id
2971 );
2972
2973 -- Code Changes for Default tax classification code Bug 5177854 END
2974
2975 -- End If;
2976 --Insertion into the Lines Temporary Table
2977 insert into Zx_transaction_lines_gt
2978 (
2979 APPLICATION_ID,
2980 ENTITY_CODE,
2981 EVENT_CLASS_CODE,
2982 TRX_ID,
2983 TRX_LEVEL_TYPE,
2984 TRX_LINE_ID,
2985 LINE_LEVEL_ACTION,
2986 LINE_CLASS,
2987 TRX_SHIPPING_DATE,
2988 TRX_LINE_TYPE,
2989 TRX_LINE_DATE,
2990 TRX_BUSINESS_CATEGORY,
2991 LINE_AMT,
2992 TRX_LINE_QUANTITY,
2993 EXEMPT_CERTIFICATE_NUMBER,
2994 EXEMPT_REASON_CODE,
2995 PRODUCT_ID,
2996 PRODUCT_ORG_ID,
2997 UOM_CODE,
2998 --PRODUCT_TYPE,
2999 FOB_POINT,
3000 SHIP_TO_PARTY_ID,
3001 SHIP_FROM_PARTY_ID,
3002 BILL_TO_PARTY_ID,
3003 SHIP_TO_PARTY_SITE_ID,
3004 BILL_TO_PARTY_SITE_ID,
3005 SHIP_TO_LOCATION_ID,
3006 BILL_TO_LOCATION_ID,
3007 SHIP_FROM_LOCATION_ID,
3008 HISTORICAL_FLAG,
3009 LINE_AMT_INCLUDES_TAX_FLAG,
3010 EXEMPTION_CONTROL_FLAG,UNIT_PRICE,
3011 TRX_LINE_GL_DATE,
3012 RECEIVABLES_TRX_TYPE_ID,
3013 BILL_TO_CUST_ACCT_SITE_USE_ID,
3014 SHIP_TO_CUST_ACCT_SITE_USE_ID,
3015 SHIP_THIRD_PTY_ACCT_SITE_ID,
3016 BILL_THIRD_PTY_ACCT_SITE_ID,
3017 SHIP_THIRD_PTY_ACCT_ID,
3018 BILL_THIRD_PTY_ACCT_ID,
3019 CTRL_HDR_TX_APPL_FLAG,
3020 TRX_LINE_NUMBER,
3021 POO_LOCATION_ID,
3022 POO_PARTY_ID,
3023 POA_PARTY_ID,
3024 POA_LOCATION_ID,
3025 OUTPUT_TAX_CLASSIFICATION_CODE,
3026 BILL_FROM_LOCATION_ID /*** Added for Bug 8474803 and 7408162 ***/
3027 )
3028 values
3029 (
3030 697,
3031 'ASO_QUOTE_HEADERS_ALL',
3032 'SALES_TRANSACTION_TAX_QUOTE',
3033 p_qte_header_id,
3034 'LINE',
3035 l_qte_line_rec.QUOTE_LINE_ID,
3036 'CREATE',
3037 'INVOICE',
3038 nvl(l_Shipment_Rec.REQUEST_DATE,l_Shipment_header_rec.request_date),
3039 'ITEM',
3040 SYSDATE,
3041 null,
3042 nvl(l_qte_line_rec.LINE_QUOTE_PRICE,0)*l_qte_line_rec.QUANTITY, -- bug 8936101
3043 l_qte_line_rec.QUANTITY,
3044 l_HDR_TAX_EXEMPT_NUMBER,
3045 l_HDR_TAX_EXEMPT_REASON_CODE,
3046 l_qte_line_rec.INVENTORY_ITEM_ID,
3047 l_qte_line_rec.organization_id,
3048 l_qte_line_rec.UOM_CODE,
3049 -- l_product_type,
3050 nvl(l_Shipment_Rec.fob_code,l_Shipment_header_rec.fob_code),
3051 nvl(l_Shipment_Rec.ship_to_cust_party_id,l_Shipment_header_rec.ship_to_cust_party_id),
3052 nvl(l_Shipment_Rec.ship_from_org_id,l_Shipment_header_rec.ship_from_org_id),
3053 nvl(l_qte_line_rec.INVOICE_TO_CUST_PARTY_ID,l_qte_header_rec.INVOICE_TO_CUST_PARTY_ID),
3054 nvl(l_Shipment_Rec.SHIP_TO_PARTY_SITE_ID,l_Shipment_header_rec.SHIP_TO_PARTY_SITE_ID),
3055 nvl(l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID,l_qte_header_rec.INVOICE_TO_PARTY_SITE_ID),
3056 l_ship_to_location,
3057 l_bill_to_location,
3058 l_SHIP_FROM_LOCATION_ID,
3059 'N',
3060 'S',
3061 nvl(l_HDR_TAX_EXEMPT_FLAG,'S'),
3062 nvl(l_qte_line_rec.LINE_QUOTE_PRICE,0), -- bug 8936101
3063 sysdate,
3064 l_ra_cust_trx_type_id,
3065 l_site_use_id_bill_lines,
3066 l_site_use_id_ship_lines,
3067 l_acct_site_id_ship_lines,
3068 l_acct_site_id_bill_lines,
3069 l_ship_cust_acct_id_lines,
3070 l_bill_cust_acct_id_lines,
3071 'N',
3072 l_qte_line_rec.LINE_NUMBER,
3073 l_poo_location_id,
3074 l_poo_party_id,
3075 l_qte_header_rec.ORG_ID,
3076 l_int_org_location,
3077 l_Tax_Classification_Code,
3078 l_bill_from_location_id /*** Added for Bug 8474803 and 7408162 ***/
3079 );
3080
3081 Else
3082 If aso_debug_pub.g_debug_flag ='Y' Then
3083 aso_debug_pub.add('ASO_TAX_INT :CALCULATE_TAX_WITH_GTT: WITHIN THE QUOTE LINES LOOP :l_ra_cust_trx_type_id Is Null ', 1, 'Y');
3084 End If;
3085 End If; -- code change done for Bug 14340122
3086
3087 END LOOP;
3088 print_tax_info(2,p_qte_header_id);
3089 IF aso_debug_pub.g_debug_flag ='Y' THEN
3090 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE QUOTE LINES LOOP ', 1, 'Y');
3091 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: BEFORE CALL TO TAX ENGINE ', 1, 'Y');
3092 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: p_api_version_number : '||p_api_version_number , 1, 'Y');
3093 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_init_msg_list : '||l_init_msg_list , 1, 'Y');
3094 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_commit : '||l_commit , 1, 'Y');
3095 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: l_validation_level : '||l_validation_level , 1, 'Y');
3096 l_tax_start_time := dbms_utility.get_time;
3097 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: CALLED TAX ENGINE AT : '|| l_tax_start_time, 1, 'Y');
3098 END IF;
3099
3100 --Changes done by anrajan on 20/09/05
3101 l_msg_cnt1:= FND_MSG_PUB.Count_Msg;
3102 IF aso_debug_pub.g_debug_flag ='Y' THEN
3103 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: Message Count Before Tax call : '|| l_msg_cnt1, 1, 'Y');
3104 END IF;
3105
3106 ZX_API_PUB.calculate_tax(p_api_version => p_api_version_number,
3107 p_init_msg_list => l_init_msg_list,
3108 p_commit => l_commit,
3109 P_VALIDATION_LEVEL =>l_validation_level,
3110 X_RETURN_STATUS =>x_return_status,
3111 X_MSG_COUNT =>X_Msg_Count,
3112 X_MSG_DATA =>X_Msg_Data);
3113
3114 --Changes done by anrajan on 20/09/05
3115 l_msg_cnt2:=FND_MSG_PUB.Count_Msg;
3116 IF aso_debug_pub.g_debug_flag ='Y' THEN
3117 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: Message Count After Tax call : '|| l_msg_cnt2, 1, 'Y');
3118 END IF;
3119
3120 IF aso_debug_pub.g_debug_flag ='Y' THEN
3121 l_tax_end_time := dbms_utility.get_time;
3122 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO TAX ENGINE ', 1, 'Y');
3123 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER CALL TO TAX ENGINE : X_RETURN_STATUS : '||x_return_status , 1, 'Y');
3124 l_tax_total_time := (l_tax_end_time - l_tax_start_time)/100;
3125 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: TAX CALL ENDED AT : '|| l_tax_end_time, 1, 'Y');
3126 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: TIME TAKEN BY TAX ENGINE IN SECONDS: '|| l_tax_total_time, 1, 'Y');
3127 END IF;
3128
3129 print_tax_info(3,p_qte_header_id);
3130
3131 IF x_return_status='S' THEN
3132
3133 if p_qte_line_id is not null THEN
3134 IF aso_debug_pub.g_debug_flag ='Y' THEN
3135 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN SUCCESSFUL ', 1, 'Y');
3136 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');
3137 END IF;
3138 Delete from
3139 ASO_TAX_DETAILS
3140 where
3141 QUOTE_HEADER_ID=p_qte_header_id
3142 and
3143 QUOTE_LINE_ID=p_qte_line_id;
3144 ELSE
3145 IF aso_debug_pub.g_debug_flag ='Y' THEN
3146 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN SUCCESSFUL ', 1, 'Y');
3147 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');
3148 END IF;
3149 Delete from ASO_TAX_DETAILS
3150 where QUOTE_HEADER_ID=p_qte_header_id
3151 and QUOTE_LINE_ID is not null;
3152 END IF;
3153
3154
3155 --Inserting the values from output temporary table into the ASO_TAX_DETAILS table.
3156
3157
3158 insert into Aso_tax_details
3159 (
3160 TAX_DETAIL_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
3161 LAST_UPDATE_LOGIN,PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE,
3162 QUOTE_HEADER_ID,QUOTE_LINE_ID,QUOTE_SHIPMENT_ID,TAX_CODE,TAX_RATE,
3163 TAX_DATE,TAX_AMOUNT,TAX_EXEMPT_NUMBER,TAX_EXEMPT_REASON_CODE,
3164 TAX_INCLUSIVE_FLAG,OBJECT_VERSION_NUMBER,TAX_RATE_ID,
3165 TAX_EXEMPT_FLAG --Added by anrajan on 05/10/2005
3166 )
3167 select
3168 ASO_TAX_DETAILS_S.nextval,SYSDATE,G_USER_ID,SYSDATE,G_USER_ID,
3169 G_LOGIN_ID,APPLICATION_ID,sysdate,
3170 a.TRX_ID,a.TRX_LINE_ID,b.SHIPMENT_ID,a.TAX_RATE_CODE,a.TAX_RATE,
3171 a.TAX_DETERMINE_DATE,a.TAX_AMT,a.EXEMPT_CERTIFICATE_NUMBER,a.EXEMPT_REASON_CODE,
3172 a.TAX_AMT_INCLUDED_FLAG,a.OBJECT_VERSION_NUMBER,a.TAX_RATE_ID,
3173 nvl(l_HDR_TAX_EXEMPT_FLAG,'S') --Added by anrajan on 05/10/2005
3174 FROM
3175 Zx_detail_tax_lines_gt a,aso_shipments b
3176 WHERE
3177 a.TRX_ID=b.QUOTE_HEADER_ID
3178 AND
3179 a.APPLICATION_ID=697
3180 AND
3181 a.ENTITY_CODE='ASO_QUOTE_HEADERS_ALL'
3182 AND
3183 a.EVENT_CLASS_CODE='SALES_TRANSACTION_TAX_QUOTE'
3184 AND
3185 a.TRX_ID=p_qte_header_id
3186 AND
3187 (
3188 a.TRX_LINE_ID=b.QUOTE_LINE_ID
3189 OR
3190 (a.TRX_LINE_ID is null AND b.QUOTE_LINE_ID is null)
3191 )
3192
3193 --AND
3194 --a.TRX_LEVEL_TYPE='LINE'
3195 ;
3196
3197 --Changed by Anoop on 14 Sep 2005.
3198 IF aso_debug_pub.g_debug_flag ='Y' THEN
3199 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :Number of rows inserted : '||sql%rowcount, 1, 'Y');
3200 END IF;
3201
3202 ELSE
3203 IF aso_debug_pub.g_debug_flag ='Y' THEN
3204 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :RETURN NOT SUCCESSFUL ', 1, 'Y');
3205 END IF;
3206 --Changes done by anrajan on 20/09/05
3207 for i in 1..(l_msg_cnt2-l_msg_cnt1)
3208 loop
3209 IF aso_debug_pub.g_debug_flag ='Y' THEN
3210 FND_MSG_PUB.GET(p_msg_index => l_msg_cnt1+1,
3211 p_data =>X_Msg_Data,
3212 p_encoded =>'F',
3213 p_msg_index_out =>X_Msg_Count);
3214 aso_debug_pub.add('ASO_TAX_INT : CALCULATE_TAX_WITH_GTT: AFTER THE TAX ENGINE CALL :X_Msg_Data : '||X_Msg_Data, 1, 'Y');
3215 END IF;
3216 FND_MSG_PUB.Delete_Msg(l_msg_cnt1+1);
3217 END LOOP;
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219 END IF;
3220 FND_MSG_PUB.Count_And_Get
3221 (
3222 p_count => x_msg_count,
3223 p_data => x_msg_data
3224 );
3225
3226 EXCEPTION
3227 WHEN FND_API.G_EXC_ERROR THEN
3228 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3229 P_API_NAME => L_API_NAME
3230 ,P_PKG_NAME => G_PKG_NAME
3231 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3232 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3233 ,X_MSG_COUNT => X_MSG_COUNT
3234 ,X_MSG_DATA => X_MSG_DATA
3235 ,X_RETURN_STATUS => X_RETURN_STATUS);
3236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3237 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3238 P_API_NAME => L_API_NAME ,P_PKG_NAME => G_PKG_NAME
3239 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3240 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3241 ,X_MSG_COUNT => X_MSG_COUNT
3242 ,X_MSG_DATA => X_MSG_DATA
3243 ,X_RETURN_STATUS => X_RETURN_STATUS);
3244
3245 WHEN OTHERS THEN
3246 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3247 P_API_NAME => L_API_NAME
3248 ,P_PKG_NAME => G_PKG_NAME
3249 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3250 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3251 ,P_SQLCODE => SQLCODE
3252 ,P_SQLERRM => SQLERRM
3253 ,X_MSG_COUNT => X_MSG_COUNT
3254 ,X_MSG_DATA => X_MSG_DATA
3255 ,X_RETURN_STATUS => X_RETURN_STATUS);
3256
3257 end CALCULATE_TAX_WITH_GTT;
3258 -- End Calculate Tax with GTT added as a part of etax By Anoop Rajan om 9 August 2005
3259
3260
3261
3262
3263 FUNCTION Get_Tax_Detail_Id (
3264 p_qte_header_id NUMBER,
3265 p_qte_line_id NUMBER,
3266 p_shipment_id NUMBER) RETURN NUMBER
3267 IS
3268 CURSOR c_tax1 IS
3269 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3270 WHERE quote_shipment_id = p_shipment_id;
3271 CURSOR c_tax2 IS
3272 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3273 WHERE quote_header_id = p_qte_header_id and quote_line_id is NULL
3274 and quote_shipment_id = p_shipment_id;
3275 CURSOR c_tax3 IS
3276 SELECT tax_detail_id FROM ASO_TAX_DETAILS
3277 WHERE quote_header_id = p_qte_header_id and quote_shipment_id = p_shipment_id
3278 and quote_line_id = p_qte_line_id ;
3279 l_tax_detail_id NUMBER;
3280 BEGIN
3281 OPEN c_tax3;
3282 FETCH c_tax3 INTO l_tax_detail_id;
3283 IF c_tax3%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3284 CLOSE c_tax3;
3285 return l_tax_detail_id;
3286 END IF;
3287 CLOSE c_tax3;
3288 OPEN c_tax2;
3289 FETCH c_tax2 INTO l_tax_detail_id ;
3290 IF c_tax2%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3291 CLOSE c_tax2;
3292 return l_tax_detail_id;
3293 END IF;
3294 CLOSE c_tax2;
3295 /*OPEN c_tax1;
3296 FETCH c_tax1 INTO l_tax_detail_id;
3297 IF c_tax1%FOUND and l_tax_detail_id is not null and l_tax_detail_id <> FND_API.G_MISS_NUM THEN
3298 CLOSE c_tax1;
3299 return l_tax_detail_id;
3300 END IF;
3301 CLOSE c_tax1;*/
3302 return l_tax_detail_id;
3303 END Get_Tax_Detail_Id;
3304
3305
3306
3307 FUNCTION Get_Tax_Code (
3308 p_qte_header_id NUMBER,
3309 p_qte_line_id NUMBER,
3310 p_shipment_id NUMBER) RETURN VARCHAR2
3311 IS
3312 CURSOR c_tax1 IS
3313 SELECT tax_code FROM ASO_TAX_DETAILS
3314 WHERE quote_shipment_id = p_shipment_id ;
3315 CURSOR c_tax2 IS
3316 SELECT tax_code FROM ASO_TAX_DETAILS
3317 WHERE quote_line_id = p_qte_line_id ;
3318 CURSOR c_tax3 IS
3319 SELECT tax_code FROM ASO_TAX_DETAILS
3320 WHERE quote_header_id = p_qte_header_id ;
3321 l_orig_tax_code VARCHAR2(240);
3322 BEGIN
3323 OPEN c_tax1;
3324 FETCH c_tax1 INTO l_orig_tax_code;
3325 IF c_tax1%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3326 CLOSE c_tax1;
3327 return l_orig_tax_code;
3328 END IF;
3329 CLOSE c_tax1;
3330 OPEN c_tax2;
3331 FETCH c_tax2 INTO l_orig_tax_code;
3332 IF c_tax2%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3333 CLOSE c_tax2;
3334 return l_orig_tax_code;
3335 END IF;
3336 CLOSE c_tax2;
3337 OPEN c_tax3;
3338 FETCH c_tax3 INTO l_orig_tax_code;
3339 IF c_tax3%FOUND and l_orig_tax_code IS NOT NULL AND l_orig_tax_code <> FND_API.G_MISS_CHAR THEN
3340 CLOSE c_tax3;
3341 return l_orig_tax_code;
3342 END IF;
3343 CLOSE c_tax3;
3344 return l_orig_tax_code;
3345 END Get_Tax_Code;
3346
3347 FUNCTION Get_Tax_exempt_flag (
3348 p_qte_header_id NUMBER,
3349 p_qte_line_id NUMBER,
3350 p_shipment_id NUMBER) RETURN VARCHAR2
3351 IS
3352 CURSOR c_tax1 IS
3353 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3354 WHERE quote_shipment_id = p_shipment_id ;
3355 CURSOR c_tax2 IS
3356 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3357 WHERE quote_line_id = p_qte_line_id ;
3358 CURSOR c_tax3 IS
3359 SELECT tax_exempt_flag FROM ASO_TAX_DETAILS
3360 WHERE quote_header_id = p_qte_header_id ;
3361 l_tax_exempt_flag VARCHAR2(1);
3362 BEGIN
3363 OPEN c_tax1;
3364 FETCH c_tax1 INTO l_tax_exempt_flag;
3365 IF c_tax1%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3366 CLOSE c_tax1;
3367 return l_tax_exempt_flag;
3368 END IF;
3369 CLOSE c_tax1;
3370 OPEN c_tax2;
3371 FETCH c_tax2 INTO l_tax_exempt_flag;
3372 IF c_tax2%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3373 CLOSE c_tax2;
3374 return l_tax_exempt_flag;
3375 END IF;
3376 CLOSE c_tax2;
3377 OPEN c_tax3;
3378 FETCH c_tax3 INTO l_tax_exempt_flag;
3379 IF c_tax3%FOUND and l_tax_exempt_flag is not null and l_tax_exempt_flag <> FND_API.G_MISS_CHAR THEN
3380 CLOSE c_tax3;
3381 return l_tax_exempt_flag;
3382 END IF;
3383 CLOSE c_tax3;
3384 return l_tax_exempt_flag;
3385 END Get_Tax_Exempt_Flag;
3386
3387 FUNCTION Get_Tax_exempt_number (
3388 p_qte_header_id NUMBER,
3389 p_qte_line_id NUMBER,
3390 p_shipment_id NUMBER) RETURN VARCHAR2
3391 IS
3392 CURSOR c_tax1 IS
3393 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3394 WHERE quote_shipment_id = p_shipment_id ;
3395 CURSOR c_tax2 IS
3396 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3397 WHERE quote_line_id = p_qte_line_id ;
3398 CURSOR c_tax3 IS
3399 SELECT tax_exempt_number FROM ASO_TAX_DETAILS
3400 WHERE quote_header_id = p_qte_header_id ;
3401 l_tax_exempt_number VARCHAR2(80);
3402 BEGIN
3403 OPEN c_tax1;
3404 FETCH c_tax1 INTO l_tax_exempt_number;
3405 IF c_tax1%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3406 CLOSE c_tax1;
3407 return l_tax_exempt_number;
3408 END IF;
3409 CLOSE c_tax1;
3410 OPEN c_tax2;
3411 FETCH c_tax2 INTO l_tax_exempt_number;
3412 IF c_tax2%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3413 CLOSE c_tax2;
3414 return l_tax_exempt_number;
3415 END IF;
3416 CLOSE c_tax2;
3417 OPEN c_tax3;
3418 FETCH c_tax3 INTO l_tax_exempt_number;
3419 IF c_tax3%FOUND and l_tax_exempt_number is not null and l_tax_exempt_number <> FND_API.G_MISS_CHAR THEN
3420 CLOSE c_tax3;
3421 return l_tax_exempt_number;
3422 END IF;
3423 CLOSE c_tax3;
3424 return l_tax_exempt_number;
3425 END Get_Tax_Exempt_number;
3426
3427 FUNCTION Get_Tax_exempt_REASON_CODE (
3428 p_qte_header_id NUMBER,
3429 p_qte_line_id NUMBER,
3430 p_shipment_id NUMBER) RETURN VARCHAR2
3431 IS
3432 CURSOR c_tax1 IS
3433 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3434 WHERE quote_shipment_id = p_shipment_id ;
3435 CURSOR c_tax2 IS
3436 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3437 WHERE quote_line_id = p_qte_line_id ;
3438 CURSOR c_tax3 IS
3439 SELECT tax_exempt_reason_code FROM ASO_TAX_DETAILS
3440 WHERE quote_header_id = p_qte_header_id ;
3441 l_tax_exempt_reason_code VARCHAR2(80);
3442 BEGIN
3443 OPEN c_tax1;
3444 FETCH c_tax1 INTO l_tax_exempt_reason_code;
3445 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
3446 CLOSE c_tax1;
3447 return l_tax_exempt_reason_code;
3448 END IF;
3449 CLOSE c_tax1;
3450 OPEN c_tax2;
3451 FETCH c_tax2 INTO l_tax_exempt_reason_code;
3452 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
3453 CLOSE c_tax2;
3454 return l_tax_exempt_reason_code;
3455 END IF;
3456 CLOSE c_tax2;
3457 OPEN c_tax3;
3458 FETCH c_tax3 INTO l_tax_exempt_reason_code;
3459 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
3460 CLOSE c_tax3;
3461 return l_tax_exempt_reason_code;
3462 END IF;
3463 CLOSE c_tax3;
3464 return l_tax_exempt_reason_code;
3465 END Get_Tax_Exempt_reason_code;
3466
3467 FUNCTION Get_Tax_Invoice_To (
3468 p_ln_invoice_id NUMBER,
3469 p_hd_invoice_id NUMBER) RETURN NUMBER
3470 IS
3471 BEGIN
3472 return NVL(p_ln_invoice_id, p_hd_invoice_id);
3473 END Get_Tax_Invoice_To;
3474
3475 FUNCTION GET_ra_trx_type_ID (p_order_type_id NUMBER,p_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type) RETURN NUMBER
3476 IS
3477 CURSOR C_OE_trns(l_order_type_Id NUMBER) IS
3478 SELECT default_inbound_line_type_id, default_outbound_line_type_id, cust_trx_type_id
3479 FROM OE_TRANSACTION_TYPES_VL
3480 WHERE transaction_type_id=l_order_type_Id;
3481 l_in_line_type NUMBER;
3482 l_out_line_type NUMBER;
3483 l_cust_trx_type_id NUMBER;
3484 l_inv_cust_trx_type_id NUMBER;
3485 l_line_type_id NUMBER;
3486 l_order_type_id NUMBER := p_order_type_id;
3487 BEGIN
3488
3489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3490 aso_debug_pub.add(' ASO_TAX_INT :p_order_type_id'||p_order_type_id , 1, 'N');
3491 END IF;
3492
3493 IF p_order_type_id is NULL OR p_order_type_id = FND_API.G_MISS_NUM THEN
3494
3495 -- Change START
3496 -- Release 12 MOAC Changes : Bug 4500739
3497 -- Changes Done by : Girish
3498 -- Comments : Changed to use HR EIT in place of org striped profile.
3499
3500 --l_order_type_id := to_number(fnd_profile.value('ASO_ORDER_TYPE_ID'));
3501 l_order_type_id := to_number(ASO_UTILITY_PVT.get_ou_attribute_value(ASO_UTILITY_PVT.G_DEFAULT_ORDER_TYPE));
3502
3503 -- Change END
3504
3505 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3506 aso_debug_pub.add(' ASO_TAX_INT Porder_type id is null:l_order_type_id'||l_order_type_id , 1, 'N');
3507 END IF;
3508
3509 END IF;
3510 OPEN C_OE_trns(l_order_type_id);
3511 FETCH C_OE_trns INTO l_in_line_type, l_out_line_type, l_cust_trx_type_id;
3512
3513 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3514
3515 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');
3516 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:l_out_line_type'||l_out_line_type , 1, 'N');
3517 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:l_cust_trx_type_id'||l_cust_trx_type_id , 1, 'N');
3518
3519 END IF;
3520
3521
3522 IF C_OE_trns%NOTFOUND THEN
3523 NULL;
3524
3525 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3526 aso_debug_pub.add(' ASO_TAX_INT C_OE_trns:NOT FOUND' , 1, 'N');
3527 END IF;
3528
3529 END IF;
3530 CLOSE C_OE_trns;
3531 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
3532
3533 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3534 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.order_line_type_id is NULL' , 1, 'N');
3535 END IF;
3536
3537 IF p_qte_line_rec.line_category_code = 'ORDER' THEN
3538 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3539 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.line_category_code '|| p_qte_line_rec.line_category_code , 1, 'N');
3540 END IF;
3541 l_line_type_id := l_out_line_type;
3542
3543 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3544 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.l_out_line_typ '|| l_out_line_type , 1, 'N');
3545 END IF;
3546
3547 ELSE
3548 l_line_type_id := l_in_line_type;
3549 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3550 aso_debug_pub.add(' ASO_TAX_INT p_qte_line_rec.li_n_line_typ '|| l_in_line_type , 1, 'N');
3551 END IF;
3552
3553 END IF;
3554 ELSE
3555 l_line_type_id := p_qte_line_rec.order_line_type_id ;
3556 END IF;
3557
3558 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3559 aso_debug_pub.add(' ASO_TAX_INT l_line_type_id '|| l_line_type_id , 1, 'N');
3560 END IF;
3561
3562 IF p_qte_line_rec.line_category_code <> 'RETURN' THEN -- Standard Order Line
3563
3564 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3565 aso_debug_pub.add(' ASO_TAX_INT Line Category return ' , 1, 'N');
3566 END IF;
3567
3568 SELECT NVL(lt.cust_trx_type_id, 0)
3569 INTO l_cust_trx_type_id
3570 FROM oe_line_types_v lt
3571 WHERE lt.line_type_id = l_line_type_id;
3572
3573 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3574 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id Category return '||l_cust_trx_type_id , 1, 'N');
3575 END IF;
3576
3577 IF l_cust_trx_type_id = 0 THEN
3578
3579 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3580 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id =0before '||l_cust_trx_type_id , 1, 'N');
3581 END IF;
3582
3583 SELECT NVL(ot.cust_trx_type_id, 0)
3584 INTO l_cust_trx_type_id
3585 FROM oe_order_types_v ot
3586 WHERE ot.order_type_id =l_order_type_id;
3587
3588 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3589 aso_debug_pub.add(' ASO_TAX_INT l_cust_trx_type_id =0after '||l_cust_trx_type_id , 1, 'N');
3590 END IF;
3591
3592 IF l_cust_trx_type_id = 0 THEN
3593
3594 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3595 aso_debug_pub.add(' ASO_TAX_INT default from oe_invoice_trans profile ' , 1, 'N');
3596 END IF;
3597 /* This profile is obsoleted
3598 SELECT NVL(FND_PROFILE.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID'), 0)
3599 INTO l_cust_trx_type_id
3600 FROM DUAL;
3601 */
3602
3603 SELECT NVL(oe_sys_parameters.value('OE_INVOICE_TRANSACTION_TYPE_ID', p_qte_line_rec.org_id), 0)
3604 INTO l_cust_trx_type_id FROM DUAL;
3605
3606 END IF;
3607 END IF;
3608 RETURN(l_cust_trx_type_id);
3609 ELSE -- Non Referenced Return Line
3610
3611 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3612 aso_debug_pub.add(' ASO_TAX_INT Non Referenced Return Line ' , 1, 'N');
3613 END IF;
3614
3615 SELECT NVL(lt.cust_trx_type_id, 0)
3616 INTO l_inv_cust_trx_type_id
3617 FROM oe_line_types_v lt
3618 WHERE lt.line_type_id = l_line_type_id;
3619
3620
3621 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3622 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');
3623 END IF;
3624
3625 IF l_inv_cust_trx_type_id = 0 THEN
3626 SELECT NVL(DECODE(ot.order_category_code, 'RETURN',ot.cust_trx_type_id, 0), 0)
3627 INTO l_inv_cust_trx_type_id
3628 FROM oe_order_types_v ot
3629 WHERE ot.order_type_id = l_order_type_id;
3630 END IF;
3631 IF l_inv_cust_trx_type_id <> 0 THEN
3632
3633 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3634 aso_debug_pub.add(' ASO_TAX_INT Non Referenced Return Line credit memo ' , 1, 'N');
3635 END IF;
3636
3637 SELECT nvl(ctt.credit_memo_type_id, 0)
3638 INTO l_cust_trx_type_id
3639 --FROM ra_cust_trx_types_all ctt Commented Code Yogeshwar (MOAC)
3640 FROM ra_cust_trx_types ctt --New Code Yogeshwar (MOAC)
3641 WHERE ctt.cust_trx_type_id = l_inv_cust_trx_type_id
3642 AND NVL(ctt.org_id, -3114) = DECODE(ctt.cust_trx_type_id,
3643 1, -3113,
3644 2, -3113,
3645 7, -3113,
3646 8, -3113,
3647 NVL(p_qte_line_rec.org_id, -3114));
3648
3649 END IF;
3650 IF l_inv_cust_trx_type_id = 0 OR l_cust_trx_type_id = 0 THEN
3651 SELECT NVL(FND_PROFILE.VALUE('OE_CREDIT_TRANSACTION_TYPE_ID'), 0)
3652 INTO l_cust_trx_type_id
3653 FROM DUAL;
3654
3655 END IF;
3656 RETURN(l_cust_trx_type_id);
3657 END IF;
3658
3659 EXCEPTION
3660 WHEN OTHERS THEN
3661
3662 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3663 aso_debug_pub.add(' ASO_TAX_INT GET_RA_TRX In WHEN others ' , 1, 'N');
3664 END IF;
3665
3666 return(0);
3667 END GET_ra_trx_type_ID;
3668
3669
3670 -- Commenting the following routine as part of release 12. Bug 5044986
3671 /*
3672 *
3673 *
3674 PROCEDURE print_tax_info_rec( p_debug_level in number := 5 ) IS
3675 -- l_IO_flag CHAR(1);
3676 dummy varchar2(80) := NULL;
3677 BEGIN
3678
3679 IF ( p_debug_level <= aso_debug_pub.G_Debug_Level) THEN
3680
3681
3682 --
3683 -- Dump tax_info_rec
3684 --
3685
3686 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3687
3688 aso_debug_pub.add( '************************************',1, 'Y' );
3689 aso_debug_pub.add( '** Begining of Tax Info Record **',1, 'Y' );
3690 aso_debug_pub.add( '************************************',1, 'Y' );
3691 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_cust_id = '||arp_tax.tax_info_rec.Bill_to_cust_id);
3692 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_cust_id = '||arp_tax.tax_info_rec.Ship_to_cust_id);
3693 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_id = '||arp_tax.tax_info_rec.Customer_trx_id);
3694 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_date = '||arp_tax.tax_info_rec.Trx_date);
3695 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': GL_date = '||arp_tax.tax_info_rec.gl_date);
3696 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_site_use_id = '||arp_tax.tax_info_rec.Ship_to_site_use_id);
3697 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_site_use_id = '||arp_tax.tax_info_rec.Bill_to_site_use_id);
3698 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_postal_code = '||arp_tax.tax_info_rec.Ship_to_postal_code);
3699 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_postal_code = '||arp_tax.tax_info_rec.Bill_to_postal_code);
3700 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_location_id = '||arp_tax.tax_info_rec.Ship_to_location_id);
3701 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Bill_to_location_id = '||arp_tax.tax_info_rec.Bill_to_location_id);
3702 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Invoicing_rule_id = '||arp_tax.tax_info_rec.Invoicing_rule_id);
3703 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': FOB_point = '||arp_tax.tax_info_rec.FOB_point);
3704 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_currency_code = '||arp_tax.tax_info_rec.Trx_currency_code);
3705 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_exchange_rate = '||arp_tax.tax_info_rec.Trx_exchange_rate);
3706 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Minimum_accountable_unit = '||arp_tax.tax_info_rec.Minimum_accountable_unit);
3707 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Precision = '||arp_tax.tax_info_rec.Precision);
3708 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_line_id = '||arp_tax.tax_info_rec.Customer_trx_line_id);
3709 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);
3710 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Memo_line_id = '||arp_tax.tax_info_rec.Memo_line_id);
3711 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxed_quantity = '||arp_tax.tax_info_rec.Taxed_quantity);
3712 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Inventory_item_id = '||arp_tax.tax_info_rec.Inventory_item_id);
3713 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Extended_amount = '||arp_tax.tax_info_rec.Extended_amount);
3714 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_code = '||arp_tax.tax_info_rec.Tax_code);
3715 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Vat_tax_id = '||arp_tax.tax_info_rec.Vat_tax_id);
3716 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_exemption_id = '||arp_tax.tax_info_rec.Tax_exemption_id);
3717 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Item_exception_rate_id = '||arp_tax.tax_info_rec.Item_exception_rate_id);
3718 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_rate = '||arp_tax.tax_info_rec.Tax_rate);
3719 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Default_ussgl_transaction_code = '||arp_tax.tax_info_rec.Default_ussgl_transaction_code);
3720 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Default_ussgl_trx_code_context = '||arp_tax.tax_info_rec.Default_ussgl_trx_code_context);
3721 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_control = '||arp_tax.tax_info_rec.Tax_control);
3722 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Qualifier = '||arp_tax.tax_info_rec.Qualifier);
3723 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_from_code = '||arp_tax.tax_info_rec.Ship_from_code);
3724 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_to_code = '||arp_tax.tax_info_rec.Ship_to_code);
3725 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poo_code = '||arp_tax.tax_info_rec.Poo_code);
3726 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poa_code = '||arp_tax.tax_info_rec.Poa_code);
3727 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Vdrctrl_exempt = '||arp_tax.tax_info_rec.Vdrctrl_exempt);
3728 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_cert_no = '||arp_tax.tax_info_rec.Xmpt_cert_no);
3729 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_reason = '||arp_tax.tax_info_rec.Xmpt_reason);
3730 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Xmpt_percent = '||arp_tax.tax_info_rec.Xmpt_percent);
3731 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Part_no = '||arp_tax.tax_info_rec.Part_no);
3732 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf1 = '||arp_tax.tax_info_rec.Userf1);
3733 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf2 = '||arp_tax.tax_info_rec.Userf2);
3734 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf3 = '||arp_tax.tax_info_rec.Userf3);
3735 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf4 = '||arp_tax.tax_info_rec.Userf4);
3736 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf5 = '||arp_tax.tax_info_rec.Userf5);
3737 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf6 = '||arp_tax.tax_info_rec.Userf6);
3738 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf7 = '||arp_tax.tax_info_rec.Userf7);
3739 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf8 = '||arp_tax.tax_info_rec.Userf8);
3740 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf9 = '||arp_tax.tax_info_rec.Userf9);
3741 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Userf10 = '||arp_tax.tax_info_rec.Userf10);
3742 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern1 = '||arp_tax.tax_info_rec.Usern1);
3743 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern2 = '||arp_tax.tax_info_rec.Usern2);
3744 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern3 = '||arp_tax.tax_info_rec.Usern3);
3745 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern4 = '||arp_tax.tax_info_rec.Usern4);
3746 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern5 = '||arp_tax.tax_info_rec.Usern5);
3747 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern6 = '||arp_tax.tax_info_rec.Usern6);
3748 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern7 = '||arp_tax.tax_info_rec.Usern7);
3749 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern8 = '||arp_tax.tax_info_rec.Usern8);
3750 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern9 = '||arp_tax.tax_info_rec.Usern9);
3751 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Usern10 = '||arp_tax.tax_info_rec.Usern10);
3752
3753 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': trx_number = '||arp_tax.tax_info_rec.trx_number);
3754 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_customer_trx_line_id = '||arp_tax.tax_info_rec.previous_customer_trx_line_id);
3755 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_customer_trx_id = '||arp_tax.tax_info_rec.previous_customer_trx_id);
3756 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': previous_trx_number = '||arp_tax.tax_info_rec.previous_trx_number);
3757 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': bill_to_customer_number = '||arp_tax.tax_info_rec.bill_to_customer_number);
3758 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': ship_to_customer_number = '||arp_tax.tax_info_rec.ship_to_customer_number);
3759 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': bill_to_customer_name = '||arp_tax.tax_info_rec.bill_to_customer_name);
3760 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': ship_to_customer_name = '||arp_tax.tax_info_rec.ship_to_customer_name);
3761 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Audit_Flag = ' || arp_tax.tax_info_rec.audit_flag);
3762 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_Line_Type = ' || arp_tax.tax_info_rec.trx_line_type);
3763 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Division Code = ' || arp_tax.tax_info_rec.division_code);
3764 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Company Code = '|| arp_tax.tax_info_rec.company_code);
3765 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Calculate_tax = '||arp_tax.tax_info_rec.Calculate_tax);
3766 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Status = '||arp_tax.tax_info_rec.Status);
3767 END IF;
3768
3769 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3770 IF ( arp_tax.tax_info_rec.tax_type = 0 ) THEN
3771 dummy := 'TAX_TYPE_INACTIVE';
3772 ELSIF ( arp_tax.tax_info_rec.tax_type = 1 ) THEN
3773 dummy := 'TAX_TYPE_LOCATION';
3774 ELSIF ( arp_tax.tax_info_rec.tax_type = 2 ) THEN
3775 dummy := 'TAX_TYPE_SALES';
3776 ELSIF ( arp_tax.tax_info_rec.tax_type = 3 ) THEN
3777 dummy := 'TAX_TYPE_VAT';
3778 ELSE
3779 dummy := null;
3780 END IF;
3781 END IF;
3782
3783
3784 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3785 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_type = '||arp_tax.tax_info_rec.tax_type||' : '||dummy);
3786 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Sales_tax_id = '||arp_tax.tax_info_rec.Sales_tax_id);
3787 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Location_segment_id = '||arp_tax.tax_info_rec.Location_segment_id);
3788 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_line_number = '||arp_tax.tax_info_rec.Tax_line_number);
3789 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_amount = '||arp_tax.tax_info_rec.Tax_amount);
3790 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_vendor_return_code = '||dummy);
3791 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_precedence = '||arp_tax.tax_info_rec.Tax_precedence);
3792 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Compound_amount = '||arp_tax.tax_info_rec.Compound_amount);
3793 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_header_level_flag = '||arp_tax.tax_info_rec.Tax_header_level_flag);
3794 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Tax_rounding_rule = '||arp_tax.tax_info_rec.Tax_rounding_rule);
3795 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Trx_type_id = '||arp_tax.tax_info_rec.Trx_type_id);
3796 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Ship_From_Warehouse_id = '||arp_tax.tax_info_rec.Ship_From_Warehouse_id);
3797 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Amount_includes_tax_flag = '||arp_tax.tax_info_rec.Amount_includes_tax_flag);
3798 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Customer_trx_charge_line_id = '||arp_tax.tax_info_rec.customer_trx_charge_line_id);
3799 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poo_id = '||arp_tax.tax_info_rec.poo_id);
3800 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Poa_id = '||arp_tax.tax_info_rec.poa_id);
3801 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxable_amount = '||arp_tax.tax_info_rec.taxable_amount);
3802 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Taxable_basis = '||arp_tax.tax_info_rec.taxable_basis);
3803 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Override Tax Rate = '||arp_tax.tax_info_rec.override_tax_rate);
3804 aso_debug_pub.add( 'arp_tax.tax_info_rec'||': Party Flag = '||arp_tax.tax_info_rec.party_flag);
3805 aso_debug_pub.add( '*******************************');
3806 aso_debug_pub.add( '** End of Tax Info Record **');
3807 aso_debug_pub.add( '*******************************');
3808 aso_debug_pub.add( 'print_tax_info_rec()-' ,1, 'N');
3809 END IF;
3810
3811 END IF;
3812 END print_tax_info_rec;
3813 *
3814 *
3815 */
3816
3817 --Procedure added by Anoop on 14 Sep 2005 to print TAX GTT details
3818
3819 Procedure print_tax_info(
3820 rec in number,
3821 qte_header_id in number)
3822 IS
3823 cursor c1 is select
3824 INTERNAL_ORGANIZATION_ID,
3825 INTERNAL_ORG_LOCATION_ID,
3826 APPLICATION_ID,
3827 ENTITY_CODE,
3828 EVENT_CLASS_CODE,
3829 EVENT_TYPE_CODE,
3830 TRX_ID,
3831 TRX_DATE,
3832 LEDGER_ID,
3833 TRX_CURRENCY_CODE,
3834 CURRENCY_CONVERSION_DATE,
3835 CURRENCY_CONVERSION_RATE,
3836 CURRENCY_CONVERSION_TYPE,
3837 MINIMUM_ACCOUNTABLE_UNIT,
3838 PRECISION,
3839 LEGAL_ENTITY_ID,
3840 QUOTE_FLAG,
3841 TRX_NUMBER,
3842 TAX_EVENT_TYPE_CODE,
3843 TAX_REPORTING_FLAG,
3844 SHIP_TO_CUST_ACCT_SITE_USE_ID,
3845 BILL_TO_CUST_ACCT_SITE_USE_ID,
3846 DOC_LEVEL_RECALC_FLAG,
3847 SHIP_THIRD_PTY_ACCT_SITE_ID,
3848 BILL_THIRD_PTY_ACCT_SITE_ID,
3849 SHIP_THIRD_PTY_ACCT_ID,
3850 BILL_THIRD_PTY_ACCT_ID,
3851 ROUNDING_BILL_TO_PARTY_ID
3852 from
3853 ZX_TRX_HEADERS_GT
3854 where
3855 TRX_ID=qte_header_id;
3856
3857 Cursor c2 is select
3858 APPLICATION_ID,
3859 ENTITY_CODE,
3860 EVENT_CLASS_CODE,
3861 TRX_ID,
3862 TRX_LEVEL_TYPE,
3863 TRX_LINE_ID,
3864 LINE_LEVEL_ACTION,
3865 LINE_CLASS,
3866 TRX_SHIPPING_DATE,
3867 TRX_LINE_TYPE,
3868 TRX_LINE_DATE,
3869 TRX_BUSINESS_CATEGORY,
3870 LINE_AMT,
3871 TRX_LINE_QUANTITY,
3872 EXEMPT_CERTIFICATE_NUMBER,
3873 EXEMPT_REASON_CODE,
3874 PRODUCT_ID,
3875 PRODUCT_ORG_ID,
3876 UOM_CODE,
3877 PRODUCT_TYPE,
3878 FOB_POINT,
3879 SHIP_TO_PARTY_ID,
3880 SHIP_FROM_PARTY_ID,
3881 BILL_TO_PARTY_ID,
3882 SHIP_TO_PARTY_SITE_ID,
3883 BILL_TO_PARTY_SITE_ID,
3884 SHIP_TO_LOCATION_ID,
3885 BILL_TO_LOCATION_ID,
3886 SHIP_FROM_LOCATION_ID,
3887 HISTORICAL_FLAG,
3888 LINE_AMT_INCLUDES_TAX_FLAG,
3889 EXEMPTION_CONTROL_FLAG,
3890 UNIT_PRICE,
3891 TRX_LINE_GL_DATE,
3892 RECEIVABLES_TRX_TYPE_ID,
3893 BILL_TO_CUST_ACCT_SITE_USE_ID,
3894 SHIP_TO_CUST_ACCT_SITE_USE_ID,
3895 SHIP_THIRD_PTY_ACCT_SITE_ID,
3896 BILL_THIRD_PTY_ACCT_SITE_ID,
3897 SHIP_THIRD_PTY_ACCT_ID,
3898 BILL_THIRD_PTY_ACCT_ID,
3899 CTRL_HDR_TX_APPL_FLAG,
3900 TRX_LINE_NUMBER,
3901 BILL_FROM_LOCATION_ID /*** Added for Bug 8474803 and 7408162 ***/
3902 from
3903 Zx_transaction_lines_gt
3904 where
3905 TRX_ID=qte_header_id;
3906
3907 Cursor c3 is select
3908 TRX_ID,
3909 TRX_LINE_ID,
3910 TAX_RATE_CODE,
3911 TAX_RATE,
3912 TAX_DETERMINE_DATE,
3913 TAX_AMT,
3914 EXEMPT_CERTIFICATE_NUMBER,
3915 EXEMPT_REASON_CODE,
3916 TAX_AMT_INCLUDED_FLAG,
3917 OBJECT_VERSION_NUMBER,
3918 TAX_RATE_ID
3919 from
3920 Zx_detail_tax_lines_gt
3921 where
3922 TRX_ID=qte_header_id;
3923
3924 BEGIN
3925 IF (aso_debug_pub.g_debug_flag = 'Y' and rec=1) THEN
3926 for i in c1 LOOP
3927 aso_debug_pub.add( '******************************************',1, 'Y' );
3928 aso_debug_pub.add( '***** After insertion into ZX_TRX_HEADERS_GTT *****',1, 'Y' );
3929 aso_debug_pub.add( '******************************************',1, 'Y' );
3930 aso_debug_pub.add(rpad('INTERNAL_ORGANIZATION_ID',50,'------')||'------>'||i.INTERNAL_ORGANIZATION_ID , 1, 'Y');
3931 aso_debug_pub.add(rpad('INTERNAL_ORG_LOCATION_ID',50,'------')||'------>'||i.INTERNAL_ORG_LOCATION_ID , 1, 'Y');
3932 aso_debug_pub.add(rpad('APPLICATION_ID',50,'------')||'------>'||i.APPLICATION_ID , 1, 'Y');
3933 aso_debug_pub.add(rpad('ENTITY_CODE',50,'------')||'------>'||i.ENTITY_CODE , 1, 'Y');
3934 aso_debug_pub.add(rpad('EVENT_CLASS_CODE',50,'------')||'------>'||i.EVENT_CLASS_CODE , 1, 'Y');
3935 aso_debug_pub.add(rpad('EVENT_TYPE_CODE',50,'------')||'------>'||i.EVENT_TYPE_CODE , 1, 'Y');
3936 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
3937 aso_debug_pub.add(rpad('TRX_DATE',50,'------')||'------>'||i.TRX_DATE , 1, 'Y');
3938 aso_debug_pub.add(rpad('LEDGER_ID',50,'------')||'------>'||i.LEDGER_ID , 1, 'Y');
3939 aso_debug_pub.add(rpad('TRX_CURRENCY_CODE',50,'------')||'------>'||i.TRX_CURRENCY_CODE , 1, 'Y');
3940 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_DATE',50,'------')||'------>'||i.CURRENCY_CONVERSION_DATE , 1, 'Y');
3941 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_RATE',50,'------')||'------>'||i.CURRENCY_CONVERSION_RATE , 1, 'Y');
3942 aso_debug_pub.add(rpad('CURRENCY_CONVERSION_TYPE',50,'------')||'------>'||i.CURRENCY_CONVERSION_TYPE , 1, 'Y');
3943 aso_debug_pub.add(rpad('MINIMUM_ACCOUNTABLE_UNIT',50,'------')||'------>'||i.MINIMUM_ACCOUNTABLE_UNIT , 1, 'Y');
3944 aso_debug_pub.add(rpad('PRECISION',50,'------')||'------>'||i.PRECISION , 1, 'Y');
3945 aso_debug_pub.add(rpad('LEGAL_ENTITY_ID',50,'------')||'------>'||i.LEGAL_ENTITY_ID , 1, 'Y');
3946 aso_debug_pub.add(rpad('QUOTE_FLAG',50,'------')||'------>'||i.QUOTE_FLAG , 1, 'Y');
3947 aso_debug_pub.add(rpad('TRX_NUMBER',50,'------')||'------>'||i.TRX_NUMBER , 1, 'Y');
3948 aso_debug_pub.add(rpad('TAX_EVENT_TYPE_CODE',50,'------')||'------>'||i.TAX_EVENT_TYPE_CODE , 1, 'Y');
3949 aso_debug_pub.add(rpad('TAX_REPORTING_FLAG',50,'------')||'------>'||i.TAX_REPORTING_FLAG , 1, 'Y');
3950 aso_debug_pub.add(rpad('SHIP_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.SHIP_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3951 aso_debug_pub.add(rpad('BILL_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.BILL_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
3952 aso_debug_pub.add(rpad('DOC_LEVEL_RECALC_FLAG',50,'------')||'------>'||i.DOC_LEVEL_RECALC_FLAG , 1, 'Y');
3953 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3954 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
3955 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_ID , 1, 'Y');
3956 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_ID , 1, 'Y');
3957 aso_debug_pub.add(rpad('ROUNDING_BILL_TO_PARTY_ID',50,'------')||'------>'||i.ROUNDING_BILL_TO_PARTY_ID , 1, 'Y');
3958 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
3959 --aso_debug_pub.add('*****INSERTED INTO ZX_TRX_HEADERS_GTT*****',1, 'Y' );
3960 --aso_debug_pub.add('******************************************',1, 'Y' );
3961 end loop;
3962 ELSIF (aso_debug_pub.g_debug_flag = 'Y' and rec=2) THEN
3963 for i in c2 loop
3964 aso_debug_pub.add( '******************************************',1, 'Y' );
3965 aso_debug_pub.add( '***** After insertion into Zx_transaction_lines_gt *****',1, 'Y' );
3966 aso_debug_pub.add( '******************************************',1, 'Y' );
3967 aso_debug_pub.add(rpad('APPLICATION_ID',50,'------')||'------>'||i.APPLICATION_ID , 1, 'Y');
3968 aso_debug_pub.add(rpad('ENTITY_CODE',50,'------')||'------>'||i.ENTITY_CODE , 1, 'Y');
3969 aso_debug_pub.add(rpad('EVENT_CLASS_CODE',50,'------')||'------>'||i.EVENT_CLASS_CODE , 1, 'Y');
3970 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
3971 aso_debug_pub.add(rpad('TRX_LEVEL_TYPE',50,'------')||'------>'||i.TRX_LEVEL_TYPE , 1, 'Y');
3972 aso_debug_pub.add(rpad('TRX_LINE_ID',50,'------')||'------>'||i.TRX_LINE_ID , 1, 'Y');
3973 aso_debug_pub.add(rpad('LINE_LEVEL_ACTION',50,'------')||'------>'||i.LINE_LEVEL_ACTION , 1, 'Y');
3974 aso_debug_pub.add(rpad('LINE_CLASS',50,'------')||'------>'||i.LINE_CLASS , 1, 'Y');
3975 aso_debug_pub.add(rpad('TRX_SHIPPING_DATE',50,'------')||'------>'||i.TRX_SHIPPING_DATE , 1, 'Y');
3976 aso_debug_pub.add(rpad('TRX_LINE_TYPE',50,'------')||'------>'||i.TRX_LINE_TYPE , 1, 'Y');
3977 aso_debug_pub.add(rpad('TRX_LINE_DATE',50,'------')||'------>'||i.TRX_LINE_DATE , 1, 'Y');
3978 aso_debug_pub.add(rpad('TRX_BUSINESS_CATEGORY',50,'------')||'------>'||i.TRX_BUSINESS_CATEGORY , 1, 'Y');
3979 aso_debug_pub.add(rpad('LINE_AMT',50,'------')||'------>'||i.LINE_AMT, 1, 'Y');
3980 aso_debug_pub.add(rpad('TRX_LINE_QUANTITY',50,'------')||'------>'||i.TRX_LINE_QUANTITY , 1, 'Y');
3981 aso_debug_pub.add(rpad('EXEMPT_CERTIFICATE_NUMBER',50,'------')||'------>'||i.EXEMPT_CERTIFICATE_NUMBER , 1, 'Y');
3982 aso_debug_pub.add(rpad('EXEMPT_REASON_CODE',50,'------')||'------>'||i.EXEMPT_REASON_CODE , 1, 'Y');
3983 aso_debug_pub.add(rpad('PRODUCT_ID',50,'------')||'------>'||i.PRODUCT_ID , 1, 'Y');
3984 aso_debug_pub.add(rpad('PRODUCT_ORG_ID',50,'------')||'------>'||i.PRODUCT_ORG_ID , 1, 'Y');
3985 aso_debug_pub.add(rpad('UOM_CODE',50,'------')||'------>'||i.UOM_CODE , 1, 'Y');
3986 aso_debug_pub.add(rpad('PRODUCT_TYPE',50,'------')||'------>'||i.PRODUCT_TYPE , 1, 'Y');
3987 aso_debug_pub.add(rpad('FOB_POINT',50,'------')||'------>'||i.FOB_POINT , 1, 'Y');
3988 aso_debug_pub.add(rpad('SHIP_TO_PARTY_ID',50,'------')||'------>'||i.SHIP_TO_PARTY_ID , 1, 'Y');
3989 aso_debug_pub.add(rpad('SHIP_FROM_PARTY_ID',50,'------')||'------>'||i.SHIP_FROM_PARTY_ID , 1, 'Y');
3990 aso_debug_pub.add(rpad('BILL_TO_PARTY_ID',50,'------')||'------>'||i.BILL_TO_PARTY_ID , 1, 'Y');
3991 aso_debug_pub.add(rpad('SHIP_TO_PARTY_SITE_ID',50,'------')||'------>'||i.SHIP_TO_PARTY_SITE_ID , 1, 'Y');
3992 aso_debug_pub.add(rpad('BILL_TO_PARTY_SITE_ID',50,'------')||'------>'||i.BILL_TO_PARTY_SITE_ID , 1, 'Y');
3993 aso_debug_pub.add(rpad('SHIP_TO_LOCATION_ID',50,'------')||'------>'||i.SHIP_TO_LOCATION_ID , 1, 'Y');
3994 aso_debug_pub.add(rpad('BILL_TO_LOCATION_ID',50,'------')||'------>'||i.BILL_TO_LOCATION_ID , 1, 'Y');
3995 aso_debug_pub.add(rpad('SHIP_FROM_LOCATION_ID',50,'------')||'------>'||i.SHIP_FROM_LOCATION_ID , 1, 'Y');
3996 aso_debug_pub.add(rpad('HISTORICAL_FLAG',50,'------')||'------>'||i.HISTORICAL_FLAG , 1, 'Y');
3997 aso_debug_pub.add(rpad('LINE_AMT_INCLUDES_TAX_FLAG',50,'------')||'------>'||i.LINE_AMT_INCLUDES_TAX_FLAG , 1, 'Y');
3998 aso_debug_pub.add(rpad('EXEMPTION_CONTROL_FLAG',50,'------')||'------>'||i.EXEMPTION_CONTROL_FLAG , 1, 'Y');
3999 aso_debug_pub.add(rpad('UNIT_PRICE',50,'------')||'------>'||i.UNIT_PRICE , 1, 'Y');
4000 aso_debug_pub.add(rpad('TRX_LINE_GL_DATE',50,'------')||'------>'||i.TRX_LINE_GL_DATE , 1, 'Y');
4001 aso_debug_pub.add(rpad('RECEIVABLES_TRX_TYPE_ID',50,'------')||'------>'||i.RECEIVABLES_TRX_TYPE_ID , 1, 'Y');
4002 aso_debug_pub.add(rpad('BILL_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.BILL_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
4003 aso_debug_pub.add(rpad('SHIP_TO_CUST_ACCT_SITE_USE_ID',50,'------')||'------>'||i.SHIP_TO_CUST_ACCT_SITE_USE_ID , 1, 'Y');
4004 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
4005 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_SITE_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_SITE_ID , 1, 'Y');
4006 aso_debug_pub.add(rpad('SHIP_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.SHIP_THIRD_PTY_ACCT_ID , 1, 'Y');
4007 aso_debug_pub.add(rpad('BILL_THIRD_PTY_ACCT_ID',50,'------')||'------>'||i.BILL_THIRD_PTY_ACCT_ID , 1, 'Y');
4008 aso_debug_pub.add(rpad('CTRL_HDR_TX_APPL_FLAG ',50,'------')||'------>'||i.CTRL_HDR_TX_APPL_FLAG , 1, 'Y');
4009 aso_debug_pub.add(rpad('TRX_LINE_NUMBER ',50,'------')||'------>'||i.TRX_LINE_NUMBER , 1, 'Y');
4010 aso_debug_pub.add(rpad('BILL_FROM_LOCATION_ID',50,'------')||'------>'||i.BILL_FROM_LOCATION_ID , 1, 'Y'); /*** Added for Bug 8474803 and 7408162 ***/
4011 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
4012 end loop;
4013 ELSIF (aso_debug_pub.g_debug_flag = 'Y' and rec=3) THEN
4014 for i in c3 loop
4015 aso_debug_pub.add( '******************************************',1, 'Y' );
4016 aso_debug_pub.add( '***** After insertion into Zx_detail_tax_lines_gt *****',1, 'Y' );
4017 aso_debug_pub.add( '******************************************',1, 'Y' );
4018 aso_debug_pub.add(rpad('TRX_ID',50,'------')||'------>'||i.TRX_ID , 1, 'Y');
4019 aso_debug_pub.add(rpad('TRX_LINE_ID',50,'------')||'------>'||i.TRX_LINE_ID , 1, 'Y');
4020 aso_debug_pub.add(rpad('TAX_RATE_CODE',50,'------')||'------>'||i.TAX_RATE_CODE , 1, 'Y');
4021 aso_debug_pub.add(rpad('TAX_RATE',50,'------')||'------>'||i.TAX_RATE , 1, 'Y');
4022 aso_debug_pub.add(rpad('TAX_DETERMINE_DATE',50,'------')||'------>'||i.TAX_DETERMINE_DATE , 1, 'Y');
4023 aso_debug_pub.add(rpad('TAX_AMT',50,'------')||'------>'||i.TAX_AMT , 1, 'Y');
4024 aso_debug_pub.add(rpad('EXEMPT_CERTIFICATE_NUMBER',50,'------')||'------>'||i.EXEMPT_CERTIFICATE_NUMBER , 1, 'Y');
4025 aso_debug_pub.add(rpad('EXEMPT_REASON_CODE',50,'------')||'------>'||i.EXEMPT_REASON_CODE , 1, 'Y');
4026 aso_debug_pub.add(rpad('TAX_AMT_INCLUDED_FLAG',50,'------')||'------>'||i.TAX_AMT_INCLUDED_FLAG , 1, 'Y');
4027 aso_debug_pub.add(rpad('OBJECT_VERSION_NUMBER',50,'------')||'------>'||i.OBJECT_VERSION_NUMBER , 1, 'Y');
4028 aso_debug_pub.add(rpad('TAX_RATE_ID',50,'------')||'------>'||i.TAX_RATE_ID , 1, 'Y');
4029 aso_debug_pub.add(rpad('*',100,'*'),1, 'Y' );
4030 end loop;
4031 END IF;
4032
4033 END print_tax_info;
4034
4035 End ASO_TAX_INT;