[Home] [Help]
PACKAGE BODY: APPS.OZF_SALES_TRANSACTIONS_PVT
Source
1 PACKAGE BODY OZF_SALES_TRANSACTIONS_PVT AS
2 /* $Header: ozfvstnb.pls 120.15.12020000.2 2013/02/11 06:29:56 annsrini ship $ */
3
4 -- Package name : OZF_SALES_TRANSACTIONS_PVT
5 -- Purpose :
6 -- History :
7 -- 24/NOV/2008 - nirprasa Fixed bug 7030415
8 -- 01/DEC/2008 - nirprasa Fixed bug 6808124
9 -- 9/16/2010 - nepanda Fixed bug 10120042
10 -- 13/DEC/2012 - annsrini Fixed bug 14761031
11 -- NOTE :
12 -- End of Comments
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SALES_TRANSACTIONS_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvstnb.pls';
16
17 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
18 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
19
20 G_COMMON_UOM_CODE VARCHAR2(30) := FND_PROFILE.value('OZF_TP_COMMON_UOM');
21 G_COMMON_CURRENCY_CODE VARCHAR2(15) := FND_PROFILE.value('OZF_TP_COMMON_CURRENCY');
22 ---------------------------------------------------------------------
23 -- PROCEDURE
24 -- Validate_transaction
25 --
26 -- PURPOSE
27 -- Validate a transaction record.
28 --
29 -- PARAMETERS
30 -- p_transaction : the transaction code record to be validated
31 --
32 -- NOTES
33 --
34 ----------------------------------------------------------------------
35 PROCEDURE Validate_Transaction (
36 p_api_version IN NUMBER
37 ,p_init_msg_list IN VARCHAR2
38 ,p_validation_level IN NUMBER
39 ,x_return_status OUT NOCOPY VARCHAR2
40 ,x_msg_count OUT NOCOPY NUMBER
41 ,x_msg_data OUT NOCOPY VARCHAR2
42 ,p_transaction IN SALES_TRANSACTION_REC_TYPE
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Validate_transaction';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
48 --
49 l_return_status VARCHAR2(30);
50 l_msg_data VARCHAR2(2000);
51 l_msg_count number;
52
53 -- nepanda : Fix for Bug 10120042
54 CURSOR c_ship_to_org_id(p_line_id IN NUMBER) IS
55 SELECT ship_to_org_id
56 FROM oe_order_lines_all
57 WHERE line_id = p_line_id;
58
59 l_ship_to_site_use_id NUMBER;
60 BEGIN
61 -- Standard begin of API savepoint
62 SAVEPOINT Validate_trans_PVT;
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call (
65 l_api_version,
66 p_api_version,
67 l_api_name,
68 G_PKG_NAME)
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72
73 --Initialize message list if p_init_msg_list is TRUE.
74 IF FND_API.To_Boolean (p_init_msg_list) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 -- Debug Message
79 IF OZF_DEBUG_LOW_ON THEN
80 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
81 END IF;
82
83 -- Initialize API return status to sucess
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 If p_transaction.source_code = 'OM' THEN
87 IF OZF_DEBUG_LOW_ON THEN
88 ozf_utility_PVT.debug_message('IN OM:' );
89 ozf_utility_PVT.debug_message('sold_to_cust_account_id: '||p_transaction.sold_to_cust_account_id);
90 ozf_utility_PVT.debug_message('sold_to_party_id: '||p_transaction.sold_to_party_id);
91 ozf_utility_PVT.debug_message('bill_to_site_use_id: '||p_transaction.bill_to_site_use_id);
92 ozf_utility_PVT.debug_message('ship_to_site_use_id: '||p_transaction.ship_to_site_use_id);
93 ozf_utility_PVT.debug_message('header_id: '||p_transaction.header_id);
94 ozf_utility_PVT.debug_message('line_id: '||p_transaction.line_id);
95 END IF;
96
97 -- nepanda : Fix for Bug 10120042 : If ship to org id is coming null from ASO message, then get it from table.
98 -- This condition will work only if oe_order_lines table has a not null value in ship_to_org id column.
99 IF p_transaction.ship_to_site_use_id is NULL THEN
100 OPEN c_ship_to_org_id(p_transaction.line_id);
101 FETCH c_ship_to_org_id INTO l_ship_to_site_use_id;
102 CLOSE c_ship_to_org_id;
103 ELSE
104 l_ship_to_site_use_id := p_transaction.ship_to_site_use_id;
105 END IF;
106 IF p_transaction.sold_to_cust_account_id is NULL OR
107 p_transaction.sold_to_party_id is NULL OR
108 p_transaction.bill_to_site_use_id is NULL OR
109 l_ship_to_site_use_id is NULL THEN -- nepanda : Fix for bug 10120042
110
111 ozf_utility_PVT.error_message('OZF_TRANS_BILLTO_NULL');
112 RAISE FND_API.g_exc_unexpected_error;
113 END IF;
114
115 IF p_transaction.header_id is NULL OR
116 p_transaction.line_id is NULL THEN
117
118 ozf_utility_PVT.error_message('OZF_TRANS_ORDER_REF_NULL');
119 RAISE FND_API.g_exc_unexpected_error;
120 END IF;
121
122 ELSIF p_transaction.source_code = 'IS' THEN
123 IF OZF_DEBUG_LOW_ON THEN
124 ozf_utility_PVT.debug_message('IN IS:' );
125 ozf_utility_PVT.debug_message('sold_from_party_id: '||p_transaction.sold_from_party_id);
126 ozf_utility_PVT.debug_message('header_id: '||p_transaction.header_id);
127 ozf_utility_PVT.debug_message('line_id: '||p_transaction.line_id);
128 END IF;
129
130 IF p_transaction.sold_from_party_id is null THEN
131
132 ozf_utility_PVT.error_message('OZF_TRANS_SOLDFROM_NULL');
133 RAISE FND_API.g_exc_unexpected_error;
134 END IF;
135
136 IF p_transaction.header_id is NULL OR
137 p_transaction.line_id is NULL THEN
138
139 ozf_utility_PVT.error_message('OZF_TRANS_ORDER_REF_NULL');
140 RAISE FND_API.g_exc_unexpected_error;
141 END IF;
142
143 ELSIF p_transaction.source_code = 'MA' THEN
144 IF OZF_DEBUG_LOW_ON THEN
145 ozf_utility_PVT.debug_message('IN MA:' );
146 ozf_utility_PVT.debug_message('sold_to_party_id: '||p_transaction.sold_to_party_id);
147 END IF;
148
149 IF p_transaction.sold_to_party_id is null THEN
150 OZF_UTILITY_PVT.error_message('OZF_TRANS_SOLD_TO_PTY_NULL');
151 RAISE FND_API.g_exc_unexpected_error;
152 END IF;
153 ELSE
154 OZF_UTILITY_PVT.error_message('OZF_TRANS_SOURCE_CD_WRG');
155 RAISE FND_API.g_exc_unexpected_error;
156 END IF;
157
158 -- Debug Message
159 IF OZF_DEBUG_LOW_ON THEN
160 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
161 END IF;
162 --Standard call to get message count and if count=1, get the message
163 FND_MSG_PUB.Count_And_Get (
164 p_encoded => FND_API.G_FALSE,
165 p_count => x_msg_count,
166 p_data => x_msg_data
167 );
168 EXCEPTION
169 WHEN FND_API.G_EXC_ERROR THEN
170 ROLLBACK TO Validate_trans_PVT;
171 x_return_status := FND_API.G_RET_STS_ERROR;
172 -- Standard call to get message count and if count=1, get the message
173 FND_MSG_PUB.Count_And_Get (
174 p_encoded => FND_API.G_FALSE,
175 p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 ROLLBACK TO Validate_trans_PVT;
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 -- Standard call to get message count and if count=1, get the message
182 FND_MSG_PUB.Count_And_Get (
183 p_encoded => FND_API.G_FALSE,
184 p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187 WHEN OTHERS THEN
188 ROLLBACK TO Validate_trans_PVT;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
191 THEN
192 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
193 END IF;
194 -- Standard call to get message count and if count=1, get the message
195 FND_MSG_PUB.Count_And_Get (
196 p_encoded => FND_API.G_FALSE,
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200 END Validate_Transaction;
201
202 ---------------------------------------------------------------------
203 -- PROCEDURE
204 -- create_transaction
205 --
206 -- PURPOSE
207 -- This procedure creates an transaction
208 --
209 -- PARAMETERS
210 --
211 --
212 -- NOTES
213 ---------------------------------------------------------------------
214 PROCEDURE Create_Transaction (
215 p_api_version IN NUMBER
216 ,p_init_msg_list IN VARCHAR2
217 ,p_commit IN VARCHAR2
218 ,p_validation_level IN NUMBER
219 ,p_transaction_rec IN SALES_TRANSACTION_REC_TYPE
220 ,x_sales_transaction_id OUT NOCOPY NUMBER
221 ,x_return_status OUT NOCOPY VARCHAR2
222 ,x_msg_data OUT NOCOPY VARCHAR2
223 ,x_msg_count OUT NOCOPY NUMBER
224 )
225 IS
226 l_api_name CONSTANT VARCHAR2(30) := 'create_transaction';
227 l_api_version CONSTANT NUMBER := 1.0;
228 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
229 --
230 l_return_status VARCHAR2(30);
231 l_msg_data VARCHAR2(2000);
232 l_msg_count number;
233
234 l_transaction_rec SALES_TRANSACTION_REC_TYPE := p_transaction_rec;
235 CURSOR primay_uom_code_csr(p_id in number) is
236 select primary_uom_code
237 from mtl_system_items
238 where inventory_item_id = p_id;
239
240 l_primary_uom_code VARCHAR2(30);
241
242 l_common_uom_code VARCHAR2(30);
243 l_common_currency_code VARCHAR2(30);
244
245 CURSOR transaction_id_csr is
246 select ozf_Sales_Transactions_all_s.nextval
247 from dual;
248 l_sales_transaction_id number;
249
250 CURSOR sales_transation_csr(p_line_id NUMBER,p_source_code VARCHAR2) IS
251 SELECT 1 FROM DUAL WHERE EXISTS
252 ( SELECT 1
253 FROM ozf_sales_transactions_all trx
254 WHERE trx.line_id = p_line_id
255 AND source_code = nvl(p_source_code,'OM')); --fix for bug 6808124
256 --Added for bug 7030415
257 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
258 SELECT exchange_rate_type
259 FROM ozf_sys_parameters_all
260 WHERE org_id = p_org_id;
261
262 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
263 l_rate NUMBER;
264 l_vol_offr_apply_discount NUMBER;
265 l_sales_trans NUMBER;
266 BEGIN
267 -- Standard begin of API savepoint
268 SAVEPOINT CREATE_TRANSACTION;
269 -- Standard call to check for call compatibility.
270 IF NOT FND_API.Compatible_API_Call (
271 l_api_version,
272 p_api_version,
273 l_api_name,
274 G_PKG_NAME)
275 THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278
279 --Initialize message list if p_init_msg_list is TRUE.
280 IF FND_API.To_Boolean (p_init_msg_list) THEN
281 FND_MSG_PUB.initialize;
282 END IF;
283
284 -- Debug Message
285 IF OZF_DEBUG_LOW_ON THEN
286 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
287 END IF;
288
289 -- Initialize API return status to sucess
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 IF OZF_DEBUG_LOW_ON THEN
293 OZF_UTILITY_PVT.debug_message('inventory_item_id:' ||l_transaction_rec.inventory_item_id);
294 OZF_UTILITY_PVT.debug_message('transaction_date:' ||l_transaction_rec.transaction_date);
295 OZF_UTILITY_PVT.debug_message('quantity:' ||l_transaction_rec.quantity);
296 OZF_UTILITY_PVT.debug_message('uom_code:' ||l_transaction_rec.uom_code );
297 END IF;
298
299 IF l_transaction_rec.inventory_item_id is null OR
300 l_transaction_rec.transaction_date is null OR
301 l_transaction_rec.quantity is null OR
302 l_transaction_rec.uom_code is null THEN
303
304 OZF_UTILITY_PVT.error_message('OZF_SALES_TRANS_MISS');
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 -- Validate the record
309 Validate_Transaction (
310 p_api_version => 1.0
311 ,p_init_msg_list => FND_API.G_FALSE
312 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
313 ,x_return_status => l_return_status
314 ,x_msg_count => l_msg_count
315 ,x_msg_data => l_msg_data
316 ,p_transaction => l_transaction_rec
317 );
318 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
319 RAISE FND_API.G_EXC_ERROR;
320 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 -- Default transfer_type if necessary
325 IF l_transaction_rec.transfer_type IS NULL THEN
326 IF l_transaction_rec.quantity > 0 THEN
327 l_transaction_rec.transfer_type := 'IN';
328 ELSE
329 l_transaction_rec.transfer_type := 'OUT';
330 l_transaction_rec.quantity := abs( l_transaction_rec.quantity );
331 END IF;
332 ELSE
333 l_transaction_rec.quantity := abs( l_transaction_rec.quantity );
334 END IF;
335
336 IF OZF_DEBUG_LOW_ON THEN
337 ozf_utility_PVT.debug_message('start conversion');
338 END IF;
339
340 l_transaction_rec.error_flag := 'N';
341 -- uom_code conversion
342
343 -- check whether the primanay uom_code = uom_code
344 -- If not convert quantity
345 IF l_transaction_rec.primary_uom_code is null or l_transaction_rec.primary_quantity is null THEN
346 OPEN primay_uom_code_csr(l_transaction_rec.inventory_item_id);
347 FETCH primay_uom_code_csr into l_primary_uom_code;
348 CLOSE primay_uom_code_csr;
349
350 IF l_primary_uom_code = l_transaction_rec.uom_code THEN
351
352 l_transaction_rec.primary_uom_code := l_transaction_rec.uom_code;
353 l_transaction_rec.primary_quantity := l_transaction_rec.quantity;
354 ELSE
355
356 l_transaction_rec.primary_quantity := inv_convert.inv_um_convert(
357 l_transaction_rec.inventory_item_id,
358 null,
359 l_transaction_rec.quantity,
360 l_transaction_rec.uom_code,
361 l_primary_uom_code,
362 null, null);
363 IF l_transaction_rec.primary_quantity = -99999 THEN
364 l_transaction_rec.primary_quantity := null;
365 l_transaction_rec.primary_uom_code := null;
366 l_transaction_rec.error_flag := 'Y';
367 ELSE
368 l_transaction_rec.primary_uom_code := l_primary_uom_code;
369 END IF;
370 END IF;
371 END IF;
372 IF OZF_DEBUG_LOW_ON THEN
373 ozf_utility_PVT.debug_message('primary_uom_code: '||l_transaction_rec.primary_uom_code);
374 ozf_utility_PVT.debug_message('primary_quantity: '||l_transaction_rec.primary_quantity);
375 ozf_utility_PVT.debug_message('error_flag: '||l_transaction_rec.error_flag);
376 END IF;
377 -- Second check whether uom_code = common uom_code
378 -- If not convert quantity
379 IF l_transaction_rec.common_uom_code is null or l_transaction_rec.common_quantity is null THEN
380 l_common_uom_code := G_COMMON_UOM_CODE; --fnd_profile.value('OZF_TP_COMMON_UOM');
381 IF l_common_uom_code = l_transaction_rec.uom_code THEN
382
383 l_transaction_rec.common_uom_code := l_transaction_rec.uom_code;
384 l_transaction_rec.common_quantity := l_transaction_rec.quantity;
385 ELSE
386
387 l_transaction_rec.common_uom_code := l_common_uom_code;
388 l_transaction_rec.common_quantity := inv_convert.inv_um_convert(
389 l_transaction_rec.inventory_item_id,
390 null,
391 l_transaction_rec.quantity,
392 l_transaction_rec.uom_code,
393 l_common_uom_code,
394 null, null);
395 IF l_transaction_rec.common_quantity = -99999 THEN
396 l_transaction_rec.common_quantity := null;
397 l_transaction_rec.common_uom_code := null;
398 l_transaction_rec.error_flag := 'Y';
399 ELSE
400 l_transaction_rec.common_uom_code := l_common_uom_code;
401 END IF;
402 END IF;
403 END IF;
404 IF OZF_DEBUG_LOW_ON THEN
405 ozf_utility_PVT.debug_message('common_code: '||l_transaction_rec.common_uom_code);
406 ozf_utility_PVT.debug_message('common_quantity: '||l_transaction_rec.common_quantity);
407 ozf_utility_PVT.debug_message('error_flag: '||l_transaction_rec.error_flag);
408 END IF;
409
410 -- Third check whether common currency_code =
411 -- If not convert currency code
412 IF l_transaction_rec.currency_code is not null AND
413 l_transaction_rec.amount is not null THEN
414 IF l_transaction_rec.common_CURRENCY_CODE is null or
415 l_transaction_rec.common_amount is null THEN
416 l_common_currency_code := G_COMMON_CURRENCY_CODE; --fnd_profile.value('OZF_TP_COMMON_CURRENCY');
417 IF l_common_currency_code = l_transaction_rec.currency_code THEN
418
419 l_transaction_rec.common_currency_code := l_transaction_rec.currency_code;
420 l_transaction_rec.common_amount := l_transaction_rec.amount;
421 ELSE
422 --Added for bug 7030415
423 OPEN c_get_conversion_type(l_transaction_rec.org_id);
424 FETCH c_get_conversion_type INTO l_exchange_rate_type;
425 CLOSE c_get_conversion_type;
426 l_transaction_rec.common_currency_code := l_common_currency_code;
427 ozf_utility_pvt.Convert_Currency (
428 x_return_status => l_return_status,
429 p_from_currency => l_transaction_rec.currency_code,
430 p_to_currency => l_common_currency_code,
431 p_conv_type => l_exchange_rate_type,
432 p_conv_date => l_transaction_rec.transaction_date,
433 p_from_amount => l_transaction_rec.amount,
434 x_to_amount => l_transaction_rec.common_amount,
435 x_rate => l_rate
436 );
437 END IF;
438 END IF;
439 END IF;
440
441 IF OZF_DEBUG_LOW_ON THEN
442 ozf_utility_PVT.debug_message('common_currency_code: '||l_transaction_rec.common_currency_code);
443 ozf_utility_PVT.debug_message('common_amount: '||l_transaction_rec.common_amount);
444 ozf_utility_PVT.debug_message('error_flag: '||l_transaction_rec.error_flag);
445 END IF;
446
447 --fix for bug 6808124
448 OPEN sales_transation_csr(l_transaction_rec.LINE_ID,l_transaction_rec.source_code);
449 FETCH sales_transation_csr INTO l_sales_trans;
450 CLOSE sales_transation_csr;
451
452 --22-FEB-2007 bug 5610124 - create sales transaction record if it doesn't exist
453 IF NVL(l_sales_trans,0) <> 1 THEN
454
455 OPEN transaction_id_csr;
456 FETCH transaction_id_csr INTO l_sales_transaction_id;
457 CLOSE transaction_id_csr;
458
459 insert into ozf_sales_transactions_all(
460 Sales_Transaction_id,
461 OBJECT_VERSION_NUMBER,
462 LAST_UPDATE_DATE,
463 LAST_UPDATED_BY,
464 CREATION_DATE,
465 REQUEST_ID,
466 CREATED_BY,
467 CREATED_FROM,
468 LAST_UPDATE_LOGIN,
469 PROGRAM_APPLICATION_ID,
470 PROGRAM_UPDATE_DATE,
471 PROGRAM_ID,
472 SOLD_FROM_CUST_ACCOUNT_ID,
473 SOLD_FROM_PARTY_ID,
474 SOLD_FROM_PARTY_SITE_ID,
475 SOLD_TO_CUST_ACCOUNT_ID,
476 SOLD_TO_PARTY_ID,
477 SOLD_TO_PARTY_SITE_ID,
478 BILL_TO_SITE_USE_ID,
479 SHIP_TO_SITE_USE_ID,
480 TRANSACTION_DATE,
481 TRANSFER_TYPE,
482 QUANTITY,
483 uom_code,
484 AMOUNT,
485 CURRENCY_CODE,
486 INVENTORY_ITEM_ID,
487 PRIMARY_QUANTITY,
488 PRIMARY_uom_code,
489 AVAILABLE_PRIMARY_QUANTITY,
490 COMMON_QUANTITY,
491 COMMON_uom_code,
492 COMMON_CURRENCY_CODE,
493 COMMON_AMOUNT,
494 HEADER_ID,
495 LINE_ID,
496 REASON_CODE,
497 SOURCE_CODE,
498 ERROR_FLAG,
499 ATTRIBUTE_CATEGORY,
500 ATTRIBUTE1,
501 ATTRIBUTE2,
502 ATTRIBUTE3,
503 ATTRIBUTE4 ,
504 ATTRIBUTE5,
505 ATTRIBUTE6,
506 ATTRIBUTE7,
507 ATTRIBUTE8,
508 ATTRIBUTE9,
509 ATTRIBUTE10,
510 ATTRIBUTE11,
511 ATTRIBUTE12,
512 ATTRIBUTE13,
513 ATTRIBUTE14 ,
514 ATTRIBUTE15,
515 org_id
516 ) values (
517 l_sales_transaction_id,
518 1.0,
519 sysdate,
520 NVL(FND_GLOBAL.user_id,-1),
521 sysdate,
522 FND_GLOBAL.CONC_REQUEST_ID,
523 NVL(FND_GLOBAL.user_id,-1),
524 NULL,
525 NVL(FND_GLOBAL.conc_login_id,-1),
526 FND_GLOBAL.PROG_APPL_ID,
527 sysdate,
528 FND_GLOBAL.CONC_PROGRAM_ID,
529 l_transaction_rec.SOLD_FROM_CUST_ACCOUNT_ID,
530 l_transaction_rec.SOLD_FROM_PARTY_ID,
531 l_transaction_rec.SOLD_FROM_PARTY_SITE_ID,
532 l_transaction_rec.SOLD_TO_CUST_ACCOUNT_ID,
533 l_transaction_rec.SOLD_TO_PARTY_ID,
534 l_transaction_rec.SOLD_TO_PARTY_SITE_ID,
535 l_transaction_rec.BILL_TO_SITE_USE_ID,
536 l_transaction_rec.SHIP_TO_SITE_USE_ID,
537 TRUNC(l_transaction_rec.TRANSACTION_DATE),
538 l_transaction_rec.TRANSFER_TYPE,
539 l_transaction_rec.QUANTITY,
540 l_transaction_rec.uom_code,
541 l_transaction_rec.AMOUNT,
542 l_transaction_rec.CURRENCY_CODE,
543 l_transaction_rec.INVENTORY_ITEM_ID,
544 l_transaction_rec.PRIMARY_QUANTITY,
545 l_transaction_rec.PRIMARY_uom_code,
546 l_transaction_rec.PRIMARY_QUANTITY,
547 l_transaction_rec.COMMON_QUANTITY,
548 l_transaction_rec.COMMON_uom_code,
549 l_transaction_rec.COMMON_CURRENCY_CODE,
550 l_transaction_rec.COMMON_AMOUNT,
551 l_transaction_rec.HEADER_ID,
552 l_transaction_rec.LINE_ID,
553 l_transaction_rec.REASON_CODE,
554 l_transaction_rec.SOURCE_CODE,
555 l_transaction_rec.ERROR_FLAG,
556 l_transaction_rec.ATTRIBUTE_CATEGORY,
557 l_transaction_rec.ATTRIBUTE1,
558 l_transaction_rec.ATTRIBUTE2,
559 l_transaction_rec.ATTRIBUTE3,
560 l_transaction_rec.ATTRIBUTE4 ,
561 l_transaction_rec.ATTRIBUTE5,
562 l_transaction_rec.ATTRIBUTE6,
563 l_transaction_rec.ATTRIBUTE7,
564 l_transaction_rec.ATTRIBUTE8,
565 l_transaction_rec.ATTRIBUTE9,
566 l_transaction_rec.ATTRIBUTE10,
567 l_transaction_rec.ATTRIBUTE11,
568 l_transaction_rec.ATTRIBUTE12,
569 l_transaction_rec.ATTRIBUTE13,
570 l_transaction_rec.ATTRIBUTE14 ,
571 l_transaction_rec.ATTRIBUTE15,
572 l_transaction_rec.org_id
573 );
574
575 x_sales_transaction_id := l_sales_transaction_id;
576 IF OZF_DEBUG_LOW_ON THEN
577 ozf_utility_PVT.debug_message('insert done' || l_sales_transaction_id);
578 END IF;
579
580 END IF; --IF NVL(l_sales_trans,0) <> 1 THEN
581
582
583 IF l_transaction_rec.SOURCE_CODE = 'OM' THEN
584 OZF_VOLUME_CALCULATION_PUB.Create_Volume(
585 p_init_msg_list => FND_API.g_false
586 ,p_api_version => 1.0
587 ,p_commit => FND_API.g_false
588 ,x_return_status => l_return_status
589 ,x_msg_count => l_msg_count
590 ,x_msg_data => l_msg_data
591 ,p_volume_detail_rec => l_transaction_rec
592 ,p_qp_list_header_id => l_transaction_rec.qp_list_header_id
593 ,x_apply_discount => l_vol_offr_apply_discount
594 );
595 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
596 RAISE FND_API.G_EXC_ERROR;
597 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
599 END IF;
600 END IF;
601
602
603 -- Debug Message
604 IF OZF_DEBUG_LOW_ON THEN
605 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
606 END IF;
607 --Standard call to get message count and if count=1, get the message
608 FND_MSG_PUB.Count_And_Get (
609 p_encoded => FND_API.G_FALSE,
610 p_count => x_msg_count,
611 p_data => x_msg_data
612 );
613 x_return_status := l_return_status;
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 ROLLBACK TO CREATE_TRANSACTION;
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 -- Standard call to get message count and if count=1, get the message
619 FND_MSG_PUB.Count_And_Get (
620 p_encoded => FND_API.G_FALSE,
621 p_count => x_msg_count,
622 p_data => x_msg_data
623 );
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 ROLLBACK TO CREATE_TRANSACTION;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627
628 -- Standard call to get message count and if count=1, get the message
629 FND_MSG_PUB.Count_And_Get (
630 p_encoded => FND_API.G_FALSE,
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634 WHEN OTHERS THEN
635 ROLLBACK TO CREATE_TRANSACTION;
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
637
638 -- Standard call to get message count and if count=1, get the message
639 FND_MSG_PUB.Count_And_Get (
640 p_encoded => FND_API.G_FALSE,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644 END create_transaction;
645
646
647 ---------------------------------------------------------------------
648 -- PROCEDURE
649 -- Initiate_Inventory_Tmp
650 --
651 -- PURPOSE
652 -- Populate the inventory temporary table
653 --
654 -- PARAMETERS
655 -- p_resale_batch_id: Resale_Batch_Id
656 -- p_start_date : The start date when we want to take a snapshot of inventory
657 -- p_end_date : The end date when we want to take a snapshot of inventory
658 --
659 -- NOTES
660 --
661 ----------------------------------------------------------------------
662 PROCEDURE Initiate_Inventory_Tmp (
663 p_api_version IN NUMBER
664 ,p_init_msg_list IN VARCHAR2
665 ,p_validation_level IN NUMBER
666 ,p_resale_batch_id IN NUMBER
667 ,p_start_date IN DATE
668 ,p_end_date IN DATE
669 ,x_return_status OUT NOCOPY VARCHAR2
670 ,x_msg_count OUT NOCOPY NUMBER
671 ,x_msg_data OUT NOCOPY VARCHAR2
672 )IS
673 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Inventory_Tmp';
674 l_api_version CONSTANT NUMBER := 1.0;
675 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
676 --
677 l_return_status VARCHAR2(30);
678 l_msg_data VARCHAR2(2000);
679 l_msg_count number;
680 -- 6511302 start
681 CURSOR c_inventory_detl IS
682 SELECT *
683 FROM ozf_inventory_tmp_t;
684 l_total_primary_quantity NUMBER;
685 -- 6511302 end
686 BEGIN
687 -- Standard begin of API savepoint
688 SAVEPOINT INIT_INVEN_TMP;
689 -- Standard call to check for call compatibility.
690 IF NOT FND_API.Compatible_API_Call (
691 l_api_version,
692 p_api_version,
693 l_api_name,
694 G_PKG_NAME)
695 THEN
696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
697 END IF;
698
699 --Initialize message list if p_init_msg_list is TRUE.
700 IF FND_API.To_Boolean (p_init_msg_list) THEN
701 FND_MSG_PUB.initialize;
702 END IF;
703
704 -- Debug Message
705 IF OZF_DEBUG_LOW_ON THEN
706 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
707 END IF;
708
709 -- Initialize API return status to sucess
710 x_return_status := FND_API.G_RET_STS_SUCCESS;
711
712
713
714 -- Force refresh snapshot before the query
715 -- Need to think whether it's necessary
716 -- Remove refresh
717 -- DBMS_MVIEW.REFRESH(
718 -- list => 'OZF_INVENTORY_SUMMARY_MV' ,
719 -- method => '?'
720 -- );
721
722 INSERT INTO ozf_inventory_tmp_t(
723 creation_date,
724 created_by ,
725 last_update_date,
726 last_updated_by ,
727 last_update_login,
728 party_id,
729 cust_account_id,
730 inventory_item_id,
731 transaction_date,
732 primary_quantity,
733 primary_uom_code,
734 source_code,
735 transfer_type
736 )
737 SELECT
738 sysdate,
739 1,
740 sysdate,
741 -1,
742 -1,
743 stn.sold_to_party_id, --NULL,
744 NULL, --stn.sold_to_cust_account_id,
745 stn.inventory_item_id,
746 p_start_date,
747 SUM(DECODE(stn.transfer_type, 'IN', 1
748 , 'OUT', -1
749 , 0
750 ) * NVL(stn.primary_quantity,0)),
751 stn.primary_uom_code,
752 NULL,
753 NULL
754 FROM ozf_sales_transactions_all stn
755 WHERE stn.transaction_date <= p_start_date
756 AND stn.source_code IN ('OM', 'MA')
757 AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
758 FROM ozf_resale_lines_int_all rli
759 , hz_cust_accounts hca
760 WHERE rli.resale_batch_id = p_resale_batch_id
761 --AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
762 AND rli.sold_from_cust_account_id = hca.cust_account_id
763 -- AND hca.party_id = stn.sold_to_party_id -- fix for bug 14761031
764 )
765 GROUP BY stn.sold_to_party_id --stn.sold_to_cust_account_id
766 , stn.inventory_item_id
767 , stn.primary_uom_code
768 UNION ALL
769 SELECT
770 sysdate,
771 1,
772 sysdate,
773 -1,
774 -1,
775 stn.sold_from_party_id, --NULL,
776 NULL, --stn.sold_from_cust_account_id,
777 stn.inventory_item_id,
778 p_start_date,
779 SUM(DECODE(stn.transfer_type, 'IN', 1
780 , 'OUT', -1
781 , 0
782 ) * NVL(stn.primary_quantity,0)),
783 stn.primary_uom_code,
784 NULL,
785 NULL
786 FROM ozf_sales_transactions_all stn
787 WHERE stn.transaction_date <= p_start_date
788 AND stn.source_code = 'IS'
789 AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
790 FROM ozf_resale_lines_int_all rli
791 , hz_cust_accounts hca
792 WHERE rli.resale_batch_id = p_resale_batch_id
793 --AND rli.sold_from_cust_account_id = stn.sold_from_cust_account_id
794 --AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
795 AND rli.sold_from_cust_account_id = hca.cust_account_id
796 -- AND hca.party_id = stn.sold_from_party_id -- fix for bug 14761031
797 )
798 GROUP BY stn.sold_from_party_id --stn.sold_from_cust_account_id
799 , stn.inventory_item_id
800 , stn.primary_uom_code;
801
802
803 INSERT INTO ozf_inventory_tmp_t(
804 creation_date,
805 created_by ,
806 last_update_date,
807 last_updated_by ,
808 last_update_login,
809 party_id,
810 cust_account_id,
811 inventory_item_id,
812 transaction_date,
813 primary_quantity,
814 primary_uom_code,
815 source_code,
816 transfer_type
817 )
818 SELECT
819 sysdate,
820 1,
821 sysdate,
822 -1,
823 -1,
824 stn.sold_to_party_id, --NULL,
825 NULL, --stn.sold_to_cust_account_id,
826 stn.inventory_item_id,
827 stn.transaction_date,
828 DECODE(stn.transfer_type, 'IN', 1
829 , 'OUT', -1
830 , 0
831 ) * NVL(stn.primary_quantity,0),
832 stn.primary_uom_code,
833 NULL,
834 NULL
835 FROM ozf_sales_transactions_all stn
836 WHERE stn.transaction_date > p_start_date
837 AND stn.transaction_date <= p_end_date
838 AND stn.source_code IN ('OM', 'MA')
839 AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
840 FROM ozf_resale_lines_int_all rli
841 , hz_cust_accounts hca
842 WHERE rli.resale_batch_id = p_resale_batch_id
843 --AND rli.sold_from_cust_account_id = stn.sold_to_cust_account_id
844 AND rli.sold_from_cust_account_id = hca.cust_account_id
845 -- AND hca.party_id = stn.sold_to_party_id -- fix for bug 14761031
846 )
847 UNION ALL
848 SELECT
849 sysdate,
850 1,
851 sysdate,
852 -1,
853 -1,
854 stn.sold_from_party_id, --NULL,
855 NULL, --stn.sold_from_cust_account_id,
856 stn.inventory_item_id,
857 stn.transaction_date,
858 DECODE(stn.transfer_type, 'IN', 1
859 , 'OUT', -1
860 , 0
861 ) * NVL(stn.primary_quantity,0),
862 stn.primary_uom_code,
863 NULL,
864 NULL
865 FROM ozf_sales_transactions_all stn
866 WHERE stn.transaction_date > p_start_date
867 AND stn.transaction_date <= p_end_date
868 AND source_code = 'IS'
869 AND stn.inventory_item_id IN ( SELECT rli.inventory_item_id
870 FROM ozf_resale_lines_int_all rli
871 , hz_cust_accounts hca
872 WHERE rli.resale_batch_id = p_resale_batch_id
873 --AND rli.sold_from_cust_account_id = stn.sold_from_cust_account_id
874 AND rli.sold_from_cust_account_id = hca.cust_account_id
875 -- AND hca.party_id = stn.sold_to_party_id -- fix for bug 14761031
876 );
877
878 /*
879 insert into ozf_inventory_tmp_t(
880 CREATION_DATE,
881 CREATED_BY ,
882 LAST_UPDATE_DATE,
883 LAST_UPDATED_BY ,
884 LAST_UPDATE_LOGIN,
885 party_id ,
886 inventory_item_id,
887 primary_uom_code ,
888 primary_quantity)
889 select sysdate,
890 1,
891 p_start_date,
892 -1,
893 -1,
894 a.party_id,
895 a.inventory_item_id,
896 a.primary_uom,
897 sum(a.primary_quantity)
898 from ozf_inventory_summary_mv a, (SELECT time_id
899 FROM OZF_TIME_RPT_STRUCT
900 WHERE report_date= trunc(p_start_date)
901 AND BITAND(record_type_id,1143)=record_type_id
902 ) b
903 where a.time_id = b.time_id
904 and a.party_id = p_party_id
905 group by sysdate,
906 1,
907 p_start_date,
908 -1,
909 -1,
910 a.party_id,
911 a.inventory_item_id,
912 a.primary_uom;
913 */
914 -- 6511302 (+)
915 IF OZF_DEBUG_LOW_ON THEN
916 SELECT SUM(primary_quantity)
917 INTO l_total_primary_quantity
918 FROM ozf_inventory_tmp_t;
919 OZF_UTILITY_PVT.debug_message(l_full_name||' : total_primary_quantity = '||l_total_primary_quantity);
920
921 OZF_UTILITY_PVT.debug_message('----------Inventory Detail----------');
922 FOR l_inventory_detl IN c_inventory_detl LOOP
923 OZF_UTILITY_PVT.debug_message('party_id = ' || l_inventory_detl.party_id);
924 OZF_UTILITY_PVT.debug_message('inventory_item_id = ' || l_inventory_detl.inventory_item_id);
925 OZF_UTILITY_PVT.debug_message('primary_uom_code = ' || l_inventory_detl.primary_uom_code);
926 OZF_UTILITY_PVT.debug_message('primary_quantity = ' || l_inventory_detl.primary_quantity);
927 OZF_UTILITY_PVT.debug_message('cust_account_id = ' || l_inventory_detl.cust_account_id);
928 OZF_UTILITY_PVT.debug_message('transfer_type = ' || l_inventory_detl.transfer_type);
929 OZF_UTILITY_PVT.debug_message('- - - - - - - - - -');
930 END LOOP;
931 OZF_UTILITY_PVT.debug_message('------------------------------');
932 END IF;
933 -- 6511302 (-)
934
935 -- Debug Message
936 IF OZF_DEBUG_LOW_ON THEN
937 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
938 END IF;
939 --Standard call to get message count and if count=1, get the message
940 FND_MSG_PUB.Count_And_Get (
941 p_encoded => FND_API.G_FALSE,
942 p_count => x_msg_count,
943 p_data => x_msg_data
944 );
945 x_return_status := l_return_status;
946 EXCEPTION
947 WHEN FND_API.G_EXC_ERROR THEN
948 ROLLBACK TO INIT_INVEN_TMP;
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 -- Standard call to get message count and if count=1, get the message
951 FND_MSG_PUB.Count_And_Get (
952 p_encoded => FND_API.G_FALSE,
953 p_count => x_msg_count,
954 p_data => x_msg_data
955 );
956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
957 ROLLBACK TO INIT_INVEN_TMP;
958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
959
960 -- Standard call to get message count and if count=1, get the message
961 FND_MSG_PUB.Count_And_Get (
962 p_encoded => FND_API.G_FALSE,
963 p_count => x_msg_count,
964 p_data => x_msg_data
965 );
966 WHEN OTHERS THEN
967 ROLLBACK TO INIT_INVEN_TMP;
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
969
970 -- Standard call to get message count and if count=1, get the message
971 FND_MSG_PUB.Count_And_Get (
972 p_encoded => FND_API.G_FALSE,
973 p_count => x_msg_count,
974 p_data => x_msg_data
975 );
976 END Initiate_Inventory_Tmp;
977
978 ---------------------------------------------------------------------
979 -- PROCEDURE
980 -- update_Inventory_tmp
981 --
982 -- PURPOSE
983 -- update the inventory temporary table
984 --
985 -- PARAMETERS
986 -- p_sales_transaction_id: the id of the salse_transaction record to update
987 --
988 -- NOTES
989 --
990 ----------------------------------------------------------------------
991 PROCEDURE update_Inventory_tmp (
992 p_api_version IN NUMBER
993 ,p_init_msg_list IN VARCHAR2
994 ,p_validation_level IN NUMBER
995 ,p_sales_transaction_id IN NUMBER
996 ,x_return_status OUT NOCOPY VARCHAR2
997 ,x_msg_count OUT NOCOPY NUMBER
998 ,x_msg_data OUT NOCOPY VARCHAR2
999 )IS
1000 l_api_name CONSTANT VARCHAR2(30) := 'update_inventory_tmp';
1001 l_api_version CONSTANT NUMBER := 1.0;
1002 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1003 --
1004 l_return_status VARCHAR2(30);
1005 l_msg_data VARCHAR2(2000);
1006 l_msg_count number;
1007
1008 CURSOR transaction_info_csr (p_id number) is
1009 select primary_quantity, inventory_item_id, sold_from_party_id
1010 from OZF_SALES_TRANSACTIONS_ALL
1011 where Sales_Transaction_id = p_id;
1012 l_primary_quantity number;
1013 l_inventory_item_id number;
1014 l_party_id number;
1015
1016 BEGIN
1017 -- Standard begin of API savepoint
1018 SAVEPOINT UPD_INVEN_TMP;
1019 -- Standard call to check for call compatibility.
1020 IF NOT FND_API.Compatible_API_Call (
1021 l_api_version,
1022 p_api_version,
1023 l_api_name,
1024 G_PKG_NAME)
1025 THEN
1026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1027 END IF;
1028
1029 --Initialize message list if p_init_msg_list is TRUE.
1030 IF FND_API.To_Boolean (p_init_msg_list) THEN
1031 FND_MSG_PUB.initialize;
1032 END IF;
1033
1034 -- Debug Message
1035 IF OZF_DEBUG_LOW_ON THEN
1036 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1037 END IF;
1038
1039 -- Initialize API return status to sucess
1040 x_return_status := FND_API.G_RET_STS_SUCCESS;
1041
1042 OPEN transaction_info_csr (p_sales_transaction_id);
1043 FETCH transaction_info_csr into l_primary_quantity, l_inventory_item_id, l_party_id;
1044 CLOSE transaction_info_csr;
1045
1046 IF OZF_DEBUG_LOW_ON THEN
1047 ozf_utility_PVT.debug_message('primary_quantity:' || l_primary_quantity);
1048 ozf_utility_PVT.debug_message('inventory_item_id:' || l_inventory_item_id);
1049 ozf_utility_PVT.debug_message('party_id:' || l_party_id);
1050 END IF;
1051
1052 update ozf_inventory_tmp_t
1053 set primary_quantity = primary_quantity - l_primary_quantity
1054 where party_id = l_party_id
1055 and inventory_item_id = l_inventory_item_id;
1056
1057 -- Debug Message
1058 IF OZF_DEBUG_LOW_ON THEN
1059 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1060 END IF;
1061 --Standard call to get message count and if count=1, get the message
1062 FND_MSG_PUB.Count_And_Get (
1063 p_encoded => FND_API.G_FALSE,
1064 p_count => x_msg_count,
1065 p_data => x_msg_data
1066 );
1067 x_return_status := l_return_status;
1068 EXCEPTION
1069 WHEN FND_API.G_EXC_ERROR THEN
1070 ROLLBACK TO UPD_INVEN_TMP;
1071 x_return_status := FND_API.G_RET_STS_ERROR;
1072 -- Standard call to get message count and if count=1, get the message
1073 FND_MSG_PUB.Count_And_Get (
1074 p_encoded => FND_API.G_FALSE,
1075 p_count => x_msg_count,
1076 p_data => x_msg_data
1077 );
1078 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1079 ROLLBACK TO UPD_INVEN_TMP;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081
1082 -- Standard call to get message count and if count=1, get the message
1083 FND_MSG_PUB.Count_And_Get (
1084 p_encoded => FND_API.G_FALSE,
1085 p_count => x_msg_count,
1086 p_data => x_msg_data
1087 );
1088 WHEN OTHERS THEN
1089 ROLLBACK TO UPD_INVEN_TMP;
1090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1091
1092 -- Standard call to get message count and if count=1, get the message
1093 FND_MSG_PUB.Count_And_Get (
1094 p_encoded => FND_API.G_FALSE,
1095 p_count => x_msg_count,
1096 p_data => x_msg_data
1097 );
1098 END update_inventory_tmp;
1099
1100 ---------------------------------------------------------------------
1101 -- PROCEDURE
1102 -- Validate_Inventory_Level
1103 --
1104 -- PURPOSE
1105 -- Validate a line against the inventory levle.
1106 --
1107 -- PARAMETERS
1108 -- p_line_int_rec: interface rece.
1109 --
1110 -- NOTES
1111 --
1112 ----------------------------------------------------------------------
1113 PROCEDURE Validate_Inventory_Level (
1114 p_api_version IN NUMBER
1115 ,p_init_msg_list IN VARCHAR2
1116 ,p_validation_level IN NUMBER
1117 ,p_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype
1118 ,x_valid OUT NOCOPY BOOLEAN
1119 ,x_return_status OUT NOCOPY VARCHAR2
1120 ,x_msg_count OUT NOCOPY NUMBER
1121 ,x_msg_data OUT NOCOPY VARCHAR2
1122 )IS
1123 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Inventory_Level';
1124 l_api_version CONSTANT NUMBER := 1.0;
1125 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1126 --
1127 l_return_status VARCHAR2(30);
1128 l_msg_data VARCHAR2(2000);
1129 l_msg_count NUMBER;
1130
1131 -- Bug 4380203 (+)
1132 CURSOR csr_inventory_level( cv_party_id IN NUMBER --cv_cust_account_id IN NUMBER
1133 , cv_inventory_item_id IN NUMBER
1134 , cv_transaction_date IN DATE
1135 ) IS
1136 SELECT SUM(primary_quantity)
1137 , primary_uom_code
1138 FROM ozf_inventory_tmp_t
1139 --WHERE cust_account_id = cv_cust_account_id
1140 WHERE party_id = cv_party_id
1141 AND inventory_item_id = cv_inventory_item_id
1142 AND transaction_date <= cv_transaction_date
1143 GROUP BY primary_uom_code;
1144 -- primary_uom_code is unique per product
1145 /*
1146 CURSOR product_level_csr (p_inventory_item_id IN NUMBER) IS
1147 SELECT primary_uom_code, primary_quantity
1148 FROM ozf_inventory_tmp_t
1149 WHERE inventory_item_id = p_inventory_item_id;
1150 */
1151
1152 CURSOR csr_primary_uom( cv_party_id IN NUMBER --cv_cust_account_id IN NUMBER
1153 , cv_inventory_item_id IN NUMBER
1154 ) IS
1155 SELECT primary_uom_code
1156 FROM ozf_inventory_tmp_t
1157 --WHERE cust_account_id = cv_cust_account_id
1158 WHERE party_id = cv_party_id
1159 AND inventory_item_id = cv_inventory_item_id
1160 AND rownum = 1;
1161
1162 CURSOR csr_sold_from_party_id (cv_cust_account_id IN NUMBER) IS
1163 SELECT party_id
1164 FROM hz_cust_accounts
1165 WHERE cust_account_id = cv_cust_account_id;
1166
1167 l_sold_from_party_id NUMBER;
1168 -- Bug 4380203 (-)
1169
1170 l_primary_uom_code VARCHAR2(30);
1171 l_primary_quantity NUMBER;
1172 l_converted_quantity NUMBER;
1173 l_transfer_type VARCHAR2(10) := 'OUT';
1174
1175 -- 6511302 start
1176 CURSOR c_inventory_detl IS
1177 SELECT *
1178 FROM ozf_inventory_tmp_t;
1179 l_total_primary_quantity NUMBER;
1180 -- 6511302 end
1181 BEGIN
1182 -- Standard begin of API savepoint
1183 SAVEPOINT VALID_INV_LVL;
1184 -- Standard call to check for call compatibility.
1185 IF NOT FND_API.Compatible_API_Call (
1186 l_api_version,
1187 p_api_version,
1188 l_api_name,
1189 G_PKG_NAME) THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 END IF;
1192
1193 --Initialize message list if p_init_msg_list is TRUE.
1194 IF FND_API.To_Boolean (p_init_msg_list) THEN
1195 FND_MSG_PUB.initialize;
1196 END IF;
1197
1198 -- Debug Message
1199 IF OZF_DEBUG_LOW_ON THEN
1200 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1201 END IF;
1202
1203 -- Initialize API return status to sucess
1204 x_return_status := FND_API.G_RET_STS_SUCCESS;
1205
1206 x_valid := false;
1207
1208 -- Bug 4380203 (+)
1209 IF p_line_int_rec.product_transfer_movement_type IN ('TI', 'CD') THEN
1210 l_transfer_type := 'IN';
1211 ELSIF p_line_int_rec.product_transfer_movement_type IN ('TO', 'DC') THEN
1212 l_transfer_type := 'OUT';
1213 END IF;
1214
1215 OPEN csr_sold_from_party_id(p_line_int_rec.sold_from_cust_account_id);
1216 FETCH csr_sold_from_party_id INTO l_sold_from_party_id;
1217 CLOSE csr_sold_from_party_id;
1218
1219 -- No need to do inventory level validation when transfering in product
1220 IF l_transfer_type = 'OUT' THEN
1221 OPEN csr_inventory_level( l_sold_from_party_id --p_line_int_rec.sold_from_cust_account_id
1222 , p_line_int_rec.inventory_item_id
1223 , p_line_int_rec.date_ordered
1224 );
1225 FETCH csr_inventory_level INTO l_primary_quantity
1226 , l_primary_uom_code;
1227 CLOSE csr_inventory_level;
1228
1229 /*
1230 -- check with tmp table whether there is enough inventory on stock.
1231 OPEN product_level_csr (p_line_int_rec.inventory_item_id);
1232 FETCH product_level_csr into l_primary_uom_code, l_primary_quantity;
1233 CLOSE product_level_csr;
1234 */
1235
1236 IF l_primary_uom_code IS NOT NULL THEN
1237 IF p_line_int_rec.uom_code = l_primary_uom_code THEN
1238 x_valid := p_line_int_rec.quantity <= l_primary_quantity;
1239 l_converted_quantity := p_line_int_rec.quantity;
1240 ELSE
1241 -- get qauntity based on primayr_uom_code
1242 l_converted_quantity := INV_CONVERT.inv_um_convert(
1243 p_line_int_rec.inventory_item_id
1244 ,null
1245 ,p_line_int_rec.quantity
1246 ,p_line_int_rec.uom_code
1247 ,l_primary_uom_code
1248 ,null
1249 ,null
1250 );
1251 x_valid := l_converted_quantity <= l_primary_quantity;
1252 END IF;
1253 END IF;
1254 l_converted_quantity := l_converted_quantity * -1;
1255 ELSIF l_transfer_type = 'IN' THEN
1256 OPEN csr_primary_uom( l_sold_from_party_id --p_line_int_rec.sold_from_cust_account_id
1257 , p_line_int_rec.inventory_item_id
1258 );
1259 FETCH csr_primary_uom INTO l_primary_uom_code;
1260 CLOSE csr_primary_uom;
1261
1262 IF p_line_int_rec.uom_code = l_primary_uom_code THEN
1263 l_converted_quantity := p_line_int_rec.quantity;
1264 ELSE
1265 l_converted_quantity := INV_CONVERT.inv_um_convert(
1266 p_line_int_rec.inventory_item_id
1267 ,null
1268 ,p_line_int_rec.quantity
1269 ,p_line_int_rec.uom_code
1270 ,l_primary_uom_code
1271 ,null
1272 ,null
1273 );
1274 END IF;
1275
1276 IF p_line_int_rec.resale_transfer_type = 'BN' THEN -- fix for bug 14761031
1277 l_converted_quantity := l_converted_quantity * -1;
1278 END IF;
1279
1280 x_valid := true;
1281 END IF;
1282
1283 -- 6511302 (+)
1284 IF OZF_DEBUG_LOW_ON THEN
1285 OZF_UTILITY_PVT.debug_message(l_full_name || ':order number = '||p_line_int_rec.order_number);
1286 OZF_UTILITY_PVT.debug_message(l_full_name || ':transfer type = '||l_transfer_type);
1287 OZF_UTILITY_PVT.debug_message(l_full_name || ':l_primary_quantity = '||l_primary_quantity);
1288 OZF_UTILITY_PVT.debug_message(l_full_name || ':actual line quantity = '||l_converted_quantity);
1289 OZF_UTILITY_PVT.debug_message(l_full_name || ':abs(line quantity) = '||ABS(l_converted_quantity));
1290 OZF_UTILITY_PVT.debug_message(l_full_name || ':item id = ' || p_line_int_rec.inventory_item_id);
1291 OZF_UTILITY_PVT.debug_message(l_full_name || ':sold_from_party_id = ' || l_sold_from_party_id);
1292 END IF;
1293 -- 6511302 (-)
1294
1295 INSERT INTO ozf_inventory_tmp_t(
1296 creation_date,
1297 created_by ,
1298 last_update_date,
1299 last_updated_by ,
1300 last_update_login,
1301 party_id,
1302 cust_account_id,
1303 inventory_item_id,
1304 transaction_date,
1305 primary_quantity,
1306 primary_uom_code,
1307 source_code,
1308 transfer_type
1309 ) VALUES (
1310 sysdate,
1311 p_line_int_rec.created_by ,
1312 sysdate,
1313 p_line_int_rec.last_updated_by ,
1314 p_line_int_rec.last_update_login,
1315 l_sold_from_party_id,
1316 NULL, --p_line_int_rec.sold_from_cust_account_id,
1317 p_line_int_rec.inventory_item_id,
1318 p_line_int_rec.date_ordered,
1319 l_converted_quantity,
1320 l_primary_uom_code,
1321 'IS',
1322 l_transfer_type
1323 );
1324 -- Bug 4380203 (-)
1325
1326 -- 6511302 (+)
1327 IF OZF_DEBUG_LOW_ON THEN
1328 OZF_UTILITY_PVT.debug_message('----------Inventory Detail----------');
1329 FOR l_inventory_detl IN c_inventory_detl LOOP
1330 OZF_UTILITY_PVT.debug_message('party_id = ' || l_inventory_detl.party_id);
1331 OZF_UTILITY_PVT.debug_message('inventory_item_id = ' || l_inventory_detl.inventory_item_id);
1332 OZF_UTILITY_PVT.debug_message('primary_uom_code = ' || l_inventory_detl.primary_uom_code);
1333 OZF_UTILITY_PVT.debug_message('primary_quantity = ' || l_inventory_detl.primary_quantity);
1334 OZF_UTILITY_PVT.debug_message('cust_account_id = ' || l_inventory_detl.cust_account_id);
1335 OZF_UTILITY_PVT.debug_message('transfer_type = ' || l_inventory_detl.transfer_type);
1336 OZF_UTILITY_PVT.debug_message('- - - - - - - - - -');
1337 END LOOP;
1338 OZF_UTILITY_PVT.debug_message('------------------------------');
1339 END IF;
1340 -- 6511302 (-)
1341
1342 -- Debug Message
1343 IF OZF_DEBUG_LOW_ON THEN
1344 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1345 END IF;
1346 --Standard call to get message count and if count=1, get the message
1347 FND_MSG_PUB.Count_And_Get (
1348 p_encoded => FND_API.G_FALSE,
1349 p_count => x_msg_count,
1350 p_data => x_msg_data
1351 );
1352 x_return_status := l_return_status;
1353
1354 EXCEPTION
1355 WHEN FND_API.G_EXC_ERROR THEN
1356 ROLLBACK TO VALID_INV_LVL;
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 -- Standard call to get message count and if count=1, get the message
1359 FND_MSG_PUB.Count_And_Get (
1360 p_encoded => FND_API.G_FALSE,
1361 p_count => x_msg_count,
1362 p_data => x_msg_data
1363 );
1364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1365 ROLLBACK TO VALID_INV_LVL;
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367
1368 -- Standard call to get message count and if count=1, get the message
1369 FND_MSG_PUB.Count_And_Get (
1370 p_encoded => FND_API.G_FALSE,
1371 p_count => x_msg_count,
1372 p_data => x_msg_data
1373 );
1374 WHEN OTHERS THEN
1375 ROLLBACK TO VALID_INV_LVL;
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1377
1378 -- Standard call to get message count and if count=1, get the message
1379 FND_MSG_PUB.Count_And_Get (
1380 p_encoded => FND_API.G_FALSE,
1381 p_count => x_msg_count,
1382 p_data => x_msg_data
1383 );
1384 END Validate_Inventory_Level;
1385
1386
1387 ---------------------------------------------------------------------
1388 -- PROCEDURE
1389 -- Get_Purchase_Price
1390 --
1391 -- PURPOSE
1392 -- Calculate the purchase price of a line based on the order management data.
1393 --
1394 -- PARAMETERS
1395 -- p_line_int_rec: interface rece.
1396 -- x_purchase_price: NUMBER
1397 --
1398 -- NOTES
1399 --
1400 ----------------------------------------------------------------------
1401 PROCEDURE Get_Purchase_Price (
1402 p_api_version IN NUMBER
1403 ,p_init_msg_list IN VARCHAR2
1404 ,p_validation_level IN NUMBER
1405 ,p_order_date IN DATE
1406 ,p_sold_from_cust_account_id IN NUMBER
1407 ,p_sold_from_site_id IN NUMBER
1408 ,p_inventory_item_id IN NUMBER
1409 ,p_uom_code IN VARCHAR2
1410 ,p_quantity IN NUMBER
1411 ,p_currency_code IN VARCHAR2
1412 ,p_x_purchase_uom_code IN OUT NOCOPY VARCHAR2
1413 ,x_purchase_price OUT NOCOPY NUMBER
1414 ,x_return_status OUT NOCOPY VARCHAR2
1415 ,x_msg_count OUT NOCOPY NUMBER
1416 ,x_msg_data OUT NOCOPY VARCHAR2
1417 )
1418 IS
1419 l_api_name CONSTANT VARCHAR2(30) := 'Get_Purchase_Price';
1420 l_api_version CONSTANT NUMBER := 1.0;
1421 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1422 --
1423 l_return_status VARCHAR2(30);
1424 l_msg_data VARCHAR2(2000);
1425 l_msg_count NUMBER;
1426
1427 l_transaction_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1428 l_transaction_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
1429 l_unit_price_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1430 l_currency_code_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1431 l_available_quan_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1432 l_primary_uom_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1433
1434 l_asking_quantity NUMBER;
1435
1436 l_trans_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1437 l_used_quantity_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1438 l_used_unit_price_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1439 l_rate NUMBER;
1440 l_default_exchange_type VARCHAR2(30);
1441
1442 l_numerator NUMBER := 0; -- [BUG 4212965 Fixing]
1443 l_denominator NUMBER := 0; -- [BUG 4212965 Fixing]
1444 l_uom_ratio NUMBER;
1445 --
1446 CURSOR Sales_Order_info_csr(p_order_date DATE,
1447 p_inventory_item_id NUMBER,
1448 p_sold_from_cust_account_id NUMBER) IS
1449 SELECT a.sales_transaction_id,
1450 a.amount / a.primary_quantity,
1451 a.currency_code,
1452 a.transaction_date,
1453 decode(a.transfer_type, 'IN', a.available_primary_quantity, 'OUT', -1 * a.available_primary_quantity),
1454 a.primary_uom_code
1455 FROM ozf_sales_transactions a
1456 WHERE a.available_primary_quantity > 0
1457 AND a.inventory_item_id = p_inventory_item_id
1458 AND a.sold_to_cust_account_id = p_sold_from_cust_account_id
1459 -- AND sold_to_party_site_id = p_sold_from_site_id
1460 AND a.source_code = 'OM'
1461 AND a.transaction_date< p_order_date
1462 ORDER BY a.transaction_date DESC;
1463
1464 BEGIN
1465 -- Standard begin of API savepoint
1466 SAVEPOINT GET_WAC;
1467 -- Standard call to check for call compatibility.
1468 IF NOT FND_API.Compatible_API_Call (
1469 l_api_version,
1470 p_api_version,
1471 l_api_name,
1472 G_PKG_NAME)
1473 THEN
1474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1475 END IF;
1476
1477 --Initialize message list if p_init_msg_list is TRUE.
1478 IF FND_API.To_Boolean (p_init_msg_list) THEN
1479 FND_MSG_PUB.initialize;
1480 END IF;
1481
1482 -- Debug Message
1483 IF OZF_DEBUG_LOW_ON THEN
1484 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1485 END IF;
1486
1487 -- Initialize API return status to sucess
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489
1490 OPEN Sales_Order_info_csr(p_order_date,
1491 p_inventory_item_id,
1492 p_sold_from_cust_account_id);
1493 FETCH sales_order_info_csr BULK COLLECT INTO l_transaction_id_tbl,
1494 l_unit_price_tbl,
1495 l_currency_code_tbl,
1496 l_transaction_date_tbl,
1497 l_available_quan_tbl,
1498 l_primary_uom_tbl;
1499 CLOSE sales_order_info_csr;
1500
1501 IF l_transaction_id_tbl.exists(1) THEN
1502 IF OZF_DEBUG_LOW_ON THEN
1503 OZF_UTILITY_PVT.debug_message('Number of OM lines: ' || l_transaction_id_tbl.LAST);
1504 END IF;
1505
1506 -- First check whether p_uom_code is the same as the primary_uom_code.
1507 -- If not, conver the quentity
1508 -- use this quantity to do the calculation.
1509 IF p_uom_code = l_primary_uom_tbl(1) THEN
1510 l_asking_quantity := p_quantity;
1511 ELSE
1512 l_asking_quantity :=inv_convert.inv_um_convert(
1513 p_inventory_item_id,
1514 null,
1515 p_quantity,
1516 p_uom_code,
1517 l_primary_uom_tbl(1),
1518 null, null);
1519 END IF;
1520
1521 -- Get default exchange type
1522 OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1523 FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1524 CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1525
1526 FOR i in 1..l_transaction_id_tbl.LAST
1527 LOOP
1528 IF l_asking_quantity = 0 THEN
1529 EXIT;
1530 ELSE
1531 l_trans_id_tbl(i) := l_transaction_id_tbl(i);
1532 IF l_asking_quantity > l_available_quan_tbl(i) THEN
1533 l_used_quantity_tbl(i) := l_available_quan_tbl(i);
1534 l_asking_quantity := l_asking_quantity - l_available_quan_tbl(i);
1535 ELSE
1536 l_used_quantity_tbl(i) := l_asking_quantity;
1537 l_asking_quantity := 0;
1538 END IF;
1539 IF p_currency_code = l_currency_code_tbl(i) THEN
1540 l_used_unit_price_tbl(i) := l_unit_price_tbl(i);
1541 ELSE
1542 -- ?? What exchange type to use ?
1543 -- We will convert it to the asking currency code at the date of transfer.
1544 OZF_UTILITY_PVT.Convert_Currency(
1545 p_from_currency => l_currency_code_tbl(i)
1546 ,p_to_currency => p_currency_code
1547 ,p_conv_type => l_default_exchange_type
1548 ,p_conv_rate => l_rate
1549 ,p_conv_date => l_transaction_date_tbl(i)
1550 ,p_from_amount => l_unit_price_tbl(i)
1551 ,x_return_status => l_return_status
1552 ,x_to_amount => l_used_unit_price_tbl(i)
1553 ,x_rate => l_rate);
1554 IF l_return_status = FND_API.g_ret_sts_error THEN
1555 RAISE FND_API.g_exc_error;
1556 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1557 RAISE FND_API.g_exc_unexpected_error;
1558 END IF;
1559 END IF;
1560 END IF;
1561 END LOOP;
1562 IF l_trans_id_tbl.exists(1) THEN
1563 FOR i IN 1..l_trans_id_tbl.LAST
1564 LOOP
1565 l_numerator := l_numerator + l_used_quantity_tbl(i) * l_used_unit_price_tbl(i);
1566 l_denominator := l_denominator + l_used_quantity_tbl(i);
1567 END LOOP;
1568 x_purchase_price := l_numerator / l_denominator;
1569
1570 FORALL i IN 1..l_trans_id_tbl.LAST
1571 UPDATE ozf_sales_transactions_all
1572 SET available_primary_quantity = available_primary_quantity - l_used_quantity_tbl(i)
1573 WHERE sales_transaction_id = l_trans_id_tbl(i);
1574
1575 IF p_x_purchase_uom_code IS NULL THEN
1576 p_x_purchase_uom_code := l_primary_uom_tbl(1);
1577 ELSE
1578 l_uom_ratio := INV_CONVERT.inv_um_convert(
1579 p_inventory_item_id,
1580 null,
1581 1,
1582 l_primary_uom_tbl(1),
1583 p_x_purchase_uom_code,
1584 null,
1585 null
1586 );
1587 x_purchase_price := OZF_UTILITY_PVT.CurrRound(
1588 (x_purchase_price * l_uom_ratio)
1589 , p_currency_code
1590 );
1591 END IF;
1592 ELSE
1593 -- Since we can not find any suitable order lines, we will return purchase price as null
1594 x_purchase_price := NULL;
1595 END IF;
1596 ELSE
1597 -- Since we can not find any suitable order lines, we will return purchase price as null
1598 x_purchase_price := NULL;
1599 END IF;
1600
1601 -- Debug Message
1602 IF OZF_DEBUG_LOW_ON THEN
1603 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1604 END IF;
1605 --Standard call to get message count and if count=1, get the message
1606 FND_MSG_PUB.Count_And_Get (
1607 p_encoded => FND_API.G_FALSE,
1608 p_count => x_msg_count,
1609 p_data => x_msg_data
1610 );
1611 x_return_status := l_return_status;
1612
1613 EXCEPTION
1614 WHEN FND_API.G_EXC_ERROR THEN
1615 ROLLBACK TO GET_WAC;
1616 x_return_status := FND_API.G_RET_STS_ERROR;
1617 -- Standard call to get message count and if count=1, get the message
1618 FND_MSG_PUB.Count_And_Get (
1619 p_encoded => FND_API.G_FALSE,
1620 p_count => x_msg_count,
1621 p_data => x_msg_data
1622 );
1623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624 ROLLBACK TO GET_WAC;
1625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626
1627 -- Standard call to get message count and if count=1, get the message
1628 FND_MSG_PUB.Count_And_Get (
1629 p_encoded => FND_API.G_FALSE,
1630 p_count => x_msg_count,
1631 p_data => x_msg_data
1632 );
1633 WHEN OTHERS THEN
1634 ROLLBACK TO GET_WAC;
1635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1636
1637 -- Standard call to get message count and if count=1, get the message
1638 FND_MSG_PUB.Count_And_Get (
1639 p_encoded => FND_API.G_FALSE,
1640 p_count => x_msg_count,
1641 p_data => x_msg_data
1642 );
1643 END Get_Purchase_Price;
1644
1645 END OZF_SALES_TRANSACTIONS_PVT;