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