[Home] [Help]
PACKAGE BODY: APPS.ONT_UPG_TAX_UTIL
Source
1 PACKAGE BODY ONT_UPG_TAX_UTIL AS
2 /* $Header: OEXUPGTB.pls 120.3 2006/02/17 16:35:06 aycui ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ONT_UPG_TAX_UTIL';
7 G_BATCH_SIZE CONSTANT NUMBER := 500;
8 G_HEADER_SIZE CONSTANT NUMBER := 5000;
9 G_BATCH_COUNT NUMBER := 0;
10
11 Procedure calculate_order_tax
12 ( p_org_id IN NUMBER
13 , p_start_date IN DATE
14 , p_end_date IN DATE
15 , x_return_status OUT NOCOPY VARCHAR2
16
17 ) IS
18
19 Type header_id_tab Is table of number index by Binary_integer;
20
21 header_id_t header_id_tab;
22
23 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
24 l_end_date date := sysdate;
25 --bug 4149275
26 l_msg_count number;
27 l_msg_data Varchar2(2000);
28
29 cursor headers is
30 select /* MOAC_SQL_CHANGE */ h.header_id
31 from oe_order_headers h
32 where h.creation_date between
33 trunc(nvl(p_start_date, add_months(sysdate, -10000)))
34 and trunc(nvl(l_end_date, add_months(sysdate, +10000)))
35 and h.upgraded_flag = 'Y'
36 AND EXISTS (SELECT 'x'
37 FROM oe_order_lines_all l
38 WHERE l.header_id = h.header_id
39 AND l.org_id = h.org_id
40 AND l.tax_code <> 'Exempt'
41 AND l.tax_exempt_flag <> 'E'
42 AND l.tax_rate is null
43 AND l.TAX_VALUE is null);
44
45 /* =========================================================================== */
46 /* In the above cursor query 'EXISTS' is added for the performance bug 3056892 */
47 /* =========================================================================== */
48
49 --
50 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
51 --
52 begin
53
54 x_return_status := l_return_status;
55 l_end_date := p_end_date + 1;
56
57
58 open headers;
59 OE_MSG_PUB.Initialize;
60 loop
61
62 IF l_debug_level > 0 THEN
63 oe_debug_pub.add( 'IN HEADERS' , 1 ) ;
64 END IF;
65
66 header_id_t.delete;
67
68 IF l_debug_level > 0 THEN
69 oe_debug_pub.add( 'IN HEADERS: 1' , 1 ) ;
70 END IF;
71
72 FETCH headers BULK COLLECT INTO header_id_t
73 Limit G_HEADER_SIZE;
74
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( 'IN HEADERS: 2' , 1 ) ;
77 END IF;
78
79 IF header_id_t.first is not null THEN
80
81 IF l_debug_level > 0 THEN
82 oe_debug_pub.add( 'INSIDE THE HEADER_ID_T.FIRST NOT NULL' , 1 ) ;
83 END IF;
84
85 FOR K in header_id_t.first..header_id_t.last LOOP
86
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add( 'HEADER ID IS : ' || HEADER_ID_T ( K ) , 1 ) ;
89 END IF;
90
91 OE_MSG_PUB.set_msg_context(
92 p_entity_code => 'HEADER'
93 ,p_entity_id => HEADER_ID_T ( K )
94 ,p_header_id => HEADER_ID_T ( K )
95 ,p_line_id => null
96 ,p_orig_sys_document_ref => null
97 ,p_orig_sys_document_line_ref => null
98 ,p_change_sequence => null
99 ,p_source_document_id => null
100 ,p_source_document_line_id => null
101 ,p_order_source_id => null
102 ,p_source_document_type_id => null);
103
104 --call om_tax_util.calculate_tax to fix bug 3056892
105 --Process_Tax
106 OM_TAX_UTIL.CALCULATE_TAX( p_header_id => header_id_t(K)
107 , x_return_status => l_return_status);
108
109
110 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
113 OE_MSG_PUB.Count_And_Get (p_count => l_msg_count
114 ,p_data => l_msg_data);
115 oe_debug_pub.add( 'MESSAGES RETURNED: '|| TO_CHAR ( l_msg_count ),1 ) ;
116 if l_msg_count > 0 then
117 OE_MSG_PUB.Add_text('Tax calculation failed for Header Id :'|| header_id_t(K));
118 l_msg_data := 'Error message for header_id ' || header_id_t(K) ||' is : '||l_msg_data;
119 IF l_debug_level > 0 THEN
120 oe_debug_pub.add( l_msg_data,1 ) ;
121 END IF;
122 end if;
123 --RAISE FND_API.G_EXC_ERROR;
124 END IF;
125 IF l_debug_level > 0 THEN
126 oe_debug_pub.add(' Save the Messages for Header: '|| header_id_t(K), 1);
127 END IF;
128 OE_MSG_PUB.save_UI_messages (0,'C');
129
130 END LOOP; /* for loop */
131
132 END IF; /* if header_id_t.first is not null */
133
134 COMMIT;
135
136 EXIT when headers%NOTFOUND;
137
138 end loop; /* loop of headers cursor */
139
140 close headers;
141
142 EXCEPTION
143
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146
147 WHEN FND_API.G_EXC_ERROR THEN
148 x_return_status := FND_API.G_RET_STS_ERROR;
149
150 WHEN NO_DATA_FOUND THEN
151 IF l_debug_level > 0 THEN
152 oe_debug_pub.add( 'TAX_ORDER: IN NO DATA FOUND' , 4 ) ;
153 END IF;
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 WHEN OTHERS THEN
157
158 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
159 THEN
160 OE_MSG_PUB.Add_Exc_Msg
161 ( G_PKG_NAME ,
162 'calculate_order_tax'
163 );
164 END IF;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167
168 end calculate_order_tax;
169
170 END ONT_UPG_TAX_UTIL;