1 PACKAGE BODY OKL_AM_REMARKET_ASSET_PVT AS
2 /* $Header: OKLRRMKB.pls 120.10 2007/06/07 11:39:28 asawanka 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
739 -- SECHAWLA 08-MAR-04 3492490 : new declarations
740
741 l_master_org_name ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
742
743 --SECHAWLA 19-MAY-04 3634514 : new declaration
744 l_assign_subinv VARCHAR2(1);
745
746 -- SECHAWLA 05-OCT-04 3924244 : New declarations
747 -- check the Remarketing flow options from the setup
748 CURSOR l_systemparamsall_csr IS
749 SELECT REMK_ORGANIZATION_ID, REMK_SUBINVENTORY, REMK_PRICE_LIST_ID
750 FROM OKL_SYSTEM_PARAMS ;
751
752 -- SECHAWLA 18-OCT-04 3924244 : new declarations
753 -- check if item already exists in inventory
754 CURSOR l_mtlsystemitems_csr(cp_inv_item_number IN VARCHAR2) IS
755 SELECT count(*)
756 FROM MTL_SYSTEM_ITEMS_B
757 WHERE segment1 = cp_inv_item_number;
758
759 l_item_cnt NUMBER;
760
761
762 BEGIN
763 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item','Begin(+)');
765 END IF;
766
767 --Print Input Variables
768 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
770 'p_init_msg_list :'||p_init_msg_list);
771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
772 'p_item_number :'||p_item_number);
773 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
774 'p_Item_Description :'||p_Item_Description);
775 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
776 'p_Item_Price :'||p_Item_Price);
777 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
778 'p_quantity :'||p_quantity);
779
780 END IF;
781
782 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
783 G_PKG_NAME,
784 p_init_msg_list,
785 l_api_version,
786 p_api_version,
787 '_PVT',
788 x_return_status);
789
790
791 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
792 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
793 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
794 RAISE OKL_API.G_EXCEPTION_ERROR;
795 END IF;
796
797 SELECT SYSDATE INTO l_sysdate FROM DUAL;
798
799 -- SECHAWLA 18-OCT-04 3924244 : Added the following validation
800 IF p_item_number IS NOT NULL THEN
801 OPEN l_mtlsystemitems_csr(p_item_number);
802 FETCH l_mtlsystemitems_csr INTO l_item_cnt;
803 CLOSE l_mtlsystemitems_csr;
804
805 IF l_item_cnt > 0 THEN
806 --Item number ITEM_NUMBER already exists in Inventory. Please enter another item number.
807 OKL_API.set_message( p_app_name => 'OKL',
808 p_msg_name => 'OKL_AM_ITEM_ALREADY_EXISTS',
809 p_token1 => 'ITEM_NUMBER',
810 p_token1_value => p_item_number);
811 x_return_status := OKL_API.G_RET_STS_ERROR;
812 RAISE OKL_API.G_EXCEPTION_ERROR;
813 END IF;
814 END IF;
815 -- SECHAWLA 18-OCT-04 3924244 : end
816
817 IF p_item_price IS NULL OR p_item_price = OKL_API.G_MISS_NUM THEN
818 x_return_status := OKL_API.G_RET_STS_ERROR;
819 --Item Price is required
820 OKC_API.set_message( p_app_name => 'OKC',
821 p_msg_name => G_REQUIRED_VALUE,
822 p_token1 => G_COL_NAME_TOKEN,
823 p_token1_value => 'ITEM_PRICE');
824
825 RAISE okl_api.G_EXCEPTION_ERROR;
826 END IF;
827
828 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM THEN
829 x_return_status := OKL_API.G_RET_STS_ERROR;
830 --Item Quantity is required
831 OKC_API.set_message( p_app_name => 'OKC',
832 p_msg_name => G_REQUIRED_VALUE,
833 p_token1 => G_COL_NAME_TOKEN,
834 p_token1_value => 'ITEM_QUANTITY');
835
836 RAISE okl_api.G_EXCEPTION_ERROR;
837 END IF;
838
839 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
840 -- Check the remarketing flow setup
841 OPEN l_systemparamsall_csr;
842 FETCH l_systemparamsall_csr INTO l_inv_org_id, l_subinv_code, l_price_list_id;
843 IF l_systemparamsall_csr%NOTFOUND THEN
844 -- Remarketing options are not setup for this operating unit.
845 OKL_API.set_message(
846 p_app_name => 'OKL',
847 p_msg_name => 'OKL_AM_NO_REMK_SETUP');
848 x_return_status := OKL_API.G_RET_STS_ERROR;
849 RAISE OKL_API.G_EXCEPTION_ERROR;
850 END IF;
851 CLOSE l_systemparamsall_csr;
852
853
854 IF l_inv_org_id IS NULL THEN
855 -- Remarketing Inventory Organization is not setup for this operating unit.
856 OKL_API.set_message(
857 p_app_name => 'OKL',
858 p_msg_name => 'OKL_AM_NO_REMK_ORG');
859 x_return_status := OKL_API.G_RET_STS_ERROR;
860 RAISE OKL_API.G_EXCEPTION_ERROR;
861 END IF;
862 -- SECHAWLA 05-OCT-04 3924244 : Migrated profiles to setups
863
864
865
866 /* -- -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
867
868 l_inv_org_id := fnd_profile.value('OKL_REMARKET_ITEMS_INV_ORG');
869 l_subinv_code := fnd_profile.value('OKL_REMARKET_SUBINVENTORY');
870
871 -- distribution account ID in mtl_transactions_interface is optional.
872
873 -- SECHAWLA Bug # 2620853 - following profile is not required
874 --l_distribution_account_id := fnd_profile.value('OKL_REMARKET_DISTRIBUTION_ACCOUNT');
875
876 l_price_list_id := fnd_profile.value('OKL_REMARKET_PRICE_LIST');
877 */
878
879 -- ASO_ORDER_TYPE_ID is an Oracle Order Capture profile to set the default Order type.
880 -- iStore uses this profile to get the default Order type and then assigns this Order type to the Orders
881 -- l_default_order_type_id := fnd_profile.value('ASO_ORDER_TYPE_ID');
882 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
883
884 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
885 -- validate organization
886 IF l_inv_org_id IS NULL THEN
887
888 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
889 -- instead of profile option name
890
891 -- SECHAWLA 16-JAN-02 Bug # 2754280 : Changed the following fn call to call this function from am util
892 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
893 p_profile_option_name => 'OKL_REMARKET_ITEMS_INV_ORG',
894 x_return_status => x_return_status);
895
896 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
897 --Remarketing Inventory Organization profile is missing.
898 OKL_API.set_message( p_app_name => 'OKL',
899 p_msg_name => 'OKL_AM_NO_INV_ORG_PROFILE'
900 );
901 RAISE okl_api.G_EXCEPTION_ERROR;
902 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
903 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
904 END IF;
905 -- SECHAWLA Bug# 2679812 -- end new code
906
907 x_return_status := OKL_API.G_RET_STS_ERROR;
908 --Profile value not defined
909 OKL_API.set_message( p_app_name => 'OKL',
910 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
911 p_token1 => 'PROFILE',
912 p_token1_value => l_user_profile_name -- modified to display user profile option name
913 );
914 RAISE okl_api.G_EXCEPTION_ERROR;
915 END IF;
916 */ -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
917
918
919 OPEN l_mtlorgcsr(l_inv_org_id);
920 FETCH l_mtlorgcsr INTO l_temp_org_name;
921 IF l_mtlorgcsr%NOTFOUND THEN
922 x_return_status := OKL_API.G_RET_STS_ERROR;
923 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
924 --Profile is invalid.
925 OKL_API.set_message( p_app_name => 'OKL',
926 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
927 p_token1 => 'PROFILE',
928 p_token1_value => 'OKL_REMARKET_ITEMS_INV_ORG'
929 );
930 */
931
932 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
933 OKC_API.set_message( p_app_name => 'OKC',
934 p_msg_name => G_INVALID_VALUE,
935 p_token1 => G_COL_NAME_TOKEN,
936 p_token1_value => 'Remarketing Inventory Organization');
937
938 RAISE okl_api.G_EXCEPTION_ERROR;
939 END IF;
940 CLOSE l_mtlorgcsr;
941
942
943 -- validate subinventory
944 IF l_subinv_code IS NULL THEN
945
946 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
947 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
948 -- instead of profile option name
949 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
950 p_profile_option_name => 'OKL_REMARKET_SUBINVENTORY',
951 x_return_status => x_return_status);
952
953 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
954 --Remarketing Subinventory profile is missing.
955 OKL_API.set_message( p_app_name => 'OKL',
956 p_msg_name => 'OKL_AM_NO_SUBINV_PROFILE'
957 );
958 RAISE okl_api.G_EXCEPTION_ERROR;
959 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
960 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
961 END IF;
962 -- SECHAWLA Bug# 2679812 -- end new code
963
964 x_return_status := OKL_API.G_RET_STS_ERROR;
965 --Profile value not defined
966 OKL_API.set_message( p_app_name => 'OKL',
967 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
968 p_token1 => 'PROFILE',
969 p_token1_value => l_user_profile_name -- modified to display user profile option
970 );
971 RAISE okl_api.G_EXCEPTION_ERROR;
972 */
973
974 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
975 -- Remarketing Subinventory is not setup for this operating unit.
976 OKL_API.set_message(
977 p_app_name => 'OKL',
978 p_msg_name => 'OKL_AM_NO_REMK_SUBINV');
979 x_return_status := OKL_API.G_RET_STS_ERROR;
980 RAISE OKL_API.G_EXCEPTION_ERROR;
981
982 END IF;
983
984
985
986 -- This profile will generally be set to NULL. We are still keeping the profile to provide flexibility
987 -- to the user, if they want to store distribution account ID in mtl_transactionjs_interface
988
989 -- SECHAWLA Bug # 2620853 - no need to validate distribution_account_id, as we are not using the corresponding profile.
990
991 /* IF l_distribution_account_id IS NOT NULL THEN
992 OPEN l_glcodecomb_csr(l_distribution_account_id);
993 FETCH l_glcodecomb_csr INTO l_temp_ccid;
994 IF l_glcodecomb_csr%NOTFOUND THEN
995 x_return_status := OKL_API.G_RET_STS_ERROR;
996 -- Profile is invalid.
997 OKL_API.set_message( p_app_name => 'OKL',
998 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
999 p_token1 => 'PROFILE',
1000 p_token1_value => 'OKL_REMARKET_DISTRIBUTION_ACCOUNT'
1001 );
1002 RAISE OKL_API.G_EXCEPTION_ERROR;
1003 END IF;
1004 CLOSE l_glcodecomb_csr;
1005 END IF;
1006 */
1007
1008
1009 IF l_price_list_id IS NULL THEN
1010
1011 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1012 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
1013 -- instead of profile option name
1014 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
1015 p_profile_option_name => 'OKL_REMARKET_PRICE_LIST',
1016 x_return_status => x_return_status);
1017
1018 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1019 --Remarketing Price List profile is missing.
1020 OKL_API.set_message( p_app_name => 'OKL',
1021 p_msg_name => 'OKL_AM_NO_PL_PROFILE'
1022 );
1023 RAISE okl_api.G_EXCEPTION_ERROR;
1024 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1025 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 END IF;
1027 -- SECHAWLA Bug# 2679812 -- end new code
1028
1029 x_return_status := OKL_API.G_RET_STS_ERROR;
1030 -- Profile value not defined
1031 OKL_API.set_message( p_app_name => 'OKL',
1032 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
1033 p_token1 => 'PROFILE',
1034 p_token1_value => l_user_profile_name -- modified to display user profile option
1035 );
1036 RAISE OKL_API.G_EXCEPTION_ERROR;
1037 */
1038 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1039
1040 -- Remarketing Price List is not setup for this operating unit.
1041 OKL_API.set_message(
1042 p_app_name => 'OKL',
1043 p_msg_name => 'OKL_AM_NO_REMK_PRICE_LIST');
1044 x_return_status := OKL_API.G_RET_STS_ERROR;
1045 RAISE OKL_API.G_EXCEPTION_ERROR;
1046 END IF;
1047
1048
1049 OPEN l_qplisthdr_csr(l_price_list_id);
1050 FETCH l_qplisthdr_csr INTO l_pricelist_name; -- SECHAWLA 08-DEC-04 4047159
1051 IF l_qplisthdr_csr%NOTFOUND THEN
1052 x_return_status := OKL_API.G_RET_STS_ERROR;
1053
1054 /* -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1055 -- Profile is invalid.
1056 OKL_API.set_message( p_app_name => 'OKL',
1057 p_msg_name => 'OKL_AM_RMK_INVALID_PROFILE',
1058 p_token1 => 'PROFILE',
1059 p_token1_value => 'OKL_REMARKET_PRICE_LIST'
1060 */
1061
1062 -- SECHAWLA 05-OCT-04 3924244 : Migrated remarketing profiles to setups
1063 OKC_API.set_message( p_app_name => 'OKC',
1064 p_msg_name => G_INVALID_VALUE,
1065 p_token1 => G_COL_NAME_TOKEN,
1066 p_token1_value => 'Remarketing Price List');
1067 RAISE OKL_API.G_EXCEPTION_ERROR;
1068 END IF;
1069 CLOSE l_qplisthdr_csr;
1070
1071 IF l_default_order_type_id IS NULL THEN
1072
1073 --SECHAWLA Bug# 2679812 : Added the following code to display user profile option name in messages
1074 -- instead of profile option name
1075 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
1076 p_profile_option_name => 'ASO_ORDER_TYPE_ID',
1077 x_return_status => x_return_status);
1078
1079 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1080 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1081 'l_user_profile_name status'||x_return_status);
1082 END IF;
1083
1084 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1085 --Remarketing Order Type profile is missing.
1086 OKL_API.set_message( p_app_name => 'OKL',
1087 p_msg_name => 'OKL_AM_NO_ORDER_TYPE_PROFILE'
1088 );
1089 RAISE okl_api.G_EXCEPTION_ERROR;
1090 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1091 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1092 END IF;
1093 -- SECHAWLA Bug# 2679812 -- end new code
1094
1095 x_return_status := OKL_API.G_RET_STS_ERROR;
1096 -- Profile value not defined
1097 OKL_API.set_message( p_app_name => 'OKL',
1098 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
1099 p_token1 => 'PROFILE',
1100 p_token1_value => l_user_profile_name -- modified to display user profile option
1101 );
1102 RAISE OKL_API.G_EXCEPTION_ERROR;
1103 END IF;
1104
1105 -- get the warehouse and the Line Type for the Default Order Type
1106 OPEN l_oetranstypesall_csr(l_default_order_type_id);
1107 FETCH l_oetranstypesall_csr INTO l_order_warehouse_id, l_def_outbound_line_type_id, l_order_name;
1108 -- This fetch will definitely find the record in oe_transaction_types_all
1109 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1110 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1111 'fetched l_oetranstypesall_csr..');
1112 END IF;
1113
1114 CLOSE l_oetranstypesall_csr;
1115
1116 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1117 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1118 'l_order_warehouse_id..'||l_order_warehouse_id);
1119 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1120 'l_def_outbound_line_type_id..'||l_def_outbound_line_type_id);
1121 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1122 'l_order_name..'||l_order_name);
1123 END IF;
1124
1125
1126 IF l_order_warehouse_id IS NULL THEN
1127 x_return_status := OKL_API.G_RET_STS_ERROR;
1128 -- Warehouse not defined for this Order Type
1129 OKL_API.set_message( p_app_name => 'OKL',
1130 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
1131 p_token1 => 'TYPE',
1132 p_token1_value => 'ORDER',
1133 p_token2 => 'NAME',
1134 p_token2_value => l_order_name
1135 );
1136 RAISE OKL_API.G_EXCEPTION_ERROR;
1137 END IF;
1138
1139 IF l_def_outbound_line_type_id IS NULL THEN
1140 x_return_status := OKL_API.G_RET_STS_ERROR;
1141 -- Line Type not defined for this Order Type
1142 OKL_API.set_message( p_app_name => 'OKL',
1143 p_msg_name => 'OKL_AM_RMK_NO_LINE_TYPE',
1144 p_token1 => 'ORDER_TYPE',
1145 p_token1_value => l_order_name
1146 );
1147 RAISE OKL_API.G_EXCEPTION_ERROR;
1148 END IF;
1149
1150 -- get the warehouse for the Line type corresponding to the Default Order Type
1151 OPEN l_oetranstypesall_csr(l_def_outbound_line_type_id);
1152 FETCH l_oetranstypesall_csr INTO l_line_warehouse_id, l_def_outbound_line_type_id, l_line_name;
1153 -- This fetch will definitely find the record in oe_transaction_types_all
1154 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1155 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1156 'fetched l_oetranstypesall_csr again');
1157 END IF;
1158
1159 CLOSE l_oetranstypesall_csr;
1160
1161 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1162 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1163 'l_line_warehouse_id'||l_line_warehouse_id);
1164 END IF;
1165
1166 IF l_line_warehouse_id IS NULL THEN
1167 x_return_status := OKL_API.G_RET_STS_ERROR;
1168 -- Warehouse not defined for this Line Type
1169 OKL_API.set_message( p_app_name => 'OKL',
1170 p_msg_name => 'OKL_AM_RMK_NO_WAREHOUSE',
1171 p_token1 => 'TYPE',
1172 p_token1_value => 'LINE',
1173 p_token2 => 'NAME',
1174 p_token2_value => l_line_name
1175 );
1176 RAISE OKL_API.G_EXCEPTION_ERROR;
1177 END IF;
1178
1179 IF l_order_warehouse_id <> l_line_warehouse_id THEN
1180 x_return_status := OKL_API.G_RET_STS_ERROR;
1181 -- Warehouses for the Order Type ORDER_TYPE and Line Type LINE_TYPE do not match.
1182 OKL_API.set_message( p_app_name => 'OKL',
1183 p_msg_name => 'OKL_AM_RMK_WHS_MISMATCH',
1184 p_token1 => 'ORDER_TYPE',
1185 p_token1_value => l_order_name,
1186 p_token2 => 'LINE_TYPE',
1187 p_token2_value => l_line_name
1188 );
1189 RAISE OKL_API.G_EXCEPTION_ERROR;
1190 END IF;
1191
1192 IF l_inv_org_id <> l_order_warehouse_id THEN
1193 x_return_status := OKL_API.G_RET_STS_ERROR;
1194 -- Shipping organization should be the same as the inventory item organization.
1195 OKL_API.set_message( p_app_name => 'OKL',
1196 p_msg_name => 'OKL_AM_RMK_INVALID_WHS'
1197 );
1198 RAISE OKL_API.G_EXCEPTION_ERROR;
1199 END IF;
1200
1201
1202 --SECHAWLA Bug# 2679812 : Added/modified the following code to first create the item in the master org and then
1203 -- assign the same item to the child org
1204
1205
1206 -- SECHAWLA Bug# 2679812 : Get the Master Org for the Inv Org
1207 OPEN l_mtlparam_csr(l_inv_org_id);
1208
1209 -- SECAHWLA 08-MAR-04 3492490 : Added master org name
1210 FETCH l_mtlparam_csr INTO l_master_org_id, l_master_org_name;
1211 IF l_mtlparam_csr%NOTFOUND THEN
1212 x_return_status := OKL_API.G_RET_STS_ERROR;
1213 -- Inventory organization is not set up in MTL Parameters.
1214 OKL_API.set_message( p_app_name => 'OKL',
1215 p_msg_name => 'OKL_AM_NO_ORG_PARAM'
1216 );
1217 RAISE OKL_API.G_EXCEPTION_ERROR;
1218 END IF;
1219 IF l_master_org_id IS NULL THEN
1220 x_return_status := OKL_API.G_RET_STS_ERROR;
1221 -- Master organization is not defined for inventory organization.
1222 OKL_API.set_message( p_app_name => 'OKL',
1223 p_msg_name => 'OKL_AM_NO_MASTER_ORG'
1224 );
1225 RAISE OKL_API.G_EXCEPTION_ERROR;
1226 END IF;
1227 CLOSE l_mtlparam_csr;
1228
1229 -- SECHAWLA Bug# 2679812 :
1230 -- If inv org is a master org then create item only in master org. If 2 orgs are different, then first craete
1231 -- the item in master org and then assign the same item to child org
1232 IF l_inv_org_id = l_master_org_id THEN
1233 l_iterations := 1;
1234 ELSE
1235 l_iterations := 2;
1236 END IF;
1237
1238 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1239 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1240 'l_iterations '||l_iterations);
1241 END IF;
1242
1243 -- SECHAWLA Bug# 2679812 :
1244 -- This loop is executed once if the inv org is also a master org whereas if the 2 orgs are different,
1245 -- this loop is executed twice, once for the master org and then for the child org
1246 FOR i IN 1..l_iterations LOOP
1247
1248 IF i = 1 THEN
1249 l_current_org := l_master_org_id;
1250 l_current_org_name := l_master_org_name; -- SECHAWLA 08-DEC-04 4047159
1251 --l_item_number := NULL; -- SECHAWLA 05-OCT-04 3924244
1252 l_item_number := p_item_number; -- SECHAWLA 05-OCT-04 3924244 : Use item no. entered by the user. It may be NULL
1253 l_item_id := NULL;
1254 ELSIF i = 2 THEN
1255 l_current_org := l_inv_org_id; --child org
1256 l_current_org_name := l_temp_org_name; -- SECHAWLA 08-DEC-04 4047159
1257 l_item_number := l_New_Item_Number;
1258 l_item_id := l_New_Item_Id;
1259 END IF;
1260
1261 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1262 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1263 'l_current_org'||l_current_org);
1264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1265 'l_item_number'||l_item_number);
1266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1267 'l_item_id'||l_item_id);
1268 END IF;
1269 ---------------------
1270 -- SECHAWLA 08-MAR-04 3492490 : Moved the following validation here from the beginning. So it can be
1271 -- performed for both Master and Child org
1272
1273 OPEN l_mtlsecinv_csr(l_current_org , l_subinv_code );
1274 FETCH l_mtlsecinv_csr INTO l_temp;
1275 IF l_mtlsecinv_csr%NOTFOUND THEN
1276 --x_return_status := OKL_API.G_RET_STS_ERROR;
1277
1278 IF (l_inv_org_id <> l_master_org_id AND i = 1 )THEN
1279 /* SECHAWLA 19-MAY-04 3634514 : Commented out
1280 --Subinventory SUBINVENTORY is not defined for the organization MASTER_ORG, which is the Master organization of the Remarketing Inventory organization CHILD_ORG.
1281 OKL_API.set_message(p_app_name => 'OKL',
1282 p_msg_name => 'OKL_AM_RMK_MST_ORG_SUBINV',
1283 p_token1 => 'SUBINVENTORY',
1284 p_token1_value => l_subinv_code,
1285 p_token2 => 'MASTER_ORG',
1286 p_token2_value => l_master_org_name,
1287 p_token3 => 'CHILD_ORG',
1288 p_token3_value => l_temp_org_name);
1289 */
1290 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1292 '(l_inv_org_id <> l_master_org_id AND i = 1) sts'||x_return_status);
1293
1294 END IF;
1295
1296 NULL;
1297 ELSE
1298 x_return_status := OKL_API.G_RET_STS_ERROR;
1299 --Subinventory SUBINVENTORY is invalid for the organization ORGANIZATION.
1300 OKL_API.set_message(p_app_name => 'OKL',
1301 p_msg_name => 'OKL_AM_RMK_ORG_SUBINV',
1302 p_token1 => 'SUBINVENTORY',
1303 p_token1_value => l_subinv_code,
1304 p_token2 => 'ORGANIZATION',
1305 p_token2_value => l_temp_org_name);
1306 RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Added
1307 END IF;
1308
1309 --RAISE OKL_API.G_EXCEPTION_ERROR; --SECHAWLA 19-MAY-04 3634514 : Commented out
1310 END IF;
1311 CLOSE l_mtlsecinv_csr;
1312
1313
1314 --SECHAWLA 19-MAY-04 3634514 : populate subinventory fields in the item master only if the
1315 --Remarketing org is Master Org OR if the remarketing org is the child org and the item is
1316 --being assigned to the child org
1317
1318 IF (l_iterations = 1) OR (i = 2) THEN
1319 l_assign_subinv := 'Y';
1320 ELSE
1321 l_assign_subinv := 'N';
1322 END IF;
1323 --SECHAWLA 19-MAY-04 3634514 : end
1324
1325
1326 ---------------------
1327
1328 -- Create the Item in Inventory
1329 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1331 'before create_inv_item call'||x_return_status);
1332
1333 END IF;
1334 create_inv_item
1335 (--p_Organization_Id => l_inv_org_id, --SECHAWLA Bug# 2679812 : use current org
1336 p_Organization_Id => l_current_org,
1337 p_organization_name => l_current_org_name, -- SECHAWLA 08-DEC-04 4047159 : added
1338 p_Item_Description => p_Item_Description,
1339 p_subinventory => l_subinv_code,
1340 -- p_distribution_acct_id => l_distribution_account_id, -- SECHAWLA Bug # 2620853 : Removed
1341 p_sysdate => l_sysdate,
1342 -- SECHAWLA 05-OCT-04 3924244 : l_item_number may have a value for the master org (if user enters item no.)
1343 p_item_number => l_item_number, --SECHAWLA Bug# 2679812 :added
1344 p_item_id => l_item_id, --SECHAWLA Bug# 2679812 :added
1345 p_assign_subinv => l_assign_subinv, --SECHAWLA 19-MAY-04 3634514: Added
1346 x_New_Item_Number => l_New_Item_Number,
1347 x_New_Item_Id => l_New_Item_Id,
1348 x_return_Status => x_return_status);
1349
1350 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1351 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1352 'after create_inv_item call'||x_return_status);
1353
1354 END IF;
1355
1356 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1359 RAISE OKL_API.G_EXCEPTION_ERROR;
1360 END IF;
1361
1362
1363
1364
1365 IF (l_iterations = 1) OR (i = 2) THEN --SECHAWLA Bug# 2679812 : Create Misc transaction if inv org is the
1366 --master org OR for the child org
1367
1368 -- Creating Inventory Receipt Transaction for the Item
1369 -- SECHAWLA Bug # 2620853 : No need to pass distribution account id, as it is optional
1370 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1372 'before Create_Inv_Misc_Receipt_Txn call'||x_return_status);
1373
1374 END IF;
1375 Create_Inv_Misc_Receipt_Txn
1376 ( p_Inventory_Item_id => l_New_Item_Id,
1377 p_Subinv_Code => l_subinv_code,
1378 --p_Organization_Id => l_inv_org_id, --SECHAWLA Bug# 2679812 : use current org
1379 p_Organization_Id => l_current_org,
1380 --p_Dist_account_id => l_distribution_account_id, -- This can be NULL
1381 p_quantity => p_quantity,
1382 p_trans_type_id => 42, --- transaction type ID for Receipt Transactions
1383 p_sysdate => l_sysdate,
1384 x_Return_Status => x_return_status);
1385
1386 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1387 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1388 'after Create_Inv_Misc_Receipt_Txn call'||x_return_status);
1389
1390 END IF;
1391
1392 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1393 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1394 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1395 RAISE OKL_API.G_EXCEPTION_ERROR;
1396 END IF;
1397
1398 END IF;
1399
1400 IF i = 1 THEN -- SECHAWLA Bug# 2679812 :Create price list only for the master item
1401 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1402 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1403 'before Create_Item_In_Price_List'||x_return_status);
1404
1405 END IF;
1406 -- Create the Item in the Price List
1407 Create_Item_In_Price_List
1408 (
1409 p_api_version => l_api_version,
1410 p_Price_List_id => l_price_list_id,
1411 p_price_list_name => l_pricelist_name, -- SECHAWLA 08-DEC-04 4047159 : added
1412 p_price_list_item => l_New_Item_Number, -- SECHAWLA 08-DEC-04 4047159 : added
1413 p_Item_Id => l_New_Item_Id,
1414 p_Item_Price => p_Item_Price,
1415 x_return_status => x_return_status);
1416 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1417 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1418 'after Create_Item_In_Price_List'||x_return_status);
1419
1420 END IF;
1421
1422 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1423 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1424 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1425 RAISE OKL_API.G_EXCEPTION_ERROR;
1426 END IF;
1427 END IF;
1428
1429 END LOOP;
1430
1431 x_New_Item_Number := l_new_item_number;
1432 x_New_Item_Id := l_new_item_id;
1433
1434 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1435 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1436 'x_New_Item_Number '||x_New_Item_Number);
1437 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item.',
1438 'x_New_Item_Id '||x_New_Item_Id);
1439
1440 END IF;
1441
1442 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ','End(-)');
1444 END IF;
1445
1446 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1447
1448 EXCEPTION
1449 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1450 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1451 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ',
1452 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1453 END IF;
1454
1455 IF l_mtlsystemitems_csr%ISOPEN THEN
1456 CLOSE l_mtlsystemitems_csr;
1457 END IF;
1458
1459 IF l_mtlorgcsr%ISOPEN THEN
1460 CLOSE l_mtlorgcsr;
1461 END IF;
1462
1463 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1464 IF l_mtlsecinv_csr%ISOPEN THEN
1465 CLOSE l_mtlsecinv_csr;
1466 END IF;
1467
1468 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1469 IF l_qplisthdr_csr%ISOPEN THEN
1470 CLOSE l_qplisthdr_csr;
1471 END IF;
1472
1473 -- SECHAWLA Bug# 2620853 : This cursor is not used
1474 /* IF l_glcodecomb_csr%ISOPEN THEN
1475 CLOSE l_glcodecomb_csr;
1476 END IF;
1477 */
1478
1479 IF l_oetranstypesall_csr%ISOPEN THEN
1480 CLOSE l_oetranstypesall_csr;
1481 END IF;
1482
1483 -- SECHAWLA Bug# 2679812 : close the new cursor
1484 IF l_mtlparam_csr%ISOPEN THEN
1485 CLOSE l_mtlparam_csr;
1486 END IF;
1487
1488 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1489 IF l_systemparamsall_csr%ISOPEN THEN
1490 CLOSE l_systemparamsall_csr;
1491 END IF;
1492
1493
1494 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1495 (
1496 l_api_name,
1497 G_PKG_NAME,
1498 'OKL_API.G_RET_STS_ERROR',
1499 x_msg_count,
1500 x_msg_data,
1501 '_PVT'
1502 );
1503 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1504 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1505 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_AM_REMARKET_ASSET_PVT.create_rmk_item ',
1506 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1507 END IF;
1508
1509 IF l_mtlsystemitems_csr%ISOPEN THEN
1510 CLOSE l_mtlsystemitems_csr;
1511 END IF;
1512
1513 IF l_mtlorgcsr%ISOPEN THEN
1514 CLOSE l_mtlorgcsr;
1515 END IF;
1516
1517 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1518 IF l_mtlsecinv_csr%ISOPEN THEN
1519 CLOSE l_mtlsecinv_csr;
1520 END IF;
1521
1522 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1523 IF l_qplisthdr_csr%ISOPEN THEN
1524 CLOSE l_qplisthdr_csr;
1525 END IF;
1526
1527 -- SECHAWLA Bug# 2620853 : This cursor is not used
1528 /* IF l_glcodecomb_csr%ISOPEN THEN
1529 CLOSE l_glcodecomb_csr;
1530 END IF;
1531 */
1532
1533 IF l_oetranstypesall_csr%ISOPEN THEN
1534 CLOSE l_oetranstypesall_csr;
1535 END IF;
1536
1537 -- SECHAWLA Bug# 2679812 : close the new cursor
1538 IF l_mtlparam_csr%ISOPEN THEN
1539 CLOSE l_mtlparam_csr;
1540 END IF;
1541
1542 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1543 IF l_systemparamsall_csr%ISOPEN THEN
1544 CLOSE l_systemparamsall_csr;
1545 END IF;
1546 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1547 (
1548 l_api_name,
1549 G_PKG_NAME,
1550 'OKL_API.G_RET_STS_UNEXP_ERROR',
1551 x_msg_count,
1552 x_msg_data,
1553 '_PVT'
1554 );
1555 WHEN OTHERS 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_REMARKET_ASSET_PVT.create_rmk_item',
1559 'EXCEPTION :'||sqlerrm);
1560 END IF;
1561
1562 IF l_mtlsystemitems_csr%ISOPEN THEN
1563 CLOSE l_mtlsystemitems_csr;
1564 END IF;
1565
1566 IF l_mtlorgcsr%ISOPEN THEN
1567 CLOSE l_mtlorgcsr;
1568 END IF;
1569
1570 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1571 IF l_mtlsecinv_csr%ISOPEN THEN
1572 CLOSE l_mtlsecinv_csr;
1573 END IF;
1574
1575 -- SECHAWLA Bug# 2679812 : Fixed the typo in the CLOSE cursor statement
1576 IF l_qplisthdr_csr%ISOPEN THEN
1577 CLOSE l_qplisthdr_csr;
1578 END IF;
1579
1580 -- SECHAWLA Bug# 2620853 : This cursor is not used
1581 /* IF l_glcodecomb_csr%ISOPEN THEN
1582 CLOSE l_glcodecomb_csr;
1583 END IF;
1584 */
1585
1586 IF l_oetranstypesall_csr%ISOPEN THEN
1587 CLOSE l_oetranstypesall_csr;
1588 END IF;
1589
1590 -- SECHAWLA Bug# 2679812 : close the new cursor
1591 IF l_mtlparam_csr%ISOPEN THEN
1592 CLOSE l_mtlparam_csr;
1593 END IF;
1594
1595 -- SECHAWLA 05-OCT-04 3924244 : close new cursor
1596 IF l_systemparamsall_csr%ISOPEN THEN
1597 CLOSE l_systemparamsall_csr;
1598 END IF;
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
1610 END create_rmk_item;
1611
1612
1613
1614 /*
1615
1616 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1617
1618
1619 -- Start of comments
1620 --
1621 -- Procedure Name : remove_inv_item
1622 -- Description : This procedure is called from the main procedure - remove_rmk_item.
1623 -- remove_inv_item inactivates an inventory item by setting the end_date_active to sysdate
1624 -- Business Rules :
1625 -- Parameters : Input parameters : p_Item_Id - Item Id of item to be removed
1626 -- p_org_id - Items' organization Id
1627 -- p_sysdate - system date
1628 --
1629 --
1630 -- Version : 1.0
1631 -- End of comments
1632
1633 PROCEDURE remove_inv_item( p_item_id IN NUMBER,
1634 p_org_id IN NUMBER,
1635 p_sysdate IN DATE,
1636 x_return_status OUT NOCOPY VARCHAR2)
1637 IS
1638 l_Item_rec INV_Item_GRP.Item_rec_type;
1639 x_Item_rec INV_Item_GRP.Item_rec_type;
1640 l_commit VARCHAR2(1);
1641 l_validation_level NUMBER;
1642 l_return_status VARCHAR2(1);
1643 x_Error_tbl INV_Item_GRP.Error_tbl_type;
1644
1645 l_lock_rows VARCHAR2(20) := fnd_api.g_TRUE;
1646
1647 BEGIN
1648
1649 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1650
1651 l_item_rec.end_date_active := p_sysdate;
1652 l_item_rec.inventory_item_id := p_Item_Id;
1653 l_Item_rec.organization_id := p_org_id;
1654
1655
1656 l_commit := OKC_API.g_FALSE;
1657
1658 INV_Item_GRP.update_item
1659 (
1660 p_commit => l_commit,
1661 p_lock_rows => l_lock_rows
1662 , p_validation_level => l_validation_level
1663 , p_Item_rec => l_Item_rec
1664 , x_Item_rec => x_item_rec
1665 , x_return_status => x_return_status
1666 , x_Error_tbl => x_Error_tbl
1667 );
1668
1669
1670 IF ( x_return_status <> okl_api.G_RET_STS_SUCCESS ) THEN
1671 -- Display the error messages from the x_error_tbl table
1672 FOR i IN 1 .. x_Error_tbl.COUNT LOOP
1673 -- Error : Transaction Id = TRX_ID
1674 OKL_API.set_message( p_app_name => 'OKL',
1675 p_msg_name => 'OKL_AM_RMK_TRANS_ID',
1676 p_token1 => 'TRX_ID',
1677 p_token1_value => x_Error_tbl(i).TRANSACTION_ID
1678 );
1679 -- Error : Unique Id = UNIQUE_ID
1680 OKL_API.set_message( p_app_name => 'OKL',
1681 p_msg_name => 'OKL_AM_RMK_UNIQUE_ID',
1682 p_token1 => 'UNIQUE_ID',
1683 p_token1_value => x_Error_tbl(i).UNIQUE_ID
1684 );
1685 -- Error : Table Name = TABLE_NAME
1686 OKL_API.set_message( p_app_name => 'OKL',
1687 p_msg_name => 'OKL_AM_RMK_TABLE_NAME',
1688 p_token1 => 'TABLE_NAME',
1689 p_token1_value => x_Error_tbl(i).TABLE_NAME
1690 );
1691 -- Error : Column Name = COLUMN_NAME
1692 OKL_API.set_message( p_app_name => 'OKL',
1693 p_msg_name => 'OKL_AM_RMK_COLUMN_NAME',
1694 p_token1 => 'COLUMN_NAME',
1695 p_token1_value => x_Error_tbl(i).COLUMN_NAME
1696 );
1697 -- Error : Message Name = MSG_NAME
1698 OKL_API.set_message( p_app_name => 'OKL',
1699 p_msg_name => 'OKL_AM_RMK_MSG_NAME',
1700 p_token1 => 'MSG_NAME',
1701 p_token1_value => x_Error_tbl(i).MESSAGE_NAME
1702 );
1703 -- Error : Message Text = MSG_TEXT
1704 OKL_API.set_message( p_app_name => 'OKL',
1705 p_msg_name => 'OKL_AM_RMK_MSG_TEXT',
1706 p_token1 => 'MSG_TEXT',
1707 p_token1_value => x_Error_tbl(i).MESSAGE_TEXT
1708 );
1709
1710 END LOOP;
1711 END IF;
1712 EXCEPTION
1713 WHEN OTHERS THEN
1714 OKL_API.set_message(p_app_name => 'OKC',
1715 p_msg_name => g_unexpected_error,
1716 p_token1 => g_sqlcode_token,
1717 p_token1_value => sqlcode,
1718 p_token2 => g_sqlerrm_token,
1719 p_token2_value => sqlerrm);
1720 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1721 END remove_inv_item;
1722
1723
1724 -- Start of comments
1725 --
1726 -- Procedure Name : remove_item_from_price_list
1727 -- Description : This procedure is called from the main procedure - remove_rmk_item.
1728 -- remove_item_from_price_list inactivates the price list of an item by setting the end_date_active
1729 -- to sysdate
1730 -- Business Rules :
1731 -- Parameters : Input parameters : p_Item_Id - Item Id of the item
1732 -- p_sysdate - system date
1733 --
1734 --
1735 -- Version : 1.0
1736 -- End of comments
1737
1738 PROCEDURE remove_item_from_price_list(p_item_id IN NUMBER,
1739 p_sysdate IN DATE,
1740 x_return_status OUT NOCOPY VARCHAR2)
1741
1742
1743 IS
1744 l_msg_count NUMBER:= 0;
1745 l_msg_data VARCHAR2(2000);
1746 l_return_status VARCHAR2(1) := NULL;
1747 gpr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1748 gpr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1749 gpr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1750 ppr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1751 ppr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
1752 ppr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1753 ppr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
1754 ppr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
1755 ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
1756 ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1757 ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
1758 k NUMBER;
1759 l_list_header_id NUMBER;
1760 l_list_line_id NUMBER;
1761
1762 -- This cursor is used to get the price lists for an inventory item
1763 CURSOR l_prodattrval_csr IS
1764 SELECT list_header_id, list_line_id
1765 FROM qp_pricing_attributes
1766 WHERE product_attr_value = to_char(p_item_id);
1767
1768
1769
1770 BEGIN
1771
1772 -- disable all the price lists for the inventory item
1773 FOR l_prodattrval_rec IN l_prodattrval_csr LOOP
1774
1775 gpr_price_list_rec.list_header_id := l_prodattrval_rec.list_header_id;
1776 gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1777 gpr_price_list_rec.end_date_active := p_sysdate;
1778
1779
1780 gpr_price_list_line_tbl(1).end_date_active := p_sysdate;
1781 gpr_price_list_line_tbl(1).list_line_type_code := 'PLL';
1782 gpr_price_list_line_tbl(1).list_line_id := l_prodattrval_rec.list_line_id;
1783
1784 gpr_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
1785
1786
1787 QP_PRICE_LIST_PUB.Process_Price_List
1788 ( p_api_version_number => 1
1789 , p_init_msg_list => okl_api.G_FALSE
1790 , p_return_values => okl_api.G_FALSE
1791 , p_commit => okl_api.G_FALSE
1792 , x_return_status => x_return_status
1793 , x_msg_count => l_msg_count
1794 , x_msg_data => l_msg_data
1795 , p_PRICE_LIST_rec => gpr_price_list_rec
1796 , p_PRICE_LIST_LINE_tbl => gpr_price_list_line_tbl
1797 , p_PRICING_ATTR_tbl => gpr_pricing_attr_tbl
1798 , x_PRICE_LIST_rec => ppr_price_list_rec
1799 , x_PRICE_LIST_val_rec => ppr_price_list_val_rec
1800 , x_PRICE_LIST_LINE_tbl => ppr_price_list_line_tbl
1801 , x_PRICE_LIST_LINE_val_tbl => ppr_price_list_line_val_tbl
1802 , x_QUALIFIERS_tbl => ppr_qualifiers_tbl
1803 , x_QUALIFIERS_val_tbl => ppr_qualifiers_val_tbl
1804 , x_PRICING_ATTR_tbl => ppr_pricing_attr_tbl
1805 , x_PRICING_ATTR_val_tbl => ppr_pricing_attr_val_tbl
1806 );
1807 END LOOP;
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 OKL_API.set_message(p_app_name => 'OKC',
1811 p_msg_name => g_unexpected_error,
1812 p_token1 => g_sqlcode_token,
1813 p_token1_value => sqlcode,
1814 p_token2 => g_sqlerrm_token,
1815 p_token2_value => sqlerrm);
1816 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1817 END remove_item_from_price_list;
1818 */
1819
1820
1821
1822 -- Start of comments
1823 --
1824 -- Procedure Name : remove_item
1825 -- Description : This procedure is used to reduce the quantity of inventory items after an Order has been
1826 -- booked, by creating an issue transaction . This procedure is called from remove_rmk_item
1827 --
1828 -- Business Rules :
1829 -- Parameters : Input parameters :
1830 -- p_inventory_item_id - item id
1831 -- p_Subinv_Code - Subinventory Code
1832 -- p_org_id - organization that the item belongs to
1833 -- p_dist_account_id - Distribution Account
1834 -- p_quantity - Ordered Quantity
1835 -- p_sysdate - system date
1836 -- Innentory Item Id and Organization Id form the PK for mtl_system_items
1837 -- Version : 1.0
1838 -- History : SECHAWLA 05-DEC-02 Bug# 2620853
1839 -- Commented out the codethat references disribution account id, as it is optional
1840 -- SECHAWLA 16-JAN-03 Bug # 2754280
1841 -- Changed the app name from OKL to OKC for g_unexpected_error
1842 -- End of comments
1843
1844 PROCEDURE remove_item
1845 (
1846 p_inventory_item_id IN NUMBER,
1847 p_Subinv_Code IN VARCHAR2,
1848 p_org_id IN NUMBER,
1849 -- SECHAWLA Bug# 2620853 : dist_account_id is not required
1850 -- p_dist_account_id IN NUMBER,
1851 p_quantity IN NUMBER,
1852 p_sysdate IN DATE,
1853 x_return_status OUT NOCOPY VARCHAR2
1854
1855 )
1856 IS
1857
1858 /*
1859 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1860
1861
1862 -- This cursor is used to make sure that the item exists in active state, before removing the item
1863 CURSOR l_mtlsysitems_csr(p_inventory_item_id NUMBER,p_organization_id NUMBER) IS
1864 SELECT 'x'
1865 FROM mtl_system_items_b
1866 WHERE inventory_item_id = p_inventory_item_id
1867 AND organization_id = p_organization_id
1868 AND end_date_active IS NULL;
1869 */
1870
1871
1872 BEGIN
1873
1874
1875 -- Creating Inventory Issue Transaction for the Item
1876
1877 Create_Inv_Misc_Receipt_Txn
1878 (p_inventory_item_id => p_inventory_item_id,
1879 p_subinv_code => p_subinv_code,
1880 p_organization_id => p_org_id,
1881 --SECHAWLA Bug# 2620853 : dist_account_id is not required
1882 -- p_dist_account_id => p_dist_account_id,
1883 p_quantity => p_quantity,
1884 p_trans_type_id => 32, --- trnasction type ID for Issue Transactions
1885 p_sysdate => p_sysdate,
1886 x_return_status => x_return_status);
1887
1888 -- return status of the above procedure call becomes the return status of the current procedure
1889 -- which is then handled in the calling procedure - remove_rmk_item
1890
1891
1892 /*
1893 -- This code is commented in anticipation of the possibility of including it again at some point in future.
1894 -- If this code needs to be uncommented, we must get the org id as a direct input parameter. We can not
1895 -- use the Org from the profile to delete (disable) an inventory item, as the pofile may change between the time of
1896 -- creation and deletion of inventory item. An item may belong to more than one org So Org Id is required to
1897 -- disable an inventory item.
1898
1899 IF p_item_id IS NULL OR p_item_id = OKL_API.G_MISS_NUM THEN
1900 x_return_status := OKL_API.G_RET_STS_ERROR;
1901 -- Item Id is required
1902 OKC_API.set_message( p_app_name => 'OKC',
1903 p_msg_name => G_REQUIRED_VALUE,
1904 p_token1 => G_COL_NAME_TOKEN,
1905 p_token1_value => 'ITEM_ID');
1906 RAISE okc_api.G_EXCEPTION_ERROR;
1907 END IF;
1908
1909 IF p_org_id IS NULL OR p_org_id = OKL_API.G_MISS_NUM THEN
1910 x_return_status := OKL_API.G_RET_STS_ERROR;
1911 -- Organization Id is required
1912 OKC_API.set_message( p_app_name => 'OKC',
1913 p_msg_name => G_REQUIRED_VALUE,
1914 p_token1 => G_COL_NAME_TOKEN,
1915 p_token1_value => 'ORGANIZATION');
1916 RAISE okc_api.G_EXCEPTION_ERROR;
1917 END IF;
1918
1919 OPEN l_mtlsysitems_csr(p_item_id,p_org_id);
1920 FETCH l_mtlsysitems_csr INTO l_temp;
1921 IF l_mtlsysitems_csr%NOTFOUND THEN
1922 x_return_status := OKL_API.G_RET_STS_ERROR;
1923 -- No active Item exists for this combination of Item and Organization
1924 OKL_API.set_message( p_app_name => 'OKL',
1925 p_msg_name => 'OKL_AM_RMK_INVALID_ITEM_ORG'
1926
1927 );
1928 RAISE OKL_API.G_EXCEPTION_ERROR;
1929 END IF;
1930 CLOSE l_mtlsysitems_csr;
1931
1932 -- Disable the Inventory Item
1933 remove_inv_item(p_item_id => p_item_id,
1934 p_org_id => p_org_id,
1935 p_sysdate => l_sysdate,
1936 x_return_status => x_return_status);
1937
1938 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1940 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1941 RAISE OKL_API.G_EXCEPTION_ERROR;
1942 END IF;
1943
1944
1945 -- Disable the Price Lists corresponding to the Inventory Item
1946 remove_item_from_price_list(p_item_id => p_item_id,
1947 p_sysdate => l_sysdate,
1948 x_return_status => x_return_status);
1949
1950 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1951 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1952 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1953 RAISE OKL_API.G_EXCEPTION_ERROR;
1954 END IF;
1955 */
1956
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959 -- unexpected error
1960
1961 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1962 OKL_API.set_message(p_app_name => 'OKC',
1963 p_msg_name => g_unexpected_error,
1964 p_token1 => g_sqlcode_token,
1965 p_token1_value => sqlcode,
1966 p_token2 => g_sqlerrm_token,
1967 p_token2_value => sqlerrm);
1968 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1969
1970
1971 END remove_item;
1972
1973
1974
1975 -- Start of comments
1976 --
1977 -- Procedure Name : remove_rmk_item
1978 -- Description : This procedure is used to reduce the quantity of of all the inventory items belonging to an
1979 -- order, after the order has been booked
1980 -- Business Rules :
1981 -- Parameters : Input parameters : p_order_header_id - header ID for the Order
1982 -- Version : 1.0
1983 -- History : SECHAWLA 05-DEC-02 : Bug # 2620853
1984 -- Commented out the code that uses distribution account id
1985 -- Removed DEFAULT hint from procedure parameters
1986 -- SECHAWLA 17-DEC-02 : Bug # 2706328
1987 -- Fixed the datatype of l_temp_org_name
1988 -- SECHAWLA 21-OCT-04 3924244
1989 -- changed p_order_header_id to p_order_line_Id and modified the code to work on order line id
1990 -- instead of order header id
1991 --
1992 -- End of comments
1993
1994 PROCEDURE remove_rmk_item
1995 ( p_api_version IN NUMBER,
1996 p_init_msg_list IN VARCHAR2 ,
1997 p_order_line_Id IN NUMBER, -- SECHAWLA 21-OCT-04 3924244
1998 x_return_status OUT NOCOPY VARCHAR2,
1999 x_msg_count OUT NOCOPY NUMBER,
2000 x_msg_data OUT NOCOPY VARCHAR2
2001
2002 ) IS
2003
2004 -- This cursor is used to validate Header ID
2005 CURSOR l_orderheaders_csr(cp_header_id NUMBER) IS
2006 SELECT order_number
2007 FROM oe_order_headers_all
2008 WHERE header_id = cp_header_id;
2009
2010 /* -- SECHAWLA 21-OCT-04 3924244
2011 -- This cursor is used to get the information about all the line items corresponding to an Order
2012 CURSOR l_orderlines_csr(p_header_id NUMBER) IS
2013 SELECT line_id, inventory_item_id, ordered_quantity, ship_from_org_id
2014 FROM oe_order_lines_all
2015 WHERE header_id = p_header_id;
2016 */
2017
2018 -- SECHAWLA 21-OCT-04 3924244 : added this cursor
2019 -- This cursor is used to get the information about an order line
2020 CURSOR l_orderlines_csr(cp_line_id NUMBER) IS
2021 SELECT header_id, inventory_item_id, ordered_quantity, ship_from_org_id
2022 FROM oe_order_lines_all
2023 WHERE line_id = cp_line_id;
2024
2025
2026 -- This cursor is used to get the source subinventory and distribution account for an inventory item
2027 CURSOR l_mtlsystemitems_csr(p_item_id NUMBER, p_org_id NUMBER) IS
2028 -- SECHAWLA Bug# 2620853 : ENCUMBRANCE_ACCOUNT (which stores the distribution accout id) is not required
2029 --SELECT SOURCE_SUBINVENTORY, ENCUMBRANCE_ACCOUNT
2030 SELECT SOURCE_SUBINVENTORY
2031 FROM mtl_system_items
2032 WHERE inventory_item_id = p_item_id
2033 AND organization_id = p_org_id;
2034
2035 l_order_number NUMBER;
2036 l_return_status VARCHAR2(1);
2037 l_sysdate DATE;
2038 l_api_name CONSTANT VARCHAR2(30) := 'remove_rmk_item';
2039 l_api_version CONSTANT NUMBER := 1;
2040
2041 l_inv_org_id NUMBER;
2042 l_subinv_code VARCHAR2(10);
2043 -- SECHAWLA Bug # 2620853 : Distribution account id is not required
2044 -- l_distribution_account_id NUMBER;
2045 --SECHAWLA 2706328 : Fixed the datatype for l_temp_org_name
2046 l_temp_org_name mtl_organizations.organization_name%TYPE;
2047
2048 l_header_id NUMBER;
2049 l_inventory_item_id NUMBER;
2050 l_ordered_quantity NUMBER;
2051 l_ship_from_org_id NUMBER;
2052
2053 BEGIN
2054
2055 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2056 G_PKG_NAME,
2057 p_init_msg_list,
2058 l_api_version,
2059 p_api_version,
2060 '_PVT',
2061 x_return_status);
2062
2063
2064 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2065 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2067 RAISE OKL_API.G_EXCEPTION_ERROR;
2068 END IF;
2069
2070 SELECT SYSDATE INTO l_sysdate FROM dual;
2071
2072 -- SECHAWLA 21-OCT-04 3924244 : changed header id to line id
2073 IF p_order_line_Id IS NULL OR p_order_line_Id = OKL_API.G_MISS_NUM THEN
2074 x_return_status := OKL_API.G_RET_STS_ERROR;
2075 -- Order Line ID is required
2076 OKL_API.set_message( p_app_name => 'OKC',
2077 p_msg_name => G_REQUIRED_VALUE,
2078 p_token1 => G_COL_NAME_TOKEN,
2079 p_token1_value => 'ORDER_LINE_ID');
2080 RAISE okl_api.G_EXCEPTION_ERROR;
2081 END IF;
2082
2083 /*-- SECHAWLA 21-OCT-04 3924244
2084 OPEN l_orderheaders_csr(p_order_header_Id);
2085 FETCH l_orderheaders_csr INTO l_order_number;
2086 IF l_orderheaders_csr%NOTFOUND THEN
2087 x_return_status := OKL_API.G_RET_STS_ERROR;
2088 -- Order Header ID is invalid
2089 OKL_API.set_message( p_app_name => 'OKC',
2090 p_msg_name => G_INVALID_VALUE,
2091 p_token1 => G_COL_NAME_TOKEN,
2092 p_token1_value => 'ORDER_HEADER_ID');
2093 RAISE okl_api.G_EXCEPTION_ERROR;
2094 END IF;
2095 CLOSE l_orderheaders_csr;
2096 */
2097
2098 -- SECHAWLA 21-OCT-04 3924244 : added
2099 OPEN l_orderlines_csr(p_order_line_Id);
2100 FETCH l_orderlines_csr INTO l_header_id, l_inventory_item_id, l_ordered_quantity, l_ship_from_org_id;
2101 IF l_orderlines_csr%NOTFOUND THEN
2102 x_return_status := OKL_API.G_RET_STS_ERROR;
2103 -- Order Line ID is invalid
2104 OKL_API.set_message( p_app_name => 'OKC',
2105 p_msg_name => G_INVALID_VALUE,
2106 p_token1 => G_COL_NAME_TOKEN,
2107 p_token1_value => 'ORDER_LINE_ID');
2108 RAISE okl_api.G_EXCEPTION_ERROR;
2109 END IF;
2110 CLOSE l_orderlines_csr;
2111
2112 OPEN l_orderheaders_csr(l_header_id);
2113 FETCH l_orderheaders_csr INTO l_order_number;
2114 CLOSE l_orderheaders_csr;
2115
2116 -- loop thru all the line items for a given order, validate the data and then reduce the quantity of each line item
2117
2118 -- SECHAWLA 21-OCT-04 3924244 : commented out the loop
2119 --FOR l_orderlines_rec IN l_orderlines_csr(p_order_header_id) LOOP
2120
2121 IF l_ship_from_org_id IS NULL THEN
2122 x_return_status := OKL_API.G_RET_STS_ERROR;
2123 -- Ship From Org ID is required
2124 OKL_API.set_message( p_app_name => 'OKC',
2125 p_msg_name => G_REQUIRED_VALUE,
2126 p_token1 => G_COL_NAME_TOKEN,
2127 p_token1_value => 'SHIP_FROM_ORG_ID');
2128 RAISE okl_api.G_EXCEPTION_ERROR;
2129 END IF;
2130
2131
2132 OPEN l_mtlsystemitems_csr(l_inventory_item_id, l_ship_from_org_id);
2133 -- SECHAWLA Bug# 2620853 : Distribution accout id is not required
2134 --FETCH l_mtlsystemitems_csr INTO l_subinv_code, l_distribution_account_id;
2135 FETCH l_mtlsystemitems_csr INTO l_subinv_code;
2136 IF l_mtlsystemitems_csr%NOTFOUND THEN
2137 -- shipping org for the order does not match the Item's organization
2138 x_return_status := OKL_API.G_RET_STS_ERROR;
2139 --Order ORDER_NUMBER has invalid combination of inventory item and organization
2140 OKL_API.set_message(p_app_name => 'OKL',
2141 p_msg_name => 'OKL_AM_INVALID_ITEM_ORG',
2142 p_token1 => 'ORDER_NUMBER',
2143 p_token1_value => l_order_number);
2144 RAISE OKL_API.G_EXCEPTION_ERROR;
2145 END IF;
2146
2147 IF l_subinv_code IS NULL THEN
2148 x_return_status := OKL_API.G_RET_STS_ERROR;
2149 -- source subinventory is required
2150 OKL_API.set_message( p_app_name => 'OKC',
2151 p_msg_name => G_REQUIRED_VALUE,
2152 p_token1 => G_COL_NAME_TOKEN,
2153 p_token1_value => 'SOURCE_SUBINVENTORY');
2154 RAISE okl_api.G_EXCEPTION_ERROR;
2155 END IF;
2156 CLOSE l_mtlsystemitems_csr;
2157
2158 -- Distribution Account ID can be NULL
2159
2160 --SECHAWLA Bug# 2620853 : Distribution Account ID is not required
2161 remove_item( p_inventory_item_id => l_inventory_item_id,
2162 p_subinv_code => l_subinv_code,
2163 p_org_id => l_ship_from_org_id,
2164 p_quantity => -(l_ordered_quantity),
2165 p_sysdate => l_sysdate,
2166 x_return_status => x_return_status);
2167
2168 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2169 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2170 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2171 RAISE OKL_API.G_EXCEPTION_ERROR;
2172 END IF;
2173
2174 -- END LOOP; -- SECHAWLA 21-OCT-04 3924244
2175
2176 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2177 EXCEPTION
2178 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2179
2180 IF l_orderheaders_csr%ISOPEN THEN
2181 CLOSE l_orderheaders_csr;
2182 END IF;
2183 IF l_orderlines_csr%ISOPEN THEN
2184 CLOSE l_orderlines_csr;
2185 END IF;
2186 IF l_mtlsystemitems_csr%ISOPEN THEN
2187 CLOSE l_mtlsystemitems_csr;
2188 END IF;
2189 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2190 (
2191 l_api_name,
2192 G_PKG_NAME,
2193 'OKL_API.G_RET_STS_ERROR',
2194 x_msg_count,
2195 x_msg_data,
2196 '_PVT'
2197 );
2198 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2199 IF l_orderheaders_csr%ISOPEN THEN
2200 CLOSE l_orderheaders_csr;
2201 END IF;
2202 IF l_orderlines_csr%ISOPEN THEN
2203 CLOSE l_orderlines_csr;
2204 END IF;
2205 IF l_mtlsystemitems_csr%ISOPEN THEN
2206 CLOSE l_mtlsystemitems_csr;
2207 END IF;
2208 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2209 (
2210 l_api_name,
2211 G_PKG_NAME,
2212 'OKL_API.G_RET_STS_UNEXP_ERROR',
2213 x_msg_count,
2214 x_msg_data,
2215 '_PVT'
2216 );
2217 WHEN OTHERS THEN
2218 IF l_orderheaders_csr%ISOPEN THEN
2219 CLOSE l_orderheaders_csr;
2220 END IF;
2221 IF l_orderlines_csr%ISOPEN THEN
2222 CLOSE l_orderlines_csr;
2223 END IF;
2224 IF l_mtlsystemitems_csr%ISOPEN THEN
2225 CLOSE l_mtlsystemitems_csr;
2226 END IF;
2227 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2228 (
2229 l_api_name,
2230 G_PKG_NAME,
2231 'OTHERS',
2232 x_msg_count,
2233 x_msg_data,
2234 '_PVT'
2235 );
2236
2237
2238 END remove_rmk_item;
2239
2240
2241
2242
2243 END OKL_AM_REMARKET_ASSET_PVT;
[Home] [Help]