[Home] [Help]
PACKAGE BODY: APPS.AMS_ITEM_OWNER_PVT
Source
1 PACKAGE BODY AMS_ITEM_OWNER_PVT as
2 /* $Header: amsvinvb.pls 120.3 2006/05/03 05:40:42 inanaiah noship $ */
3 -- Start of Comments
4 -- Package name : AMS_ITEM_OWNER_PVT
5 -- Purpose :
6
7 -- History :
8 -- 10/06/2000 abhola created.
9 -- 12/05/2000 musman Added codes for wrapping the Inv_item_grp pkg
10 -- so that the create_item will work.
11 -- 01/08/2001 musman Bug 1572663 fix. Raised an exception if the item status
12 -- is cancelled.
13 -- 01/09/2001 musman Added a call to the cue card for organization assignment
14 --
15 -- 01/29/2001 abhola Added code to make Item inactive ( in Inventory ) if the status is being
16 -- changed to Cancel.
17 -- 11/06/2001 musman Added a function to determine whether an item attributes are controlled
18 -- by status code.
19 -- 12/27/2001 musman In the create api before passing the item type checking for null value
20 -- 01/21/2002 musman Added three more attributes unit_weight,weight_uom_code and event_flag
21 -- 03/22/2002 musman Added one more flag comms_nl_trackable_flag for install base
22 -- 12/18/2002 musman Added one more flag so_transactions_flag for i-store
23 -- SEP-14-2004 mkothari Commented PRIMARY_UNIT_OF_MEASURE for fixing bug 3882054
24 -- 1/25/2006 inanaiah Bug 4956191 fix - sql id 14421999, 14421965
25 -- 05/03/2006 inanaiah Bug 5191150 fix - cleb.lse_id is a NUMBER so decode l_item_type
26
27
28
29 -- NOTE :
30 -- End of Comments
31
32
33 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_ITEM_OWNER_PVT';
34 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvinvb.pls';
35
36 ------------------------------------------------------------------
37 -- Function
38 -- is_it_status_control_attr
39 -- Purpose
40 -- This function will return the whether the item_sttribute
41 -- passed in is as controlled by status code.
42 -- History
43 -- 07/31/2001 musman@us created
44 --------------------------------------------------------------------------
45 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
46 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
47 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
48
49 FUNCTION is_it_status_control_attr
50 ( p_attribute_name IN VARCHAR2
51 ) RETURN VARCHAR2;
52
53 ------------------------------------------------------------------
54 -- Procedure
55 -- add_default_attributes
56 -- Purpose
57 -- This procedure is used to add the default attributes from the template
58 -- depending whether its a product or service
59 -- History
60 -- 05/29/2002 musman@us created
61 --------------------------------------------------------------------------
62 PROCEDURE add_default_attributes
63 ( P_ITEM_REC_In IN Item_rec_type
64 ,l_inv_item_rec_in IN OUT NOCOPY INV_Item_GRP.Item_Rec_Type
65 ,x_return_status OUT NOCOPY VARCHAR2
66 );
67
68 ------------------------------------------------------------------
69 -- Procedure
70 -- init_inv_item_rec
71 -- Purpose
72 -- This procedure is used to set the value to the add the default attributes from the template
73 -- depending whether its a product or service
74 -- History
75 -- 05/29/2002 musman@us created
76 --------------------------------------------------------------------------
77 PROCEDURE init_inv_item_rec(
78 p_item_rec_in IN Item_Rec_Type
79 ,l_inv_item_rec_in IN OUT NOCOPY INV_Item_GRP.Item_Rec_Type);
80
81 -------------------------------------------------------------------------
82
83
84 -- Hint: Primary key needs to be returned.
85 PROCEDURE Create_item_owner(
86 P_Api_Version_Number IN NUMBER,
87 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
88 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90
91 X_Return_Status OUT NOCOPY VARCHAR2,
92 X_Msg_Count OUT NOCOPY NUMBER,
93 X_Msg_Data OUT NOCOPY VARCHAR2,
94
95 P_ITEM_OWNER_Rec IN ITEM_OWNER_Rec_Type := G_MISS_ITEM_OWNER_REC,
96 X_ITEM_OWNER_ID OUT NOCOPY NUMBER,
97
98 P_ITEM_REC_In IN ITEM_Rec_Type := G_MISS_ITEM_REC, /*INV_Item_GRP.Item_rec_type := INV_Item_GRP.g_miss_Item_rec,*/
99 P_ITEM_REC_Out OUT NOCOPY ITEM_Rec_Type, /*INV_Item_GRP.Item_rec_type,*/
100 x_item_return_status OUT NOCOPY VARCHAR2,
101 x_error_tbl OUT NOCOPY Error_tbl_type /*INV_Item_GRP.Error_tbl_type*/
102
103 )
104
105 IS
106
107 CURSOR get_object_id(l_inv_id NUMBER)
108 IS
109 SELECT item_owner_id
110 FROM ams_item_attributes
111 WHERE inventory_item_id = l_inv_id
112 AND is_master_item='Y';
113
114
115 l_api_name CONSTANT VARCHAR2(30) := 'Create_item_owner';
116 l_api_version_number CONSTANT NUMBER := 1.0;
117 l_return_status_full VARCHAR2(1);
118 l_object_version_number NUMBER := 1;
119 l_org_id NUMBER := FND_API.G_MISS_NUM;
120 l_ITEM_OWNER_ID NUMBER;
121 l_item_return_status VARCHAR2(1) := fnd_api.g_MISS_CHAR;
122 l_inv_item_rec_in INV_Item_GRP.Item_Rec_Type ;
123 l_inv_item_rec_out INV_Item_GRP.Item_Rec_Type ;
124 l_error_tbl INV_Item_GRP.Error_tbl_Type ;
125 l_object_id NUMBER;
126 l_return_status VARCHAR(1);
127
128
129 BEGIN
130 -- Standard Start of API savepoint
131 SAVEPOINT CREATE_ITEM_OWNER_PVT;
132
133 -- Standard call to check for call compatibility.
134 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
135 p_api_version_number,
136 l_api_name,
137 G_PKG_NAME)
138 THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141
142 -- Initialize message list if p_init_msg_list is set to TRUE.
143 IF FND_API.to_Boolean( p_init_msg_list )
144 THEN
145 FND_MSG_PUB.initialize;
146 END IF;
147
148 -- Debug Message
149 IF (AMS_DEBUG_HIGH_ON) THEN
150
151 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
152 END IF;
153
154 -- Initialize API return status to SUCCESS
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156
157
158 -- Initialize inventory API return status to SUCCESS
159 x_item_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 -- ******************************************************************
162 -- Validate Environment
163 -- ******************************************************************
164 IF FND_GLOBAL.User_Id IS NULL
165 THEN
166 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
167 THEN
168 FND_MESSAGE.Set_Name('AMS', 'USER_PROFILE_MISSING');
169 FND_MSG_PUB.ADD;
170 END IF;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
175 THEN
176 -- Debug message
177 IF (AMS_DEBUG_HIGH_ON) THEN
178
179 AMS_UTILITY_PVT.debug_message('Private API: Validate_item_owner');
180 END IF;
181
182 -- Invoke validation procedures
183 Validate_item_owner(
184 p_api_version_number => 1.0,
185 p_init_msg_list => FND_API.G_FALSE,
186 p_validation_level => p_validation_level,
187 P_ITEM_OWNER_Rec => P_ITEM_OWNER_Rec,
188 P_ITEM_REC_In => p_item_rec_in,
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data);
192 END IF;
193
194 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198
199
200 /********** Call Item Creation API **************/
201 ----wrapper for INV_Item_GRP.Item_rec_type
202 -- these cols commented by ABHOLA, added new one below.
203 IF (P_ITEM_OWNER_Rec.is_master_item = 'Y')
204 THEN
205 l_inv_item_rec_in.ITEM_NUMBER := p_item_rec_in.ITEM_NUMBER ;
206 l_inv_item_rec_in.DESCRIPTION := p_item_rec_in.DESCRIPTION ;
207
208 IF p_item_rec_in.ITEM_TYPE IS NOT NULL
209 THEN
210 l_inv_item_rec_in.ITEM_TYPE := p_item_rec_in.ITEM_TYPE;
211 END IF;
212
213 IF p_item_rec_in.LONG_DESCRIPTION IS NOT NULL
214 THEN
215 l_inv_item_rec_in.LONG_DESCRIPTION := p_item_rec_in.LONG_DESCRIPTION ;
216 END IF;
217
218 /* Depending upon the user's responsibility ,implementing the template defined or
219 using the default template. */
220
221 IF (AMS_DEBUG_HIGH_ON) THEN
222 AMS_UTILITY_PVT.debug_message('calling add_default_attributes');
223 END IF;
224
225 add_default_attributes( P_ITEM_REC_In
226 , l_inv_item_rec_in
227 , x_return_status);
228
229 ELSE
230 l_inv_item_rec_in.INVENTORY_ITEM_ID := p_item_rec_in.INVENTORY_ITEM_ID ;
231 END IF;
232
233 l_inv_item_rec_in.ORGANIZATION_ID := p_item_rec_in.ORGANIZATION_ID ;
234 l_inv_item_rec_in.PRIMARY_UOM_CODE := p_item_rec_in.PRIMARY_UOM_CODE ;
235 --l_inv_item_rec_in.PRIMARY_UNIT_OF_MEASURE := p_item_rec_in.PRIMARY_UNIT_OF_MEASURE ;
236 --Bug 3882054 - Do not pass both code and p_u_o_m. Code is sufficient.
237
238
239 /* Certain flags have to be defaulted */
240 IF (l_inv_item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y')
241 THEN
242 l_inv_item_rec_in.INVENTORY_ITEM_FLAG := 'Y';
243 l_inv_item_rec_in.STOCK_ENABLED_FLAG := 'Y';
244 END IF;
245
246 IF (l_inv_item_rec_in.STOCK_ENABLED_FLAG = 'Y')
247 THEN
248 l_inv_item_rec_in.INVENTORY_ITEM_FLAG := 'Y';
249
250 END IF;
251
252
253 /** Flags added for DELV and EVEH requirements ************/
254 /** By ABHOLA *********************************************/
255
256 -- intiating the attributes only if its not null and not fnd_api.g_miss_char
257 init_inv_item_rec(p_item_rec_in,l_inv_item_rec_in);
258
259 /*
260 IF (p_item_rec_in.costing_enabled_flag IS NOT NULL)
261 OR (p_item_rec_in.costing_enabled_flag <> FND_API.G_MISS_CHAR )
262 THEN
263 l_inv_item_rec_in.costing_enabled_flag := p_item_rec_in.costing_enabled_flag;
264 END IF;
265
266 IF (p_item_rec_in.collateral_flag IS NOT NULL)
267 OR (p_item_rec_in.collateral_flag <> FND_API.G_MISS_CHAR )
268 THEN
269 l_inv_item_rec_in.collateral_flag := p_item_rec_in.collateral_flag ;
270 END IF;
271
272 IF (p_item_rec_in.customer_order_flag IS NOT NULL)
273 OR (p_item_rec_in.customer_order_flag <> FND_API.G_MISS_CHAR )
274 THEN
275 l_inv_item_rec_in.customer_order_flag := p_item_rec_in.customer_order_flag ;
276 END IF;
277
278 IF (p_item_rec_in.customer_order_enabled_flag IS NOT NULL)
279 OR (p_item_rec_in.customer_order_enabled_flag <> FND_API.G_MISS_CHAR )
280 THEN
281 l_inv_item_rec_in.customer_order_enabled_flag := p_item_rec_in.customer_order_enabled_flag ;
282 END IF;
283
284 IF (p_item_rec_in.shippable_item_flag IS NOT NULL)
285 OR (p_item_rec_in.shippable_item_flag <> FND_API.G_MISS_CHAR )
286 THEN
287 l_inv_item_rec_in.shippable_item_flag := p_item_rec_in.shippable_item_flag ;
288 END IF;
289
290 IF (p_item_rec_in.event_flag IS NOT NULL)
291 OR (p_item_rec_in.event_flag <> FND_API.G_MISS_CHAR )
292 THEN
293 l_inv_item_rec_in.event_flag := p_item_rec_in.event_flag ;
294 END IF;
295 */
296 IF (AMS_DEBUG_HIGH_ON) THEN
297
298 AMS_UTILITY_PVT.debug_message('inventory_item_flag:'||l_inv_item_rec_in.INVENTORY_ITEM_FLAG);
299 AMS_UTILITY_PVT.debug_message('description:'||l_inv_item_rec_in.DESCRIPTION);
300 AMS_UTILITY_PVT.debug_message('stock_enabled_flag:'||l_inv_item_rec_in.STOCK_ENABLED_FLAG);
301 AMS_UTILITY_PVT.debug_message('mtl_transactions_enabled_flag:'||l_inv_item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG);
302 AMS_UTILITY_PVT.debug_message('revision_qty_control_code :'||l_inv_item_rec_in.REVISION_QTY_CONTROL_CODE );
303 AMS_UTILITY_PVT.debug_message('bom_enabled_flag :'||l_inv_item_rec_in.BOM_ENABLED_FLAG );
304 AMS_UTILITY_PVT.debug_message('costing_enabled_flag :'||l_inv_item_rec_in.COSTING_ENABLED_FLAG );
305 AMS_UTILITY_PVT.debug_message('electronic_flag :'||l_inv_item_rec_in.ELECTRONIC_FLAG );
306 AMS_UTILITY_PVT.debug_message('downloadable_flag :'||l_inv_item_rec_in.DOWNLOADABLE_FLAG );
307 AMS_UTILITY_PVT.debug_message('customer_order_flag :'||l_inv_item_rec_in.CUSTOMER_ORDER_FLAG );
308 AMS_UTILITY_PVT.debug_message('customer_order_enabled_flag :'||l_inv_item_rec_in.CUSTOMER_ORDER_ENABLED_FLAG );
309 AMS_UTILITY_PVT.debug_message('internal_order_flag :'||l_inv_item_rec_in.INTERNAL_ORDER_FLAG );
310 AMS_UTILITY_PVT.debug_message('internal_order_enabled_flag :'||l_inv_item_rec_in.INTERNAL_ORDER_ENABLED_FLAG );
311 AMS_UTILITY_PVT.debug_message('shippable_item_flag :'||l_inv_item_rec_in.SHIPPABLE_ITEM_FLAG );
312 AMS_UTILITY_PVT.debug_message('returnable_flag :'||l_inv_item_rec_in.RETURNABLE_FLAG );
313 AMS_UTILITY_PVT.debug_message('comms_activation_reqd_flag :'||l_inv_item_rec_in.COMMS_ACTIVATION_REQD_FLAG );
314 AMS_UTILITY_PVT.debug_message('service_item_flag :'||l_inv_item_rec_in.SERVICE_ITEM_FLAG );
315 END IF;
316
317
318 /************ END OF CODE BY ABHOLA ***********************/
319
320 IF (AMS_DEBUG_HIGH_ON) THEN
321 AMS_UTILITY_PVT.debug_message('Before Calling inv api');
322 END IF;
323
324 INV_Item_GRP.Create_Item
325 ( p_commit => P_Commit
326 , p_validation_level => p_validation_level
327 , p_Item_rec => l_inv_item_rec_in /*P_ITEM_REC_In*/
328 , x_Item_rec => l_inv_item_rec_out /*P_ITEM_REC_Out*/
329 , x_return_status => l_item_return_status
330 , x_Error_tbl => l_Error_tbl /*x_Error_tbl*/
331 );
332
333
334 x_item_return_status := l_item_return_status;
335 IF l_item_return_status <> FND_API.G_RET_STS_SUCCESS THEN
336 IF l_error_tbl.count >0 THEN
337 FOR l_cnt IN 1..l_error_tbl.count LOOP
338 x_error_tbl(l_cnt).transaction_id := l_error_tbl(l_cnt).transaction_id;
339 x_error_tbl(l_cnt).unique_id := l_error_tbl(l_cnt).unique_id;
340 x_error_tbl(l_cnt).message_name := l_error_tbl(l_cnt).message_name;
341 --dbms_output.put_line('The message name is '||x_error_tbl(l_cnt).message_name);
342 x_error_tbl(l_cnt).message_text := l_error_tbl(l_cnt).message_text;
343 --dbms_output.put_line('The message text is '||x_error_tbl(l_cnt).message_text);
344 x_error_tbl(l_cnt).table_name := l_error_tbl(l_cnt).table_name;
345 x_error_tbl(l_cnt).column_name := l_error_tbl(l_cnt).column_name;
346 --dbms_output.put_line('The coulmn name is '||x_error_tbl(l_cnt).column_name);
347 x_error_tbl(l_cnt).organization_id := l_error_tbl(l_cnt).organization_id;
348 END LOOP;
349 END IF;
350 RAISE FND_API.G_EXC_ERROR;
351 ELSE
352 p_item_rec_out.INVENTORY_ITEM_ID := l_inv_item_rec_out.INVENTORY_ITEM_ID ;
353 p_item_rec_out.ORGANIZATION_ID := l_inv_item_rec_out.ORGANIZATION_ID ;
354 p_item_rec_out.ITEM_NUMBER := l_inv_item_rec_out.ITEM_NUMBER ;
355 p_item_rec_out.DESCRIPTION := l_inv_item_rec_out.DESCRIPTION ;
356 p_item_rec_out.LONG_DESCRIPTION := l_inv_item_rec_out.LONG_DESCRIPTION ;
357 p_item_rec_out.ITEM_TYPE := l_inv_item_rec_out.ITEM_TYPE ;
358 p_item_rec_out.PRIMARY_UOM_CODE := l_inv_item_rec_out.PRIMARY_UOM_CODE ;
359 p_item_rec_out.PRIMARY_UNIT_OF_MEASURE := l_inv_item_rec_out.PRIMARY_UNIT_OF_MEASURE ;
360 p_item_rec_out.START_DATE_ACTIVE := l_inv_item_rec_out.START_DATE_ACTIVE ;
361 p_item_rec_out.END_DATE_ACTIVE := l_inv_item_rec_out.END_DATE_ACTIVE ;
362 p_item_rec_out.INVENTORY_ITEM_STATUS_CODE := l_inv_item_rec_out.INVENTORY_ITEM_STATUS_CODE ;
363 p_item_rec_out.INVENTORY_ITEM_FLAG := l_inv_item_rec_out.INVENTORY_ITEM_FLAG ;
364 p_item_rec_out.STOCK_ENABLED_FLAG := l_inv_item_rec_out.STOCK_ENABLED_FLAG ;
365 p_item_rec_out.MTL_TRANSACTIONS_ENABLED_FLAG := l_inv_item_rec_out.MTL_TRANSACTIONS_ENABLED_FLAG ;
366 p_item_rec_out.REVISION_QTY_CONTROL_CODE := l_inv_item_rec_out.REVISION_QTY_CONTROL_CODE ;
367 p_item_rec_out.BOM_ENABLED_FLAG := l_inv_item_rec_out.BOM_ENABLED_FLAG ;
368 p_item_rec_out.BOM_ITEM_TYPE := l_inv_item_rec_out.BOM_ITEM_TYPE ;
369 p_item_rec_out.COSTING_ENABLED_FLAG := l_inv_item_rec_out.COSTING_ENABLED_FLAG ;
370 p_item_rec_out.ELECTRONIC_FLAG := l_inv_item_rec_out.ELECTRONIC_FLAG ;
371 p_item_rec_out.DOWNLOADABLE_FLAG := l_inv_item_rec_out.DOWNLOADABLE_FLAG ;
372 p_item_rec_out.CUSTOMER_ORDER_FLAG := l_inv_item_rec_out.CUSTOMER_ORDER_FLAG ;
373 p_item_rec_out.CUSTOMER_ORDER_ENABLED_FLAG := l_inv_item_rec_out.CUSTOMER_ORDER_ENABLED_FLAG ;
374 p_item_rec_out.INTERNAL_ORDER_FLAG := l_inv_item_rec_out.INTERNAL_ORDER_FLAG ;
375 p_item_rec_out.INTERNAL_ORDER_ENABLED_FLAG := l_inv_item_rec_out.INTERNAL_ORDER_ENABLED_FLAG ;
376 p_item_rec_out.SHIPPABLE_ITEM_FLAG := l_inv_item_rec_out.SHIPPABLE_ITEM_FLAG ;
377 p_item_rec_out.RETURNABLE_FLAG := l_inv_item_rec_out.RETURNABLE_FLAG ;
378 p_item_rec_out.COMMS_ACTIVATION_REQD_FLAG := l_inv_item_rec_out.COMMS_ACTIVATION_REQD_FLAG ;
379 p_item_rec_out.REPLENISH_TO_ORDER_FLAG := l_inv_item_rec_out.REPLENISH_TO_ORDER_FLAG ;
380 p_item_rec_out.INVOICEABLE_ITEM_FLAG := l_inv_item_rec_out.INVOICEABLE_ITEM_FLAG ;
381 p_item_rec_out.INVOICE_ENABLED_FLAG := l_inv_item_rec_out.INVOICE_ENABLED_FLAG;
382 --p_item_rec_out.SERVICE_ITEM_FLAG := l_inv_item_rec_out.SERVICE_ITEM_FLAG ;
383 p_item_rec_out.SERVICEABLE_PRODUCT_FLAG := l_inv_item_rec_out.SERVICEABLE_PRODUCT_FLAG ;
384 --p_item_rec_out.VENDOR_WARRANTY_FLAG := l_inv_item_rec_out.VENDOR_WARRANTY_FLAG ;
385 p_item_rec_out.COVERAGE_SCHEDULE_ID := l_inv_item_rec_out.COVERAGE_SCHEDULE_ID ;
386 p_item_rec_out.SERVICE_DURATION := l_inv_item_rec_out.SERVICE_DURATION ;
387 p_item_rec_out.SERVICE_DURATION_PERIOD_CODE := l_inv_item_rec_out.SERVICE_DURATION_PERIOD_CODE ;
388 p_item_rec_out.DEFECT_TRACKING_ON_FLAG := l_inv_item_rec_out.DEFECT_TRACKING_ON_FLAG ;
389 p_item_rec_out.ORDERABLE_ON_WEB_FLAG := l_inv_item_rec_out.ORDERABLE_ON_WEB_FLAG ;
390 p_item_rec_out.BACK_ORDERABLE_FLAG := l_inv_item_rec_out.BACK_ORDERABLE_FLAG ;
391 p_item_rec_out.COLLATERAL_FLAG := l_inv_item_rec_out.COLLATERAL_FLAG;
392 p_item_rec_out.WEIGHT_UOM_CODE := l_inv_item_rec_out.WEIGHT_UOM_CODE;
393 p_item_rec_out.UNIT_WEIGHT := l_inv_item_rec_out.UNIT_WEIGHT;
394 p_item_rec_out.EVENT_FLAG := l_inv_item_rec_out.EVENT_FLAG;
395
396 END IF;
397 /***************************************/
398
399 -- Debug Message
400 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler'); END IF;
401
402 -- Invoke table handler(AMS_ITEM_OWNERS_PKG.Insert_Row)
403
404 AMS_ITEM_OWNERS_PKG.Insert_Row(
405 px_ITEM_OWNER_ID => l_ITEM_OWNER_ID,
406 px_OBJECT_VERSION_NUMBER => l_object_version_number,
407 p_INVENTORY_ITEM_ID => P_ITEM_REC_Out.INVENTORY_ITEM_ID,
408 p_ORGANIZATION_ID => p_ITEM_REC_Out.ORGANIZATION_ID,
409 p_ITEM_NUMBER => p_item_rec_out.ITEM_NUMBER,
410 p_OWNER_ID => p_ITEM_OWNER_rec.OWNER_ID,
411 p_STATUS_CODE => 'DRAFT' , -- p_ITEM_OWNER_rec.STATUS_CODE,
412 p_EFFECTIVE_DATE => SYSDATE, -- p_ITEM_OWNER_rec.EFFECTIVE_DATE,
413 p_IS_MASTER_ITEM => p_ITEM_OWNER_rec.IS_MASTER_ITEM,
414 p_ITEM_SETUP_TYPE => 'S', -- p_ITEM_OWNER_rec.ITEM_SETUP_TYPE,
415 p_CUSTOM_SETUP_ID => p_ITEM_OWNER_rec.CUSTOM_SETUP_ID); --'1200'
416
417 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 X_ITEM_OWNER_ID := l_ITEM_OWNER_ID;
422
423 --
424 -- End of API body
425 --
426
427 -- Standard check for p_commit
428 IF FND_API.to_Boolean( p_commit )
429 THEN
430 COMMIT WORK;
431 END IF;
432
433
434 -- Debug Message
435 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end'); END IF;
436
437 -- Standard call to get message count and if count is 1, get message info.
438 FND_MSG_PUB.Count_And_Get
439 (p_count => x_msg_count,
440 p_data => x_msg_data
441 );
442 EXCEPTION
443 WHEN FND_API.G_EXC_ERROR THEN
444 -- ROLLBACK TO CREATE_ITEM_OWNER_PVT;
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 -- Standard call to get message count and if count=1, get the message
447 FND_MSG_PUB.Count_And_Get (
448 p_encoded => FND_API.G_FALSE,
449 p_count => x_msg_count,
450 p_data => x_msg_data
451 );
452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
453 -- ROLLBACK TO CREATE_ITEM_OWNER_PVT;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 -- Standard call to get message count and if count=1, get the message
456 FND_MSG_PUB.Count_And_Get (
457 p_encoded => FND_API.G_FALSE,
458 p_count => x_msg_count,
459 p_data => x_msg_data
460 );
461 WHEN OTHERS THEN
462 -- ROLLBACK TO CREATE_ITEM_OWNER_PVT;
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
465 THEN
466 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
467 END IF;
468 -- Standard call to get message count and if count=1, get the message
469 FND_MSG_PUB.Count_And_Get (
470 p_encoded => FND_API.G_FALSE,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474 End Create_item_owner;
475
476
477 PROCEDURE Update_item_owner(
478 P_Api_Version_Number IN NUMBER,
479 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
480 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
481 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
482
483 X_Return_Status OUT NOCOPY VARCHAR2,
484 X_Msg_Count OUT NOCOPY NUMBER,
485 X_Msg_Data OUT NOCOPY VARCHAR2,
486
487 P_ITEM_OWNER_Rec IN ITEM_OWNER_Rec_Type,
488 X_Object_Version_Number OUT NOCOPY NUMBER,
489
490 P_ITEM_REC_In IN ITEM_rec_type := G_MISS_ITEM_REC,/*INV_Item_GRP.Item_rec_type := INV_Item_GRP.g_miss_Item_rec,*/
491 P_ITEM_REC_Out OUT NOCOPY ITEM_rec_type ,/*INV_Item_GRP.Item_rec_type,*/
492 x_item_return_status OUT NOCOPY VARCHAR2,
493 x_Error_tbl OUT NOCOPY Error_tbl_type/*INV_Item_GRP.Error_tbl_type*/
494
495 )
496
497 IS
498
499 Cursor C_Get_item_owner(l_ITEM_OWNER_ID Number) IS
500 Select rowid,
501 ITEM_OWNER_ID,
502 OBJECT_VERSION_NUMBER,
503 INVENTORY_ITEM_ID,
504 ORGANIZATION_ID,
505 ITEM_NUMBER,
506 OWNER_ID,
507 STATUS_CODE,
508 EFFECTIVE_DATE,
509 IS_MASTER_ITEM,
510 ITEM_SETUP_TYPE
511 From AMS_ITEM_ATTRIBUTES
512 WHERE ITEM_OWNER_ID = l_ITEM_OWNER_ID; -- item owner id is the PK for am item attributes table
513
514
515 Cursor C_check_INV_or_OMO_item ( l_inv_id IN NUMBER, l_org_id IN NUMBER) IS
516 SELECT count(*)
517 FROM ams_item_attributes
518 WHERE inventory_item_id = l_inv_id
519 AND organization_id = l_org_id;
520
521 item_count NUMBER;
522 IS_OMO_ITEM VARCHAR2(1);
523
524 -- Hint: Developer need to provide Where clause
525
526 l_api_name CONSTANT VARCHAR2(30) := 'Update_item_owner';
527 l_api_version_number CONSTANT NUMBER := 1.0;
528 -- Local Variables
529 l_object_version_number NUMBER;
530 l_ITEM_OWNER_ID NUMBER;
531 l_ref_ITEM_OWNER_rec AMS_item_owner_PVT.ITEM_OWNER_Rec_Type;
532 l_tar_ITEM_OWNER_rec AMS_item_owner_PVT.ITEM_OWNER_Rec_Type := P_ITEM_OWNER_Rec;
533 l_rowid ROWID;
534 l_inv_item_rec_in INV_Item_GRP.Item_Rec_Type ;
535 l_inv_item_rec_out INV_Item_GRP.Item_Rec_Type ;
536 l_error_tbl INV_Item_GRP.Error_tbl_Type ;
537
538 l_item_return_status VARCHAR2(1) := fnd_api.g_MISS_CHAR;
539
540 l_can_update_inv_item VARCHAR2(1);
541
542 l_status_controlled_item VARCHAR2(1) := 'N';
543
544 BEGIN
545 -- Standard Start of API savepoint
546 SAVEPOINT UPDATE_ITEM_OWNER_PVT;
547
548 -- Standard call to check for call compatibility.
549 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
550 p_api_version_number,
551 l_api_name,
552 G_PKG_NAME)
553 THEN
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END IF;
556
557 -- Initialize message list if p_init_msg_list is set to TRUE.
558 IF FND_API.to_Boolean( p_init_msg_list )
559 THEN
560 FND_MSG_PUB.initialize;
561 END IF;
562
563 -- Debug Message
564 IF (AMS_DEBUG_HIGH_ON) THEN
565
566 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
567 END IF;
568
569
570 -- Initialize API return status to SUCCESS
571 x_return_status := FND_API.G_RET_STS_SUCCESS;
572
573 -- Initialize inventory API return status to SUCCESS
574 x_item_return_status := FND_API.G_RET_STS_SUCCESS;
575
576 -- Debug Message
577 IF (AMS_DEBUG_HIGH_ON) THEN
578
579 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
580 END IF;
581 -- check whether item is OMO or INV item
582 -- if INV item set IS_OMO_ITEM = Y and check whenever updating
583 -- ams item attributes
584
585 OPEN C_check_INV_or_OMO_item(p_item_rec_in.INVENTORY_ITEM_ID, p_item_rec_in.ORGANIZATION_ID);
586 FETCH C_check_INV_or_OMO_item INTO item_count;
587 if (item_count > 0 ) then
588 IS_OMO_ITEM := 'Y';
589 else
590 IS_OMO_ITEM := 'N';
591 end if;
592 CLOSE C_check_INV_or_OMO_item;
593
594 /*** This code added by ABHOLA
595 id the item is OMO item , that is created by a marketing user
596 it can only be updated if AMS_ALLOW_INVENTORY_UPDATE profile is Y
597 ***/
598 l_can_update_inv_item := FND_PROFILE.value('AMS_ALLOW_INVENTORY_UPDATE');
599
600 if (l_can_update_inv_item = 'N') AND (IS_OMO_ITEM = 'N') then
601 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
602 THEN
603 FND_MESSAGE.Set_Name('AMS', 'AMS_CANNOT_UPDATE_INV_ITEM');
604 FND_MSG_PUB.Add;
605 END IF;
606 raise FND_API.G_EXC_ERROR;
607 end if;
608 /******************* end by ABHOLA ****************************************/
609
610 if (IS_OMO_ITEM = 'Y') then
611
612 Open C_Get_item_owner( l_tar_ITEM_OWNER_rec.ITEM_OWNER_ID);
613 Fetch C_Get_item_owner into
614 l_rowid,
615 l_ref_ITEM_OWNER_rec.ITEM_OWNER_ID,
616 l_ref_ITEM_OWNER_rec.OBJECT_VERSION_NUMBER,
617 l_ref_ITEM_OWNER_rec.INVENTORY_ITEM_ID,
618 l_ref_ITEM_OWNER_rec.ORGANIZATION_ID,
619 l_ref_ITEM_OWNER_rec.ITEM_NUMBER,
620 l_ref_ITEM_OWNER_rec.OWNER_ID,
621 l_ref_ITEM_OWNER_rec.STATUS_CODE,
622 l_ref_ITEM_OWNER_rec.EFFECTIVE_DATE,
623 l_ref_ITEM_OWNER_rec.IS_MASTER_ITEM,
624 l_ref_ITEM_OWNER_rec.ITEM_SETUP_TYPE;
625
626
627
628 If ( C_Get_item_owner%NOTFOUND) Then
629 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
630 THEN
631 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
632 FND_MESSAGE.Set_Token ('INFO', 'item_owner', FALSE);
633 FND_MSG_PUB.Add;
634 END IF;
635 raise FND_API.G_EXC_ERROR;
636 END IF;
637 -- Debug Message
638 IF (AMS_DEBUG_HIGH_ON) THEN
639
640 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
641 END IF;
642 Close C_Get_item_owner;
643 end if; -- end if of if (IS_OMO_ITEM = 'Y') then check
644
645 if (IS_OMO_ITEM = 'Y') then
646
647 If (l_tar_ITEM_OWNER_rec.object_version_number is NULL or
648 l_tar_ITEM_OWNER_rec.object_version_number = FND_API.G_MISS_NUM ) Then
649 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
650 THEN
651 FND_MESSAGE.Set_Name('AMS', 'API_VERSION_MISSING');
652 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
653 FND_MSG_PUB.ADD;
654 END IF;
655 raise FND_API.G_EXC_ERROR;
656 End if;
657
658
659
660
661 -- Check Whether record has been changed by someone else
662 If (l_tar_ITEM_OWNER_rec.object_version_number <> l_ref_ITEM_OWNER_rec.object_version_number) Then
663 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
664 THEN
665 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
666 FND_MESSAGE.Set_Token('INFO', 'item_owner', FALSE);
667 FND_MSG_PUB.ADD;
668 END IF;
669 raise FND_API.G_EXC_ERROR;
670 End if;
671
672 end if ;-- end of if (IS_OMO_ITEM = 'Y') check
673
674 /* Bug fix start */
675 IF (l_ref_ITEM_OWNER_rec.STATUS_CODE='CANCEL')THEN
676 FND_MESSAGE.Set_Name('AMS', 'AMS_CANNOT_UPDATE_PRODUCT');
677 FND_MSG_PUB.Add;
678 RAISE FND_API.G_EXC_ERROR;
679 END IF;
680 /* Bug fix ends*/
681 /* calling validate item */
682 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
683 THEN
684
685 -- Invoke validation procedures
686 Validate_item_owner(
687 p_api_version_number => 1.0,
688 p_init_msg_list => FND_API.G_FALSE,
689 p_validation_level => p_validation_level,
690 P_ITEM_OWNER_Rec => P_ITEM_OWNER_Rec,
691 P_ITEM_REC_In => p_item_rec_in,
692 x_return_status => x_return_status,
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data);
695 END IF;
696
697 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700
701 /*******************************************************************/
702 -- calling the Inventory update api
703
704 IF ( is_it_status_control_attr('BOM_ENABLED_FLAG')='N' )
705 THEN
706 l_inv_item_rec_in.BOM_ENABLED_FLAG := p_item_rec_in.BOM_ENABLED_FLAG;
707 END IF;
708
709 IF ( is_it_status_control_attr('INVOICE_ENABLED_FLAG')='N' )
710 THEN
711 l_inv_item_rec_in.INVOICE_ENABLED_FLAG := p_item_rec_in.INVOICE_ENABLED_FLAG;
712 END IF;
713
714 IF ( is_it_status_control_attr('CUSTOMER_ORDER_ENABLED_FLAG')='N' )
715 THEN
716 l_inv_item_rec_in.CUSTOMER_ORDER_ENABLED_FLAG := p_item_rec_in.CUSTOMER_ORDER_ENABLED_FLAG;
717 END IF;
718
719 IF ( is_it_status_control_attr('INTERNAL_ORDER_ENABLED_FLAG')='N' )
720 THEN
721 l_inv_item_rec_in.INTERNAL_ORDER_ENABLED_FLAG := p_item_rec_in.INTERNAL_ORDER_ENABLED_FLAG;
722 END IF;
723
724 IF ( is_it_status_control_attr('MTL_TRANSACTIONS_ENABLED_FLAG')='N' )
725 THEN
726 l_inv_item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG := p_item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG ;
727 END IF;
728
729 IF ( is_it_status_control_attr('STOCK_ENABLED_FLAG')='N' )
730 THEN
731 l_inv_item_rec_in.STOCK_ENABLED_FLAG := p_item_rec_in.STOCK_ENABLED_FLAG;
732 END IF;
733
734 l_inv_item_rec_in.INVENTORY_ITEM_ID := p_item_rec_in.INVENTORY_ITEM_ID ;
735 l_inv_item_rec_in.ORGANIZATION_ID := p_item_rec_in.ORGANIZATION_ID ;
736 l_inv_item_rec_in.ITEM_NUMBER := p_item_rec_in.ITEM_NUMBER ;
737 l_inv_item_rec_in.DESCRIPTION := p_item_rec_in.DESCRIPTION ;
738 l_inv_item_rec_in.LONG_DESCRIPTION := p_item_rec_in.LONG_DESCRIPTION ;
739 l_inv_item_rec_in.ITEM_TYPE := p_item_rec_in.ITEM_TYPE ;
740 l_inv_item_rec_in.PRIMARY_UOM_CODE := p_item_rec_in.PRIMARY_UOM_CODE ;
741 l_inv_item_rec_in.PRIMARY_UNIT_OF_MEASURE := p_item_rec_in.PRIMARY_UNIT_OF_MEASURE ;
742 l_inv_item_rec_in.START_DATE_ACTIVE := p_item_rec_in.START_DATE_ACTIVE ;
743 l_inv_item_rec_in.END_DATE_ACTIVE := p_item_rec_in.END_DATE_ACTIVE ;
744 l_inv_item_rec_in.INVENTORY_ITEM_STATUS_CODE := p_item_rec_in.INVENTORY_ITEM_STATUS_CODE ;
745 l_inv_item_rec_in.INVENTORY_ITEM_FLAG := p_item_rec_in.INVENTORY_ITEM_FLAG ;
746 l_inv_item_rec_in.REVISION_QTY_CONTROL_CODE := p_item_rec_in.REVISION_QTY_CONTROL_CODE ;
747 l_inv_item_rec_in.BOM_ITEM_TYPE := p_item_rec_in.BOM_ITEM_TYPE ;
748 l_inv_item_rec_in.COSTING_ENABLED_FLAG := p_item_rec_in.COSTING_ENABLED_FLAG ;
749 l_inv_item_rec_in.ELECTRONIC_FLAG := p_item_rec_in.ELECTRONIC_FLAG ;
750 l_inv_item_rec_in.DOWNLOADABLE_FLAG := p_item_rec_in.DOWNLOADABLE_FLAG ;
751 l_inv_item_rec_in.CUSTOMER_ORDER_FLAG := p_item_rec_in.CUSTOMER_ORDER_FLAG ;
752 l_inv_item_rec_in.INTERNAL_ORDER_FLAG := p_item_rec_in.INTERNAL_ORDER_FLAG ;
753 l_inv_item_rec_in.SHIPPABLE_ITEM_FLAG := p_item_rec_in.SHIPPABLE_ITEM_FLAG ;
754 l_inv_item_rec_in.RETURNABLE_FLAG := p_item_rec_in.RETURNABLE_FLAG ;
755 l_inv_item_rec_in.COMMS_ACTIVATION_REQD_FLAG := p_item_rec_in.COMMS_ACTIVATION_REQD_FLAG ;
756 l_inv_item_rec_in.REPLENISH_TO_ORDER_FLAG := p_item_rec_in.REPLENISH_TO_ORDER_FLAG ;
757 l_inv_item_rec_in.INVOICEABLE_ITEM_FLAG := p_item_rec_in.INVOICEABLE_ITEM_FLAG ;
758 --l_inv_item_rec_in.SERVICE_ITEM_FLAG := p_item_rec_in.SERVICE_ITEM_FLAG ;
759 l_inv_item_rec_in.SERVICEABLE_PRODUCT_FLAG := p_item_rec_in.SERVICEABLE_PRODUCT_FLAG ;
760 --l_inv_item_rec_in.VENDOR_WARRANTY_FLAG := p_item_rec_in.VENDOR_WARRANTY_FLAG ;
761 l_inv_item_rec_in.COVERAGE_SCHEDULE_ID := p_item_rec_in.COVERAGE_SCHEDULE_ID ;
762 l_inv_item_rec_in.SERVICE_DURATION := p_item_rec_in.SERVICE_DURATION ;
763 l_inv_item_rec_in.SERVICE_DURATION_PERIOD_CODE := p_item_rec_in.SERVICE_DURATION_PERIOD_CODE ;
764 l_inv_item_rec_in.DEFECT_TRACKING_ON_FLAG := p_item_rec_in.DEFECT_TRACKING_ON_FLAG ;
765 l_inv_item_rec_in.ORDERABLE_ON_WEB_FLAG := p_item_rec_in.ORDERABLE_ON_WEB_FLAG ;
766 l_inv_item_rec_in.BACK_ORDERABLE_FLAG := p_item_rec_in.BACK_ORDERABLE_FLAG ;
767 /* Bug 2726989 Fix - by musman 26-dec-02*/
768 --l_inv_item_rec_in.COLLATERAL_FLAG := p_item_rec_in.COLLATERAL_FLAG;
769 IF (p_item_rec_in.COLLATERAL_FLAG IS NOT NULL)
770 OR (p_item_rec_in.COLLATERAL_FLAG <> FND_API.G_MISS_CHAR )
771 THEN
772 l_inv_item_rec_in.COLLATERAL_FLAG := p_item_rec_in.COLLATERAL_FLAG;
773 END IF;
774
775 l_inv_item_rec_in.WEIGHT_UOM_CODE := p_item_rec_in.WEIGHT_UOM_CODE;
776 l_inv_item_rec_in.UNIT_WEIGHT := p_item_rec_in.UNIT_WEIGHT;
777 l_inv_item_rec_in.EVENT_FLAG := p_item_rec_in.EVENT_FLAG;
778 l_inv_item_rec_in.COMMS_NL_TRACKABLE_FLAG := p_item_rec_in.COMMS_NL_TRACKABLE_FLAG;
779 l_inv_item_rec_in.SUBSCRIPTION_DEPEND_FLAG := p_item_rec_in.SUBSCRIPTION_DEPEND_FLAG;
780 --l_inv_item_rec_in.CONTRACT_ITEM_TYPE_CODE := p_item_rec_in.CONTRACT_ITEM_TYPE_CODE;
781 l_inv_item_rec_in.WEB_STATUS := p_item_rec_in.WEB_STATUS;
782 l_inv_item_rec_in.INDIVISIBLE_FLAG := p_item_rec_in.INDIVISIBLE_FLAG;
783 l_inv_item_rec_in.MATERIAL_BILLABLE_FLAG := p_item_rec_in.MATERIAL_BILLABLE_FLAG;
784 l_inv_item_rec_in.PICK_COMPONENTS_FLAG := p_item_rec_in.PICK_COMPONENTS_FLAG;
785 l_inv_item_rec_in.so_transactions_flag := p_item_rec_in.so_transactions_flag;
786
787 l_inv_item_rec_in.attribute_category := p_item_rec_in.attribute_category;
788 l_inv_item_rec_in.attribute1 := p_item_rec_in.attribute1;
789 l_inv_item_rec_in.attribute2 := p_item_rec_in.attribute2;
790 l_inv_item_rec_in.attribute3 := p_item_rec_in.attribute3;
791 l_inv_item_rec_in.attribute4 := p_item_rec_in.attribute4;
792 l_inv_item_rec_in.attribute5 := p_item_rec_in.attribute5;
793 l_inv_item_rec_in.attribute6 := p_item_rec_in.attribute6;
794 l_inv_item_rec_in.attribute7 := p_item_rec_in.attribute7;
795 l_inv_item_rec_in.attribute8 := p_item_rec_in.attribute8;
796 l_inv_item_rec_in.attribute9 := p_item_rec_in.attribute9;
797 l_inv_item_rec_in.attribute10 := p_item_rec_in.attribute10;
798 l_inv_item_rec_in.attribute11 := p_item_rec_in.attribute11;
799 l_inv_item_rec_in.attribute12 := p_item_rec_in.attribute12;
800 l_inv_item_rec_in.attribute13 := p_item_rec_in.attribute13;
801 l_inv_item_rec_in.attribute14 := p_item_rec_in.attribute14;
802 l_inv_item_rec_in.attribute15 := p_item_rec_in.attribute15;
803
804 -- code added by ABHOLA
805 -- If we are making an item cancelled , it will be made inactive in the
806 -- Oracle Inventory
807 --
808 IF (p_ITEM_OWNER_rec.STATUS_CODE = 'CANCEL') THEN
809 l_inv_item_rec_in.inventory_item_status_code := 'Inactive';
810 END IF;
811
812 /* Certain flags have to be defaulted */
813 IF (l_inv_item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y') THEN
814 l_inv_item_rec_in.INVENTORY_ITEM_FLAG := 'Y';
815 l_inv_item_rec_in.STOCK_ENABLED_FLAG := 'Y';
816 END IF;
817
818 IF (l_inv_item_rec_in.STOCK_ENABLED_FLAG = 'Y') THEN
819 l_inv_item_rec_in.INVENTORY_ITEM_FLAG := 'Y';
820 END IF;
821
822 /* IF ( l_inv_item_rec_in.CONTRACT_ITEM_TYPE_CODE = 'SUBSCRIPTION')
823 THEN
824 l_inv_item_rec_in.SERVICE_ITEM_FLAG := 'Y';
825 END IF;
826 */
827
828 INV_Item_GRP.update_Item
829 ( p_commit => P_Commit
830 , p_validation_level => p_validation_level
831 , p_Item_rec => l_inv_item_rec_in /*P_ITEM_REC_In*/
832 , x_Item_rec => l_inv_item_rec_out /*P_ITEM_REC_Out*/
833 , x_return_status => l_item_return_status
834 , x_Error_tbl => l_Error_tbl /*x_Error_tbl*/
835 );
836
837 x_item_return_status := l_item_return_status;
838 IF l_item_return_status <> FND_API.G_RET_STS_SUCCESS THEN
839 IF l_error_tbl.count >0 THEN
840 FOR l_cnt IN 1..l_error_tbl.count LOOP
841 x_error_tbl(l_cnt).transaction_id := l_error_tbl(l_cnt).transaction_id;
842 x_error_tbl(l_cnt).unique_id := l_error_tbl(l_cnt).unique_id;
843 x_error_tbl(l_cnt).message_name := l_error_tbl(l_cnt).message_name;
844 x_error_tbl(l_cnt).message_text := l_error_tbl(l_cnt).message_text;
845 x_error_tbl(l_cnt).table_name := l_error_tbl(l_cnt).table_name;
846 x_error_tbl(l_cnt).column_name := l_error_tbl(l_cnt).column_name;
847 x_error_tbl(l_cnt).organization_id := l_error_tbl(l_cnt).organization_id;
848 END LOOP;
849 END IF;
850 RAISE FND_API.G_EXC_ERROR;
851 ELSE
852 p_item_rec_out.INVENTORY_ITEM_ID := l_inv_item_rec_out.INVENTORY_ITEM_ID ;
853 p_item_rec_out.ORGANIZATION_ID := l_inv_item_rec_out.ORGANIZATION_ID ;
854 p_item_rec_out.ITEM_NUMBER := l_inv_item_rec_out.ITEM_NUMBER ;
855 p_item_rec_out.DESCRIPTION := l_inv_item_rec_out.DESCRIPTION ;
856 p_item_rec_out.LONG_DESCRIPTION := l_inv_item_rec_out.LONG_DESCRIPTION ;
857 p_item_rec_out.ITEM_TYPE := l_inv_item_rec_out.ITEM_TYPE ;
858 p_item_rec_out.PRIMARY_UOM_CODE := l_inv_item_rec_out.PRIMARY_UOM_CODE ;
859 p_item_rec_out.PRIMARY_UNIT_OF_MEASURE := l_inv_item_rec_out.PRIMARY_UNIT_OF_MEASURE ;
860 p_item_rec_out.START_DATE_ACTIVE := l_inv_item_rec_out.START_DATE_ACTIVE ;
861 p_item_rec_out.END_DATE_ACTIVE := l_inv_item_rec_out.END_DATE_ACTIVE ;
862 p_item_rec_out.INVENTORY_ITEM_STATUS_CODE := l_inv_item_rec_out.INVENTORY_ITEM_STATUS_CODE ;
863 p_item_rec_out.INVENTORY_ITEM_FLAG := l_inv_item_rec_out.INVENTORY_ITEM_FLAG ;
864 p_item_rec_out.STOCK_ENABLED_FLAG := l_inv_item_rec_out.STOCK_ENABLED_FLAG ;
865 p_item_rec_out.MTL_TRANSACTIONS_ENABLED_FLAG := l_inv_item_rec_out.MTL_TRANSACTIONS_ENABLED_FLAG ;
866 p_item_rec_out.REVISION_QTY_CONTROL_CODE := l_inv_item_rec_out.REVISION_QTY_CONTROL_CODE ;
867 p_item_rec_out.BOM_ENABLED_FLAG := l_inv_item_rec_out.BOM_ENABLED_FLAG ;
868 p_item_rec_out.BOM_ITEM_TYPE := l_inv_item_rec_out.BOM_ITEM_TYPE ;
869 p_item_rec_out.COSTING_ENABLED_FLAG := l_inv_item_rec_out.COSTING_ENABLED_FLAG ;
870 p_item_rec_out.ELECTRONIC_FLAG := l_inv_item_rec_out.ELECTRONIC_FLAG ;
871 p_item_rec_out.DOWNLOADABLE_FLAG := l_inv_item_rec_out.DOWNLOADABLE_FLAG ;
872 p_item_rec_out.CUSTOMER_ORDER_FLAG := l_inv_item_rec_out.CUSTOMER_ORDER_FLAG ;
873 p_item_rec_out.CUSTOMER_ORDER_ENABLED_FLAG := l_inv_item_rec_out.CUSTOMER_ORDER_ENABLED_FLAG ;
874 p_item_rec_out.INTERNAL_ORDER_FLAG := l_inv_item_rec_out.INTERNAL_ORDER_FLAG ;
875 p_item_rec_out.INTERNAL_ORDER_ENABLED_FLAG := l_inv_item_rec_out.INTERNAL_ORDER_ENABLED_FLAG ;
876 p_item_rec_out.SHIPPABLE_ITEM_FLAG := l_inv_item_rec_out.SHIPPABLE_ITEM_FLAG ;
877 p_item_rec_out.RETURNABLE_FLAG := l_inv_item_rec_out.RETURNABLE_FLAG ;
878 p_item_rec_out.COMMS_ACTIVATION_REQD_FLAG := l_inv_item_rec_out.COMMS_ACTIVATION_REQD_FLAG ;
879 p_item_rec_out.REPLENISH_TO_ORDER_FLAG := l_inv_item_rec_out.REPLENISH_TO_ORDER_FLAG ;
880 p_item_rec_out.INVOICEABLE_ITEM_FLAG := l_inv_item_rec_out.INVOICEABLE_ITEM_FLAG ;
881 p_item_rec_out.INVOICE_ENABLED_FLAG := l_inv_item_rec_out.INVOICE_ENABLED_FLAG;
882 --p_item_rec_out.SERVICE_ITEM_FLAG := l_inv_item_rec_out.SERVICE_ITEM_FLAG ;
883 p_item_rec_out.SERVICEABLE_PRODUCT_FLAG := l_inv_item_rec_out.SERVICEABLE_PRODUCT_FLAG ;
884 --p_item_rec_out.VENDOR_WARRANTY_FLAG := l_inv_item_rec_out.VENDOR_WARRANTY_FLAG ;
885 p_item_rec_out.COVERAGE_SCHEDULE_ID := l_inv_item_rec_out.COVERAGE_SCHEDULE_ID ;
886 p_item_rec_out.SERVICE_DURATION := l_inv_item_rec_out.SERVICE_DURATION ;
887 p_item_rec_out.SERVICE_DURATION_PERIOD_CODE := l_inv_item_rec_out.SERVICE_DURATION_PERIOD_CODE ;
888 p_item_rec_out.DEFECT_TRACKING_ON_FLAG := l_inv_item_rec_out.DEFECT_TRACKING_ON_FLAG ;
889 p_item_rec_out.ORDERABLE_ON_WEB_FLAG := l_inv_item_rec_out.ORDERABLE_ON_WEB_FLAG ;
890 p_item_rec_out.BACK_ORDERABLE_FLAG := l_inv_item_rec_out.BACK_ORDERABLE_FLAG ;
891 p_item_rec_out.COLLATERAL_FLAG := l_inv_item_rec_out.COLLATERAL_FLAG;
892 p_item_rec_out.WEIGHT_UOM_CODE := l_inv_item_rec_out.WEIGHT_UOM_CODE;
893 p_item_rec_out.UNIT_WEIGHT := l_inv_item_rec_out.UNIT_WEIGHT;
894 p_item_rec_out.EVENT_FLAG := l_inv_item_rec_out.EVENT_FLAG;
895 p_item_rec_out.COMMS_NL_TRACKABLE_FLAG := l_inv_item_rec_out.COMMS_NL_TRACKABLE_FLAG;
896 p_item_rec_out.SUBSCRIPTION_DEPEND_FLAG := l_inv_item_rec_out.SUBSCRIPTION_DEPEND_FLAG;
897 p_item_rec_out.CONTRACT_ITEM_TYPE_CODE := l_inv_item_rec_out.CONTRACT_ITEM_TYPE_CODE;
898 p_item_rec_out.WEB_STATUS := l_inv_item_rec_out.WEB_STATUS;
899 p_item_rec_out.INDIVISIBLE_FLAG := l_inv_item_rec_out.INDIVISIBLE_FLAG;
900 p_item_rec_out.MATERIAL_BILLABLE_FLAG := l_inv_item_rec_out.MATERIAL_BILLABLE_FLAG;
901 p_item_rec_out.PICK_COMPONENTS_FLAG := l_inv_item_rec_out.PICK_COMPONENTS_FLAG;
902 p_item_rec_out.so_transactions_flag := l_inv_item_rec_out.so_transactions_flag;
903
904 END IF;
905
906 -- Invoke table handler(AMS_ITEM_OWNERS_PKG.Update_Row)
907
908 if (IS_OMO_ITEM = 'Y') then
909
910 AMS_ITEM_OWNERS_PKG.Update_Row(
911 p_ITEM_OWNER_ID => p_ITEM_OWNER_rec.ITEM_OWNER_ID,
912 p_OBJECT_VERSION_NUMBER => p_ITEM_OWNER_rec.OBJECT_VERSION_NUMBER,
913 p_INVENTORY_ITEM_ID => p_ITEM_OWNER_rec.INVENTORY_ITEM_ID,
914 p_ORGANIZATION_ID => p_ITEM_OWNER_rec.ORGANIZATION_ID,
915 p_ITEM_NUMBER => p_ITEM_OWNER_rec.ITEM_NUMBER,
916 p_OWNER_ID => p_ITEM_OWNER_rec.OWNER_ID,
917 p_STATUS_CODE => p_ITEM_OWNER_rec.STATUS_CODE,
918 p_EFFECTIVE_DATE => SYSDATE , -- p_ITEM_OWNER_rec.EFFECTIVE_DATE,
919 p_IS_MASTER_ITEM => p_ITEM_OWNER_rec.IS_MASTER_ITEM,
920 p_ITEM_SETUP_TYPE => p_ITEM_OWNER_rec.ITEM_SETUP_TYPE
921 );
922
923 end if ; -- end of if (IS_OMO_ITEM = 'Y') check
924 --
925 -- End of API body.
926 --
927
928 -- Standard check for p_commit
929 IF FND_API.to_Boolean( p_commit )
930 THEN
931 COMMIT WORK;
932 END IF;
933
934
935 -- Debug Message
936 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end'); END IF;
937
938 -- Standard call to get message count and if count is 1, get message info.
939 FND_MSG_PUB.Count_And_Get
940 (p_count => x_msg_count,
941 p_data => x_msg_data
942 );
943 EXCEPTION
944 WHEN FND_API.G_EXC_ERROR THEN
945 -- ROLLBACK TO UPDATE_ITEM_OWNER_PVT;
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 -- Standard call to get message count and if count=1, get the message
948 FND_MSG_PUB.Count_And_Get (
949 p_encoded => FND_API.G_FALSE,
950 p_count => x_msg_count,
951 p_data => x_msg_data
952 );
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 -- ROLLBACK TO UPDATE_ITEM_OWNER_PVT;
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956 -- Standard call to get message count and if count=1, get the message
957 FND_MSG_PUB.Count_And_Get (
958 p_encoded => FND_API.G_FALSE,
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962 WHEN OTHERS THEN
963 -- ROLLBACK TO UPDATE_ITEM_OWNER_PVT;
964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
965 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
966 THEN
967 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
968 END IF;
969 -- Standard call to get message count and if count=1, get the message
970 FND_MSG_PUB.Count_And_Get (
971 p_encoded => FND_API.G_FALSE,
972 p_count => x_msg_count,
973 p_data => x_msg_data
974 );
975 End Update_item_owner;
976
977
978 PROCEDURE Delete_item_owner(
979 P_Api_Version_Number IN NUMBER,
980 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
981 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
982 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
983 X_Return_Status OUT NOCOPY VARCHAR2,
984 X_Msg_Count OUT NOCOPY NUMBER,
985 X_Msg_Data OUT NOCOPY VARCHAR2,
986 P_ITEM_OWNER_ID IN NUMBER,
987 P_Object_Version_Number IN NUMBER
988 )
989
990 IS
991 l_api_name CONSTANT VARCHAR2(30) := 'Delete_item_owner';
992 l_api_version_number CONSTANT NUMBER := 1.0;
993 l_object_version_number NUMBER;
994
995 BEGIN
996 -- Standard Start of API savepoint
997 SAVEPOINT DELETE_ITEM_OWNER_PVT;
998
999 -- Standard call to check for call compatibility.
1000 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1001 p_api_version_number,
1002 l_api_name,
1003 G_PKG_NAME)
1004 THEN
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006 END IF;
1007
1008 -- Initialize message list if p_init_msg_list is set to TRUE.
1009 IF FND_API.to_Boolean( p_init_msg_list )
1010 THEN
1011 FND_MSG_PUB.initialize;
1012 END IF;
1013
1014 -- Debug Message
1015 IF (AMS_DEBUG_HIGH_ON) THEN
1016
1017 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1018 END IF;
1019
1020
1021 -- Initialize API return status to SUCCESS
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 --
1025 -- Api body
1026 --
1027 -- Debug Message
1028 IF (AMS_DEBUG_HIGH_ON) THEN
1029
1030 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1031 END IF;
1032
1033 -- Invoke table handler(AMS_ITEM_OWNERS_PKG.Delete_Row)
1034 AMS_ITEM_OWNERS_PKG.Delete_Row(
1035 p_ITEM_OWNER_ID => p_ITEM_OWNER_ID);
1036 --
1037 -- End of API body
1038 --
1039
1040 -- Standard check for p_commit
1041 IF FND_API.to_Boolean( p_commit )
1042 THEN
1043 COMMIT WORK;
1044 END IF;
1045
1046
1047 -- Debug Message
1048 IF (AMS_DEBUG_HIGH_ON) THEN
1049
1050 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1051 END IF;
1052
1053 -- Standard call to get message count and if count is 1, get message info.
1054 FND_MSG_PUB.Count_And_Get
1055 (p_count => x_msg_count,
1056 p_data => x_msg_data
1057 );
1058 EXCEPTION
1059 WHEN FND_API.G_EXC_ERROR THEN
1060 ROLLBACK TO DELETE_ITEM_OWNER_PVT;
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 -- Standard call to get message count and if count=1, get the message
1063 FND_MSG_PUB.Count_And_Get (
1064 p_encoded => FND_API.G_FALSE,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069 ROLLBACK TO DELETE_ITEM_OWNER_PVT;
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071 -- Standard call to get message count and if count=1, get the message
1072 FND_MSG_PUB.Count_And_Get (
1073 p_encoded => FND_API.G_FALSE,
1074 p_count => x_msg_count,
1075 p_data => x_msg_data
1076 );
1077 WHEN OTHERS THEN
1078 ROLLBACK TO DELETE_ITEM_OWNER_PVT;
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1081 THEN
1082 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1083 END IF;
1084 -- Standard call to get message count and if count=1, get the message
1085 FND_MSG_PUB.Count_And_Get (
1086 p_encoded => FND_API.G_FALSE,
1087 p_count => x_msg_count,
1088 p_data => x_msg_data
1089 );
1090 End Delete_item_owner;
1091
1092
1093
1094
1095 PROCEDURE Complete_ITEM_OWNER_Rec (
1096 P_ITEM_OWNER_Rec IN ITEM_OWNER_Rec_Type,
1097 x_complete_rec OUT NOCOPY ITEM_OWNER_Rec_Type
1098 )
1099 IS
1100
1101 CURSOR c_ITEM_OWNER IS
1102 SELECT *
1103 FROM ams_item_ATTRIBUTES
1104 WHERE ITEM_OWNER_ID = p_ITEM_OWNER_Rec.ITEM_OWNER_ID;
1105
1106 l_ITEM_OWNER_Rec_Type c_ITEM_OWNER%ROWTYPE;
1107
1108 BEGIN
1109
1110
1111 x_complete_rec := p_ITEM_OWNER_Rec;
1112
1113 OPEN c_ITEM_OWNER;
1114 FETCH c_ITEM_OWNER INTO l_ITEM_OWNER_Rec_Type;
1115 IF c_ITEM_OWNER%NOTFOUND THEN
1116 CLOSE c_ITEM_OWNER;
1117 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1118 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1119 FND_MSG_PUB.add;
1120 END IF;
1121 RAISE FND_API.g_exc_error;
1122 END IF;
1123 CLOSE c_ITEM_OWNER;
1124
1125 IF p_ITEM_OWNER_Rec.INVENTORY_ITEM_ID = FND_API.g_miss_num THEN
1126 x_complete_rec.INVENTORY_ITEM_ID := l_ITEM_OWNER_Rec_Type.INVENTORY_ITEM_ID;
1127 END IF;
1128
1129 IF p_ITEM_OWNER_Rec.ORGANIZATION_ID = FND_API.g_miss_num THEN
1130 x_complete_rec.ORGANIZATION_ID := l_ITEM_OWNER_Rec_Type.ORGANIZATION_ID;
1131 END IF;
1132
1133 IF p_ITEM_OWNER_Rec.ITEM_NUMBER = FND_API.g_miss_num THEN
1134 x_complete_rec.ITEM_NUMBER := l_ITEM_OWNER_Rec_Type.ITEM_NUMBER;
1135 END IF;
1136
1137 IF p_ITEM_OWNER_Rec.OWNER_ID = FND_API.g_miss_num THEN
1138 x_complete_rec.OWNER_ID := l_ITEM_OWNER_Rec_Type.OWNER_ID;
1139 END IF;
1140
1141 IF p_ITEM_OWNER_Rec.STATUS_CODE = FND_API.g_miss_char THEN
1142 x_complete_rec.STATUS_CODE := l_ITEM_OWNER_Rec_Type.STATUS_CODE;
1143 END IF;
1144
1145 IF p_ITEM_OWNER_Rec.EFFECTIVE_DATE = FND_API.g_miss_date THEN
1146 x_complete_rec.EFFECTIVE_DATE := l_ITEM_OWNER_Rec_Type.EFFECTIVE_DATE;
1147 END IF;
1148
1149
1150 END Complete_ITEM_OWNER_Rec;
1151
1152 PROCEDURE Validate_item_owner(
1153 P_Api_Version_Number IN NUMBER,
1154 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1155 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1156 P_ITEM_OWNER_Rec IN ITEM_OWNER_Rec_Type,
1157 P_ITEM_REC_In IN ITEM_rec_type := G_MISS_ITEM_REC,
1158 X_Return_Status OUT NOCOPY VARCHAR2,
1159 X_Msg_Count OUT NOCOPY NUMBER,
1160 X_Msg_Data OUT NOCOPY VARCHAR2
1161 )
1162 IS
1163 l_api_name CONSTANT VARCHAR2(30) := 'Validate_item_owner';
1164 l_api_version_number CONSTANT NUMBER := 1.0;
1165 l_object_version_number NUMBER;
1166 l_ITEM_OWNER_rec AMS_item_owner_PVT.ITEM_OWNER_Rec_Type;
1167
1168 BEGIN
1169 -- Standard Start of API savepoint
1170 SAVEPOINT VALIDATE_ITEM_OWNER_;
1171
1172 -- Standard call to check for call compatibility.
1173 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1174 p_api_version_number,
1175 l_api_name,
1176 G_PKG_NAME)
1177 THEN
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180
1181 -- Initialize message list if p_init_msg_list is set to TRUE.
1182 IF FND_API.to_Boolean( p_init_msg_list )
1183 THEN
1184 FND_MSG_PUB.initialize;
1185 END IF;
1186
1187 -- Debug Message
1188 IF (AMS_DEBUG_HIGH_ON) THEN
1189
1190 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1191 END IF;
1192
1193
1194 -- Initialize API return status to SUCCESS
1195 x_return_status := FND_API.G_RET_STS_SUCCESS;
1196
1197 -- check 1 : both inv item flag and service item cannot be Y
1198 IF (( P_ITEM_REC_In.INVENTORY_ITEM_FLAG = 'Y')
1199 AND ( P_ITEM_REC_In.SERVICE_ITEM_FLAG = 'Y'))
1200 THEN
1201 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1202 THEN
1203 FND_MESSAGE.Set_Name('AMS', 'AMS_INV_SRV_ITM_FLG_ERR');
1204 FND_MSG_PUB.Add;
1205 END IF;
1206 RAISE FND_API.G_EXC_ERROR;
1207 END IF;
1208
1209 -- check 2 : both service item flag and service able product flag cannot be Y
1210 IF (( P_ITEM_REC_In.SERVICEABLE_PRODUCT_FLAG = 'Y')
1211 AND ( P_ITEM_REC_In.SERVICE_ITEM_FLAG = 'Y'))
1212 THEN
1213 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1214 THEN
1215 FND_MESSAGE.Set_Name('AMS', 'AMS_SRPRD_SRV_ITM_FLG_ERR');
1216 FND_MSG_PUB.Add;
1217 END IF;
1218 raise FND_API.G_EXC_ERROR;
1219 END IF;
1220
1221 /* -- if the contract Item type code is Warranty, inv api automatically updates
1222 -- vendor_warrantty_flag,service_item_flag to 'Y'
1223 -- if warranty is Y, then service has to be Y
1224 IF (( P_ITEM_REC_In.VENDOR_WARRANTY_FLAG = 'Y')
1225 AND ( P_ITEM_REC_In.SERVICE_ITEM_FLAG = 'N'))
1226 THEN
1227 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1228 THEN
1229 FND_MESSAGE.Set_Name('AMS', 'AMS_WARRANTY_SRV_ERR');
1230 FND_MSG_PUB.Add;
1231 END IF;
1232 raise FND_API.G_EXC_ERROR;
1233 END IF;
1234 */
1235 --- if warranty is Y, there has to be a value for Duration Period
1236 /**
1237 IF (( P_ITEM_REC_In.VENDOR_WARRANTY_FLAG = 'Y') AND
1238 ( P_ITEM_REC_In.SERVICE_DURATION_PERIOD_CODE IS NULL ) OR ( P_ITEM_REC_In.COVERAGE_SCHEDULE_ID IS NULL ))
1239 THEN
1240 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1241 THEN
1242 FND_MESSAGE.Set_Name('AMS', 'AMS_WARRANTY_SRVDUR_PER_ERR');
1243 FND_MSG_PUB.Add;
1244 END IF;
1245 raise FND_API.G_EXC_ERROR;
1246 END IF;
1247 **/
1248 /*-- by musman dec-30-2002
1249 -- because even the service items with vendor_warranty_flag (Contract itemType pf warranty)
1250 -- checked can have bom_enabled_flag true.
1251 -- if BOM flag is Y , INV ITEM should also be Y
1252 IF (( P_ITEM_REC_In.INVENTORY_ITEM_FLAG = 'N')
1253 AND ( P_ITEM_REC_In.BOM_ENABLED_FLAG = 'Y'))
1254 THEN
1255 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1256 THEN
1257 FND_MESSAGE.Set_Name('AMS', 'AMS_INV_BOM_FLG_ERR');
1258 FND_MSG_PUB.Add;
1259 END IF;
1260 raise FND_API.G_EXC_ERROR;
1261 END IF;
1262 */
1263 -- if service flag is N , and NO VALUES IN COVERage or DURATION
1264 /**
1265 IF (( P_ITEM_REC_In.VENDOR_WARRANTY_FLAG = 'N' )
1266 AND ( P_ITEM_REC_In.SERVICE_DURATION_PERIOD_CODE IS NOT NULL ) OR ( P_ITEM_REC_In.COVERAGE_SCHEDULE_ID IS NOT NULL ) )
1267 THEN
1268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1269 THEN
1270 FND_MESSAGE.Set_Name('AMS', 'AMS_WARRN_SRV_COMB_ERR');
1271 FND_MSG_PUB.Add;
1272 END IF;
1273 raise FND_API.G_EXC_ERROR;
1274 END IF;
1275 ***/
1276
1277 -- if Bom Item Type is Model or option class, either "Assemble to order" or "pick components" has to be yes.
1278 IF ( ( P_ITEM_REC_In.BOM_ITEM_TYPE = 1 --Model
1279 OR P_ITEM_REC_In.BOM_ITEM_TYPE = 2 ) -- option Class
1280 AND P_ITEM_REC_In.PICK_COMPONENTS_FLAG <> 'Y'
1281 AND P_ITEM_REC_In.REPLENISH_TO_ORDER_FLAG <> 'Y')
1282 THEN
1283 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1284 THEN
1285 FND_MESSAGE.Set_Name('AMS', 'AMS_PROD_BOM_ERROR');
1286 FND_MSG_PUB.Add;
1287 END IF;
1288 raise FND_API.G_EXC_ERROR;
1289 END IF;
1290
1291 -- if SUBSCRIPTION_DEPEND_FLAG is "Y" then the contract item_type code has to be subscription
1292
1293 IF ( P_ITEM_REC_In.SUBSCRIPTION_DEPEND_FLAG = 'Y'
1294 AND P_ITEM_REC_In.CONTRACT_ITEM_TYPE_CODE <> 'SUBSCRIPTION' )
1295 THEN
1296 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1297 THEN
1298 FND_MESSAGE.Set_Name('AMS', 'AMS_PROD_SUBSCRIP_ERROR');
1299 FND_MSG_PUB.Add;
1300 END IF;
1301 raise FND_API.G_EXC_ERROR;
1302 END IF;
1303
1304 /*
1305 -- if contract item type code is subscription ,service _item_flag has to 'Y'
1306 IF ( P_ITEM_REC_In.CONTRACT_ITEM_TYPE_CODE = 'SUBSCRIPTION'
1307 AND P_ITEM_REC_In.SERVICE_ITEM_FLAG <> 'Y' )
1308 THEN
1309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1310 THEN
1311 FND_MESSAGE.Set_Name('AMS', 'AMS_PROD_CONTRACT_ERROR');
1312 FND_MSG_PUB.Add;
1313 END IF;
1314 raise FND_API.G_EXC_ERROR;
1315 END IF;
1316 */
1317
1318 -- Standard call to get message count and if count is 1, get message info.
1319 FND_MSG_PUB.Count_And_Get
1320 (p_count => x_msg_count,
1321 p_data => x_msg_data
1322 );
1323 EXCEPTION
1324 WHEN FND_API.G_EXC_ERROR THEN
1325 ROLLBACK TO VALIDATE_ITEM_OWNER_;
1326 x_return_status := FND_API.G_RET_STS_ERROR;
1327 -- Standard call to get message count and if count=1, get the message
1328 FND_MSG_PUB.Count_And_Get (
1329 p_encoded => FND_API.G_FALSE,
1330 p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1334 ROLLBACK TO VALIDATE_ITEM_OWNER_;
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 -- Standard call to get message count and if count=1, get the message
1337 FND_MSG_PUB.Count_And_Get (
1338 p_encoded => FND_API.G_FALSE,
1339 p_count => x_msg_count,
1340 p_data => x_msg_data
1341 );
1342 WHEN OTHERS THEN
1343 ROLLBACK TO VALIDATE_ITEM_OWNER_;
1344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1346 THEN
1347 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1348 END IF;
1349 -- Standard call to get message count and if count=1, get the message
1350 FND_MSG_PUB.Count_And_Get (
1351 p_encoded => FND_API.G_FALSE,
1352 p_count => x_msg_count,
1353 p_data => x_msg_data
1354 );
1355 End Validate_item_owner;
1356 -------------------------------------------------------------------------
1357 -- Function
1358 -- is_it_status_control_attr
1359 --
1360 --
1361 --------------------------------------------------------------------------
1362
1363 FUNCTION is_it_status_control_attr(p_attribute_name IN VARCHAR2)
1364 RETURN VARCHAR2
1365 IS
1366
1367 CURSOR c_get_status_code(p_attribute_name IN VARCHAR2)
1368 IS
1369 SELECT status_control_code
1370 FROM mtl_item_attributes
1371 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.'||p_attribute_name;
1372
1373 l_status_code NUMBER :=0 ;
1374
1375 l_return_flag VARCHAR2(1) := 'N';
1376
1377 BEGIN
1378
1379 OPEN c_get_status_code(p_attribute_name);
1380 FETCH c_get_status_code INTO l_status_code;
1381 CLOSE c_get_status_code;
1382
1383 IF l_status_code = 1 THEN
1384 l_return_flag := 'Y';
1385 ELSE
1386 l_return_flag :='N';
1387 END IF;
1388
1389 RETURN l_return_flag;
1390
1391 End is_it_status_control_attr;
1392
1393 PROCEDURE addProdAttr(
1394 p_template_id IN NUMBER
1395 ,l_inv_item_rec_in IN OUT NOCOPY INV_Item_GRP.Item_Rec_Type
1396 ,x_return_status OUT NOCOPY VARCHAR2)
1397
1398 IS
1399
1400 CURSOR get_product_attr ( p_template_id IN NUMBER)-- p_parent_attribute_code IN VARCHAR2)
1401 IS
1402 SELECT attribute_code,default_flag,parent_select_all
1403 FROM ams_prod_template_attr
1404 WHERE template_id = p_template_id ;
1405
1406 l_get_product_attr get_product_attr%ROWTYPE;
1407
1408
1409 --- bug 3544835 Fix Start
1410
1411 -- inanaiah: bug fix 4956191 - sql id 14421965
1412 /*CURSOR get_coverage_template(l_item_type varchar2)
1413 IS
1414 SELECT id
1415 FROM oks_coverage_templts_v
1416 WHERE item_type = l_item_type;
1417 */
1418 -- inanaiah: bug fix 5191150 - cleb.lse_id is a NUMBER so decode l_item_type
1419 CURSOR get_coverage_template(l_item_type varchar2)
1420 IS
1421 SELECT cleb.id id
1422 FROM okc_k_lines_b cleb ,
1423 okc_k_lines_tl clet
1424 WHERE cleb.chr_id < 0
1425 AND cleb.lse_id IN (2,15,65,66)
1426 AND clet.id = cleb.id
1427 AND clet.language = userenv('LANG')
1428 AND cleb.lse_id = decode(l_item_type,'SERVICE',2,'WARRANTY',15,'USAGE',66)
1429 UNION
1430 SELECT cleb.id id
1431 FROM oks_subscr_header_v cleb
1432 WHERE cleb.dnz_chr_id = -1
1433 AND 'SUBSCRIPTION' = l_item_type;
1434
1435 l_coverage_template_id get_coverage_template%ROWTYPE;
1436
1437 -- inanaiah: bug fix 4956191 - sql id 14421999
1438 /*
1439 CURSOR get_duration_period
1440 IS
1441 SELECT uom_code
1442 FROM mtl_all_primary_uoms_vv
1443 WHERE inventory_item_id = 0
1444 AND UOM_CLASS = Fnd_profile.value('TIME_UOM_CLASS');
1445 */
1446 CURSOR get_duration_period
1447 IS
1448 SELECT DISTINCT UOMT.uom_code
1449 FROM MTL_UOM_CONVERSIONS CONV , MTL_UNITS_OF_MEASURE_TL UOMT
1450 WHERE NVL(CONV.DISABLE_DATE, SYSDATE+1) > SYSDATE
1451 AND CONV.UOM_CODE = UOMT.UOM_CODE
1452 AND UOMT.LANGUAGE = USERENV('LANG')
1453 AND NVL(UOMT.DISABLE_DATE, SYSDATE+1) > SYSDATE
1454 AND CONV.inventory_item_id = 0
1455 AND UOMT.UOM_CLASS = Fnd_profile.value('TIME_UOM_CLASS');
1456
1457 l_duration_period get_duration_period%ROWTYPE;
1458
1459 --bug 3544835 end
1460
1461 --l_inv_item_rec_in Item_Rec_type;
1462
1463 BEGIN
1464
1465 -- Initialize API return status to SUCCESS
1466 x_return_status := FND_API.G_RET_STS_SUCCESS;
1467
1468 --bug 3544835 fix start
1469
1470 OPEN get_duration_period;
1471 FETCH get_duration_period INTO l_duration_period;
1472 CLOSE get_duration_period;
1473
1474 -- bug 3544835 fix end
1475
1476 OPEN get_product_attr(p_template_id);
1477 LOOP
1478
1479 FETCH get_product_attr INTO l_get_product_attr;
1480 EXIT WHEN get_product_attr%NOTFOUND;
1481
1482 IF l_get_product_attr.attribute_code = 'AMS_PROD_INV_ITM'
1483 AND (l_get_product_attr.default_flag = 'Y'
1484 OR l_get_product_attr.parent_select_all = 'Y')
1485 THEN
1486 l_inv_item_rec_in.inventory_item_flag := 'Y';
1487
1488 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_STK'
1489 AND (l_get_product_attr.default_flag = 'Y'
1490 OR l_get_product_attr.parent_select_all = 'Y')
1491 THEN
1492 l_inv_item_rec_in.stock_enabled_flag := 'Y';
1493
1494 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_TRN'
1495 AND (l_get_product_attr.default_flag = 'Y'
1496 OR l_get_product_attr.parent_select_all = 'Y')
1497 THEN
1498 l_inv_item_rec_in.mtl_transactions_enabled_flag := 'Y';
1499
1500 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_REV_CNTRL'
1501 AND (l_get_product_attr.default_flag = 'Y'
1502 OR l_get_product_attr.parent_select_all = 'Y')
1503 THEN
1504 l_inv_item_rec_in.revision_qty_control_code := 2;
1505
1506 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_BOA'
1507 AND (l_get_product_attr.default_flag = 'Y'
1508 OR l_get_product_attr.parent_select_all = 'Y')
1509 THEN
1510 l_inv_item_rec_in.bom_enabled_flag := 'Y';
1511 /*
1512 ELSIF l_get_product_attr.attribute_code = 'AMS_PICK_COMPONENT'
1513 AND (l_get_product_attr.default_flag = 'Y'
1514 OR l_get_product_attr.parent_select_all = 'Y')
1515 THEN
1516 l_inv_item_rec_in.pick_components_flag := 'Y';
1517 */
1518 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_COST'
1519 AND (l_get_product_attr.default_flag = 'Y'
1520 OR l_get_product_attr.parent_select_all = 'Y')
1521 THEN
1522 l_inv_item_rec_in.costing_enabled_flag := 'Y';
1523
1524 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_COLL_ITM'
1525 AND (l_get_product_attr.default_flag = 'Y'
1526 OR l_get_product_attr.parent_select_all = 'Y')
1527 THEN
1528 l_inv_item_rec_in.collateral_flag := 'Y';
1529
1530 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_ELEC'
1531 AND (l_get_product_attr.default_flag = 'Y'
1532 OR l_get_product_attr.parent_select_all = 'Y')
1533 THEN
1534 l_inv_item_rec_in.electronic_flag := 'Y';
1535
1536 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_DOWN'
1537 AND (l_get_product_attr.default_flag = 'Y'
1538 OR l_get_product_attr.parent_select_all = 'Y')
1539 THEN
1540 l_inv_item_rec_in.downloadable_flag := 'Y';
1541
1542 ELSIF l_get_product_attr.attribute_code = 'AMS_OM_INDIVISIBLE'
1543 AND (l_get_product_attr.default_flag = 'Y'
1544 OR l_get_product_attr.parent_select_all = 'Y')
1545 THEN
1546 l_inv_item_rec_in.indivisible_flag := 'Y';
1547 /*
1548 ELSIF l_get_product_attr.attribute_code = 'AMS_UNIT_WEIGHT'
1549 AND (l_get_product_attr.default_flag = 'Y'
1550 OR l_get_product_attr.parent_select_all = 'Y')
1551 THEN
1552 l_inv_item_rec_in.unit_weight := 'Y';
1553
1554 ELSIF l_get_product_attr.attribute_code = 'AMS_PRODUCT_WEIGHT'
1555 AND (l_get_product_attr.default_flag = 'Y'
1556 OR l_get_product_attr.parent_select_all = 'Y')
1557 THEN
1558 l_inv_item_rec_in.weight_uom_code := 'Y';
1559 */
1560 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_CUST_O'
1561 AND (l_get_product_attr.default_flag = 'Y'
1562 OR l_get_product_attr.parent_select_all = 'Y')
1563 THEN
1564 l_inv_item_rec_in.customer_order_flag := 'Y';
1565
1566 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_CUST_O_ENBL'
1567 AND (l_get_product_attr.default_flag = 'Y'
1568 OR l_get_product_attr.parent_select_all = 'Y')
1569 THEN
1570 l_inv_item_rec_in.customer_order_enabled_flag := 'Y';
1571
1572 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_INT_O'
1573 AND (l_get_product_attr.default_flag = 'Y'
1574 OR l_get_product_attr.parent_select_all = 'Y')
1575 THEN
1576 l_inv_item_rec_in.internal_order_flag := 'Y';
1577
1578 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_INT_O_ENBL'
1579 AND (l_get_product_attr.default_flag = 'Y'
1580 OR l_get_product_attr.parent_select_all = 'Y')
1581 THEN
1582 l_inv_item_rec_in.internal_order_enabled_flag := 'Y';
1583
1584 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_SHP'
1585 AND (l_get_product_attr.default_flag = 'Y'
1586 OR l_get_product_attr.parent_select_all = 'Y')
1587 THEN
1588 l_inv_item_rec_in.shippable_item_flag := 'Y';
1589
1590 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_RET'
1591 AND (l_get_product_attr.default_flag = 'Y'
1592 OR l_get_product_attr.parent_select_all = 'Y')
1593 THEN
1594 l_inv_item_rec_in.returnable_flag := 'Y';
1595
1596 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_ACT_REQ'
1597 AND (l_get_product_attr.default_flag = 'Y'
1598 OR l_get_product_attr.parent_select_all = 'Y')
1599 THEN
1600 l_inv_item_rec_in.comms_activation_reqd_flag := 'Y';
1601
1602 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_ASSEMBLE'
1603 AND (l_get_product_attr.default_flag = 'Y'
1604 OR l_get_product_attr.parent_select_all = 'Y')
1605 THEN
1606 l_inv_item_rec_in.replenish_to_order_flag := 'Y';
1607
1608 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_INV'
1609 AND (l_get_product_attr.default_flag = 'Y'
1610 OR l_get_product_attr.parent_select_all = 'Y')
1611 THEN
1612 l_inv_item_rec_in.invoiceable_item_flag := 'Y';
1613
1614 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_INV_ENBL'
1615 AND (l_get_product_attr.default_flag = 'Y'
1616 OR l_get_product_attr.parent_select_all = 'Y')
1617 THEN
1618 l_inv_item_rec_in.invoice_enabled_flag := 'Y';
1619
1620 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_SUPP_SRV'
1621 AND (l_get_product_attr.default_flag = 'Y'
1622 OR l_get_product_attr.parent_select_all = 'Y')
1623 THEN
1624 --l_inv_item_rec_in.service_item_flag := 'Y';
1625 l_inv_item_rec_in.contract_item_type_code := 'SERVICE';
1626
1627 --bug 3544835 start
1628 OPEN get_coverage_template(l_inv_item_rec_in.contract_item_type_code);
1629 FETCH get_coverage_template INTO l_coverage_template_id;
1630 CLOSE get_coverage_template;
1631
1632 l_inv_item_rec_in.coverage_schedule_id := l_coverage_template_id.id;
1633 l_inv_item_rec_in.service_duration := 0; ---duration value
1634 l_inv_item_rec_in.service_duration_period_code := l_duration_period.uom_code; ---duration period
1635
1636 --bug 3544835 end
1637
1638 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_DEFTR'
1639 AND (l_get_product_attr.default_flag = 'Y'
1640 OR l_get_product_attr.parent_select_all = 'Y')
1641 THEN
1642 l_inv_item_rec_in.defect_tracking_on_flag := 'Y';
1643
1644 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_SRP'
1645 AND (l_get_product_attr.default_flag = 'Y'
1646 OR l_get_product_attr.parent_select_all = 'Y')
1647 THEN
1648 l_inv_item_rec_in.serviceable_product_flag := 'Y';
1649
1650 ELSIF l_get_product_attr.attribute_code = 'AMS_INSTALL_BASE'
1651 AND (l_get_product_attr.default_flag = 'Y'
1652 OR l_get_product_attr.parent_select_all = 'Y')
1653 THEN
1654 l_inv_item_rec_in.comms_nl_trackable_flag := 'Y';
1655
1656
1657 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_WARRN'
1658 AND (l_get_product_attr.default_flag = 'Y'
1659 OR l_get_product_attr.parent_select_all = 'Y')
1660 THEN
1661 --l_inv_item_rec_in.vendor_warranty_flag := 'Y';
1662 l_inv_item_rec_in.contract_item_type_code := 'WARRANTY';
1663
1664 -- bug 3544835 fix start
1665
1666 OPEN get_coverage_template(l_inv_item_rec_in.contract_item_type_code);
1667 FETCH get_coverage_template INTO l_coverage_template_id;
1668 CLOSE get_coverage_template;
1669
1670 l_inv_item_rec_in.coverage_schedule_id := l_coverage_template_id.id;
1671 l_inv_item_rec_in.service_duration := 0; ---duration value
1672 l_inv_item_rec_in.service_duration_period_code := l_duration_period.uom_code; ---duration period
1673
1674 -- bug 3544835 fix end
1675
1676 ELSIF l_get_product_attr.attribute_code = 'AMS_SUBSCRIPTION_DEPENDENCY'
1677 AND (l_get_product_attr.default_flag = 'Y'
1678 OR l_get_product_attr.parent_select_all = 'Y')
1679 THEN
1680 l_inv_item_rec_in.subscription_depend_flag := 'Y';
1681 l_inv_item_rec_in.contract_item_type_code := 'SUBSCRIPTION';
1682
1683 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_ORDWB'
1684 AND (l_get_product_attr.default_flag = 'Y'
1685 OR l_get_product_attr.parent_select_all = 'Y')
1686 THEN
1687 l_inv_item_rec_in.orderable_on_web_flag := 'Y';
1688
1689 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_BACK_ORD'
1690 AND (l_get_product_attr.default_flag = 'Y'
1691 OR l_get_product_attr.parent_select_all = 'Y')
1692 THEN
1693 l_inv_item_rec_in.back_orderable_flag := 'Y';
1694
1695 ELSIF l_get_product_attr.attribute_code = 'AMS_WEB_STATUS'
1696 AND (l_get_product_attr.default_flag = 'Y'
1697 OR l_get_product_attr.parent_select_all = 'Y')
1698 THEN
1699 l_inv_item_rec_in.web_status := 'PUBLISHED'; --thats the default value for web status
1700 /*
1701 ELSIF l_get_product_attr.attribute_code = 'AMS_BILLING_TYPE'
1702 AND (l_get_product_attr.default_flag = 'Y'
1703 OR l_get_product_attr.parent_select_all = 'Y')
1704 THEN
1705 l_inv_item_rec_in.material_billable_flag := 'Y'; -- dropdown
1706
1707 ELSIF l_get_product_attr.attribute_code = 'AMS_CONTRACT_ITEM_TYPE'
1708 AND (l_get_product_attr.default_flag = 'Y'
1709 OR l_get_product_attr.parent_select_all = 'Y')
1710 THEN
1711 l_inv_item_rec_in.contract_item_type_code := 'Y'; -- dropdown
1712
1713 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_DUR_PER'
1714 AND (l_get_product_attr.default_flag = 'Y'
1715 OR l_get_product_attr.parent_select_all = 'Y')
1716 THEN
1717 l_inv_item_rec_in.service_duration_period_code := 'Y'; --dropdown
1718
1719 ELSIF l_get_product_attr.attribute_code = 'AMS_PROD_DUR_VAL'
1720 AND (l_get_product_attr.default_flag = 'Y'
1721 OR l_get_product_attr.parent_select_all = 'Y') -- input field
1722 THEN
1723 l_inv_item_rec_in.service_duration := 'Y';
1724
1725 ELSIF l_get_product_attr.attribute_code = 'AMS_OE_TRANSACTABLE'
1726 AND (l_get_product_attr.default_flag = 'Y'
1727 OR l_get_product_attr.parent_select_all = 'Y') -- input field
1728 THEN
1729 l_inv_item_rec_in.so_transactions_flag := 'Y';
1730
1731 */
1732 END IF;
1733 END LOOP;
1734 CLOSE get_product_attr;
1735
1736 END addProdAttr;
1737
1738 PROCEDURE add_default_attributes(
1739 P_ITEM_REC_In IN ITEM_Rec_Type
1740 , l_inv_item_rec_in IN OUT NOCOPY INV_Item_GRP.Item_Rec_Type
1741 , x_return_status OUT NOCOPY VARCHAR2)
1742 IS
1743
1744
1745 l_responsibility_id NUMBER := FND_GLOBAL.resp_id;
1746
1747 CURSOR c_get_template_id ( p_resp_id IN NUMBER
1748 , p_flag IN VARCHAR2)
1749 IS
1750 SELECT tr.template_id
1751 FROM ams_templ_responsibility tr
1752 , ams_prod_templates_b tb
1753 WHERE responsibility_id = p_resp_id
1754 AND tr.template_id = tb.template_id
1755 AND tb.product_service_flag = p_flag;
1756
1757 l_template_id NUMBER := -1;
1758 l_flag VARCHAR2(1) :='X'; --defaulting some x value
1759
1760
1761 BEGIN
1762
1763 -- Initialize API return status to SUCCESS
1764 x_return_status := FND_API.G_RET_STS_SUCCESS;
1765
1766 IF (AMS_DEBUG_HIGH_ON) THEN
1767
1768
1769
1770 AMS_UTILITY_PVT.debug_message('IN the add_default_attributes');
1771
1772 END IF;
1773
1774 IF (( p_item_rec_in.inventory_item_flag <> FND_API.G_MISS_CHAR
1775 AND p_item_rec_in.inventory_item_flag IS NOT NULL
1776 AND p_item_rec_in.inventory_item_flag = 'Y')
1777 OR ( p_item_rec_in.service_item_flag <> FND_API.G_MISS_CHAR
1778 AND p_item_rec_in.service_item_flag IS NOT NULL
1779 AND p_item_rec_in.service_item_flag = 'N'))
1780 THEN
1781 l_flag := 'P';
1782 ELSIF ((p_item_rec_in.service_item_flag <> FND_API.G_MISS_CHAR
1783 AND p_item_rec_in.service_item_flag IS NOT NULL
1784 AND p_item_rec_in.service_item_flag = 'Y')
1785 OR ( p_item_rec_in.inventory_item_flag <> FND_API.G_MISS_CHAR
1786 AND p_item_rec_in.inventory_item_flag IS NOT NULL
1787 AND p_item_rec_in.inventory_item_flag = 'N'))
1788 THEN
1789 l_flag := 'S';
1790 END IF;
1791
1792 OPEN c_get_template_id(l_responsibility_id, l_flag);
1793 FETCH c_get_template_id INTO l_template_id;
1794 CLOSE c_get_template_id;
1795
1796 IF l_template_id = -1
1797 AND l_flag = 'P'
1798 THEN
1799 l_template_id := 1000 ; --seeded template for product
1800 ELSIF l_template_id = -1
1801 AND l_flag ='S'
1802 THEN
1803 l_template_id := 1001 ; --seeded template for service
1804 END IF;
1805
1806 IF (AMS_DEBUG_HIGH_ON) THEN
1807
1808
1809
1810 Ams_Utility_Pvt.debug_message(' from the add_default_attributes the tempId :'||l_template_id||' the flag :'||l_flag);
1811
1812 END IF;
1813
1814 addProdAttr(l_template_id,l_inv_item_rec_in,x_return_status);
1815
1816 END add_default_attributes;
1817
1818
1819 PROCEDURE init_inv_item_rec(
1820 p_item_rec_in IN Item_Rec_Type
1821 ,l_inv_item_rec_in IN OUT NOCOPY INV_Item_GRP.Item_Rec_Type)
1822
1823 IS
1824
1825 BEGIN
1826
1827 IF (p_item_rec_in.costing_enabled_flag IS NOT NULL
1828 AND p_item_rec_in.costing_enabled_flag <> FND_API.G_MISS_CHAR )
1829 THEN
1830 l_inv_item_rec_in.costing_enabled_flag := p_item_rec_in.costing_enabled_flag;
1831 END IF;
1832
1833 IF (p_item_rec_in.collateral_flag IS NOT NULL
1834 AND p_item_rec_in.collateral_flag <> FND_API.G_MISS_CHAR )
1835 THEN
1836 l_inv_item_rec_in.collateral_flag := p_item_rec_in.collateral_flag ;
1837 END IF;
1838
1839 IF (p_item_rec_in.customer_order_flag IS NOT NULL
1840 AND p_item_rec_in.customer_order_flag <> FND_API.G_MISS_CHAR )
1841 THEN
1842 l_inv_item_rec_in.customer_order_flag := p_item_rec_in.customer_order_flag ;
1843 END IF;
1844
1845 IF (p_item_rec_in.customer_order_enabled_flag IS NOT NULL
1846 AND p_item_rec_in.customer_order_enabled_flag <> FND_API.G_MISS_CHAR )
1847 THEN
1848 l_inv_item_rec_in.customer_order_enabled_flag := p_item_rec_in.customer_order_enabled_flag ;
1849 END IF;
1850
1851 IF (p_item_rec_in.shippable_item_flag IS NOT NULL
1852 AND p_item_rec_in.shippable_item_flag <> FND_API.G_MISS_CHAR )
1853 THEN
1854 l_inv_item_rec_in.shippable_item_flag := p_item_rec_in.shippable_item_flag ;
1855 END IF;
1856
1857 IF (p_item_rec_in.event_flag IS NOT NULL
1858 AND p_item_rec_in.event_flag <> FND_API.G_MISS_CHAR )
1859 THEN
1860 l_inv_item_rec_in.event_flag := p_item_rec_in.event_flag ;
1861 END IF;
1862
1863 IF (p_item_rec_in.inventory_item_status_code IS NOT NULL
1864 AND p_item_rec_in.inventory_item_status_code <> FND_API.G_MISS_CHAR )
1865 THEN
1866 l_inv_item_rec_in.inventory_item_status_code := p_item_rec_in.inventory_item_status_code;
1867 END IF;
1868
1869 IF (p_item_rec_in.stock_enabled_flag IS NOT NULL
1870 AND p_item_rec_in.stock_enabled_flag <> FND_API.G_MISS_CHAR )
1871 THEN
1872 l_inv_item_rec_in.stock_enabled_flag := p_item_rec_in.stock_enabled_flag ;
1873 END IF;
1874
1875 IF (p_item_rec_in.mtl_transactions_enabled_flag IS NOT NULL
1876 AND p_item_rec_in.mtl_transactions_enabled_flag <> FND_API.G_MISS_CHAR )
1877 THEN
1878 l_inv_item_rec_in.mtl_transactions_enabled_flag := p_item_rec_in.mtl_transactions_enabled_flag ;
1879 END IF;
1880
1881 IF (p_item_rec_in.bom_enabled_flag IS NOT NULL
1882 AND p_item_rec_in.bom_enabled_flag <> FND_API.G_MISS_CHAR )
1883 THEN
1884 l_inv_item_rec_in.bom_enabled_flag := p_item_rec_in.bom_enabled_flag ;
1885 END IF;
1886
1887 IF (p_item_rec_in.bom_item_type IS NOT NULL
1888 AND p_item_rec_in.bom_item_type <> FND_API.G_MISS_NUM)
1889 THEN
1890 l_inv_item_rec_in.bom_item_type := p_item_rec_in.bom_item_type ;
1891 END IF;
1892
1893 IF (p_item_rec_in.electronic_flag IS NOT NULL
1894 AND p_item_rec_in.electronic_flag <> FND_API.G_MISS_CHAR )
1895 THEN
1896 l_inv_item_rec_in.electronic_flag := p_item_rec_in.electronic_flag ;
1897 END IF;
1898
1899 IF (p_item_rec_in.downloadable_flag IS NOT NULL
1900 AND p_item_rec_in.downloadable_flag <> FND_API.G_MISS_CHAR )
1901 THEN
1902 l_inv_item_rec_in.downloadable_flag := p_item_rec_in.downloadable_flag ;
1903 END IF;
1904
1905 IF (p_item_rec_in.internal_order_flag IS NOT NULL
1906 AND p_item_rec_in.internal_order_flag <> FND_API.G_MISS_CHAR )
1907 THEN
1908 l_inv_item_rec_in.internal_order_flag := p_item_rec_in.internal_order_flag ;
1909 END IF;
1910
1911 IF (p_item_rec_in.internal_order_enabled_flag IS NOT NULL
1912 AND p_item_rec_in.internal_order_enabled_flag <> FND_API.G_MISS_CHAR )
1913 THEN
1914 l_inv_item_rec_in.internal_order_enabled_flag := p_item_rec_in.internal_order_enabled_flag ;
1915 END IF;
1916
1917 IF (p_item_rec_in.returnable_flag IS NOT NULL
1918 AND p_item_rec_in.returnable_flag <> FND_API.G_MISS_CHAR )
1919 THEN
1920 l_inv_item_rec_in.returnable_flag := p_item_rec_in.returnable_flag ;
1921 END IF;
1922 /*
1923 IF (p_item_rec_in.service_item_flag IS NOT NULL
1924 AND p_item_rec_in.service_item_flag <> FND_API.G_MISS_CHAR )
1925 THEN
1926 l_inv_item_rec_in.service_item_flag := p_item_rec_in.service_item_flag ;
1927 END IF;
1928 */
1929
1930 IF (p_item_rec_in.serviceable_product_flag IS NOT NULL
1931 AND p_item_rec_in.serviceable_product_flag <> FND_API.G_MISS_CHAR )
1932 THEN
1933 l_inv_item_rec_in.serviceable_product_flag := p_item_rec_in.serviceable_product_flag ;
1934 END IF;
1935
1936 IF (p_item_rec_in.defect_tracking_on_flag IS NOT NULL
1937 AND p_item_rec_in.defect_tracking_on_flag <> FND_API.G_MISS_CHAR )
1938 THEN
1939 l_inv_item_rec_in.defect_tracking_on_flag := p_item_rec_in.defect_tracking_on_flag ;
1940 END IF;
1941
1942 IF (p_item_rec_in.orderable_on_web_flag IS NOT NULL
1943 AND p_item_rec_in.orderable_on_web_flag <> FND_API.G_MISS_CHAR )
1944 THEN
1945 l_inv_item_rec_in.orderable_on_web_flag := p_item_rec_in.orderable_on_web_flag ;
1946 END IF;
1947
1948 IF (p_item_rec_in.back_orderable_flag IS NOT NULL
1949 AND p_item_rec_in.back_orderable_flag <> FND_API.G_MISS_CHAR )
1950 THEN
1951 l_inv_item_rec_in.back_orderable_flag := p_item_rec_in.back_orderable_flag ;
1952 END IF;
1953
1954 IF (p_item_rec_in.comms_nl_trackable_flag IS NOT NULL
1955 AND p_item_rec_in.comms_nl_trackable_flag <> FND_API.G_MISS_CHAR )
1956 THEN
1957 l_inv_item_rec_in.comms_nl_trackable_flag := p_item_rec_in.comms_nl_trackable_flag ;
1958 END IF;
1959
1960 IF (p_item_rec_in.contract_item_type_code IS NOT NULL
1961 AND p_item_rec_in.contract_item_type_code <> FND_API.G_MISS_CHAR )
1962 THEN
1963 l_inv_item_rec_in.contract_item_type_code := p_item_rec_in.contract_item_type_code ;
1964 END IF;
1965
1966
1967 IF (p_item_rec_in.web_status IS NOT NULL
1968 AND p_item_rec_in.web_status <> FND_API.G_MISS_CHAR )
1969 THEN
1970 l_inv_item_rec_in.web_status := p_item_rec_in.web_status ;
1971 END IF;
1972
1973 IF (p_item_rec_in.indivisible_flag IS NOT NULL
1974 AND p_item_rec_in.indivisible_flag <> FND_API.G_MISS_CHAR )
1975 THEN
1976 l_inv_item_rec_in.indivisible_flag := p_item_rec_in.indivisible_flag ;
1977 END IF;
1978
1979 IF (p_item_rec_in.revision_qty_control_code IS NOT NULL
1980 AND p_item_rec_in.revision_qty_control_code <> FND_API.G_MISS_NUM )
1981 THEN
1982 l_inv_item_rec_in.revision_qty_control_code := p_item_rec_in.revision_qty_control_code ;
1983 END IF;
1984
1985 IF (p_item_rec_in.so_transactions_flag IS NOT NULL
1986 AND p_item_rec_in.so_transactions_flag <> FND_API.G_MISS_CHAR )
1987 THEN
1988 l_inv_item_rec_in.so_transactions_flag := p_item_rec_in.so_transactions_flag ;
1989 END IF;
1990
1991
1992 END init_inv_item_rec;
1993
1994 End AMS_ITEM_OWNER_PVT;