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