[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_CUSTOM_RMK_ASSET_PVT
Source
1 PACKAGE BODY OKL_AM_CUSTOM_RMK_ASSET_PVT AS
2 /* $Header: OKLRCREB.pls 120.5 2006/07/18 10:59:00 cdubey noship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : validate_item_info
8 -- Description : Validate Item Information
9 -- Business Rules :
10 -- Parameters : Input parameters : p_asset_return_id -- Asset Return ID
11 -- p_item_number -- Item #
12 -- p_Item_Description -- Item Description
13 -- p_Item_Price -- Item Price
14 -- p_quantity -- Item Quantity
15 --
16 -- Output Parameters : x_inv_org_id -- Inventory Org ID
17 -- x_inv_org_name -- Inventory Org Name
18 -- x_subinv_code -- Subinventory Code
19 -- x_sys_date -- System Date
20 -- x_price_list_id -- price List ID
21 -- x_item_templ_id -- Item Template ID
22 --
23 -- Version : 1.0
24 -- History : 25-OCT-04 SECHAWLA - Created
25 -- End of comments
26 PROCEDURE validate_item_info(
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
29 p_asset_return_id IN NUMBER,
30 p_item_number IN VARCHAR2,
31 p_Item_Description IN VARCHAR2,
32 p_Item_Price IN NUMBER DEFAULT OKL_API.G_MISS_NUM,
33 p_quantity IN NUMBER DEFAULT 1,
34 x_inv_org_id OUT NOCOPY NUMBER,
35 x_inv_org_name OUT NOCOPY VARCHAR2,
36 x_subinv_code OUT NOCOPY VARCHAR2,
37 x_sys_date OUT NOCOPY DATE,
38 x_price_list_id OUT NOCOPY NUMBER,
39 x_item_templ_id OUT NOCOPY NUMBER,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2)
43 IS
44
45 -- validate asset return id
46 CURSOR l_assetreturn_csr(cp_asset_return_id IN NUMBER) IS
47 SELECT 'x'
48 FROM okl_asset_returns_b
49 WHERE id = cp_asset_return_id;
50
51 -- This cursor is used to validate an organization Id against mtl_organization
52 CURSOR l_mtlorgcsr(cp_org_id NUMBER) IS
53 SELECT organization_name
54 FROM ORG_ORGANIZATION_DEFINITIONS
55 WHERE organization_id = cp_org_id;
56
57
58
59 -- This cursor is used to validate the list_header_id
60 CURSOR l_qplisthdr_csr(cp_list_header_id NUMBER) IS
61 SELECT 'x'
62 --FROM QP_LIST_HEADERS_B -- SECHAWLA 08-DEC-04 4047159
63 FROM QP_LIST_HEADERS -- SECHAWLA 08-DEC-04 4047159
64 WHERE LIST_HEADER_ID = cp_list_header_id;
65
66
67 -- This cursor is used to get the warehouse for the Order and Line Transaction types
68 CURSOR l_oetranstypesall_csr(cp_trans_id NUMBER) IS
69 SELECT warehouse_id, default_outbound_line_type_id, name
70 FROM oe_transaction_types_all a, oe_transaction_types_tl b
71 WHERE a.transaction_type_id = b.transaction_type_id
72 AND a.transaction_type_id = cp_trans_id;
73
74 -- check the Remarketing flow options from the setup
75 CURSOR l_systemparamsall_csr IS
76 SELECT REMK_ORGANIZATION_ID, REMK_SUBINVENTORY, REMK_PRICE_LIST_ID, REMK_ITEM_TEMPLATE_ID
77 FROM OKL_SYSTEM_PARAMS ;
78
79 -- check if item already exists in inventory
80 CURSOR l_mtlsystemitems_csr(cp_inv_item_number IN VARCHAR2) IS
81 SELECT count(*)
82 FROM MTL_SYSTEM_ITEMS_B
83 WHERE segment1 = cp_inv_item_number;
84
85 -- validate item template
86 CURSOR l_mtltemplates_csr(cp_item_templ_id IN NUMBER, cp_org_id IN NUMBER) IS
87 SELECT TEMPLATE_NAME
88 -- DESCRIPTION ,
89 FROM MTL_ITEM_TEMPLATES
90 WHERE TEMPLATE_ID = cp_item_templ_id
91 AND ( (CONTEXT_ORGANIZATION_ID IS NULL ) OR (CONTEXT_ORGANIZATION_ID = cp_org_id));
92
93 l_item_cnt NUMBER;
94
95 l_order_warehouse_id NUMBER;
96 l_line_warehouse_id NUMBER;
97 l_def_outbound_line_type_id NUMBER;
98 l_order_name VARCHAR2(30);
99 l_line_name VARCHAR2(30);
100 l_inv_org_id NUMBER;
101 l_subinv_code VARCHAR2(10);
102 l_price_list_id NUMBER;
103 l_item_template_id NUMBER;
104 l_template_name VARCHAR2(30);
105
106 l_return_status VARCHAR2(1);
107 l_pricelist_exists VARCHAR2(1);
108 l_temp_org_name VARCHAR2(240);
109 l_default_order_type_id NUMBER;
110
111
112 l_sysdate DATE;
113
114 l_user_profile_name VARCHAR2(240);
115
116 -- SECHAWLA 08-MAR-04 3492490 : new declarations
117
118
119 l_api_name CONSTANT VARCHAR2(30) := 'validate_item_info';
120 l_api_version CONSTANT NUMBER := 1;
121
122 l_dummy VARCHAR2(1);
123 BEGIN
124
125 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
126 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info','Begin(+)');
127 END IF;
128
129 --Print Input Variables
130 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
132 'p_api_version :'||p_api_version);
133 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
134 'p_init_msg_list :'||p_init_msg_list);
135 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
136 'p_asset_return_id :'||p_asset_return_id);
137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
138 'p_item_number :'||p_item_number);
139 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
140 'p_Item_Description :'||p_Item_Description);
141 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
142 'p_Item_Price :'||p_Item_Price);
143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
144 'p_quantity :'||p_quantity);
145
146 END IF;
147
148
149
150
151
152 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
153 G_PKG_NAME,
154 p_init_msg_list,
155 l_api_version,
156 p_api_version,
157 '_PVT',
158 x_return_status);
159
160
161 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
163 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
164 RAISE OKL_API.G_EXCEPTION_ERROR;
165 END IF;
166
167 SELECT SYSDATE INTO l_sysdate FROM DUAL;
168
169 x_sys_date := l_sysdate;
170
171 IF p_item_number IS NOT NULL THEN
172 OPEN l_mtlsystemitems_csr(p_item_number);
173 FETCH l_mtlsystemitems_csr INTO l_item_cnt;
174 CLOSE l_mtlsystemitems_csr;
175
176 IF l_item_cnt > 0 THEN
177 --Item number ITEM_NUMBER already exists in Inventory. Please enter another item number.
178 OKL_API.set_message( p_app_name => 'OKL',
179 p_msg_name => 'OKL_AM_ITEM_ALREADY_EXISTS',
180 p_token1 => 'ITEM_NUMBER',
181 p_token1_value => p_item_number);
182 x_return_status := OKL_API.G_RET_STS_ERROR;
183 RAISE OKL_API.G_EXCEPTION_ERROR;
184 END IF;
185 END IF;
186
187
188 IF p_item_price IS NULL OR p_item_price = OKL_API.G_MISS_NUM THEN
189 x_return_status := OKL_API.G_RET_STS_ERROR;
190 --Item Price is required
191 OKC_API.set_message( p_app_name => 'OKC',
192 p_msg_name => G_REQUIRED_VALUE,
193 p_token1 => G_COL_NAME_TOKEN,
194 p_token1_value => 'ITEM_PRICE');
195
196 RAISE okl_api.G_EXCEPTION_ERROR;
197 END IF;
198
199 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM THEN
200 x_return_status := OKL_API.G_RET_STS_ERROR;
201 --Item Quantity is required
202 OKC_API.set_message( p_app_name => 'OKC',
203 p_msg_name => G_REQUIRED_VALUE,
204 p_token1 => G_COL_NAME_TOKEN,
205 p_token1_value => 'ITEM_QUANTITY');
206
207 RAISE okl_api.G_EXCEPTION_ERROR;
208 END IF;
209
210 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
211 -- Check the remarketing flow setup
212 OPEN l_systemparamsall_csr;
213 FETCH l_systemparamsall_csr INTO l_inv_org_id, l_subinv_code, l_price_list_id, l_item_template_id;
214 IF l_systemparamsall_csr%NOTFOUND THEN
215 -- Remarketing options are not setup for this operating unit.
216 OKL_API.set_message(
217 p_app_name => 'OKL',
218 p_msg_name => 'OKL_AM_NO_REMK_SETUP');
219 x_return_status := OKL_API.G_RET_STS_ERROR;
220 RAISE OKL_API.G_EXCEPTION_ERROR;
221 END IF;
222 CLOSE l_systemparamsall_csr;
223
224
225 IF l_inv_org_id IS NULL THEN
226 -- Remarketing Inventory Organization is not setup for this operating unit.
227 OKL_API.set_message(
228 p_app_name => 'OKL',
229 p_msg_name => 'OKL_AM_NO_REMK_ORG');
230 x_return_status := OKL_API.G_RET_STS_ERROR;
231 RAISE OKL_API.G_EXCEPTION_ERROR;
232 ELSE
233 x_inv_org_id := l_inv_org_id;
234 END IF;
235 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
236
237
238
239 OPEN l_mtlorgcsr(l_inv_org_id);
240 FETCH l_mtlorgcsr INTO l_temp_org_name;
241 IF l_mtlorgcsr%NOTFOUND THEN
242 x_return_status := OKL_API.G_RET_STS_ERROR;
243
244 OKC_API.set_message( p_app_name => 'OKC',
245 p_msg_name => G_INVALID_VALUE,
246 p_token1 => G_COL_NAME_TOKEN,
247 p_token1_value => 'Remarketing Inventory Organization');
248
249 RAISE okl_api.G_EXCEPTION_ERROR;
250 END IF;
251 CLOSE l_mtlorgcsr;
252
253
254 x_inv_org_name := l_temp_org_name;
255
256
257 -- validate subinventory
258 IF l_subinv_code IS NULL THEN
259
260 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
261 -- Remarketing Subinventory is not setup for this operating unit.
262 OKL_API.set_message(
263 p_app_name => 'OKL',
264 p_msg_name => 'OKL_AM_NO_REMK_SUBINV');
265 x_return_status := OKL_API.G_RET_STS_ERROR;
266 RAISE OKL_API.G_EXCEPTION_ERROR;
267 ELSE
268 x_subinv_code := l_subinv_code;
269 END IF;
270
271
272
273 IF l_price_list_id IS NULL THEN
274
275 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
276
277 -- Remarketing Price List is not setup for this operating unit.
278 OKL_API.set_message(
279 p_app_name => 'OKL',
280 p_msg_name => 'OKL_AM_NO_REMK_PRICE_LIST');
281 x_return_status := OKL_API.G_RET_STS_ERROR;
282 RAISE OKL_API.G_EXCEPTION_ERROR;
283 END IF;
284
285
286 OPEN l_qplisthdr_csr(l_price_list_id);
287 FETCH l_qplisthdr_csr INTO l_pricelist_exists;
288 IF l_qplisthdr_csr%NOTFOUND THEN
289 x_return_status := OKL_API.G_RET_STS_ERROR;
290 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
291 OKC_API.set_message( p_app_name => 'OKC',
292 p_msg_name => G_INVALID_VALUE,
293 p_token1 => G_COL_NAME_TOKEN,
294 p_token1_value => 'Remarketing Price List');
295 RAISE OKL_API.G_EXCEPTION_ERROR;
296 END IF;
297 CLOSE l_qplisthdr_csr;
298
299 x_price_list_id := l_price_list_id ;
300
301
302 IF l_item_template_id IS NOT NULL THEN
303 OPEN l_mtltemplates_csr(l_item_template_id, l_inv_org_id);
304 FETCH l_mtltemplates_csr INTO l_template_name;
305 IF l_mtltemplates_csr%NOTFOUND THEN
306 x_return_status := OKL_API.G_RET_STS_ERROR;
307 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
308 OKC_API.set_message( p_app_name => 'OKC',
309 p_msg_name => G_INVALID_VALUE,
310 p_token1 => G_COL_NAME_TOKEN,
311 p_token1_value => 'Item Template');
312 RAISE OKL_API.G_EXCEPTION_ERROR;
313 END IF;
314 CLOSE l_mtltemplates_csr;
315 END IF;
316
317 x_item_templ_id := l_item_template_id;
318
319 -- ASO_ORDER_TYPE_ID is an Oracle Order Capture profile to set the default Order type.
320 -- iStore uses this profile to get the default Order type and then assigns this Order type to the Orders
321
322 --l_default_order_type_id := fnd_profile.value('ASO_ORDER_TYPE_ID');
323 l_default_order_type_id := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_ORDER_TYPE,l_inv_org_id); -- CDUBEY - For MOAC Bug 4421236
324
325
326
327 IF l_default_order_type_id IS NULL THEN
328
329 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
330 -- instead of profile option name
331 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
332 p_profile_option_name => 'ASO_ORDER_TYPE_ID',
333 x_return_status => x_return_status);
334
335 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
336 --Remarketing Order Type profile is missing.
337 OKL_API.set_message( p_app_name => 'OKL',
338 p_msg_name => 'OKL_AM_NO_ORDER_TYPE_PROFILE'
339 );
340 RAISE okl_api.G_EXCEPTION_ERROR;
341 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
342 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
343 END IF;
344 -- SECHAWLA Bug# 2679812 -- end new code
345
346 x_return_status := OKL_API.G_RET_STS_ERROR;
347 -- Profile value not defined
348 OKL_API.set_message( p_app_name => 'OKL',
349 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
350 p_token1 => 'PROFILE',
351 p_token1_value => l_user_profile_name -- modified to display user profile option
352 );
353 RAISE OKL_API.G_EXCEPTION_ERROR;
354 END IF;
355
356 -- get the warehouse and the Line Type for the Default Order Type
357 OPEN l_oetranstypesall_csr(l_default_order_type_id);
358 FETCH l_oetranstypesall_csr INTO l_order_warehouse_id, l_def_outbound_line_type_id, l_order_name;
359 -- This fetch will definitely find the record in oe_transaction_types_all
360 CLOSE l_oetranstypesall_csr;
361
362
363 IF l_order_warehouse_id IS NULL THEN
364 x_return_status := OKL_API.G_RET_STS_ERROR;
365 -- Warehouse not defined for this Order Type
366 OKL_API.set_message( p_app_name => 'OKL',
367 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
368 p_token1 => 'TYPE',
369 p_token1_value => 'ORDER',
370 p_token2 => 'NAME',
371 p_token2_value => l_order_name
372 );
373 RAISE OKL_API.G_EXCEPTION_ERROR;
374 END IF;
375
376 IF l_def_outbound_line_type_id IS NULL THEN
377 x_return_status := OKL_API.G_RET_STS_ERROR;
378 -- Line Type not defined for this Order Type
379 OKL_API.set_message( p_app_name => 'OKL',
380 p_msg_name => 'OKL_AM_RMK_NO_LINE_TYPE',
381 p_token1 => 'ORDER_TYPE',
382 p_token1_value => l_order_name
383 );
384 RAISE OKL_API.G_EXCEPTION_ERROR;
385 END IF;
386
387 -- get the warehouse for the Line type corresponding to the Default Order Type
388 OPEN l_oetranstypesall_csr(l_def_outbound_line_type_id);
389 FETCH l_oetranstypesall_csr INTO l_line_warehouse_id, l_def_outbound_line_type_id, l_line_name;
390 -- This fetch will definitely find the record in oe_transaction_types_all
391 CLOSE l_oetranstypesall_csr;
392
393 IF l_line_warehouse_id IS NULL THEN
394 x_return_status := OKL_API.G_RET_STS_ERROR;
395 -- Warehouse not defined for this Line Type
396 OKL_API.set_message( p_app_name => 'OKL',
397 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
398 p_token1 => 'TYPE',
399 p_token1_value => 'LINE',
400 p_token2 => 'NAME',
401 p_token2_value => l_line_name
402 );
403 RAISE OKL_API.G_EXCEPTION_ERROR;
404 END IF;
405
406 IF l_order_warehouse_id <> l_line_warehouse_id THEN
407 x_return_status := OKL_API.G_RET_STS_ERROR;
408 -- Warehouses for the Order Type ORDER_TYPE and Line Type LINE_TYPE do not match.
409 OKL_API.set_message( p_app_name => 'OKL',
410 p_msg_name => 'OKL_AM_RMK_WHS_MISMATCH',
411 p_token1 => 'ORDER_TYPE',
412 p_token1_value => l_order_name,
413 p_token2 => 'LINE_TYPE',
414 p_token2_value => l_line_name
415 );
416 RAISE OKL_API.G_EXCEPTION_ERROR;
417 END IF;
418
419 IF l_inv_org_id <> l_order_warehouse_id THEN
420 x_return_status := OKL_API.G_RET_STS_ERROR;
421 -- Shipping organization should be the same as the inventory item organization.
422 OKL_API.set_message( p_app_name => 'OKL',
423 p_msg_name => 'OKL_AM_RMK_INVALID_WHS'
424 );
425 RAISE OKL_API.G_EXCEPTION_ERROR;
426 END IF;
427
428 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.','End(-)');
430 END IF;
431 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
432
433 EXCEPTION
434 WHEN OKL_API.G_EXCEPTION_ERROR THEN
435 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
436 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info',
437 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
438 END IF;
439
440 IF l_mtlsystemitems_csr%ISOPEN THEN
441 CLOSE l_mtlsystemitems_csr;
442 END IF;
443
444 IF l_mtlorgcsr%ISOPEN THEN
445 CLOSE l_mtlorgcsr;
446 END IF;
447
448
449 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
450 IF l_qplisthdr_csr%ISOPEN THEN
451 CLOSE l_qplisthdr_csr;
452 END IF;
453
454 IF l_oetranstypesall_csr%ISOPEN THEN
455 CLOSE l_oetranstypesall_csr;
456 END IF;
457
458
459 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
460 IF l_systemparamsall_csr%ISOPEN THEN
461 CLOSE l_systemparamsall_csr;
462 END IF;
463
464 IF l_assetreturn_csr%ISOPEN THEN
465 CLOSE l_assetreturn_csr;
466 END IF;
467
468 IF l_mtltemplates_csr%ISOPEN THEN
469 CLOSE l_mtltemplates_csr;
470 END IF;
471 x_return_status := OKL_API.HANDLE_EXCEPTIONS
472 (
473 l_api_name,
474 G_PKG_NAME,
475 'OKL_API.G_RET_STS_ERROR',
476 x_msg_count,
477 x_msg_data,
478 '_PVT'
479 );
480 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
481
482 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info.',
484 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
485 END IF;
486
487 IF l_mtlsystemitems_csr%ISOPEN THEN
488 CLOSE l_mtlsystemitems_csr;
489 END IF;
490
491 IF l_mtlorgcsr%ISOPEN THEN
492 CLOSE l_mtlorgcsr;
493 END IF;
494
495 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
496 IF l_qplisthdr_csr%ISOPEN THEN
497 CLOSE l_qplisthdr_csr;
498 END IF;
499
500 IF l_oetranstypesall_csr%ISOPEN THEN
501 CLOSE l_oetranstypesall_csr;
502 END IF;
503
504 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
505 IF l_systemparamsall_csr%ISOPEN THEN
506 CLOSE l_systemparamsall_csr;
507 END IF;
508
509 IF l_assetreturn_csr%ISOPEN THEN
510 CLOSE l_assetreturn_csr;
511 END IF;
512
513 IF l_mtltemplates_csr%ISOPEN THEN
514 CLOSE l_mtltemplates_csr;
515 END IF;
516 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
517 (
518 l_api_name,
519 G_PKG_NAME,
520 'OKL_API.G_RET_STS_UNEXP_ERROR',
521 x_msg_count,
522 x_msg_data,
523 '_PVT'
524 );
525 WHEN OTHERS THEN
526
527 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.validate_item_info. ',
529 'EXCEPTION :'||sqlerrm);
530 END IF;
531
532 IF l_mtlsystemitems_csr%ISOPEN THEN
533 CLOSE l_mtlsystemitems_csr;
534 END IF;
535
536 IF l_mtlorgcsr%ISOPEN THEN
537 CLOSE l_mtlorgcsr;
538 END IF;
539 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
540 IF l_qplisthdr_csr%ISOPEN THEN
541 CLOSE l_qplisthdr_csr;
542 END IF;
543
544 IF l_oetranstypesall_csr%ISOPEN THEN
545 CLOSE l_oetranstypesall_csr;
546 END IF;
547
548 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
549 IF l_systemparamsall_csr%ISOPEN THEN
550 CLOSE l_systemparamsall_csr;
551 END IF;
552
553 IF l_assetreturn_csr%ISOPEN THEN
554 CLOSE l_assetreturn_csr;
555 END IF;
556
557 IF l_mtltemplates_csr%ISOPEN THEN
558 CLOSE l_mtltemplates_csr;
559 END IF;
560 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
561 (
562 l_api_name,
563 G_PKG_NAME,
564 'OTHERS',
565 x_msg_count,
566 x_msg_data,
567 '_PVT'
568 );
569
570
571 END validate_item_info;
572
573
574 -- Start of comments
575 --
576 -- Procedure Name : create_inv_item
577 -- Description : Create Inventory Item
578 -- Business Rules :
579 -- Parameters : Input parameters : p_asset_return_id -- Asset Return ID
580 -- p_Organization_Id -- Org ID
581 -- p_organization_name -- Org Name
582 -- p_Item_Description -- Item Description
583 -- p_subinventory -- Subinventory
584 -- p_sysdate -- System Date
585 -- p_item_number -- Item #
586 -- p_item_templ_id -- Item Template ID
587 --
588 -- Output Parameters : x_New_Item_Number -- Item #
589 -- x_New_Item_Id -- Item ID
590 --
591 -- Version : 1.0
592 -- History : 25-OCT-04 SECHAWLA - Created
593 -- End of comments
594 PROCEDURE create_inv_item
595 ( p_api_version IN NUMBER,
596 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
597 p_asset_return_id IN NUMBER,
598 p_Organization_Id IN NUMBER,
599 p_organization_name IN VARCHAR2 -- remk org name
600 , p_Item_Description IN VARCHAR2
601 , p_subinventory IN VARCHAR2
602 , p_sysdate IN DATE
603 -- SECHAWLA 05-OCT-04 3924244 : p_item_number may be populated for the master org (if user entered item no.)
604 , p_item_number IN VARCHAR2 --SECHAWLA Bug# 2679812 : Added new parameter
605 , p_item_templ_id IN NUMBER
606 , x_New_Item_Number OUT NOCOPY VARCHAR2
607 , x_New_Item_Id OUT NOCOPY NUMBER
608 , x_Return_Status OUT NOCOPY VARCHAR2
609 , x_msg_count OUT NOCOPY NUMBER
610 , x_msg_data OUT NOCOPY VARCHAR2
611 ) IS
612
613 -- This cursor is used to validate Organization and subinventory
614 CURSOR l_mtlsecinv_csr(p_inv_org_id NUMBER, p_subinv_code VARCHAR2) IS
615 SELECT 'Validate Org and Subinv'
616 FROM mtl_secondary_inventories
617 WHERE organization_id = p_inv_org_id
618 AND secondary_inventory_name = p_subinv_code;
619
620 CURSOR l_mtlparam_csr(p_org_id NUMBER) IS
621 SELECT a.master_organization_id, b.organization_name master_org_name
622 FROM mtl_parameters a , ORG_ORGANIZATION_DEFINITIONS b
623 WHERE a.organization_id = p_org_id
624 AND a.master_organization_id = b.organization_id ;
625
626
627
628
629 --- from create_inv_item------
630 SUBTYPE item_rec_type IS inv_item_grp.item_rec_type;
631 SUBTYPE error_tbl_type IS inv_item_grp.error_tbl_type;
632 SUBTYPE artv_rec_type IS OKL_ASSET_RETURNS_PUB.artv_rec_type;
633 -- sequence for item_number
634 CURSOR l_seqnextval_csr IS
635 SELECT OKL_IMR_SEQ.NEXTVAL
636 FROM DUAL;
637
638 l_Item_rec item_rec_type;
639 x_Item_rec item_rec_type;
640 l_commit VARCHAR2(1);
641 l_validation_level NUMBER;
642 x_Error_tbl error_tbl_type;
643 l_description VARCHAR2(240);
644 l_long_description VARCHAR2(4000);
645 l_Item_Number VARCHAR2(2000);
646 -- l_Organization_Id NUMBER;
647 l_return_status VARCHAR2(1);
648 l_api_name CONSTANT VARCHAR2(30) := 'create_inv_item';
649 l_api_version CONSTANT NUMBER := 1;
650 ---
651 l_master_org_id NUMBER;
652 l_master_org_name ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
653 l_iterations NUMBER;
654 l_current_org NUMBER;
655 l_current_org_name ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE; -- SECHAWLA 08-DEC-04 4047159
656 l_item_id NUMBER;
657 l_New_Item_Number VARCHAR2(2000);
658 l_New_Item_Id NUMBER;
659 l_temp VARCHAR2(25);
660 l_assign_subinv VARCHAR2(1);
661
662 lp_artv_rec artv_rec_type;
663 lx_artv_rec artv_rec_type;
664
665 BEGIN
666 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
667 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item','Begin(+)');
668 END IF;
669
670 --Print Input Variables
671 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
672 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
673 'p_api_version :'||p_api_version);
674 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
675 'p_init_msg_list :'||p_init_msg_list);
676 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
677 'p_asset_return_id :'||p_asset_return_id);
678 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
679 'p_Organization_Id :'||p_Organization_Id);
680 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
681 'p_organization_name :'||p_organization_name);
682 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
683 'p_Item_Description :'||p_Item_Description);
684 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
685 'p_subinventory :'||p_subinventory);
686 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
687 'p_sysdate :'||p_sysdate);
688 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
689 'p_item_number :'||p_item_number);
690 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
691 'p_item_templ_id :'||p_item_templ_id);
692
693 END IF;
694
695
696
697
698 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
699 G_PKG_NAME,
700 p_init_msg_list,
701 l_api_version,
702 p_api_version,
703 '_PVT',
704 x_return_status);
705
706
707 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
708 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
709 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
710 RAISE OKL_API.G_EXCEPTION_ERROR;
711 END IF;
712
713 -- SECHAWLA Bug# 2679812 : Get the Master Org for the Inv Org
714 OPEN l_mtlparam_csr(p_organization_id);
715
716 -- SECAHWLA 08-MAR-04 3492490 : Added master org name
717 FETCH l_mtlparam_csr INTO l_master_org_id, l_master_org_name;
718 IF l_mtlparam_csr%NOTFOUND THEN
719 x_return_status := OKL_API.G_RET_STS_ERROR;
720 -- Inventory organization is not set up in MTL Parameters.
721 OKL_API.set_message( p_app_name => 'OKL',
722 p_msg_name => 'OKL_AM_NO_ORG_PARAM'
723 );
724 RAISE OKL_API.G_EXCEPTION_ERROR;
725 END IF;
726 IF l_master_org_id IS NULL THEN
727 x_return_status := OKL_API.G_RET_STS_ERROR;
728 -- Master organization is not defined for inventory organization.
729 OKL_API.set_message( p_app_name => 'OKL',
730 p_msg_name => 'OKL_AM_NO_MASTER_ORG'
731 );
732 RAISE OKL_API.G_EXCEPTION_ERROR;
733 END IF;
734 CLOSE l_mtlparam_csr;
735
736 -- SECHAWLA Bug# 2679812 :
737 -- If inv org is a master org then create item only in master org. If 2 orgs are different, then first craete
738 -- the item in master org and then assign the same item to child org
739 IF p_organization_id = l_master_org_id THEN
740 l_iterations := 1;
741 ELSE
742 l_iterations := 2;
743 END IF;
744
745 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
747 'l_iterations '||l_iterations);
748 END IF;
749
750
751 -- SECHAWLA Bug# 2679812 :
752 -- This loop is executed once if the inv org is also a master org whereas if the 2 orgs are different,
753 -- this loop is executed twice, once for the master org and then for the child org
754 FOR i IN 1..l_iterations LOOP
755
756 IF i = 1 THEN
757 l_current_org := l_master_org_id;
758 l_current_org_name := l_master_org_name; -- SECHAWLA 08-DEC-04 4047159
759 --l_item_number := NULL; -- SECHAWLA 05-OCT-04 3924244
760 l_item_number := p_item_number; -- SECHAWLA 05-OCT-04 3924244 : Use item no. entered by the user. It may be NULL
761 l_item_id := NULL;
762 ELSIF i = 2 THEN
763 l_current_org := p_organization_id; --child org
764 l_current_org_name := p_organization_name; -- SECHAWLA 08-DEC-04 4047159
765 l_item_number := l_New_Item_Number;
766 l_item_id := l_New_Item_Id;
767 END IF;
768
769 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
770 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
771 'l_current_org'||l_current_org);
772 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
773 'l_item_number'||l_item_number);
774 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
775 'l_item_id'||l_item_id);
776 END IF;
777
778 ---------------------
779 -- SECHAWLA 08-MAR-04 3492490 : Moved the following validation here from the beginning. So it can be
780 -- performed for both Master and Child org
781
782 OPEN l_mtlsecinv_csr(l_current_org , p_subinventory );
783 FETCH l_mtlsecinv_csr INTO l_temp;
784 IF l_mtlsecinv_csr%NOTFOUND THEN
785 -- x_return_status := OKL_API.G_RET_STS_ERROR;
786
787 IF (p_organization_id <> l_master_org_id AND i = 1 )THEN
788 /* SECHAWLA 19-MAY-04 3634514 : Commented out
789 --Subinventory SUBINVENTORY is not defined for the organization MASTER_ORG, which is the Master organization of the Remarketing Inventory organization CHILD_ORG.
790 OKL_API.set_message(p_app_name => 'OKL',
791 p_msg_name => 'OKL_AM_RMK_MST_ORG_SUBINV',
792 p_token1 => 'SUBINVENTORY',
793 p_token1_value => l_subinv_code,
794 p_token2 => 'MASTER_ORG',
795 p_token2_value => l_master_org_name,
796 p_token3 => 'CHILD_ORG',
797 p_token3_value => l_temp_org_name);
798 */
799 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
801 '(l_inv_org_id <> l_master_org_id AND i = 1) sts'||x_return_status);
802
803 END IF;
804 NULL;
805 ELSE
806 x_return_status := OKL_API.G_RET_STS_ERROR;
807 --Subinventory SUBINVENTORY is invalid for the organization ORGANIZATION.
808 OKL_API.set_message(p_app_name => 'OKL',
809 p_msg_name => 'OKL_AM_RMK_ORG_SUBINV',
810 p_token1 => 'SUBINVENTORY',
811 p_token1_value => p_subinventory,
812 p_token2 => 'ORGANIZATION',
813 p_token2_value => p_organization_name);
814 RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Added
815 END IF;
816
817 --RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Commented out
818 END IF;
819 CLOSE l_mtlsecinv_csr;
820
821
822 --SECHAWLA 19-MAY-04 3634514 : populate subinventory fields in the item master only if the
823 --Remarketing org is Master Org OR if the remarketing org is the child org and the item is
824 --being assigned to the child org
825
826 IF (l_iterations = 1) OR (i = 2) THEN
827 l_assign_subinv := 'Y';
828 ELSE
829 l_assign_subinv := 'N';
830 END IF;
831 --SECHAWLA 19-MAY-04 3634514 : end
832
833
834 ---------------------
835
836 -- Create the Item in Inventory
837 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
838
839
840
841 IF l_item_number IS NULL THEN --SECHAWLA Bug# 2679812 : Item number is null when the item has not been created
842 --in the master org yet.
843 OPEN l_seqnextval_csr;
844 FETCH l_seqnextval_csr INTO l_Item_Number;
845 IF l_seqnextval_csr%NOTFOUND THEN
846 x_return_status := OKL_API.G_RET_STS_ERROR;
847 -- Failed to create sequence for Item Number
848 OKL_API.set_message( p_app_name => 'OKL',
849 p_msg_name => 'OKL_AM_RMK_NO_ITEM_NUM_SEQ'
850 );
851 RAISE okl_api.G_EXCEPTION_ERROR;
852 END IF;
853 CLOSE l_seqnextval_csr;
854 --SECHAWLA 14-MAR-03 : Prefixed the sequence generated item number with 'OKL' to prevent duplicate
855 -- item numbers within the same org, incase an external application has already created an item with same
856 -- item # generated by OKL's sequence.
857
858 l_Item_rec.ITEM_NUMBER := 'OKL'||l_Item_Number;
859 ELSE
860 l_Item_rec.ITEM_NUMBER := l_item_number; --SECHAWLA Bug# 2679812 : If Item has alreday been created in the
861 -- master org, then use the same item number for the child org assignment
862 END IF;
863
864 --SECHAWLA Bug# 2679812 : Item id is not null when the item has already been created in the master org. use the
865 --same item id for child org assignment
866 IF l_item_id IS NOT NULL THEN
867 l_Item_rec.INVENTORY_ITEM_ID := l_item_id;
868 END IF;
869
870
871 IF (p_Item_Description IS NULL) THEN
872 l_description := l_Item_Number;
873 l_long_description := l_Item_Number;
874 ELSE
875 l_description := p_Item_Description;
876 l_long_description := p_Item_Description;
877 END IF;
878
879 l_Item_rec.ORGANIZATION_ID := l_current_org;
880
881 l_Item_rec.ENABLED_FLAG := 'Y';
882 -- SECHAWLA 15-MAY-04 3633627 : start_date_active should not have the time portion
883 l_Item_rec.START_DATE_ACTIVE := trunc(p_sysdate);
884 l_Item_rec.DESCRIPTION := l_description;
885 l_Item_rec.LONG_DESCRIPTION := l_long_description;
886
887 IF p_item_templ_id IS NULL THEN -- No template is specified
888 l_Item_rec.PRIMARY_UOM_CODE := 'EA';
889 l_Item_rec.INVENTORY_ITEM_STATUS_CODE := 'Active';
890 l_Item_rec.ITEM_TYPE := 'I';
891 l_Item_rec.INVENTORY_ITEM_FLAG := 'Y';
892 l_Item_rec.STOCK_ENABLED_FLAG := 'Y';
893 l_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG := 'Y';
894 l_Item_rec.CUSTOMER_ORDER_FLAG := 'Y';
895 l_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG := 'Y';
896 l_Item_rec.SHIPPABLE_ITEM_FLAG := 'Y';
897 l_Item_rec.INTERNAL_ORDER_FLAG := 'Y';
898 l_Item_rec.INTERNAL_ORDER_ENABLED_FLAG := 'Y';
899 l_Item_rec.ATP_FLAG := 'Y';
900 l_Item_rec.SO_TRANSACTIONS_FLAG := 'Y';
901 l_Item_rec.ORDERABLE_ON_WEB_FLAG := 'Y';
902 l_Item_rec.WEB_STATUS := 'PUBLISHED';
903
904 -- SECHAWLA 08-DEC-04 4047159 : Need to set the following 2 attributes for the billing process
905 l_Item_rec.invoiceable_item_flag := 'Y';
906 l_Item_rec.invoice_enabled_flag := 'Y';
907 END IF;
908 -- subinventory and distribution account ID are retrieved from profiles at the time of item creation. Since profiles
909 -- may change between the time item is created and time when Order is booked against the item, we store these values
910 -- in the following fields. Later when we reduce the quantitiy of an item, after the order is booked, we can
911 -- query mtl_system_items with inventory_item_id and org_id and get source_subinventory and distribution account.
912
913 --SECHAWLA 19-MAY-04 3634514: Populate subinventory only if p_assign_subinv = 'Y'
914 IF l_assign_subinv = 'Y' THEN
915 l_item_rec.SOURCE_TYPE := 1;
916 l_Item_rec.SOURCE_SUBINVENTORY := p_subinventory;
917 l_Item_rec.SOURCE_ORGANIZATION_ID := l_current_org;
918 END IF;
919 -- SECHAWLA Bug# 2620853 : Distribution accout id is not stored, as it is optional
920 -- l_Item_rec.ENCUMBRANCE_ACCOUNT := p_distribution_acct_id;
921
922 l_commit := okl_api.g_FALSE;
923
924 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
926 'before INV_Item_GRP.create_item'||x_return_status);
927
928 END IF;
929 IF p_item_templ_id IS NULL THEN
930 INV_Item_GRP.create_item
931 ( p_commit => l_commit
932 , p_validation_level => l_validation_level
933 , p_Item_rec => l_Item_rec
934 , x_Item_rec => x_item_rec
935 , x_return_status => x_return_status
936 , x_Error_tbl => x_Error_tbl
937 );
938 ELSE
939 INV_Item_GRP.create_item
940 ( p_commit => l_commit
941 , p_validation_level => l_validation_level
942 , p_Item_rec => l_Item_rec
943 , x_Item_rec => x_item_rec
944 , x_return_status => x_return_status
945 , x_Error_tbl => x_Error_tbl
946 , p_Template_Id => p_item_templ_id
947 );
948 END IF;
949
950 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
951 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
952 'after INV_Item_GRP.create_item'||x_return_status);
953
954 END IF;
955 IF ( x_return_status = okl_api.G_RET_STS_SUCCESS ) THEN
956 l_New_Item_Number := x_item_rec.ITEM_NUMBER;
957 l_New_Item_Id := x_item_rec.INVENTORY_ITEM_ID;
958 ELSE
959
960 -- SECHAWLA 08-DEC-04 4047159 : added the following message
961 -- Error creating inventory item ITEM_NUMBER in organization ORG_NAME.
962 OKL_API.set_message( p_app_name => 'OKL',
963 p_msg_name => 'OKL_AM_RMK_ITEM_FAILED',
964 p_token1 => 'ITEM_NUMBER',
965 p_token1_value => l_Item_rec.ITEM_NUMBER,
966 p_token2 => 'ORG_NAME',
967 p_token2_value => l_current_org_name);
968
969 -- display the error messages from the x_error_tbl table
970 FOR i IN 1 .. x_Error_tbl.COUNT LOOP
971 -- Error: Transaction Id = TRX_ID
972 OKL_API.set_message( p_app_name => 'OKL',
973 p_msg_name => 'OKL_AM_RMK_TRANS_ID',
974 p_token1 => 'TRX_ID',
975 p_token1_value => x_Error_tbl(i).TRANSACTION_ID
976 );
977 -- Error : Unique Id = UNIQUE_ID
978 OKL_API.set_message( p_app_name => 'OKL',
979 p_msg_name => 'OKL_AM_RMK_UNIQUE_ID',
980 p_token1 => 'UNIQUE_ID',
981 p_token1_value => x_Error_tbl(i).UNIQUE_ID
982 );
983 -- Error : Table Name = TABLE_NAME
984 OKL_API.set_message( p_app_name => 'OKL',
985 p_msg_name => 'OKL_AM_RMK_TABLE_NAME',
986 p_token1 => 'TABLE_NAME',
987 p_token1_value => x_Error_tbl(i).TABLE_NAME
988 );
989
990 --Error : Column Name = COLUMN_NAME
991 OKL_API.set_message( p_app_name => 'OKL',
992 p_msg_name => 'OKL_AM_RMK_COLUMN_NAME',
993 p_token1 => 'COLUMN_NAME',
994 p_token1_value => x_Error_tbl(i).COLUMN_NAME
995 );
996
997 --Error : Message Name = MSG_NAME
998 OKL_API.set_message( p_app_name => 'OKL',
999 p_msg_name => 'OKL_AM_RMK_MSG_NAME',
1000 p_token1 => 'MSG_NAME',
1001 p_token1_value => x_Error_tbl(i).MESSAGE_NAME
1002 );
1003
1004 -- Error : Message Text = MSG_TEXT
1005 OKL_API.set_message( p_app_name => 'OKL',
1006 p_msg_name => 'OKL_AM_RMK_MSG_TEXT',
1007 p_token1 => 'MSG_TEXT',
1008 p_token1_value => x_Error_tbl(i).MESSAGE_TEXT
1009 );
1010
1011 END LOOP;
1012 RAISE OKL_API.G_EXCEPTION_ERROR;
1013 END IF;
1014
1015 -----------
1016
1017
1018
1019 END LOOP;
1020
1021 -------
1022 -- set the item_id and Item_name
1023 lp_artv_rec.id := p_asset_return_id;
1024 lp_artv_rec.imr_id := l_new_item_id;
1025 --If item number is automatically generated, then
1026 -- populate new col new_item_number with item number
1027 IF p_item_number IS NULL THEN -- user did not enter item no.
1028 lp_artv_rec.new_item_number := l_new_item_number;
1029 END IF;
1030
1031 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1032 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
1033 'before OKL_ASSET_RETURNS_PUB.update_asset_returns'||x_return_status);
1034
1035 END IF;
1036
1037 -- call update of tapi
1038 OKL_ASSET_RETURNS_PUB.update_asset_returns(
1039 p_api_version => p_api_version,
1040 p_init_msg_list => OKL_API.G_FALSE,
1041 x_return_status => x_return_status,
1042 x_msg_count => x_msg_count,
1043 x_msg_data => x_msg_data,
1044 p_artv_rec => lp_artv_rec,
1045 x_artv_rec => lx_artv_rec);
1046
1047 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
1049 'after OKL_ASSET_RETURNS_PUB.update_asset_returns'||x_return_status);
1050
1051 END IF;
1052
1053 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1054 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1055 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1056 RAISE OKL_API.G_EXCEPTION_ERROR;
1057 END IF;
1058 -------
1059
1060 x_New_Item_Number := l_new_item_number;
1061 x_New_Item_Id := l_new_item_id;
1062
1063 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
1065 'x_New_Item_Number..'||x_New_Item_Number);
1066 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item.',
1067 'x_New_Item_Id..'||x_New_Item_Id);
1068
1069 END IF;
1070
1071 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1072
1073 EXCEPTION
1074 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1075
1076 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1077 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_item',
1078 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1079 END IF;
1080
1081 IF l_mtlsecinv_csr%ISOPEN THEN
1082 CLOSE l_mtlsecinv_csr;
1083 END IF;
1084
1085 IF l_mtlparam_csr%ISOPEN THEN
1086 CLOSE l_mtlparam_csr;
1087 END IF;
1088
1089 IF l_seqnextval_csr%ISOPEN THEN
1090 CLOSE l_seqnextval_csr;
1091 END IF;
1092
1093 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1094 (
1095 l_api_name,
1096 G_PKG_NAME,
1097 'OKL_API.G_RET_STS_ERROR',
1098 x_msg_count,
1099 x_msg_data,
1100 '_PVT'
1101 );
1102 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1103 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_inv_item',
1105 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1106 END IF;
1107
1108 IF l_mtlsecinv_csr%ISOPEN THEN
1109 CLOSE l_mtlsecinv_csr;
1110 END IF;
1111
1112 IF l_mtlparam_csr%ISOPEN THEN
1113 CLOSE l_mtlparam_csr;
1114 END IF;
1115
1116 IF l_seqnextval_csr%ISOPEN THEN
1117 CLOSE l_seqnextval_csr;
1118 END IF;
1119 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1120 (
1121 l_api_name,
1122 G_PKG_NAME,
1123 'OKL_API.G_RET_STS_UNEXP_ERROR',
1124 x_msg_count,
1125 x_msg_data,
1126 '_PVT'
1127 );
1128 WHEN OTHERS THEN
1129
1130 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1131 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_inv_item',
1132 'EXCEPTION :'||sqlerrm);
1133 END IF;
1134
1135 IF l_mtlsecinv_csr%ISOPEN THEN
1136 CLOSE l_mtlsecinv_csr;
1137 END IF;
1138
1139 IF l_mtlparam_csr%ISOPEN THEN
1140 CLOSE l_mtlparam_csr;
1141 END IF;
1142
1143 IF l_seqnextval_csr%ISOPEN THEN
1144 CLOSE l_seqnextval_csr;
1145 END IF;
1146
1147 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OTHERS',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156
1157 END create_inv_item;
1158
1159 -- Start of comments
1160 --
1161 -- Procedure Name : create_inv_misc_receipt
1162 -- Description : Create Inventory Misc Receipt
1163 -- Business Rules :
1164 -- Parameters : Input parameters : p_Inventory_Item_id -- Inventory Item ID
1165 -- p_Subinv_Code -- Subinventory Code
1166 -- p_Organization_Id -- Org ID
1167 -- p_quantity -- Item quantity
1168 -- p_trans_type_id -- Transaction Type ID
1169 -- p_sysdate -- System Date
1170 --
1171 --
1172 -- Version : 1.0
1173 -- History : 25-OCT-04 SECHAWLA - Created
1174 -- End of comments
1175
1176 PROCEDURE create_inv_misc_receipt(
1177 p_api_version IN NUMBER,
1178 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1179 p_Inventory_Item_id IN NUMBER
1180 , p_Subinv_Code IN VARCHAR2
1181 , p_Organization_Id IN NUMBER
1182 -- SECHAWLA Bug# 2620853 : Distribution accout id is not required
1183 -- , p_Dist_account_id IN NUMBER
1184 , p_quantity IN NUMBER
1185 , p_trans_type_id IN NUMBER
1186 , p_sysdate IN DATE
1187 , x_Return_Status OUT NOCOPY VARCHAR2
1188 , x_msg_count OUT NOCOPY NUMBER
1189 , x_msg_data OUT NOCOPY VARCHAR2
1190 )
1191 IS
1192 l_return_status VARCHAR2(1);
1193 l_api_name CONSTANT VARCHAR2(30) := 'create_inv_misc_receipt';
1194 l_api_version CONSTANT NUMBER := 1;
1195
1196 BEGIN
1197 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt','Begin(+)');
1199 END IF;
1200
1201 --Print Input Variables
1202 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1204 'p_api_version :'||p_api_version);
1205 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1206 'p_init_msg_list :'||p_init_msg_list);
1207 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1208 'p_Inventory_Item_id :'||p_Inventory_Item_id);
1209 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1210 'p_Subinv_Code :'||p_Subinv_Code);
1211 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1212 'p_Organization_Id :'||p_Organization_Id);
1213 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1214 'p_quantity :'||p_quantity);
1215 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1216 'p_trans_type_id :'||p_trans_type_id);
1217 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt.',
1218 'p_sysdate :'||p_sysdate);
1219
1220 END IF;
1221
1222
1223
1224 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1225 G_PKG_NAME,
1226 p_init_msg_list,
1227 l_api_version,
1228 p_api_version,
1229 '_PVT',
1230 x_return_status);
1231
1232
1233 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1234 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1235 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1236 RAISE OKL_API.G_EXCEPTION_ERROR;
1237 END IF;
1238
1239 -- There is a direct insert into the table here as there is no TAPI with insert procedure to insert into
1240 -- mtl_transactions_interface
1241
1242 INSERT INTO mtl_transactions_interface
1243 (source_code,
1244 source_header_id,
1245 lock_Flag,
1246 Source_line_id,
1247 process_flag,
1248 transaction_mode,
1249 last_update_date,
1250 last_updated_by,
1251 creation_date,
1252 created_by,
1253 transaction_header_id,
1254 validation_required,
1255 inventory_item_id,
1256 organization_id,
1257 subinventory_code,
1258 transaction_quantity,
1259 transaction_uom,
1260 transaction_date,
1261 transaction_type_id,
1262 transaction_reference,
1263 --SECHAWLA Bug # 2620853 : No need to store distribution account id
1264 -- distribution_account_id,
1265 transaction_source_id,
1266 transaction_source_name,
1267 expenditure_type)
1268 VALUES
1269 ('LEASE', /* source_code */
1270 0, /* source_header_id */
1271 -- '', /* lock_Flag */
1272 2, /* lock_Flag */
1273 0, /* Source_line_id */
1274 1, /* process_flag */
1275 3, /* transaction_mode */
1276 p_sysdate, /* last_update_date */
1277 FND_GLOBAL.USER_ID, /* last_updated_by */
1278 p_sysdate, /* creation_date */
1279 FND_GLOBAL.USER_ID, /* created_by */
1280 112, /* transaction_header_id */
1281 1, /* validation_required */
1282 p_Inventory_Item_id, /* inventory_item_id */
1283 p_Organization_Id, /* organization_id */
1284 p_Subinv_Code, /* subinventory_code */
1285 p_quantity, /* transaction_quantity */
1286 'EA', /* transaction_uom */
1287 p_sysdate, /* transaction_date */
1288 p_trans_type_id, /* transaction_type_id */
1289 'LEASE' , /* transaction_reference */
1290 -- SECHAWLA Bug# 2620853 : No need to store distribution account id
1291 -- p_Dist_account_id, /* distribution_account_id */
1292 0, /* transaction_source_id */
1293 'LEASE', /* transaction_source_name */
1294 '' /* expenditure_type */
1295 );
1296 x_return_status := okl_api.g_RET_STS_SUCCESS;
1297
1298 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1299 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt ','End(-)');
1300 END IF;
1301
1302 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1303 EXCEPTION
1304 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1305 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1306 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt',
1307 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1308 END IF;
1309
1310 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1311 (
1312 l_api_name,
1313 G_PKG_NAME,
1314 'OKL_API.G_RET_STS_ERROR',
1315 x_msg_count,
1316 x_msg_data,
1317 '_PVT'
1318 );
1319 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1320 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1321 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt',
1322 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1323 END IF;
1324
1325 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1326 (
1327 l_api_name,
1328 G_PKG_NAME,
1329 'OKL_API.G_RET_STS_UNEXP_ERROR',
1330 x_msg_count,
1331 x_msg_data,
1332 '_PVT'
1333 );
1334 WHEN OTHERS THEN
1335 -- unexpected error
1336 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1337 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.create_inv_misc_receipt ',
1338 'EXCEPTION :'||sqlerrm);
1339 END IF;
1340 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1341 OKL_API.set_message(p_app_name => 'OKC',
1342 p_msg_name => g_unexpected_error,
1343 p_token1 => g_sqlcode_token,
1344 p_token1_value => sqlcode,
1345 p_token2 => g_sqlerrm_token,
1346 p_token2_value => sqlerrm);
1347
1348 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1349 (
1350 l_api_name,
1351 G_PKG_NAME,
1352 'OTHERS',
1353 x_msg_count,
1354 x_msg_data,
1355 '_PVT'
1356 );
1357
1358
1359 END Create_Inv_Misc_Receipt;
1360
1361 -- Procedure Name : Create_Item_Price_List
1362 -- Description : Create Item in price List
1363 -- Business Rules :
1364 -- Parameters : Input parameters : p_Price_List_id - price list Id from setup,
1365 -- p_Item_Id - Item Id of the newly craeted item
1366 -- p_Item_Price - price of the item ( from asset return)
1367 --
1368 -- History : 25-OCT-04 SECHAWLA - Created
1369 -- Version : 1.0
1370 -- End of comments
1371
1372 PROCEDURE Create_Item_Price_List
1373 ( p_api_version IN NUMBER
1374 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1375 , p_Price_List_id IN NUMBER
1376 , p_Item_Id IN NUMBER
1377 , p_Item_Price IN NUMBER
1378 , x_return_status OUT NOCOPY VARCHAR2
1379 , x_msg_count OUT NOCOPY NUMBER
1380 , x_msg_data OUT NOCOPY VARCHAR2
1381 )
1382 IS
1383 l_msg_count NUMBER:= 0;
1384 l_msg_data VARCHAR2(2000);
1385 l_return_status VARCHAR2(1) := NULL;
1386 gpr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1387 gpr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
1388 gpr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1389 gpr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
1390 gpr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
1391 gpr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
1392 gpr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1393 gpr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
1394 ppr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1395 ppr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
1396 ppr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1397 ppr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
1398 ppr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
1399 ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
1400 ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1401 ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
1402 k NUMBER;
1403
1404 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Price_List';
1405 l_api_version CONSTANT NUMBER := 1;
1406
1407
1408 -- SECHAWLA 08-DEC-04 4047159 : added
1409 -- This cursor is used to get the price list name
1410 CURSOR l_qplisthdr_csr(cp_list_header_id NUMBER) IS
1411 SELECT name
1412 FROM QP_LIST_HEADERS
1413 WHERE LIST_HEADER_ID = cp_list_header_id;
1414
1415 -- SECHAWLA 08-DEC-04 4047159 : Added
1416 CURSOR l_mtlsystemitems_b(cp_item_id IN NUMBER) IS
1417 SELECT segment1
1418 FROM mtl_system_items_b
1419 WHERE inventory_item_id = cp_item_id;
1420
1421 l_pricelist_name QP_LIST_HEADERS.name%TYPE;
1422 l_item_number VARCHAR2(40);
1423 BEGIN
1424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List','Begin(+)');
1426 END IF;
1427
1428 --Print Input Variables
1429 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1430 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1431 'p_api_version :'||p_api_version);
1432 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1433 'p_init_msg_list :'||p_init_msg_list);
1434 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1435 'p_Price_List_id :'||p_Price_List_id);
1436 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1437 'p_Item_Id :'||p_Item_Id);
1438 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1439 'p_Item_Price :'||p_Item_Price);
1440
1441 END IF;
1442
1443
1444 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1445 G_PKG_NAME,
1446 p_init_msg_list,
1447 l_api_version,
1448 p_api_version,
1449 '_PVT',
1450 x_return_status);
1451
1452
1453 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1454 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1455 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1456 RAISE OKL_API.G_EXCEPTION_ERROR;
1457 END IF;
1458
1459 gpr_price_list_rec.list_header_id := p_Price_List_id;
1460 gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1461 gpr_price_list_rec.org_id := mo_global.get_current_org_id(); --CDUBEY l_authoring_org_id added for MOAC
1462
1463 gpr_price_list_line_tbl(1).list_line_id := okl_api.G_MISS_NUM;
1464 gpr_price_list_line_tbl(1).list_line_type_code := 'PLL';
1465 gpr_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
1466 gpr_price_list_line_tbl(1).operand := p_Item_Price;
1467 gpr_price_list_line_tbl(1).arithmetic_operator := 'UNIT_PRICE';
1468
1469
1470 gpr_pricing_attr_tbl(1).pricing_attribute_id := okl_api.G_MISS_NUM;
1471 gpr_pricing_attr_tbl(1).list_line_id := okl_api.G_MISS_NUM;
1472 gpr_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
1473 gpr_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
1474 gpr_pricing_attr_tbl(1).PRODUCT_ATTR_VALUE := to_char(p_Item_Id);
1475 gpr_pricing_attr_tbl(1).PRODUCT_UOM_CODE := 'EA';
1476 gpr_pricing_attr_tbl(1).EXCLUDER_FLAG := 'N';
1477 gpr_pricing_attr_tbl(1).ATTRIBUTE_GROUPING_NO := 1;
1478 gpr_pricing_attr_tbl(1).PRICE_LIST_LINE_INDEX := 1;
1479 gpr_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
1480
1481
1482 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1484 'before QP_PRICE_LIST_PUB.Process_Price_List call status'||x_return_status);
1485 END IF;
1486
1487 QP_PRICE_LIST_PUB.Process_Price_List
1488 ( p_api_version_number => p_api_version
1489 , p_init_msg_list => okl_api.G_FALSE
1490 , p_return_values => okl_api.G_FALSE
1491 , p_commit => okl_api.G_FALSE
1492 , x_return_status => x_return_status
1493 , x_msg_count => l_msg_count
1494 , x_msg_data => l_msg_data
1495 , p_PRICE_LIST_rec => gpr_price_list_rec
1496 , p_PRICE_LIST_LINE_tbl => gpr_price_list_line_tbl
1497 , p_PRICING_ATTR_tbl => gpr_pricing_attr_tbl
1498 , x_PRICE_LIST_rec => ppr_price_list_rec
1499 , x_PRICE_LIST_val_rec => ppr_price_list_val_rec
1500 , x_PRICE_LIST_LINE_tbl => ppr_price_list_line_tbl
1501 , x_PRICE_LIST_LINE_val_tbl => ppr_price_list_line_val_tbl
1502 , x_QUALIFIERS_tbl => ppr_qualifiers_tbl
1503 , x_QUALIFIERS_val_tbl => ppr_qualifiers_val_tbl
1504 , x_PRICING_ATTR_tbl => ppr_pricing_attr_tbl
1505 , x_PRICING_ATTR_val_tbl => ppr_pricing_attr_val_tbl
1506 );
1507
1508 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1509 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List.',
1510 'after QP_PRICE_LIST_PUB.Process_Price_List call status'||x_return_status);
1511 END IF;
1512
1513 -- SECHAWLA 08-DEC-04 4047159 : added the following message
1514 IF ( x_return_status <> okl_api.G_RET_STS_SUCCESS ) THEN
1515 OPEN l_qplisthdr_csr(p_Price_List_id) ;
1516 FETCH l_qplisthdr_csr INTO l_pricelist_name;
1517 CLOSE l_qplisthdr_csr;
1518
1519 OPEN l_mtlsystemitems_b(p_Item_Id) ;
1520 FETCH l_mtlsystemitems_b INTO l_item_number;
1521 CLOSE l_mtlsystemitems_b;
1522
1523 -- Error assigning item ITEM_NUMBER to price list PRICE_LIST.
1524 OKL_API.set_message( p_app_name => 'OKL',
1525 p_msg_name => 'OKL_AM_RMK_PL_FAILED',
1526 p_token1 => 'ITEM_NUMBER',
1527 p_token1_value => l_item_number,
1528 p_token2 => 'PRICE_LIST',
1529 p_token2_value => l_pricelist_name);
1530 END IF;
1531
1532
1533 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1535 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1536 RAISE OKL_API.G_EXCEPTION_ERROR;
1537 END IF;
1538
1539
1540 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1541 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List ','End(-)');
1542 END IF;
1543
1544 -- return status of the above procedure call becomes the return status of the current procedure which is then
1545 -- handled in the calling procedure
1546 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1547
1548 EXCEPTION
1549 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1550
1551 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1552 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List',
1553 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1554 END IF;
1555
1556 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1557 (
1558 l_api_name,
1559 G_PKG_NAME,
1560 'OKL_API.G_RET_STS_ERROR',
1561 x_msg_count,
1562 x_msg_data,
1563 '_PVT'
1564 );
1565 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1566
1567 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List',
1569 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1570 END IF;
1571
1572 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1573 (
1574 l_api_name,
1575 G_PKG_NAME,
1576 'OKL_API.G_RET_STS_UNEXP_ERROR',
1577 x_msg_count,
1578 x_msg_data,
1579 '_PVT'
1580 );
1581 WHEN OTHERS THEN
1582 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1583 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_CUSTOM_RMK_ASSET_PVT.Create_Item_Price_List ',
1584 'EXCEPTION :'||sqlerrm);
1585 END IF;
1586 OKL_API.set_message(p_app_name => 'OKC',
1587 p_msg_name => g_unexpected_error,
1588 p_token1 => g_sqlcode_token,
1589 p_token1_value => sqlcode,
1590 p_token2 => g_sqlerrm_token,
1591 p_token2_value => sqlerrm);
1592 -- unexpected error
1593 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1594 (
1595 l_api_name,
1596 G_PKG_NAME,
1597 'OTHERS',
1598 x_msg_count,
1599 x_msg_data,
1600 '_PVT'
1601 );
1602
1603 END Create_Item_Price_List;
1604
1605 END OKL_AM_CUSTOM_RMK_ASSET_PVT;