1 PACKAGE BODY OE_Party_TOTALS AS
2 /* $Header: OEXBTOTB.pls 120.1 2005/06/02 23:23:23 appldev $ */
3
4 -- Forward declaration of procedure
5
6 PROCEDURE Update_HZ_Parties(p_party_id IN NUMBER
7 , p_party_type IN VARCHAR2
8 , p_last_update_date IN DATE
9 , p_party_total IN NUMBER
10 , p_order_count IN NUMBER
11 , p_last_order_date IN DATE
12 , x_return_status OUT NOCOPY VARCHAR2
13 );
14
15
16 -- Start of Comments
17 -- API name Update_Party_Totals
18 -- Type Private
19 -- Function
20 --
21 -- Pre-reqs
22 --
23 -- Parameters
24 --
25 -- Version Current version = 1.0
26 -- Initial version = 1.0
27 --
28 -- Notes
29 --
30 -- End of Comments
31
32 PROCEDURE Update_Party_Totals(err_buff OUT NOCOPY VARCHAR2,
33 retcode out NOCOPY NUMBER)
34 IS
35 a_date DATE;
36 CURSOR C_PARTIES IS
37 SELECT DISTINCT a.party_id,
38 a.party_type,
39 a.last_update_date
40 FROM hz_cust_accounts b,
41 hz_parties a
42 WHERE b.party_id = a.party_id;
43
44 CURSOR C_ORDER_HEADER(p_party_id NUMBER) IS
45 SELECT a.header_id,
46 a.org_id,
47 a.order_number,
48 a.TRANSACTIONAL_CURR_CODE,
49 a.CONVERSION_RATE,
50 a.CONVERSION_TYPE_CODE,
51 a.sold_to_org_id,
52 a.CONVERSION_RATE_DATE,
53 a.ORDERED_DATE,
54 a.ORDER_CATEGORY_CODE
55 FROM hz_cust_accounts b,
56 oe_order_headers_all a
57 WHERE b.party_id = p_party_id
58 AND b.cust_account_id = a.sold_to_org_id
59 AND a.booked_flag = 'Y'
60 AND a.cancelled_flag = 'N';
61
62 CURSOR C_LINE_TOTALS(p_header_id NUMBER) IS
63 SELECT SUM(DECODE(l.line_category_code,'RETURN',-1,1)*
64 l.unit_selling_price*l.ordered_quantity)
65 FROM oe_order_lines_all l
66 WHERE l.header_id = p_header_id
67 AND l.cancelled_flag <> 'Y'
68 AND l.charge_periodicity_code IS NULL; -- Added for Recurring Charges
69
70 CURSOR C_ALL_CHARGES(p_header_id NUMBER) IS
71 SELECT SUM( DECODE(p.LINE_ID, NULL,
72 DECODE(p.CREDIT_OR_CHARGE_FLAG,'C',(-1) * p.OPERAND,p.OPERAND),
73 DECODE(p.CREDIT_OR_CHARGE_FLAG,'C',
74 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
75 (-1) * (P.OPERAND),
76 (-1) * (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
77 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
78 P.OPERAND,
79 (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
80 )))
81 FROM oe_price_adjustments p,
82 oe_order_lines_all l
83 WHERE p.header_id = p_header_id
84 AND p.list_line_type_code = 'FREIGHT_CHARGE'
85 AND p.applied_flag = 'Y'
86 AND p.line_id = l.line_id(+)
87 AND DECODE(p.line_id,NULL,'P',l.cancelled_flag)=
88 DECODE(p.line_id,NULL,'P','N')
89 AND l.charge_periodicity_code IS NULL; -- Added for Recurring Charges
90
91 l_cust_currency_code VARCHAR2(15);
92 l_sob_currency_code VARCHAR2(15);
93 l_return_status VARCHAR2(1);
94 l_conversion_type VARCHAR2(30);
95 l_last_order_date DATE := NULL;
96 l_conversion_rate NUMBER;
97 l_party_id NUMBER;
98 l_order_count NUMBER;
99 l_party_total NUMBER;
100 l_converted_total NUMBER;
101 l_converted_total_new NUMBER;
102 l_line_total NUMBER;
103 l_charge_total NUMBER;
104 l_commit_ctr NUMBER;
105 v_errcode NUMBER := 0;
106 v_errmsg VARCHAR2(500);
107
108 ERROR_IN_CURRENCY_CONVERSION EXCEPTION;
109
110 BEGIN
111
112 l_commit_ctr := 0;
113
114 -- Open the HZ_PARTY cursor for update;
115 l_cust_currency_code := fnd_profile.value('OM_CUST_TOTAL_CURRENCY');
116
117 IF l_cust_currency_code IS NULL THEN
118 oe_debug_pub.ADD('Profile OM: Party Total Currency is not set', 2);
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121 oe_debug_pub.ADD('The Party Total Currency is '||l_cust_currency_code, 2);
122 FOR C1 IN C_PARTIES LOOP
123 BEGIN
124 -- Open the Order Header Cursor
125 l_party_total := 0;
126 l_order_count := 0;
127 l_last_order_date := NULL;
128
129 FOR C2 IN C_ORDER_HEADER(p_party_id => C1.party_id) LOOP
130 BEGIN
131
132
133 -- Get Order Level Totals = LINE Total
134
135 OPEN C_LINE_TOTALS(C2.header_id);
136 FETCH C_LINE_TOTALS INTO l_line_total;
137 CLOSE C_LINE_TOTALS;
138
139 -- Get Total Charges (Freight and Special Charges)
140
141 OPEN C_ALL_CHARGES(C2.header_id);
142 FETCH C_ALL_CHARGES INTO l_charge_total;
143 CLOSE C_ALL_CHARGES;
144
145 -- Get the Set Of Books currency
146 l_sob_currency_code := OE_Upgrade_Misc.GET_SOB_CURRENCY(c2.org_id);
147
148 IF l_sob_currency_code IS NULL THEN
149 oe_debug_pub.ADD('Set Of Books currency does not exist for order '||to_char(c2.order_number), 2);
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 l_converted_total := NVL(l_line_total,0) +
154 NVL(l_charge_total,0);
155
156 IF C2.TRANSACTIONAL_CURR_CODE <> l_cust_currency_code
157 AND l_converted_total <> 0 THEN
158
159 -- Convert the Currency:
160 -- If the Order Currency is different than SOB currency then
161 -- convert the order currency to SOB currency.
162
163 IF C2.TRANSACTIONAL_CURR_CODE <> l_sob_currency_code THEN
164 OE_UPGRADE_MISC.CONVERT_CURRENCY(
165 l_converted_total,
166 C2.TRANSACTIONAL_CURR_CODE,
167 l_sob_currency_code,
168 C2.conversion_rate_date,
169 C2.conversion_rate,
170 C2.conversion_type_code,
171 l_return_status,
172 l_converted_total_new
173 );
174 IF l_return_status <> 'S' THEN
175 oe_debug_pub.ADD('Error in currency conversion from Order to SOB currency for order '||to_char(c2.order_number), 2);
176 RAISE ERROR_IN_CURRENCY_CONVERSION;
177 END IF;
178
179 if l_converted_total_new is not null then
180 l_converted_total := l_converted_total_new;
181 end if;
182
183 END IF;
184
185
186 -- If the SOB currency is different than the Customer Total
187 -- currency then convert the SOB currency to customer total
188 -- currency.
189 IF l_cust_currency_code <> l_sob_currency_code THEN
190
191 IF C2.conversion_type_code = 'User' THEN
192 l_conversion_type := 'Spot';
193 l_conversion_rate := NULL;
194 ELSE
195 l_conversion_type := C2.conversion_type_code;
196 l_conversion_rate := C2.conversion_rate;
197 END IF;
198
199 OE_UPGRADE_MISC.CONVERT_CURRENCY(
200 l_converted_total_new,
201 l_sob_currency_code,
202 l_cust_currency_code,
203 C2.conversion_rate_date,
204 l_conversion_rate,
205 l_conversion_type,
206 l_return_status,
207 l_converted_total_new
208 );
209 IF l_return_status <> 'S' THEN
210 oe_debug_pub.ADD('Error in currency conversion from SOB to Party Total currency for order '||to_char(c2.order_number), 2);
211 RAISE ERROR_IN_CURRENCY_CONVERSION;
212 END IF;
213 if l_converted_total_new is not null then
214 l_converted_total := l_converted_total_new;
215 end if;
216 END IF;
217
218 END IF;
219
220 l_party_total := l_party_total + l_converted_total;
221
222 IF C2.ORDER_CATEGORY_CODE <> 'RETURN' THEN
223
224 -- Add the Order Total Counter
225 l_order_count := l_order_count + 1;
226
227 -- Get the Last Order Date
228 IF l_last_order_date IS NULL THEN
229 l_last_order_date := C2.ORDERED_DATE;
230 END IF;
231
232 IF C2.ORDERED_DATE > l_last_order_date THEN
233 l_last_order_date := C2.ORDERED_DATE;
234 END IF;
235
236 END IF;
237 EXCEPTION
238 WHEN OTHERS THEN
239 v_errcode := SQLCODE;
240 v_errmsg := SQLERRM;
241 oe_debug_pub.ADD('Error in processing Order '||to_char(c2.order_number) || ' SQL error is '|| to_char(v_errcode) || v_errmsg, 2);
242 END;
243 END LOOP; -- End Loop For C_ORDER_HEADER
244
245 IF NOT (l_party_total = 0 AND l_order_count = 0 AND
246 l_last_order_date IS NULL)
247 THEN
248
249 IF l_order_count = 0 THEN
250 l_order_count := NULL;
251 END IF;
252
253 oe_debug_pub.ADD('The Party Total was '||to_char(l_party_total), 2);
254 Update_HZ_Parties(p_party_id => C1.party_id
255 , p_party_type => C1.party_type
256 , p_last_update_date => C1.last_update_date
257 , p_party_total => l_party_total
258 , p_order_count => l_order_count
259 , p_last_order_date => l_last_order_date
260 , x_return_status => l_return_status
261 );
262 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263 oe_debug_pub.ADD('Error in updating the Party record for party '||to_char(c1.party_id), 2);
264 END IF;
265 END IF;
266
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 oe_debug_pub.ADD('No Order for PARTY '||to_char(C1.party_id), 2);
270
271 WHEN OTHERS THEN
272 v_errcode := SQLCODE;
273 v_errmsg := SQLERRM;
274 oe_debug_pub.ADD('Error in processing Party '||to_char(c1.party_id) || ' SQL error is '|| to_char(v_errcode) || v_errmsg, 2);
275 END;
276 l_commit_ctr := l_commit_ctr + 1;
277 IF l_commit_ctr > 500 THEN
278 commit;
279 l_commit_ctr := 0;
280 END IF;
281
282 END LOOP; -- End Loop For C_PARTIES
283 -- set return status
284 err_buff := '';
285 retcode := 0;
286 commit;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 v_errcode := SQLCODE;
291 v_errmsg := SQLERRM;
292 oe_debug_pub.ADD('Error in processing Parties '|| ' SQL error is '|| to_char(v_errcode) || v_errmsg, 2);
293 retcode := 2;
294 END Update_Party_Totals;
295
296 PROCEDURE Update_HZ_Parties(p_party_id IN NUMBER
297 , p_party_type IN VARCHAR2
298 , p_last_update_date IN DATE
299 , p_party_total IN NUMBER
300 , p_order_count IN NUMBER
301 , p_last_order_date IN DATE
302 , x_return_status OUT NOCOPY VARCHAR2
303 )
304 IS
305 v_errcode NUMBER := 0;
306 v_errmsg VARCHAR2(500);
307 l_profile_id NUMBER;
308 l_msg_count NUMBER;
309 l_msg_data VARCHAR2(2000);
310 l_return_status VARCHAR2(1);
311 l_rel_date DATE := sysdate;
312 l_date DATE := p_last_update_date;
313
314 BEGIN
315
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 IF p_party_type = 'PERSON' OR
319 p_party_type = 'GROUP' OR
320 p_party_type = 'PARTY_RELATIONSHIP' OR
321 p_party_type = 'ORGANIZATION' THEN
322
323 UPDATE hz_parties
324 SET total_num_of_orders = p_order_count,
325 total_ordered_amount = p_party_total,
326 last_ordered_date = p_last_order_date
327 WHERE party_id = p_party_id;
328
329 ELSE
330 oe_debug_pub.ADD('Invalid Party type for PARTY '||to_char(p_party_id), 2);
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 END IF;
333
334 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335
336 If l_msg_count > 0 THEN
337
338 FOR k in 1 .. l_msg_count LOOP
339 l_msg_data := fnd_msg_pub.get( p_msg_index => k,
340 p_encoded => 'F');
341 oe_debug_pub.add(substr(l_msg_data,1,255),2);
342 END LOOP;
343
344 END IF;
345 ELSE
346 oe_debug_pub.ADD('Success in Processing PARTY '||to_char(p_party_id), 2);
347 END IF;
348 x_return_status := l_return_status;
349 EXCEPTION
350 WHEN OTHERS THEN
351 v_errcode := SQLCODE;
352 v_errmsg := SQLERRM;
353 oe_debug_pub.ADD('Error in updating the Party '||to_char(p_party_id) || ' SQL error is '|| to_char(v_errcode) || v_errmsg, 2);
354 x_return_status := FND_API.G_RET_STS_ERROR;
355
356 END Update_HZ_Parties;
357
358 END OE_Party_TOTALS;
[Home] [Help]