1 PACKAGE BODY OKL_AM_REMARKET_ASSET_PVT AS
2 /* $Header: OKLRRMKB.pls 120.12 2010/10/15 23:53:47 gkadarka noship $ */
3
4
5
6 -- Start of comments
7 --
8 -- Procedure Name : create_inv_item
9 -- Description : This procedure is called from the main procedure - create_rmk_item.
10 -- create_inv_item creates an inventory item
11 -- Business Rules :
12 -- Parameters : Input parameters : p_Organization_Id - Item's Orga
13 -- p_Item_Description - Item description (from asset return)
14 -- p_subinventory - Item's subinventory
15 -- p_sysdate - system date
16 --
17 -- Output Parameters : x_New_Item_Number - Item number of the newly created item
18 -- x_New_Item_Id - item Id of the newly created item
19 --
20 -- Version : 1.0
21 -- History : SECHAWLA 05-DEC-02 Bug# 2620853
22 -- Commented out the code that references Distribution Account ID, as it is optional
23 -- SECHAWLA 16-JAN-03 Bug # 2754280
24 -- Changed the app name from OKL to OKC for g_unexpected_error
25 -- SECHAWLA 14-MAR-03
26 -- Prefixed the sequence generated item number with 'OKL' to prevent duplicate item numbers
27 -- within the same org
28 -- SECHAWLA 19-MAY-04 Bug # 3633627
29 -- start_date_active should not have the time portion
30 -- SECHAWLA 19-MAY-04 Bug # 3634514
31 -- added p_assign_subinv parameter to conditionally populate subinventory fields
32 -- SECHAWLA 08-DEC-04 4047159 : added p_organization_name parameter and a message
33 -- End of comments
34
35
36 PROCEDURE create_inv_item
37 (
38 p_Organization_Id IN NUMBER
39 , p_organization_name IN VARCHAR2 -- SECHAWLA 08-DEC-04 4047159 added
40 , p_Item_Description IN VARCHAR2
41 , p_subinventory IN VARCHAR2
42 -- SECHAWLA Bug# 2620853 : distribution account id not required
43 --, p_distribution_acct_id IN NUMBER
44 , p_sysdate IN DATE
45 -- SECHAWLA 05-OCT-04 3924244 : p_item_number may be populated for the master org (if user entered item no.)
46 , p_item_number IN VARCHAR2 --SECHAWLA Bug# 2679812 : Added new parameter
47 , p_item_id IN NUMBER --SECHAWLA Bug# 2679812 : Added new parameter
48 , p_assign_subinv IN VARCHAR2 --SECHAWLA 19-MAY-04 3634514: Added new parameter
49 , x_New_Item_Number OUT NOCOPY VARCHAR2
50 , x_New_Item_Id OUT NOCOPY NUMBER
51 , x_Return_Status OUT NOCOPY VARCHAR2
52 )
53 IS
54
55 SUBTYPE item_rec_type IS inv_item_grp.item_rec_type;
56 SUBTYPE error_tbl_type IS inv_item_grp.error_tbl_type;
57
58 -- sequence for item_number
59 CURSOR l_seqnextval_csr IS
60 SELECT OKL_IMR_SEQ.NEXTVAL
61 FROM DUAL;
62
63 l_Item_rec item_rec_type;
64 x_Item_rec item_rec_type;
65 l_commit VARCHAR2(1);
66 l_validation_level NUMBER;
67 l_return_status VARCHAR2(1);
68 x_Error_tbl error_tbl_type;
69 l_description VARCHAR2(240);
70 l_long_description VARCHAR2(4000);
71 l_Item_Number VARCHAR2(2000);
72 l_Organization_Id NUMBER;
73
74 BEGIN
75
76 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
77 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item','Begin(+)');
78 END IF;
79
80 --Print Input Variables
81 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
82 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
83 'p_Organization_Id :'||p_Organization_Id);
84 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
85 'p_Item_Description :'||p_Item_Description);
86 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
87 'p_subinventory :'||p_subinventory);
88 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
89 'p_sysdate :'||p_sysdate);
90 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
91 'p_item_number :'||p_item_number);
92 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
93 'p_item_id :'||p_item_id);
94 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
95 'p_assign_subinv :'||p_assign_subinv);
96 END IF;
97
98 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
99
100 l_Organization_Id := p_Organization_Id;
101
102 IF p_item_number IS NULL THEN --SECHAWLA Bug# 2679812 : Item number is null when the item has not been created
103 --in the master org yet.
104 OPEN l_seqnextval_csr;
105 FETCH l_seqnextval_csr INTO l_Item_Number;
106 IF l_seqnextval_csr%NOTFOUND THEN
107 x_return_status := OKL_API.G_RET_STS_ERROR;
108 -- Failed to create sequence for Item Number
109 OKL_API.set_message( p_app_name => 'OKL',
110 p_msg_name => 'OKL_AM_RMK_NO_ITEM_NUM_SEQ'
111 );
112 RAISE okl_api.G_EXCEPTION_ERROR;
113 END IF;
114 CLOSE l_seqnextval_csr;
115 --SECHAWLA 14-MAR-03 : Prefixed the sequence generated item number with 'OKL' to prevent duplicate
116 -- item numbers within the same org, incase an external application has already created an item with same
117 -- item # generated by OKL's sequence.
118
119 l_Item_rec.ITEM_NUMBER := 'OKL'||l_Item_Number;
120 ELSE
121 l_Item_rec.ITEM_NUMBER := p_item_number; --SECHAWLA Bug# 2679812 : If Item has alreday been created in the
122 -- master org, then use the same item number for the child org assignment
123 END IF;
124
125 --SECHAWLA Bug# 2679812 : Item id is not null when the item has already been created in the master org. use the
126 --same item id for child org assignment
127 IF p_item_id IS NOT NULL THEN
128 l_Item_rec.INVENTORY_ITEM_ID := p_item_id;
129 END IF;
130
131
132 IF (p_Item_Description IS NULL) THEN
133 l_description := l_Item_Number;
134 l_long_description := l_Item_Number;
135 ELSE
136 l_description := p_Item_Description;
137 l_long_description := p_Item_Description;
138 END IF;
139
140 l_Item_rec.ORGANIZATION_ID := l_Organization_Id;
141
142 l_Item_rec.ENABLED_FLAG := 'Y';
143 -- SECHAWLA 15-MAY-04 3633627 : start_date_active should not have the time portion
144 l_Item_rec.START_DATE_ACTIVE := trunc(p_sysdate);
145 l_Item_rec.DESCRIPTION := l_description;
146 l_Item_rec.LONG_DESCRIPTION := l_long_description;
147 l_Item_rec.PRIMARY_UOM_CODE := 'EA';
148
149 l_Item_rec.INVENTORY_ITEM_STATUS_CODE := 'Active';
150 l_Item_rec.ITEM_TYPE := 'I';
151 l_Item_rec.INVENTORY_ITEM_FLAG := 'Y';
152 l_Item_rec.STOCK_ENABLED_FLAG := 'Y';
153 l_Item_rec.MTL_TRANSACTIONS_ENABLED_FLAG := 'Y';
154 l_Item_rec.CUSTOMER_ORDER_FLAG := 'Y';
155 l_Item_rec.CUSTOMER_ORDER_ENABLED_FLAG := 'Y';
156 l_Item_rec.SHIPPABLE_ITEM_FLAG := 'Y';
157 l_Item_rec.INTERNAL_ORDER_FLAG := 'Y';
158 l_Item_rec.INTERNAL_ORDER_ENABLED_FLAG := 'Y';
159 l_Item_rec.ATP_FLAG := 'Y';
160 l_Item_rec.SO_TRANSACTIONS_FLAG := 'Y';
161 l_Item_rec.ORDERABLE_ON_WEB_FLAG := 'Y';
162 l_Item_rec.WEB_STATUS := 'PUBLISHED';
163
164 -- SECHAWLA 08-DEC-04 4047159 : Need to set the following 2 attributes for the billing process
165 l_Item_rec.invoiceable_item_flag := 'Y';
166 l_Item_rec.invoice_enabled_flag := 'Y';
167
168
169 -- subinventory and distribution account ID are retrieved from profiles at the time of item creation. Since profiles
170 -- may change between the time item is created and time when Order is booked against the item, we store these values
171 -- in the following fields. Later when we reduce the quantitiy of an item, after the order is booked, we can
172 -- query mtl_system_items with inventory_item_id and org_id and get source_subinventory and distribution account.
173
174 --SECHAWLA 19-MAY-04 3634514: Populate subinventory only if p_assign_subinv = 'Y'
175 IF p_assign_subinv = 'Y' THEN
176 l_item_rec.SOURCE_TYPE := 1;
177 l_Item_rec.SOURCE_SUBINVENTORY := p_subinventory;
178 l_Item_rec.SOURCE_ORGANIZATION_ID := l_Organization_Id;
179 END IF;
180 -- SECHAWLA Bug# 2620853 : Distribution accout id is not stored, as it is optional
181 -- l_Item_rec.ENCUMBRANCE_ACCOUNT := p_distribution_acct_id;
182
183 l_commit := okl_api.g_FALSE;
184
185 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
186 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
187 'before INV_Item_GRP.create_item call sts'||x_return_status);
188 END IF;
189
190 INV_Item_GRP.create_item
191 (
192 p_commit => l_commit
193 , p_validation_level => l_validation_level
194 , p_Item_rec => l_Item_rec
195 , x_Item_rec => x_item_rec
196 , x_return_status => x_return_status
197 , x_Error_tbl => x_Error_tbl
198 );
199
200 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
202 'after INV_Item_GRP.create_item call sts'||x_return_status);
203 END IF;
204
205 IF ( x_return_status = okl_api.G_RET_STS_SUCCESS ) THEN
206 x_New_Item_Number := x_item_rec.ITEM_NUMBER;
207 x_New_Item_Id := x_item_rec.INVENTORY_ITEM_ID;
208 ELSE
209 -- SECHAWLA 08-DEC-04 4047159 : added the following message
210 -- Error creating inventory item ITEM_NUMBER in organization ORG_NAME.
211 OKL_API.set_message( p_app_name => 'OKL',
212 p_msg_name => 'OKL_AM_RMK_ITEM_FAILED',
213 p_token1 => 'ITEM_NUMBER',
214 p_token1_value => l_Item_rec.ITEM_NUMBER,
215 p_token2 => 'ORG_NAME',
216 p_token2_value => p_organization_name);
217
218
219 -- display the error messages from the x_error_tbl table
220 FOR i IN 1 .. x_Error_tbl.COUNT LOOP
221 -- Error: Transaction Id = TRX_ID
222 OKL_API.set_message( p_app_name => 'OKL',
223 p_msg_name => 'OKL_AM_RMK_TRANS_ID',
224 p_token1 => 'TRX_ID',
225 p_token1_value => x_Error_tbl(i).TRANSACTION_ID
226 );
227 -- Error : Unique Id = UNIQUE_ID
228 OKL_API.set_message( p_app_name => 'OKL',
229 p_msg_name => 'OKL_AM_RMK_UNIQUE_ID',
230 p_token1 => 'UNIQUE_ID',
231 p_token1_value => x_Error_tbl(i).UNIQUE_ID
232 );
233 -- Error : Table Name = TABLE_NAME
234 OKL_API.set_message( p_app_name => 'OKL',
235 p_msg_name => 'OKL_AM_RMK_TABLE_NAME',
236 p_token1 => 'TABLE_NAME',
237 p_token1_value => x_Error_tbl(i).TABLE_NAME
238 );
239
240 --Error : Column Name = COLUMN_NAME
241 OKL_API.set_message( p_app_name => 'OKL',
242 p_msg_name => 'OKL_AM_RMK_COLUMN_NAME',
243 p_token1 => 'COLUMN_NAME',
244 p_token1_value => x_Error_tbl(i).COLUMN_NAME
245 );
246
247 --Error : Message Name = MSG_NAME
248 OKL_API.set_message( p_app_name => 'OKL',
249 p_msg_name => 'OKL_AM_RMK_MSG_NAME',
250 p_token1 => 'MSG_NAME',
251 p_token1_value => x_Error_tbl(i).MESSAGE_NAME
252 );
253
254 -- Error : Message Text = MSG_TEXT
255 OKL_API.set_message( p_app_name => 'OKL',
256 p_msg_name => 'OKL_AM_RMK_MSG_TEXT',
257 p_token1 => 'MSG_TEXT',
258 p_token1_value => x_Error_tbl(i).MESSAGE_TEXT
259 );
260
261 END LOOP;
262 END IF;
263 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
265 'x_New_Item_Number..'||x_New_Item_Number);
266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
267 'x_New_Item_Id..'||x_New_Item_Id);
268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item.',
269 'ret status at the end.. '||x_return_status);
270
271 END IF;
272
273 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item ','End(-)');
275 END IF;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_inv_item ',
281 'EXCEPTION :'||sqlerrm);
282 END IF;
283
284 IF l_seqnextval_csr%ISOPEN THEN
285 CLOSE l_seqnextval_csr;
286 END IF;
287 -- unexpected error
288 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
289 OKL_API.set_message(p_app_name => 'OKC',
290 p_msg_name => g_unexpected_error,
291 p_token1 => g_sqlcode_token,
292 p_token1_value => sqlcode,
293 p_token2 => g_sqlerrm_token,
294 p_token2_value => sqlerrm);
295 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
296
297 END create_inv_item;
298
299
300
301 -- Start of comments
302 --
303 -- Procedure Name : Create_Inv_Misc_Receipt_Txn
304 -- Description : This procedure is called from the main procedure - create_rmk_item.
305 -- Create_Inv_Misc_Receipt_Txn moves the item to inventory
306 -- Business Rules :
307 -- Parameters : Input parameters : p_Inventory_Item_id - Item Id of the newly craeted item,
308 -- p_Subinv_Code - Item subinventory (setup)
309 -- p_Organization_Id - Item's Organization (setup)
310 -- p_Dist_account_id - Distribution Account Id
311 -- p_quantity - Item's quantity
312 -- p_trans_type_id - Transaction Type (Receipt/Issue)
313 -- p_sysdate - system date
314 --
315 -- Version : 1.0
316 -- History : SECHAWLA 05-DEC-02 Bug# 2620853
317 -- Commented out the code that references Distribution account id, as it is optional
318 -- SECHAWLA 16-JAN-03 Bug # 2754280
319 -- Changed the app name from OKL to OKC for g_unexpected_error
320 --
321 -- End of comments
322
323 PROCEDURE Create_Inv_Misc_Receipt_Txn
324 ( p_Inventory_Item_id IN NUMBER
325 , p_Subinv_Code IN VARCHAR2
326 , p_Organization_Id IN NUMBER
327 -- SECHAWLA Bug# 2620853 : Distribution accout id is not required
328 -- , p_Dist_account_id IN NUMBER
329 , p_quantity IN NUMBER
330 , p_trans_type_id IN NUMBER
331 , p_sysdate IN DATE
332 , x_Return_Status OUT NOCOPY VARCHAR2
333 )
334 IS
335
336
337 BEGIN
338
339 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn','Begin(+)');
341 END IF;
342
343 --Print Input Variables
344 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
345 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
346 'p_Inventory_Item_id :'||p_Inventory_Item_id);
347
348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
349 'p_Subinv_Code :'||p_Subinv_Code);
350 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
351 'p_Organization_Id :'||p_Organization_Id);
352
353 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
354 'p_quantity :'||p_quantity);
355 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
356 'p_trans_type_id :'||p_trans_type_id);
357 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
358 'p_sysdate :'||p_sysdate);
359 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn.',
360 'before insert call sts..'||x_return_status);
361 END IF;
362
363 -- There is a direct insert into the table here as there is no TAPI with insert procedure to insert into
364 -- mtl_transactions_interface
365
366 INSERT INTO mtl_transactions_interface
367 (source_code,
368 source_header_id,
369 lock_Flag,
370 Source_line_id,
371 process_flag,
372 transaction_mode,
373 last_update_date,
374 last_updated_by,
375 creation_date,
376 created_by,
377 transaction_header_id,
378 validation_required,
379 inventory_item_id,
380 organization_id,
381 subinventory_code,
382 transaction_quantity,
383 transaction_uom,
384 transaction_date,
385 transaction_type_id,
386 transaction_reference,
387 --SECHAWLA Bug # 2620853 : No need to store distribution account id
388 -- distribution_account_id,
389 transaction_source_id,
390 transaction_source_name,
391 expenditure_type)
392 VALUES
393 ('LEASE', /* source_code */
394 0, /* source_header_id */
395 -- '', /* lock_Flag */
396 2, /* lock_Flag */
397 0, /* Source_line_id */
398 1, /* process_flag */
399 3, /* transaction_mode */
400 p_sysdate, /* last_update_date */
401 FND_GLOBAL.USER_ID, /* last_updated_by */
402 p_sysdate, /* creation_date */
403 FND_GLOBAL.USER_ID, /* created_by */
404 112, /* transaction_header_id */
405 1, /* validation_required */
406 p_Inventory_Item_id, /* inventory_item_id */
407 p_Organization_Id, /* organization_id */
408 p_Subinv_Code, /* subinventory_code */
409 p_quantity, /* transaction_quantity */
410 'EA', /* transaction_uom */
411 p_sysdate, /* transaction_date */
412 p_trans_type_id, /* transaction_type_id */
413 'LEASE' , /* transaction_reference */
414 -- SECHAWLA Bug# 2620853 : No need to store distribution account id
415 -- p_Dist_account_id, /* distribution_account_id */
416 0, /* transaction_source_id */
417 'LEASE', /* transaction_source_name */
418 '' /* expenditure_type */
419 );
420 x_return_status := okl_api.g_RET_STS_SUCCESS;
421
422 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn ','End(-)');
424 END IF;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 -- unexpected error
429 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
430 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.Create_Inv_Misc_Receipt_Txn',
431 'EXCEPTION :'||sqlerrm);
432 END IF;
433 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
434 OKL_API.set_message(p_app_name => 'OKC',
435 p_msg_name => g_unexpected_error,
436 p_token1 => g_sqlcode_token,
437 p_token1_value => sqlcode,
438 p_token2 => g_sqlerrm_token,
439 p_token2_value => sqlerrm);
440 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
441
442
443 END Create_Inv_Misc_Receipt_Txn;
444
445
446 -- Start of comments
447 --
448 -- Procedure Name : Create_Item_In_Price_List
449 -- Description : This procedure is called from the main procedure - create_rmk_item.
450 -- Create_Item_In_Price_List creates a price list for the newly created item
451 -- Business Rules :
452 -- Parameters : Input parameters : p_Price_List_id - price list Id from setup,
453 -- p_Item_Id - Item Id of the newly craeted item
454 -- p_Item_Price - price of the item ( from asset return)
455 --
456 -- History : SECHAWLA 16-JAN-03 Bug # 2754280
457 -- Changed the app name from OKL to OKC for g_unexpected_error
458 -- Version : 1.0
459 -- End of comments
460
461 PROCEDURE Create_Item_In_Price_List
462 ( p_api_version IN NUMBER
463 , p_Price_List_id IN NUMBER
464 , p_price_list_name IN VARCHAR2 -- SECHAWLA 08-DEC-04 4047159 : added
465 , p_price_list_item IN VARCHAR2 -- SECHAWLA 08-DEC-04 4047159 : added
466 , p_Item_Id IN NUMBER
467 , p_Item_Price IN NUMBER
468 , x_return_status OUT NOCOPY VARCHAR2
469 )
470 IS
471 l_msg_count NUMBER:= 0;
472 l_msg_data VARCHAR2(2000);
473 l_return_status VARCHAR2(1) := NULL;
474 gpr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
475 gpr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
476 gpr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
477 gpr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
478 gpr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
479 gpr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
480 gpr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
481 gpr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
482 ppr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
483 ppr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
484 ppr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
485 ppr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
486 ppr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
487 ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
488 ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
489 ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
490 k NUMBER;
491 BEGIN
492 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List','Begin(+)');
494 END IF;
495
496 --Print Input Variables
497 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
498 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List.',
499 'p_Price_List_id :'||p_Price_List_id);
500 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List.',
501 'p_Item_Id :'||p_Item_Id);
502 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List.',
503 'p_Item_Price :'||p_Item_Price);
504
505 END IF;
506
507 gpr_price_list_rec.list_header_id := p_Price_List_id;
508 gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
509 --asawanka commented out as we need not pass org_id
510 --gpr_price_list_rec.org_id := mo_global.get_current_org_id(); --CDUBEY l_authoring_org_id added for MOAC
511
512
513 gpr_price_list_line_tbl(1).list_line_id := okl_api.G_MISS_NUM;
514 gpr_price_list_line_tbl(1).list_line_type_code := 'PLL';
515 gpr_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
516 gpr_price_list_line_tbl(1).operand := p_Item_Price;
517 gpr_price_list_line_tbl(1).arithmetic_operator := 'UNIT_PRICE';
518
519
520 gpr_pricing_attr_tbl(1).pricing_attribute_id := okl_api.G_MISS_NUM;
521 gpr_pricing_attr_tbl(1).list_line_id := okl_api.G_MISS_NUM;
522 gpr_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
523 gpr_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
524 gpr_pricing_attr_tbl(1).PRODUCT_ATTR_VALUE := to_char(p_Item_Id);
525 gpr_pricing_attr_tbl(1).PRODUCT_UOM_CODE := 'EA';
526 gpr_pricing_attr_tbl(1).EXCLUDER_FLAG := 'N';
527 gpr_pricing_attr_tbl(1).ATTRIBUTE_GROUPING_NO := 1;
528 gpr_pricing_attr_tbl(1).PRICE_LIST_LINE_INDEX := 1;
529 gpr_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
530
531
532 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List.',
534 'before QP_PRICE_LIST_PUB.Process_Price_List sts..'||x_return_status);
535 END IF;
536 QP_PRICE_LIST_PUB.Process_Price_List
537 ( p_api_version_number => p_api_version
538 , p_init_msg_list => okl_api.G_FALSE
539 , p_return_values => okl_api.G_FALSE
540 , p_commit => okl_api.G_FALSE
541 , x_return_status => x_return_status
542 , x_msg_count => l_msg_count
543 , x_msg_data => l_msg_data
544 , p_PRICE_LIST_rec => gpr_price_list_rec
545 , p_PRICE_LIST_LINE_tbl => gpr_price_list_line_tbl
546 , p_PRICING_ATTR_tbl => gpr_pricing_attr_tbl
547 , x_PRICE_LIST_rec => ppr_price_list_rec
548 , x_PRICE_LIST_val_rec => ppr_price_list_val_rec
549 , x_PRICE_LIST_LINE_tbl => ppr_price_list_line_tbl
550 , x_PRICE_LIST_LINE_val_tbl => ppr_price_list_line_val_tbl
551 , x_QUALIFIERS_tbl => ppr_qualifiers_tbl
552 , x_QUALIFIERS_val_tbl => ppr_qualifiers_val_tbl
553 , x_PRICING_ATTR_tbl => ppr_pricing_attr_tbl
554 , x_PRICING_ATTR_val_tbl => ppr_pricing_attr_val_tbl
555 );
556
557 -- return status of the above procedure call becomes the return status of the current procedure which is then
558 -- handled in the calling procedure - create_rmk_item
559 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
560 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List.',
561 'after QP_PRICE_LIST_PUB.Process_Price_List sts..'||x_return_status);
562 END IF;
563
564 -- SECHAWLA 08-DEC-04 4047159 : added the following message
565 IF ( x_return_status <> okl_api.G_RET_STS_SUCCESS ) THEN
566 -- Error assigning item ITEM_NUMBER to price list PRICE_LIST.
567 OKL_API.set_message( p_app_name => 'OKL',
568 p_msg_name => 'OKL_AM_RMK_PL_FAILED',
569 p_token1 => 'ITEM_NUMBER',
570 p_token1_value => p_price_list_item,
571 p_token2 => 'PRICE_LIST',
572 p_token2_value => p_price_list_name);
573 END IF;
574
575 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
576 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List ','End(-)');
577 END IF;
578 EXCEPTION
579 WHEN OTHERS THEN
580 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
581 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.Create_Item_In_Price_List ',
582 'EXCEPTION :'||sqlerrm);
583 END IF;
584 -- unexpected error
585 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
586 OKL_API.set_message(p_app_name => 'OKC',
587 p_msg_name => g_unexpected_error,
588 p_token1 => g_sqlcode_token,
589 p_token1_value => sqlcode,
590 p_token2 => g_sqlerrm_token,
591 p_token2_value => sqlerrm);
592 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
593 END Create_Item_In_Price_List;
594
595
596
597
598
599 -- Start of comments
600 --
601 -- Procedure Name : create_rmk_item
602 -- Description : The main body of the package. This procedure creats an inventory item, moves the item to
603 -- Inventory and creates a price list for the Item. These 3 steps are considered as a part of
604 -- single transaction and are all required to complete successfully in order to create an inventory
605 -- item.
606 -- Business Rules :
607 -- Parameters : Input parameters : p_Item_Description - item desc , p_Item_Price- item price,
608 -- p_quantity - Item's quantity
609 -- Output parameters : x_new_item_number - item number of the item created
610 -- x_new_item_id - item Id of the item created
611 -- Version : 1.0
612 -- History : SECHAWLA 05-DEC-02 - Bug# 2620853
613 -- Commented out the code that uses the distribution account id profile, as it is optional
614 -- SECHAWLA 05-DEC-02 - Bug# 2679812
615 -- Fixed the typos in the CLOSE cursor statement in the exception block
616 -- Removed DEFAULT hint from procedure parameters
617 -- Modified messages to display user profile option name instead of profile option name
618 -- Added/Modified code to first create the item in the master org and then assign it to
619 -- child org, if the 2 Orgs are different
620 -- SECHAWLA 17-DEC-02 : Bug # 2706328
621 -- Fixed the datatype of l_temp_org_name
622 -- SECHAWLA 16-JAN-02 Bug # 2754280
623 -- Changed the call to fn get_user_profile_option_name to refer it from am util
624 -- SECHAWLA 08-MAR-04 Bug # 3492490
625 -- Use ORG_ORGANIZATION_DEFINITIONS instead of mtl_organizations to validate the inventory org
626 -- Validate subinventory is defined for Master org when the remarketing inv org is a child org
627 -- SECHAWLA 19-MAY-04 Bug # 3634514
628 -- Populate subinventory fields on the item master only if 1) the remarketing org is a master org OR
629 -- 2) Remarketing org is a child org and the item is being assigned to the child org
630 -- SECHAWLA 04-OCT-04 3924244 : added p_item_number parameter
631 -- End of comments
632
633 PROCEDURE create_rmk_item
634 ( p_api_version IN NUMBER,
635 p_init_msg_list IN VARCHAR2,
636 p_item_number IN VARCHAR2, -- 04-OCT-04 SECHAWLA 3924244 : new parameter
637 p_Item_Description IN VARCHAR2,
638 p_Item_Price IN NUMBER ,
639 p_quantity IN NUMBER ,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2,
643 x_new_item_number OUT NOCOPY VARCHAR2,
644 x_new_item_id OUT NOCOPY NUMBER
645
646 )
647 IS
648
649 -- This cursor is used to validate an organization Id against mtl_organization
650 CURSOR l_mtlorgcsr(p_org_id NUMBER) IS
651 SELECT organization_name
652 -- SECHAWLA 08-MAR-04 3492490 : Validate against ORG_ORGANIZATION_DEFINITIONS, as we use this view to set the LOV for
653 -- inventory organization. mtl_organizations may not have all the orgs that ORG_ORGANIZATION_DEFINITIONS has. For
654 -- our validation purposes, we do not need the restrictions that mtl_organizations uses to filter out certain orgs
655 --FROM mtl_organizations
656 FROM ORG_ORGANIZATION_DEFINITIONS
657 WHERE organization_id = p_org_id;
658
659 -- This cursor is used to validate Organization and subinventory
660 CURSOR l_mtlsecinv_csr(p_inv_org_id NUMBER, p_subinv_code VARCHAR2) IS
661 SELECT 'Validate Org and Subinv'
662 FROM mtl_secondary_inventories
663 WHERE organization_id = p_inv_org_id
664 AND secondary_inventory_name = p_subinv_code;
665
666 -- This cursor is used to validate the list_header_id
667 CURSOR l_qplisthdr_csr(p_list_header_id NUMBER) IS
668 --SELECT 'x' -- SECHAWLA 08-DEC-04 4047159
669 --FROM QP_LIST_HEADERS_B -- SECHAWLA 08-DEC-04 4047159
670 SELECT name
671 FROM QP_LIST_HEADERS
672 WHERE LIST_HEADER_ID = p_list_header_id;
673
674
675 -- SECHAWLA Bug# 2620853 : Cursor not required, as we are not going to use distribution account id
676 /*
677 -- This cursor is used to validate distribution_account_id
678 CURSOR l_glcodecomb_csr(p_ccid NUMBER) IS
679 SELECT 'x'
680 FROM GL_CODE_COMBINATIONS
681 WHERE code_combination_id = p_ccid
682 AND enabled_flag = 'Y';
683 */
684
685
686 -- This cursor is used to get the warehouse for the Order and Line Transaction types
687 CURSOR l_oetranstypesall_csr(p_trans_id NUMBER) IS
688 SELECT warehouse_id, default_outbound_line_type_id, name
689 FROM oe_transaction_types_all a, oe_transaction_types_tl b
690 WHERE a.transaction_type_id = b.transaction_type_id
691 AND a.transaction_type_id = p_trans_id;
692
693 --SECHAWLA 05-DEC-02 - Bug# 2679812 : Added a new cursor
694 -- This cursor is used to find the master org for an organization
695
696 -- SECHAWLA 08-MAR-04 3492490 : Added master org name
697 CURSOR l_mtlparam_csr(p_org_id NUMBER) IS
698 SELECT a.master_organization_id, b.organization_name master_org_name
699 FROM mtl_parameters a , ORG_ORGANIZATION_DEFINITIONS b
700 WHERE a.organization_id = p_org_id
701 AND a.master_organization_id = b.organization_id ;
702
703
704 l_order_warehouse_id NUMBER;
705 l_line_warehouse_id NUMBER;
706 l_def_outbound_line_type_id NUMBER;
707 l_order_name VARCHAR2(30);
708 l_line_name VARCHAR2(30);
709 l_inv_org_id NUMBER;
710 l_subinv_code VARCHAR2(10);
711 --SECHAWLA Bug# 2620853 : Dist Account ID is not required
712 -- l_distribution_account_id NUMBER;
713 l_price_list_id NUMBER;
714 l_New_Item_Number VARCHAR2(2000);
715 l_New_Item_Id NUMBER;
716 l_return_status VARCHAR2(1);
717 l_temp VARCHAR2(25);
718 -- l_pricelist_exists VARCHAR2(1); -- SECHAWLA 08-DEC-04 4047159
719 l_pricelist_name QP_LIST_HEADERS.name%TYPE; -- SECHAWLA 08-DEC-04 4047159
720 --SECHAWLA 2706328 : Fixed the datatype for l_temp_org_name
721 l_temp_org_name mtl_organizations.organization_name%TYPE;
722 l_temp_ccid VARCHAR2(1);
723 l_count NUMBER;
724 l_default_order_type_id NUMBER;
725
726 l_api_name CONSTANT VARCHAR2(30) := 'create_rmk_item';
727 l_api_version CONSTANT NUMBER := 1;
728 l_sysdate DATE;
729
730 --SECHAWLA Bug# 2679812 : new declarations
731 l_master_org_id NUMBER;
732 l_current_org NUMBER;
733 l_current_org_name ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE; -- SECHAWLA 08-DEC-04 4047159
734 l_iterations NUMBER;
735 l_item_id NUMBER;
736 l_item_number VARCHAR2(2000);
737 l_user_profile_name VARCHAR2(240);
738 l_org_id NUMBER; -- Added for bug 10081463
739
740 -- SECHAWLA 08-MAR-04 3492490 : new declarations
741
742 l_master_org_name ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
743
744 --SECHAWLA 19-MAY-04 3634514 : new declaration
745 l_assign_subinv VARCHAR2(1);
746
747 -- SECHAWLA 05-OCT-04 3924244 : New declarations
748 -- check the Remarketing flow options from the setup
749 CURSOR l_systemparamsall_csr IS
750 SELECT REMK_ORGANIZATION_ID, REMK_SUBINVENTORY, REMK_PRICE_LIST_ID
751 ,ORG_ID -- Added for bug 10081463
752 FROM OKL_SYSTEM_PARAMS ;
753
754 -- SECHAWLA 18-OCT-04 3924244 : new declarations
755 -- check if item already exists in inventory
756 CURSOR l_mtlsystemitems_csr(cp_inv_item_number IN VARCHAR2) IS
757 SELECT count(*)
758 FROM MTL_SYSTEM_ITEMS_B
759 WHERE segment1 = cp_inv_item_number;
760
761 l_item_cnt NUMBER;
762
763
764 BEGIN
765 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
766 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item','Begin(+)');
767 END IF;
768
769 --Print Input Variables
770 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
772 'p_init_msg_list :'||p_init_msg_list);
773 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
774 'p_item_number :'||p_item_number);
775 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
776 'p_Item_Description :'||p_Item_Description);
777 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
778 'p_Item_Price :'||p_Item_Price);
779 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
780 'p_quantity :'||p_quantity);
781
782 END IF;
783
784 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
785 G_PKG_NAME,
786 p_init_msg_list,
787 l_api_version,
788 p_api_version,
789 '_PVT',
790 x_return_status);
791
792
793 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
794 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
795 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
796 RAISE OKL_API.G_EXCEPTION_ERROR;
797 END IF;
798
799 SELECT SYSDATE INTO l_sysdate FROM DUAL;
800
801 -- SECHAWLA 18-OCT-04 3924244 : Added the following validation
802 IF p_item_number IS NOT NULL THEN
803 OPEN l_mtlsystemitems_csr(p_item_number);
804 FETCH l_mtlsystemitems_csr INTO l_item_cnt;
805 CLOSE l_mtlsystemitems_csr;
806
807 IF l_item_cnt > 0 THEN
808 --Item number ITEM_NUMBER already exists in Inventory. Please enter another item number.
809 OKL_API.set_message( p_app_name => 'OKL',
810 p_msg_name => 'OKL_AM_ITEM_ALREADY_EXISTS',
811 p_token1 => 'ITEM_NUMBER',
812 p_token1_value => p_item_number);
813 x_return_status := OKL_API.G_RET_STS_ERROR;
814 RAISE OKL_API.G_EXCEPTION_ERROR;
815 END IF;
816 END IF;
817 -- SECHAWLA 18-OCT-04 3924244 : end
818
819 IF p_item_price IS NULL OR p_item_price = OKL_API.G_MISS_NUM THEN
820 x_return_status := OKL_API.G_RET_STS_ERROR;
821 --Item Price is required
822 OKC_API.set_message( p_app_name => 'OKC',
823 p_msg_name => G_REQUIRED_VALUE,
824 p_token1 => G_COL_NAME_TOKEN,
825 p_token1_value => 'ITEM_PRICE');
826
827 RAISE okl_api.G_EXCEPTION_ERROR;
828 END IF;
829
830 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM THEN
831 x_return_status := OKL_API.G_RET_STS_ERROR;
832 --Item Quantity is required
833 OKC_API.set_message( p_app_name => 'OKC',
834 p_msg_name => G_REQUIRED_VALUE,
835 p_token1 => G_COL_NAME_TOKEN,
836 p_token1_value => 'ITEM_QUANTITY');
837
838 RAISE okl_api.G_EXCEPTION_ERROR;
839 END IF;
840
841 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
842 -- Check the remarketing flow setup
843 OPEN l_systemparamsall_csr;
844 FETCH l_systemparamsall_csr INTO l_inv_org_id, l_subinv_code, l_price_list_id,l_org_id; -- Added l_org_id for bug 10081463
845 IF l_systemparamsall_csr%NOTFOUND THEN
846 -- Remarketing options are not setup for this operating unit.
847 OKL_API.set_message(
848 p_app_name => 'OKL',
849 p_msg_name => 'OKL_AM_NO_REMK_SETUP');
850 x_return_status := OKL_API.G_RET_STS_ERROR;
851 RAISE OKL_API.G_EXCEPTION_ERROR;
852 END IF;
853 CLOSE l_systemparamsall_csr;
854
855
856 IF l_inv_org_id IS NULL THEN
857 -- Remarketing Inventory Organization is not setup for this operating unit.
858 OKL_API.set_message(
859 p_app_name => 'OKL',
860 p_msg_name => 'OKL_AM_NO_REMK_ORG');
861 x_return_status := OKL_API.G_RET_STS_ERROR;
862 RAISE OKL_API.G_EXCEPTION_ERROR;
863 END IF;
864 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
865
866
867
868 /* -- -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
869
870 l_inv_org_id := fnd_profile.value('OKL_REMARKET_ITEMS_INV_ORG');
871 l_subinv_code := fnd_profile.value('OKL_REMARKET_SUBINVENTORY');
872
873 -- distribution account ID in mtl_transactions_interface is optional.
874
875 -- SECHAWLA Bug # 2620853 - following profile is not required
876 --l_distribution_account_id := fnd_profile.value('OKL_REMARKET_DISTRIBUTION_ACCOUNT');
877
878 l_price_list_id := fnd_profile.value('OKL_REMARKET_PRICE_LIST');
879 */
880
881 -- ASO_ORDER_TYPE_ID is an Oracle Order Capture profile to set the default Order type.
882 -- iStore uses this profile to get the default Order type and then assigns this Order type to the Orders
883 -- l_default_order_type_id := fnd_profile.value('ASO_ORDER_TYPE_ID');
884 -- 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
885
886 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
887
888 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
889 -- validate organization
890 IF l_inv_org_id IS NULL THEN
891
892 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
893 -- instead of profile option name
894
895 -- SECHAWLA 16-JAN-02 Bug # 2754280 : Changed the following fn call to call this function from am util
896 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
897 p_profile_option_name => 'OKL_REMARKET_ITEMS_INV_ORG',
898 x_return_status => x_return_status);
899
900 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
901 --Remarketing Inventory Organization profile is missing.
902 OKL_API.set_message( p_app_name => 'OKL',
903 p_msg_name => 'OKL_AM_NO_INV_ORG_PROFILE'
904 );
905 RAISE okl_api.G_EXCEPTION_ERROR;
906 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
907 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
908 END IF;
909 -- SECHAWLA Bug# 2679812 -- end new code
910
911 x_return_status := OKL_API.G_RET_STS_ERROR;
912 --Profile value not defined
913 OKL_API.set_message( p_app_name => 'OKL',
914 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
915 p_token1 => 'PROFILE',
916 p_token1_value => l_user_profile_name -- modified to display user profile option name
917 );
918 RAISE okl_api.G_EXCEPTION_ERROR;
919 END IF;
920 */ -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
921
922
923 OPEN l_mtlorgcsr(l_inv_org_id);
924 FETCH l_mtlorgcsr INTO l_temp_org_name;
925 IF l_mtlorgcsr%NOTFOUND THEN
926 x_return_status := OKL_API.G_RET_STS_ERROR;
927 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
928 --Profile is invalid.
929 OKL_API.set_message( p_app_name => 'OKL',
930 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
931 p_token1 => 'PROFILE',
932 p_token1_value => 'OKL_REMARKET_ITEMS_INV_ORG'
933 );
934 */
935
936 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
937 OKC_API.set_message( p_app_name => 'OKC',
938 p_msg_name => G_INVALID_VALUE,
939 p_token1 => G_COL_NAME_TOKEN,
940 p_token1_value => 'Remarketing Inventory Organization');
941
942 RAISE okl_api.G_EXCEPTION_ERROR;
943 END IF;
944 CLOSE l_mtlorgcsr;
945
946
947 -- validate subinventory
948 IF l_subinv_code IS NULL THEN
949
950 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
951 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
952 -- instead of profile option name
953 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
954 p_profile_option_name => 'OKL_REMARKET_SUBINVENTORY',
955 x_return_status => x_return_status);
956
957 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
958 --Remarketing Subinventory profile is missing.
959 OKL_API.set_message( p_app_name => 'OKL',
960 p_msg_name => 'OKL_AM_NO_SUBINV_PROFILE'
961 );
962 RAISE okl_api.G_EXCEPTION_ERROR;
963 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
964 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
965 END IF;
966 -- SECHAWLA Bug# 2679812 -- end new code
967
968 x_return_status := OKL_API.G_RET_STS_ERROR;
969 --Profile value not defined
970 OKL_API.set_message( p_app_name => 'OKL',
971 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
972 p_token1 => 'PROFILE',
973 p_token1_value => l_user_profile_name -- modified to display user profile option
974 );
975 RAISE okl_api.G_EXCEPTION_ERROR;
976 */
977
978 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
979 -- Remarketing Subinventory is not setup for this operating unit.
980 OKL_API.set_message(
981 p_app_name => 'OKL',
982 p_msg_name => 'OKL_AM_NO_REMK_SUBINV');
983 x_return_status := OKL_API.G_RET_STS_ERROR;
984 RAISE OKL_API.G_EXCEPTION_ERROR;
985
986 END IF;
987
988
989
990 -- This profile will generally be set to NULL. We are still keeping the profile to provide flexibility
991 -- to the user, if they want to store distribution account ID in mtl_transactionjs_interface
992
993 -- SECHAWLA Bug # 2620853 - no need to validate distribution_account_id, as we are not using the corresponding profile.
994
995 /* IF l_distribution_account_id IS NOT NULL THEN
996 OPEN l_glcodecomb_csr(l_distribution_account_id);
997 FETCH l_glcodecomb_csr INTO l_temp_ccid;
998 IF l_glcodecomb_csr%NOTFOUND THEN
999 x_return_status := OKL_API.G_RET_STS_ERROR;
1000 -- Profile is invalid.
1001 OKL_API.set_message( p_app_name => 'OKL',
1002 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
1003 p_token1 => 'PROFILE',
1004 p_token1_value => 'OKL_REMARKET_DISTRIBUTION_ACCOUNT'
1005 );
1006 RAISE OKL_API.G_EXCEPTION_ERROR;
1007 END IF;
1008 CLOSE l_glcodecomb_csr;
1009 END IF;
1010 */
1011
1012
1013 IF l_price_list_id IS NULL THEN
1014
1015 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1016 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
1017 -- instead of profile option name
1018 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
1019 p_profile_option_name => 'OKL_REMARKET_PRICE_LIST',
1020 x_return_status => x_return_status);
1021
1022 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1023 --Remarketing Price List profile is missing.
1024 OKL_API.set_message( p_app_name => 'OKL',
1025 p_msg_name => 'OKL_AM_NO_PL_PROFILE'
1026 );
1027 RAISE okl_api.G_EXCEPTION_ERROR;
1028 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1029 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1030 END IF;
1031 -- SECHAWLA Bug# 2679812 -- end new code
1032
1033 x_return_status := OKL_API.G_RET_STS_ERROR;
1034 -- Profile value not defined
1035 OKL_API.set_message( p_app_name => 'OKL',
1036 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
1037 p_token1 => 'PROFILE',
1038 p_token1_value => l_user_profile_name -- modified to display user profile option
1039 );
1040 RAISE OKL_API.G_EXCEPTION_ERROR;
1041 */
1042 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1043
1044 -- Remarketing Price List is not setup for this operating unit.
1045 OKL_API.set_message(
1046 p_app_name => 'OKL',
1047 p_msg_name => 'OKL_AM_NO_REMK_PRICE_LIST');
1048 x_return_status := OKL_API.G_RET_STS_ERROR;
1049 RAISE OKL_API.G_EXCEPTION_ERROR;
1050 END IF;
1051
1052
1053 OPEN l_qplisthdr_csr(l_price_list_id);
1054 FETCH l_qplisthdr_csr INTO l_pricelist_name; -- SECHAWLA 08-DEC-04 4047159
1055 IF l_qplisthdr_csr%NOTFOUND THEN
1056 x_return_status := OKL_API.G_RET_STS_ERROR;
1057
1058 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1059 -- Profile is invalid.
1060 OKL_API.set_message( p_app_name => 'OKL',
1061 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
1062 p_token1 => 'PROFILE',
1063 p_token1_value => 'OKL_REMARKET_PRICE_LIST'
1064 */
1065
1066 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1067 OKC_API.set_message( p_app_name => 'OKC',
1068 p_msg_name => G_INVALID_VALUE,
1069 p_token1 => G_COL_NAME_TOKEN,
1070 p_token1_value => 'Remarketing Price List');
1071 RAISE OKL_API.G_EXCEPTION_ERROR;
1072 END IF;
1073 CLOSE l_qplisthdr_csr;
1074
1075 IF l_default_order_type_id IS NULL THEN
1076
1077 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
1078 -- instead of profile option name
1079 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
1080 p_profile_option_name => 'ASO_ORDER_TYPE_ID',
1081 x_return_status => x_return_status);
1082
1083 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1085 'l_user_profile_name status'||x_return_status);
1086 END IF;
1087
1088 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1089 --Remarketing Order Type profile is missing.
1090 OKL_API.set_message( p_app_name => 'OKL',
1091 p_msg_name => 'OKL_AM_NO_ORDER_TYPE_PROFILE'
1092 );
1093 RAISE okl_api.G_EXCEPTION_ERROR;
1094 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1095 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1096 END IF;
1097 -- SECHAWLA Bug# 2679812 -- end new code
1098
1099 x_return_status := OKL_API.G_RET_STS_ERROR;
1100 -- Profile value not defined
1101 OKL_API.set_message( p_app_name => 'OKL',
1102 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
1103 p_token1 => 'PROFILE',
1104 p_token1_value => l_user_profile_name -- modified to display user profile option
1105 );
1106 RAISE OKL_API.G_EXCEPTION_ERROR;
1107 END IF;
1108
1109 -- get the warehouse and the Line Type for the Default Order Type
1110 OPEN l_oetranstypesall_csr(l_default_order_type_id);
1111 FETCH l_oetranstypesall_csr INTO l_order_warehouse_id, l_def_outbound_line_type_id, l_order_name;
1112 -- This fetch will definitely find the record in oe_transaction_types_all
1113 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1114 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1115 'fetched l_oetranstypesall_csr..');
1116 END IF;
1117
1118 CLOSE l_oetranstypesall_csr;
1119
1120 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1121 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1122 'l_order_warehouse_id..'||l_order_warehouse_id);
1123 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1124 'l_def_outbound_line_type_id..'||l_def_outbound_line_type_id);
1125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1126 'l_order_name..'||l_order_name);
1127 END IF;
1128
1129
1130 IF l_order_warehouse_id IS NULL THEN
1131 x_return_status := OKL_API.G_RET_STS_ERROR;
1132 -- Warehouse not defined for this Order Type
1133 OKL_API.set_message( p_app_name => 'OKL',
1134 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
1135 p_token1 => 'TYPE',
1136 p_token1_value => 'ORDER',
1137 p_token2 => 'NAME',
1138 p_token2_value => l_order_name
1139 );
1140 RAISE OKL_API.G_EXCEPTION_ERROR;
1141 END IF;
1142
1143 IF l_def_outbound_line_type_id IS NULL THEN
1144 x_return_status := OKL_API.G_RET_STS_ERROR;
1145 -- Line Type not defined for this Order Type
1146 OKL_API.set_message( p_app_name => 'OKL',
1147 p_msg_name => 'OKL_AM_RMK_NO_LINE_TYPE',
1148 p_token1 => 'ORDER_TYPE',
1149 p_token1_value => l_order_name
1150 );
1151 RAISE OKL_API.G_EXCEPTION_ERROR;
1152 END IF;
1153
1154 -- get the warehouse for the Line type corresponding to the Default Order Type
1155 OPEN l_oetranstypesall_csr(l_def_outbound_line_type_id);
1156 FETCH l_oetranstypesall_csr INTO l_line_warehouse_id, l_def_outbound_line_type_id, l_line_name;
1157 -- This fetch will definitely find the record in oe_transaction_types_all
1158 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1159 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1160 'fetched l_oetranstypesall_csr again');
1161 END IF;
1162
1163 CLOSE l_oetranstypesall_csr;
1164
1165 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1167 'l_line_warehouse_id'||l_line_warehouse_id);
1168 END IF;
1169
1170 IF l_line_warehouse_id IS NULL THEN
1171 x_return_status := OKL_API.G_RET_STS_ERROR;
1172 -- Warehouse not defined for this Line Type
1173 OKL_API.set_message( p_app_name => 'OKL',
1174 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
1175 p_token1 => 'TYPE',
1176 p_token1_value => 'LINE',
1177 p_token2 => 'NAME',
1178 p_token2_value => l_line_name
1179 );
1180 RAISE OKL_API.G_EXCEPTION_ERROR;
1181 END IF;
1182
1183 IF l_order_warehouse_id <> l_line_warehouse_id THEN
1184 x_return_status := OKL_API.G_RET_STS_ERROR;
1185 -- Warehouses for the Order Type ORDER_TYPE and Line Type LINE_TYPE do not match.
1186 OKL_API.set_message( p_app_name => 'OKL',
1187 p_msg_name => 'OKL_AM_RMK_WHS_MISMATCH',
1188 p_token1 => 'ORDER_TYPE',
1189 p_token1_value => l_order_name,
1190 p_token2 => 'LINE_TYPE',
1191 p_token2_value => l_line_name
1192 );
1193 RAISE OKL_API.G_EXCEPTION_ERROR;
1194 END IF;
1195
1196 IF l_inv_org_id <> l_order_warehouse_id THEN
1197 x_return_status := OKL_API.G_RET_STS_ERROR;
1198 -- Shipping organization should be the same as the inventory item organization.
1199 OKL_API.set_message( p_app_name => 'OKL',
1200 p_msg_name => 'OKL_AM_RMK_INVALID_WHS'
1201 );
1202 RAISE OKL_API.G_EXCEPTION_ERROR;
1203 END IF;
1204
1205
1206 --SECHAWLA Bug# 2679812 : Added/modified the following code to first create the item in the master org and then
1207 -- assign the same item to the child org
1208
1209
1210 -- SECHAWLA Bug# 2679812 : Get the Master Org for the Inv Org
1211 OPEN l_mtlparam_csr(l_inv_org_id);
1212
1213 -- SECAHWLA 08-MAR-04 3492490 : Added master org name
1214 FETCH l_mtlparam_csr INTO l_master_org_id, l_master_org_name;
1215 IF l_mtlparam_csr%NOTFOUND THEN
1216 x_return_status := OKL_API.G_RET_STS_ERROR;
1217 -- Inventory organization is not set up in MTL Parameters.
1218 OKL_API.set_message( p_app_name => 'OKL',
1219 p_msg_name => 'OKL_AM_NO_ORG_PARAM'
1220 );
1221 RAISE OKL_API.G_EXCEPTION_ERROR;
1222 END IF;
1223 IF l_master_org_id IS NULL THEN
1224 x_return_status := OKL_API.G_RET_STS_ERROR;
1225 -- Master organization is not defined for inventory organization.
1226 OKL_API.set_message( p_app_name => 'OKL',
1227 p_msg_name => 'OKL_AM_NO_MASTER_ORG'
1228 );
1229 RAISE OKL_API.G_EXCEPTION_ERROR;
1230 END IF;
1231 CLOSE l_mtlparam_csr;
1232
1233 -- SECHAWLA Bug# 2679812 :
1234 -- If inv org is a master org then create item only in master org. If 2 orgs are different, then first craete
1235 -- the item in master org and then assign the same item to child org
1236 IF l_inv_org_id = l_master_org_id THEN
1237 l_iterations := 1;
1238 ELSE
1239 l_iterations := 2;
1240 END IF;
1241
1242 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1244 'l_iterations '||l_iterations);
1245 END IF;
1246
1247 -- SECHAWLA Bug# 2679812 :
1248 -- This loop is executed once if the inv org is also a master org whereas if the 2 orgs are different,
1249 -- this loop is executed twice, once for the master org and then for the child org
1250 FOR i IN 1..l_iterations LOOP
1251
1252 IF i = 1 THEN
1253 l_current_org := l_master_org_id;
1254 l_current_org_name := l_master_org_name; -- SECHAWLA 08-DEC-04 4047159
1255 --l_item_number := NULL; -- SECHAWLA 05-OCT-04 3924244
1256 l_item_number := p_item_number; -- SECHAWLA 05-OCT-04 3924244 : Use item no. entered by the user. It may be NULL
1257 l_item_id := NULL;
1258 ELSIF i = 2 THEN
1259 l_current_org := l_inv_org_id; --child org
1260 l_current_org_name := l_temp_org_name; -- SECHAWLA 08-DEC-04 4047159
1261 l_item_number := l_New_Item_Number;
1262 l_item_id := l_New_Item_Id;
1263 END IF;
1264
1265 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1267 'l_current_org'||l_current_org);
1268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1269 'l_item_number'||l_item_number);
1270 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1271 'l_item_id'||l_item_id);
1272 END IF;
1273 ---------------------
1274 -- SECHAWLA 08-MAR-04 3492490 : Moved the following validation here from the beginning. So it can be
1275 -- performed for both Master and Child org
1276
1277 OPEN l_mtlsecinv_csr(l_current_org , l_subinv_code );
1278 FETCH l_mtlsecinv_csr INTO l_temp;
1279 IF l_mtlsecinv_csr%NOTFOUND THEN
1280 --x_return_status := OKL_API.G_RET_STS_ERROR;
1281
1282 IF (l_inv_org_id <> l_master_org_id AND i = 1 )THEN
1283 /* SECHAWLA 19-MAY-04 3634514 : Commented out
1284 --Subinventory SUBINVENTORY is not defined for the organization MASTER_ORG, which is the Master organization of the Remarketing Inventory organization CHILD_ORG.
1285 OKL_API.set_message(p_app_name => 'OKL',
1286 p_msg_name => 'OKL_AM_RMK_MST_ORG_SUBINV',
1287 p_token1 => 'SUBINVENTORY',
1288 p_token1_value => l_subinv_code,
1289 p_token2 => 'MASTER_ORG',
1290 p_token2_value => l_master_org_name,
1291 p_token3 => 'CHILD_ORG',
1292 p_token3_value => l_temp_org_name);
1293 */
1294 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1296 '(l_inv_org_id <> l_master_org_id AND i = 1) sts'||x_return_status);
1297
1298 END IF;
1299
1300 NULL;
1301 ELSE
1302 x_return_status := OKL_API.G_RET_STS_ERROR;
1303 --Subinventory SUBINVENTORY is invalid for the organization ORGANIZATION.
1304 OKL_API.set_message(p_app_name => 'OKL',
1305 p_msg_name => 'OKL_AM_RMK_ORG_SUBINV',
1306 p_token1 => 'SUBINVENTORY',
1307 p_token1_value => l_subinv_code,
1308 p_token2 => 'ORGANIZATION',
1309 p_token2_value => l_temp_org_name);
1310 RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Added
1311 END IF;
1312
1313 --RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Commented out
1314 END IF;
1315 CLOSE l_mtlsecinv_csr;
1316
1317
1318 --SECHAWLA 19-MAY-04 3634514 : populate subinventory fields in the item master only if the
1319 --Remarketing org is Master Org OR if the remarketing org is the child org and the item is
1320 --being assigned to the child org
1321
1322 IF (l_iterations = 1) OR (i = 2) THEN
1323 l_assign_subinv := 'Y';
1324 ELSE
1325 l_assign_subinv := 'N';
1326 END IF;
1327 --SECHAWLA 19-MAY-04 3634514 : end
1328
1329
1330 ---------------------
1331
1332 -- Create the Item in Inventory
1333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1335 'before create_inv_item call'||x_return_status);
1336
1337 END IF;
1338 create_inv_item
1339 (--p_Organization_Id => l_inv_org_id, --SECHAWLA Bug# 2679812 : use current org
1340 p_Organization_Id => l_current_org,
1341 p_organization_name => l_current_org_name, -- SECHAWLA 08-DEC-04 4047159 : added
1342 p_Item_Description => p_Item_Description,
1343 p_subinventory => l_subinv_code,
1344 -- p_distribution_acct_id => l_distribution_account_id, -- SECHAWLA Bug # 2620853 : Removed
1345 p_sysdate => l_sysdate,
1346 -- SECHAWLA 05-OCT-04 3924244 : l_item_number may have a value for the master org (if user enters item no.)
1347 p_item_number => l_item_number, --SECHAWLA Bug# 2679812 :added
1348 p_item_id => l_item_id, --SECHAWLA Bug# 2679812 :added
1349 p_assign_subinv => l_assign_subinv, --SECHAWLA 19-MAY-04 3634514: Added
1350 x_New_Item_Number => l_New_Item_Number,
1351 x_New_Item_Id => l_New_Item_Id,
1352 x_return_Status => x_return_status);
1353
1354 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1355 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1356 'after create_inv_item call'||x_return_status);
1357
1358 END IF;
1359
1360 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1361 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1362 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1363 RAISE OKL_API.G_EXCEPTION_ERROR;
1364 END IF;
1365
1366
1367
1368
1369 IF (l_iterations = 1) OR (i = 2) THEN --SECHAWLA Bug# 2679812 : Create Misc transaction if inv org is the
1370 --master org OR for the child org
1371
1372 -- Creating Inventory Receipt Transaction for the Item
1373 -- SECHAWLA Bug # 2620853 : No need to pass distribution account id, as it is optional
1374 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1375 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1376 'before Create_Inv_Misc_Receipt_Txn call'||x_return_status);
1377
1378 END IF;
1379 Create_Inv_Misc_Receipt_Txn
1380 ( p_Inventory_Item_id => l_New_Item_Id,
1381 p_Subinv_Code => l_subinv_code,
1382 --p_Organization_Id => l_inv_org_id, --SECHAWLA Bug# 2679812 : use current org
1383 p_Organization_Id => l_current_org,
1384 --p_Dist_account_id => l_distribution_account_id, -- This can be NULL
1385 p_quantity => p_quantity,
1386 p_trans_type_id => 42, --- transaction type ID for Receipt Transactions
1387 p_sysdate => l_sysdate,
1388 x_Return_Status => x_return_status);
1389
1390 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1391 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1392 'after Create_Inv_Misc_Receipt_Txn call'||x_return_status);
1393
1394 END IF;
1395
1396 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1397 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1398 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1399 RAISE OKL_API.G_EXCEPTION_ERROR;
1400 END IF;
1401
1402 END IF;
1403
1404 IF i = 1 THEN -- SECHAWLA Bug# 2679812 :Create price list only for the master item
1405 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1406 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1407 'before Create_Item_In_Price_List'||x_return_status);
1408
1409 END IF;
1410 -- Create the Item in the Price List
1411 Create_Item_In_Price_List
1412 (
1413 p_api_version => l_api_version,
1414 p_Price_List_id => l_price_list_id,
1415 p_price_list_name => l_pricelist_name, -- SECHAWLA 08-DEC-04 4047159 : added
1416 p_price_list_item => l_New_Item_Number, -- SECHAWLA 08-DEC-04 4047159 : added
1417 p_Item_Id => l_New_Item_Id,
1418 p_Item_Price => p_Item_Price,
1419 x_return_status => x_return_status);
1420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1422 'after Create_Item_In_Price_List'||x_return_status);
1423
1424 END IF;
1425
1426 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1427 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1428 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1429 RAISE OKL_API.G_EXCEPTION_ERROR;
1430 END IF;
1431 END IF;
1432
1433 END LOOP;
1434
1435 x_New_Item_Number := l_new_item_number;
1436 x_New_Item_Id := l_new_item_id;
1437
1438 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1440 'x_New_Item_Number '||x_New_Item_Number);
1441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1442 'x_New_Item_Id '||x_New_Item_Id);
1443
1444 END IF;
1445
1446 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1447 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ','End(-)');
1448 END IF;
1449
1450 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1451
1452 EXCEPTION
1453 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1454 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1455 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ',
1456 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1457 END IF;
1458
1459 IF l_mtlsystemitems_csr%ISOPEN THEN
1460 CLOSE l_mtlsystemitems_csr;
1461 END IF;
1462
1463 IF l_mtlorgcsr%ISOPEN THEN
1464 CLOSE l_mtlorgcsr;
1465 END IF;
1466
1467 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1468 IF l_mtlsecinv_csr%ISOPEN THEN
1469 CLOSE l_mtlsecinv_csr;
1470 END IF;
1471
1472 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1473 IF l_qplisthdr_csr%ISOPEN THEN
1474 CLOSE l_qplisthdr_csr;
1475 END IF;
1476
1477 -- SECHAWLA Bug# 2620853 : This cursor is not used
1478 /* IF l_glcodecomb_csr%ISOPEN THEN
1479 CLOSE l_glcodecomb_csr;
1480 END IF;
1481 */
1482
1483 IF l_oetranstypesall_csr%ISOPEN THEN
1484 CLOSE l_oetranstypesall_csr;
1485 END IF;
1486
1487 -- SECHAWLA Bug# 2679812 : close the new cursor
1488 IF l_mtlparam_csr%ISOPEN THEN
1489 CLOSE l_mtlparam_csr;
1490 END IF;
1491
1492 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1493 IF l_systemparamsall_csr%ISOPEN THEN
1494 CLOSE l_systemparamsall_csr;
1495 END IF;
1496
1497
1498 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1499 (
1500 l_api_name,
1501 G_PKG_NAME,
1502 'OKL_API.G_RET_STS_ERROR',
1503 x_msg_count,
1504 x_msg_data,
1505 '_PVT'
1506 );
1507 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1508 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1509 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ',
1510 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1511 END IF;
1512
1513 IF l_mtlsystemitems_csr%ISOPEN THEN
1514 CLOSE l_mtlsystemitems_csr;
1515 END IF;
1516
1517 IF l_mtlorgcsr%ISOPEN THEN
1518 CLOSE l_mtlorgcsr;
1519 END IF;
1520
1521 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1522 IF l_mtlsecinv_csr%ISOPEN THEN
1523 CLOSE l_mtlsecinv_csr;
1524 END IF;
1525
1526 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1527 IF l_qplisthdr_csr%ISOPEN THEN
1528 CLOSE l_qplisthdr_csr;
1529 END IF;
1530
1531 -- SECHAWLA Bug# 2620853 : This cursor is not used
1532 /* IF l_glcodecomb_csr%ISOPEN THEN
1533 CLOSE l_glcodecomb_csr;
1534 END IF;
1535 */
1536
1537 IF l_oetranstypesall_csr%ISOPEN THEN
1538 CLOSE l_oetranstypesall_csr;
1539 END IF;
1540
1541 -- SECHAWLA Bug# 2679812 : close the new cursor
1542 IF l_mtlparam_csr%ISOPEN THEN
1543 CLOSE l_mtlparam_csr;
1544 END IF;
1545
1546 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1547 IF l_systemparamsall_csr%ISOPEN THEN
1548 CLOSE l_systemparamsall_csr;
1549 END IF;
1550 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1551 (
1552 l_api_name,
1553 G_PKG_NAME,
1554 'OKL_API.G_RET_STS_UNEXP_ERROR',
1555 x_msg_count,
1556 x_msg_data,
1557 '_PVT'
1558 );
1559 WHEN OTHERS THEN
1560
1561 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1562 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item',
1563 'EXCEPTION :'||sqlerrm);
1564 END IF;
1565
1566 IF l_mtlsystemitems_csr%ISOPEN THEN
1567 CLOSE l_mtlsystemitems_csr;
1568 END IF;
1569
1570 IF l_mtlorgcsr%ISOPEN THEN
1571 CLOSE l_mtlorgcsr;
1572 END IF;
1573
1574 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1575 IF l_mtlsecinv_csr%ISOPEN THEN
1576 CLOSE l_mtlsecinv_csr;
1577 END IF;
1578
1579 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1580 IF l_qplisthdr_csr%ISOPEN THEN
1581 CLOSE l_qplisthdr_csr;
1582 END IF;
1583
1584 -- SECHAWLA Bug# 2620853 : This cursor is not used
1585 /* IF l_glcodecomb_csr%ISOPEN THEN
1586 CLOSE l_glcodecomb_csr;
1587 END IF;
1588 */
1589
1590 IF l_oetranstypesall_csr%ISOPEN THEN
1591 CLOSE l_oetranstypesall_csr;
1592 END IF;
1593
1594 -- SECHAWLA Bug# 2679812 : close the new cursor
1595 IF l_mtlparam_csr%ISOPEN THEN
1596 CLOSE l_mtlparam_csr;
1597 END IF;
1598
1599 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1600 IF l_systemparamsall_csr%ISOPEN THEN
1601 CLOSE l_systemparamsall_csr;
1602 END IF;
1603 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1604 (
1605 l_api_name,
1606 G_PKG_NAME,
1607 'OTHERS',
1608 x_msg_count,
1609 x_msg_data,
1610 '_PVT'
1611 );
1612
1613
1614 END create_rmk_item;
1615
1616
1617
1618 /*
1619
1620 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1621
1622
1623 -- Start of comments
1624 --
1625 -- Procedure Name : remove_inv_item
1626 -- Description : This procedure is called from the main procedure - remove_rmk_item.
1627 -- remove_inv_item inactivates an inventory item by setting the end_date_active to sysdate
1628 -- Business Rules :
1629 -- Parameters : Input parameters : p_Item_Id - Item Id of item to be removed
1630 -- p_org_id - Items' organization Id
1631 -- p_sysdate - system date
1632 --
1633 --
1634 -- Version : 1.0
1635 -- End of comments
1636
1637 PROCEDURE remove_inv_item( p_item_id IN NUMBER,
1638 p_org_id IN NUMBER,
1639 p_sysdate IN DATE,
1640 x_return_status OUT NOCOPY VARCHAR2)
1641 IS
1642 l_Item_rec INV_Item_GRP.Item_rec_type;
1643 x_Item_rec INV_Item_GRP.Item_rec_type;
1644 l_commit VARCHAR2(1);
1645 l_validation_level NUMBER;
1646 l_return_status VARCHAR2(1);
1647 x_Error_tbl INV_Item_GRP.Error_tbl_type;
1648
1649 l_lock_rows VARCHAR2(20) := fnd_api.g_TRUE;
1650
1651 BEGIN
1652
1653 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1654
1655 l_item_rec.end_date_active := p_sysdate;
1656 l_item_rec.inventory_item_id := p_Item_Id;
1657 l_Item_rec.organization_id := p_org_id;
1658
1659
1660 l_commit := OKC_API.g_FALSE;
1661
1662 INV_Item_GRP.update_item
1663 (
1664 p_commit => l_commit,
1665 p_lock_rows => l_lock_rows
1666 , p_validation_level => l_validation_level
1667 , p_Item_rec => l_Item_rec
1668 , x_Item_rec => x_item_rec
1669 , x_return_status => x_return_status
1670 , x_Error_tbl => x_Error_tbl
1671 );
1672
1673
1674 IF ( x_return_status <> okl_api.G_RET_STS_SUCCESS ) THEN
1675 -- Display the error messages from the x_error_tbl table
1676 FOR i IN 1 .. x_Error_tbl.COUNT LOOP
1677 -- Error : Transaction Id = TRX_ID
1678 OKL_API.set_message( p_app_name => 'OKL',
1679 p_msg_name => 'OKL_AM_RMK_TRANS_ID',
1680 p_token1 => 'TRX_ID',
1681 p_token1_value => x_Error_tbl(i).TRANSACTION_ID
1682 );
1683 -- Error : Unique Id = UNIQUE_ID
1684 OKL_API.set_message( p_app_name => 'OKL',
1685 p_msg_name => 'OKL_AM_RMK_UNIQUE_ID',
1686 p_token1 => 'UNIQUE_ID',
1687 p_token1_value => x_Error_tbl(i).UNIQUE_ID
1688 );
1689 -- Error : Table Name = TABLE_NAME
1690 OKL_API.set_message( p_app_name => 'OKL',
1691 p_msg_name => 'OKL_AM_RMK_TABLE_NAME',
1692 p_token1 => 'TABLE_NAME',
1693 p_token1_value => x_Error_tbl(i).TABLE_NAME
1694 );
1695 -- Error : Column Name = COLUMN_NAME
1696 OKL_API.set_message( p_app_name => 'OKL',
1697 p_msg_name => 'OKL_AM_RMK_COLUMN_NAME',
1698 p_token1 => 'COLUMN_NAME',
1699 p_token1_value => x_Error_tbl(i).COLUMN_NAME
1700 );
1701 -- Error : Message Name = MSG_NAME
1702 OKL_API.set_message( p_app_name => 'OKL',
1703 p_msg_name => 'OKL_AM_RMK_MSG_NAME',
1704 p_token1 => 'MSG_NAME',
1705 p_token1_value => x_Error_tbl(i).MESSAGE_NAME
1706 );
1707 -- Error : Message Text = MSG_TEXT
1708 OKL_API.set_message( p_app_name => 'OKL',
1709 p_msg_name => 'OKL_AM_RMK_MSG_TEXT',
1710 p_token1 => 'MSG_TEXT',
1711 p_token1_value => x_Error_tbl(i).MESSAGE_TEXT
1712 );
1713
1714 END LOOP;
1715 END IF;
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 OKL_API.set_message(p_app_name => 'OKC',
1719 p_msg_name => g_unexpected_error,
1720 p_token1 => g_sqlcode_token,
1721 p_token1_value => sqlcode,
1722 p_token2 => g_sqlerrm_token,
1723 p_token2_value => sqlerrm);
1724 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1725 END remove_inv_item;
1726
1727
1728 -- Start of comments
1729 --
1730 -- Procedure Name : remove_item_from_price_list
1731 -- Description : This procedure is called from the main procedure - remove_rmk_item.
1732 -- remove_item_from_price_list inactivates the price list of an item by setting the end_date_active
1733 -- to sysdate
1734 -- Business Rules :
1735 -- Parameters : Input parameters : p_Item_Id - Item Id of the item
1736 -- p_sysdate - system date
1737 --
1738 --
1739 -- Version : 1.0
1740 -- End of comments
1741
1742 PROCEDURE remove_item_from_price_list(p_item_id IN NUMBER,
1743 p_sysdate IN DATE,
1744 x_return_status OUT NOCOPY VARCHAR2)
1745
1746
1747 IS
1748 l_msg_count NUMBER:= 0;
1749 l_msg_data VARCHAR2(2000);
1750 l_return_status VARCHAR2(1) := NULL;
1751 gpr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1752 gpr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1753 gpr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1754 ppr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1755 ppr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
1756 ppr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1757 ppr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
1758 ppr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
1759 ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
1760 ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1761 ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
1762 k NUMBER;
1763 l_list_header_id NUMBER;
1764 l_list_line_id NUMBER;
1765
1766 -- This cursor is used to get the price lists for an inventory item
1767 CURSOR l_prodattrval_csr IS
1768 SELECT list_header_id, list_line_id
1769 FROM qp_pricing_attributes
1770 WHERE product_attr_value = to_char(p_item_id);
1771
1772
1773
1774 BEGIN
1775
1776 -- disable all the price lists for the inventory item
1777 FOR l_prodattrval_rec IN l_prodattrval_csr LOOP
1778
1779 gpr_price_list_rec.list_header_id := l_prodattrval_rec.list_header_id;
1780 gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1781 gpr_price_list_rec.end_date_active := p_sysdate;
1782
1783
1784 gpr_price_list_line_tbl(1).end_date_active := p_sysdate;
1785 gpr_price_list_line_tbl(1).list_line_type_code := 'PLL';
1786 gpr_price_list_line_tbl(1).list_line_id := l_prodattrval_rec.list_line_id;
1787
1788 gpr_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
1789
1790
1791 QP_PRICE_LIST_PUB.Process_Price_List
1792 ( p_api_version_number => 1
1793 , p_init_msg_list => okl_api.G_FALSE
1794 , p_return_values => okl_api.G_FALSE
1795 , p_commit => okl_api.G_FALSE
1796 , x_return_status => x_return_status
1797 , x_msg_count => l_msg_count
1798 , x_msg_data => l_msg_data
1799 , p_PRICE_LIST_rec => gpr_price_list_rec
1800 , p_PRICE_LIST_LINE_tbl => gpr_price_list_line_tbl
1801 , p_PRICING_ATTR_tbl => gpr_pricing_attr_tbl
1802 , x_PRICE_LIST_rec => ppr_price_list_rec
1803 , x_PRICE_LIST_val_rec => ppr_price_list_val_rec
1804 , x_PRICE_LIST_LINE_tbl => ppr_price_list_line_tbl
1805 , x_PRICE_LIST_LINE_val_tbl => ppr_price_list_line_val_tbl
1806 , x_QUALIFIERS_tbl => ppr_qualifiers_tbl
1807 , x_QUALIFIERS_val_tbl => ppr_qualifiers_val_tbl
1808 , x_PRICING_ATTR_tbl => ppr_pricing_attr_tbl
1809 , x_PRICING_ATTR_val_tbl => ppr_pricing_attr_val_tbl
1810 );
1811 END LOOP;
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 OKL_API.set_message(p_app_name => 'OKC',
1815 p_msg_name => g_unexpected_error,
1816 p_token1 => g_sqlcode_token,
1817 p_token1_value => sqlcode,
1818 p_token2 => g_sqlerrm_token,
1819 p_token2_value => sqlerrm);
1820 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1821 END remove_item_from_price_list;
1822 */
1823
1824
1825
1826 -- Start of comments
1827 --
1828 -- Procedure Name : remove_item
1829 -- Description : This procedure is used to reduce the quantity of inventory items after an Order has been
1830 -- booked, by creating an issue transaction . This procedure is called from remove_rmk_item
1831 --
1832 -- Business Rules :
1833 -- Parameters : Input parameters :
1834 -- p_inventory_item_id - item id
1835 -- p_Subinv_Code - Subinventory Code
1836 -- p_org_id - organization that the item belongs to
1837 -- p_dist_account_id - Distribution Account
1838 -- p_quantity - Ordered Quantity
1839 -- p_sysdate - system date
1840 -- Innentory Item Id and Organization Id form the PK for mtl_system_items
1841 -- Version : 1.0
1842 -- History : SECHAWLA 05-DEC-02 Bug# 2620853
1843 -- Commented out the codethat references disribution account id, as it is optional
1844 -- SECHAWLA 16-JAN-03 Bug # 2754280
1845 -- Changed the app name from OKL to OKC for g_unexpected_error
1846 -- End of comments
1847
1848 PROCEDURE remove_item
1849 (
1850 p_inventory_item_id IN NUMBER,
1851 p_Subinv_Code IN VARCHAR2,
1852 p_org_id IN NUMBER,
1853 -- SECHAWLA Bug# 2620853 : dist_account_id is not required
1854 -- p_dist_account_id IN NUMBER,
1855 p_quantity IN NUMBER,
1856 p_sysdate IN DATE,
1857 x_return_status OUT NOCOPY VARCHAR2
1858
1859 )
1860 IS
1861
1862 /*
1863 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1864
1865
1866 -- This cursor is used to make sure that the item exists in active state, before removing the item
1867 CURSOR l_mtlsysitems_csr(p_inventory_item_id NUMBER,p_organization_id NUMBER) IS
1868 SELECT 'x'
1869 FROM mtl_system_items_b
1870 WHERE inventory_item_id = p_inventory_item_id
1871 AND organization_id = p_organization_id
1872 AND end_date_active IS NULL;
1873 */
1874
1875
1876 BEGIN
1877
1878
1879 -- Creating Inventory Issue Transaction for the Item
1880
1881 Create_Inv_Misc_Receipt_Txn
1882 (p_inventory_item_id => p_inventory_item_id,
1883 p_subinv_code => p_subinv_code,
1884 p_organization_id => p_org_id,
1885 --SECHAWLA Bug# 2620853 : dist_account_id is not required
1886 -- p_dist_account_id => p_dist_account_id,
1887 p_quantity => p_quantity,
1888 p_trans_type_id => 32, --- trnasction type ID for Issue Transactions
1889 p_sysdate => p_sysdate,
1890 x_return_status => x_return_status);
1891
1892 -- return status of the above procedure call becomes the return status of the current procedure
1893 -- which is then handled in the calling procedure - remove_rmk_item
1894
1895
1896 /*
1897 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1898 -- If this code needs to be uncommented, we must get the org id as a direct input parameter. We can not
1899 -- use the Org from the profile to delete (disable) an inventory item, as the pofile may change between the time of
1900 -- creation and deletion of inventory item. An item may belong to more than one org So Org Id is required to
1901 -- disable an inventory item.
1902
1903 IF p_item_id IS NULL OR p_item_id = OKL_API.G_MISS_NUM THEN
1904 x_return_status := OKL_API.G_RET_STS_ERROR;
1905 -- Item Id is required
1906 OKC_API.set_message( p_app_name => 'OKC',
1907 p_msg_name => G_REQUIRED_VALUE,
1908 p_token1 => G_COL_NAME_TOKEN,
1909 p_token1_value => 'ITEM_ID');
1910 RAISE okc_api.G_EXCEPTION_ERROR;
1911 END IF;
1912
1913 IF p_org_id IS NULL OR p_org_id = OKL_API.G_MISS_NUM THEN
1914 x_return_status := OKL_API.G_RET_STS_ERROR;
1915 -- Organization Id is required
1916 OKC_API.set_message( p_app_name => 'OKC',
1917 p_msg_name => G_REQUIRED_VALUE,
1918 p_token1 => G_COL_NAME_TOKEN,
1919 p_token1_value => 'ORGANIZATION');
1920 RAISE okc_api.G_EXCEPTION_ERROR;
1921 END IF;
1922
1923 OPEN l_mtlsysitems_csr(p_item_id,p_org_id);
1924 FETCH l_mtlsysitems_csr INTO l_temp;
1925 IF l_mtlsysitems_csr%NOTFOUND THEN
1926 x_return_status := OKL_API.G_RET_STS_ERROR;
1927 -- No active Item exists for this combination of Item and Organization
1928 OKL_API.set_message( p_app_name => 'OKL',
1929 p_msg_name => 'OKL_AM_RMK_INVALID_ITEM_ORG'
1930
1931 );
1932 RAISE OKL_API.G_EXCEPTION_ERROR;
1933 END IF;
1934 CLOSE l_mtlsysitems_csr;
1935
1936 -- Disable the Inventory Item
1937 remove_inv_item(p_item_id => p_item_id,
1938 p_org_id => p_org_id,
1939 p_sysdate => l_sysdate,
1940 x_return_status => x_return_status);
1941
1942 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1943 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1944 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1945 RAISE OKL_API.G_EXCEPTION_ERROR;
1946 END IF;
1947
1948
1949 -- Disable the Price Lists corresponding to the Inventory Item
1950 remove_item_from_price_list(p_item_id => p_item_id,
1951 p_sysdate => l_sysdate,
1952 x_return_status => x_return_status);
1953
1954 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1957 RAISE OKL_API.G_EXCEPTION_ERROR;
1958 END IF;
1959 */
1960
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 -- unexpected error
1964
1965 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1966 OKL_API.set_message(p_app_name => 'OKC',
1967 p_msg_name => g_unexpected_error,
1968 p_token1 => g_sqlcode_token,
1969 p_token1_value => sqlcode,
1970 p_token2 => g_sqlerrm_token,
1971 p_token2_value => sqlerrm);
1972 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1973
1974
1975 END remove_item;
1976
1977
1978
1979 -- Start of comments
1980 --
1981 -- Procedure Name : remove_rmk_item
1982 -- Description : This procedure is used to reduce the quantity of of all the inventory items belonging to an
1983 -- order, after the order has been booked
1984 -- Business Rules :
1985 -- Parameters : Input parameters : p_order_header_id - header ID for the Order
1986 -- Version : 1.0
1987 -- History : SECHAWLA 05-DEC-02 : Bug # 2620853
1988 -- Commented out the code that uses distribution account id
1989 -- Removed DEFAULT hint from procedure parameters
1990 -- SECHAWLA 17-DEC-02 : Bug # 2706328
1991 -- Fixed the datatype of l_temp_org_name
1992 -- SECHAWLA 21-OCT-04 3924244
1993 -- changed p_order_header_id to p_order_line_Id and modified the code to work on order line id
1994 -- instead of order header id
1995 --
1996 -- End of comments
1997
1998 PROCEDURE remove_rmk_item
1999 ( p_api_version IN NUMBER,
2000 p_init_msg_list IN VARCHAR2 ,
2001 p_order_line_Id IN NUMBER, -- SECHAWLA 21-OCT-04 3924244
2002 x_return_status OUT NOCOPY VARCHAR2,
2003 x_msg_count OUT NOCOPY NUMBER,
2004 x_msg_data OUT NOCOPY VARCHAR2
2005
2006 ) IS
2007
2008 -- This cursor is used to validate Header ID
2009 CURSOR l_orderheaders_csr(cp_header_id NUMBER) IS
2010 SELECT order_number
2011 FROM oe_order_headers_all
2012 WHERE header_id = cp_header_id;
2013
2014 /* -- SECHAWLA 21-OCT-04 3924244
2015 -- This cursor is used to get the information about all the line items corresponding to an Order
2016 CURSOR l_orderlines_csr(p_header_id NUMBER) IS
2017 SELECT line_id, inventory_item_id, ordered_quantity, ship_from_org_id
2018 FROM oe_order_lines_all
2019 WHERE header_id = p_header_id;
2020 */
2021
2022 -- SECHAWLA 21-OCT-04 3924244 : added this cursor
2023 -- This cursor is used to get the information about an order line
2024 CURSOR l_orderlines_csr(cp_line_id NUMBER) IS
2025 SELECT header_id, inventory_item_id, ordered_quantity, ship_from_org_id
2026 FROM oe_order_lines_all
2027 WHERE line_id = cp_line_id;
2028
2029
2030 -- This cursor is used to get the source subinventory and distribution account for an inventory item
2031 CURSOR l_mtlsystemitems_csr(p_item_id NUMBER, p_org_id NUMBER) IS
2032 -- SECHAWLA Bug# 2620853 : ENCUMBRANCE_ACCOUNT (which stores the distribution accout id) is not required
2033 --SELECT SOURCE_SUBINVENTORY, ENCUMBRANCE_ACCOUNT
2034 SELECT SOURCE_SUBINVENTORY
2035 FROM mtl_system_items
2036 WHERE inventory_item_id = p_item_id
2037 AND organization_id = p_org_id;
2038
2039 l_order_number NUMBER;
2040 l_return_status VARCHAR2(1);
2041 l_sysdate DATE;
2042 l_api_name CONSTANT VARCHAR2(30) := 'remove_rmk_item';
2043 l_api_version CONSTANT NUMBER := 1;
2044
2045 l_inv_org_id NUMBER;
2046 l_subinv_code VARCHAR2(10);
2047 -- SECHAWLA Bug # 2620853 : Distribution account id is not required
2048 -- l_distribution_account_id NUMBER;
2049 --SECHAWLA 2706328 : Fixed the datatype for l_temp_org_name
2050 l_temp_org_name mtl_organizations.organization_name%TYPE;
2051
2052 l_header_id NUMBER;
2053 l_inventory_item_id NUMBER;
2054 l_ordered_quantity NUMBER;
2055 l_ship_from_org_id NUMBER;
2056
2057 BEGIN
2058
2059 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2060 G_PKG_NAME,
2061 p_init_msg_list,
2062 l_api_version,
2063 p_api_version,
2064 '_PVT',
2065 x_return_status);
2066
2067
2068 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2069 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2070 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2071 RAISE OKL_API.G_EXCEPTION_ERROR;
2072 END IF;
2073
2074 SELECT SYSDATE INTO l_sysdate FROM dual;
2075
2076 -- SECHAWLA 21-OCT-04 3924244 : changed header id to line id
2077 IF p_order_line_Id IS NULL OR p_order_line_Id = OKL_API.G_MISS_NUM THEN
2078 x_return_status := OKL_API.G_RET_STS_ERROR;
2079 -- Order Line ID is required
2080 OKL_API.set_message( p_app_name => 'OKC',
2081 p_msg_name => G_REQUIRED_VALUE,
2082 p_token1 => G_COL_NAME_TOKEN,
2083 p_token1_value => 'ORDER_LINE_ID');
2084 RAISE okl_api.G_EXCEPTION_ERROR;
2085 END IF;
2086
2087 /*-- SECHAWLA 21-OCT-04 3924244
2088 OPEN l_orderheaders_csr(p_order_header_Id);
2089 FETCH l_orderheaders_csr INTO l_order_number;
2090 IF l_orderheaders_csr%NOTFOUND THEN
2091 x_return_status := OKL_API.G_RET_STS_ERROR;
2092 -- Order Header ID is invalid
2093 OKL_API.set_message( p_app_name => 'OKC',
2094 p_msg_name => G_INVALID_VALUE,
2095 p_token1 => G_COL_NAME_TOKEN,
2096 p_token1_value => 'ORDER_HEADER_ID');
2097 RAISE okl_api.G_EXCEPTION_ERROR;
2098 END IF;
2099 CLOSE l_orderheaders_csr;
2100 */
2101
2102 -- SECHAWLA 21-OCT-04 3924244 : added
2103 OPEN l_orderlines_csr(p_order_line_Id);
2104 FETCH l_orderlines_csr INTO l_header_id, l_inventory_item_id, l_ordered_quantity, l_ship_from_org_id;
2105 IF l_orderlines_csr%NOTFOUND THEN
2106 x_return_status := OKL_API.G_RET_STS_ERROR;
2107 -- Order Line ID is invalid
2108 OKL_API.set_message( p_app_name => 'OKC',
2109 p_msg_name => G_INVALID_VALUE,
2110 p_token1 => G_COL_NAME_TOKEN,
2111 p_token1_value => 'ORDER_LINE_ID');
2112 RAISE okl_api.G_EXCEPTION_ERROR;
2113 END IF;
2114 CLOSE l_orderlines_csr;
2115
2116 OPEN l_orderheaders_csr(l_header_id);
2117 FETCH l_orderheaders_csr INTO l_order_number;
2118 CLOSE l_orderheaders_csr;
2119
2120 -- loop thru all the line items for a given order, validate the data and then reduce the quantity of each line item
2121
2122 -- SECHAWLA 21-OCT-04 3924244 : commented out the loop
2123 --FOR l_orderlines_rec IN l_orderlines_csr(p_order_header_id) LOOP
2124
2125 IF l_ship_from_org_id IS NULL THEN
2126 x_return_status := OKL_API.G_RET_STS_ERROR;
2127 -- Ship From Org ID is required
2128 OKL_API.set_message( p_app_name => 'OKC',
2129 p_msg_name => G_REQUIRED_VALUE,
2130 p_token1 => G_COL_NAME_TOKEN,
2131 p_token1_value => 'SHIP_FROM_ORG_ID');
2132 RAISE okl_api.G_EXCEPTION_ERROR;
2133 END IF;
2134
2135
2136 OPEN l_mtlsystemitems_csr(l_inventory_item_id, l_ship_from_org_id);
2137 -- SECHAWLA Bug# 2620853 : Distribution accout id is not required
2138 --FETCH l_mtlsystemitems_csr INTO l_subinv_code, l_distribution_account_id;
2139 FETCH l_mtlsystemitems_csr INTO l_subinv_code;
2140 IF l_mtlsystemitems_csr%NOTFOUND THEN
2141 -- shipping org for the order does not match the Item's organization
2142 x_return_status := OKL_API.G_RET_STS_ERROR;
2143 --Order ORDER_NUMBER has invalid combination of inventory item and organization
2144 OKL_API.set_message(p_app_name => 'OKL',
2145 p_msg_name => 'OKL_AM_INVALID_ITEM_ORG',
2146 p_token1 => 'ORDER_NUMBER',
2147 p_token1_value => l_order_number);
2148 RAISE OKL_API.G_EXCEPTION_ERROR;
2149 END IF;
2150
2151 IF l_subinv_code IS NULL THEN
2152 x_return_status := OKL_API.G_RET_STS_ERROR;
2153 -- source subinventory is required
2154 OKL_API.set_message( p_app_name => 'OKC',
2155 p_msg_name => G_REQUIRED_VALUE,
2156 p_token1 => G_COL_NAME_TOKEN,
2157 p_token1_value => 'SOURCE_SUBINVENTORY');
2158 RAISE okl_api.G_EXCEPTION_ERROR;
2159 END IF;
2160 CLOSE l_mtlsystemitems_csr;
2161
2162 -- Distribution Account ID can be NULL
2163
2164 --SECHAWLA Bug# 2620853 : Distribution Account ID is not required
2165 remove_item( p_inventory_item_id => l_inventory_item_id,
2166 p_subinv_code => l_subinv_code,
2167 p_org_id => l_ship_from_org_id,
2168 p_quantity => -(l_ordered_quantity),
2169 p_sysdate => l_sysdate,
2170 x_return_status => x_return_status);
2171
2172 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2173 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2174 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2175 RAISE OKL_API.G_EXCEPTION_ERROR;
2176 END IF;
2177
2178 -- END LOOP; -- SECHAWLA 21-OCT-04 3924244
2179
2180 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2181 EXCEPTION
2182 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2183
2184 IF l_orderheaders_csr%ISOPEN THEN
2185 CLOSE l_orderheaders_csr;
2186 END IF;
2187 IF l_orderlines_csr%ISOPEN THEN
2188 CLOSE l_orderlines_csr;
2189 END IF;
2190 IF l_mtlsystemitems_csr%ISOPEN THEN
2191 CLOSE l_mtlsystemitems_csr;
2192 END IF;
2193 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2194 (
2195 l_api_name,
2196 G_PKG_NAME,
2197 'OKL_API.G_RET_STS_ERROR',
2198 x_msg_count,
2199 x_msg_data,
2200 '_PVT'
2201 );
2202 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2203 IF l_orderheaders_csr%ISOPEN THEN
2204 CLOSE l_orderheaders_csr;
2205 END IF;
2206 IF l_orderlines_csr%ISOPEN THEN
2207 CLOSE l_orderlines_csr;
2208 END IF;
2209 IF l_mtlsystemitems_csr%ISOPEN THEN
2210 CLOSE l_mtlsystemitems_csr;
2211 END IF;
2212 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2213 (
2214 l_api_name,
2215 G_PKG_NAME,
2216 'OKL_API.G_RET_STS_UNEXP_ERROR',
2217 x_msg_count,
2218 x_msg_data,
2219 '_PVT'
2220 );
2221 WHEN OTHERS THEN
2222 IF l_orderheaders_csr%ISOPEN THEN
2223 CLOSE l_orderheaders_csr;
2224 END IF;
2225 IF l_orderlines_csr%ISOPEN THEN
2226 CLOSE l_orderlines_csr;
2227 END IF;
2228 IF l_mtlsystemitems_csr%ISOPEN THEN
2229 CLOSE l_mtlsystemitems_csr;
2230 END IF;
2231 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2232 (
2233 l_api_name,
2234 G_PKG_NAME,
2235 'OTHERS',
2236 x_msg_count,
2237 x_msg_data,
2238 '_PVT'
2239 );
2240
2241
2242 END remove_rmk_item;
2243
2244
2245
2246
2247 END OKL_AM_REMARKET_ASSET_PVT;
[Home] [Help]