[Home] [Help]
PACKAGE BODY: APPS.INV_ABC_ASSIGNMENTS_PVT
Source
1 PACKAGE BODY inv_abc_assignments_pvt AS
2 /* $Header: INVVAASB.pls 120.0.12020000.1 2013/02/14 15:04:58 ksaripal noship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_ABC_Assignments_PVT';
6 is_debug BOOLEAN := TRUE;
7 PROCEDURE Mydebug(msg IN VARCHAR2)
8 IS
9 BEGIN
10 inv_log_util.Trace(msg, g_pkg_name, 9);
11 --Dbms_Output.put_line(msg);
12 END mydebug;
13
14 /* **
15 Procedure : create_abc_assignments
16 This procedure is to validate and check whether to insert/update
17 for the input values assignment_group_id ,abc_class_id and item_id combination
18 ** */
19
20 PROCEDURE Create_abc_assignments (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
21 p_commit IN VARCHAR2 := fnd_api.g_false,
22 p_api_version_number IN NUMBER,
23 p_assignment_group_id IN NUMBER,
24 p_inventory_item_id IN NUMBER,
25 p_abc_class_id IN NUMBER,
26 x_return_status OUT nocopy VARCHAR2,
27 x_msg_count OUT nocopy NUMBER,
28 x_msg_data OUT nocopy VARCHAR2)
29 IS
30
31 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'), 0);
32
33 l_abc_class_id NUMBER;
34 l_abc_classid_old NUMBER;
35 l_org_id mtl_abc_assignment_groups.organization_id%TYPE;
36 l_item_id mtl_abc_assignments.inventory_item_id%TYPE;
37 l_assignment_group_id mtl_abc_assignments.assignment_group_id%TYPE;
38 l_last_updated_by mtl_abc_assignments.last_updated_by%TYPE;
39 l_last_update_date mtl_abc_assignments.last_update_date%TYPE;
40 l_created_by mtl_abc_assignments.created_by%TYPE;
41 l_creation_date mtl_abc_assignments.creation_date%TYPE;
42
43 l_api_version_number CONSTANT NUMBER := 1.0;
44 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ABC_ASSIGNMENTS';
45
46 CURSOR check_assignment_group (
47 p_assignment_group_id NUMBER) IS
48 SELECT assignment_group_id,
49 organization_id
50 FROM mtl_abc_assignment_groups
51 WHERE assignment_group_id = p_assignment_group_id;
52
53 CURSOR check_item_exists (
54 p_item_id NUMBER,
55 p_org_id NUMBER) IS
56 SELECT inventory_item_id
57 FROM mtl_system_items_fvl
58 WHERE inventory_item_id = p_item_id
59 AND organization_id = p_org_id
60 AND enabled_flag = 'Y'
61 AND trunc(SYSDATE) between nvl(trunc(start_date_active),trunc(SYSDATE)) and nvl(trunc(end_date_active),trunc(SYSDATE)) ;
62
63 CURSOR check_abc_group_class_id (
64 p_assignment_group_id NUMBER,
65 p_abc_class_id NUMBER) IS
66 SELECT abc_class_id
67 FROM mtl_abc_assgn_group_classes
68 WHERE assignment_group_id = p_assignment_group_id
69 AND abc_class_id = p_abc_class_id;
70
71 CURSOR chk_abc_assignments (
72 p_item_id NUMBER,
73 p_assignment_group_id NUMBER) IS
74 SELECT abc_class_id
75 FROM mtl_abc_assignments
76 WHERE inventory_item_id = p_item_id
77 AND assignment_group_id = p_assignment_group_id;
78 BEGIN
79 IF ( l_debug = 1 ) THEN
80 Mydebug(l_api_name ||' : Begin create abc assignments API');
81 END IF;
82
83 -- Initialize message list.
84 IF FND_API.to_Boolean(p_init_msg_list) THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87
88 l_item_id := p_inventory_item_id;
89 l_abc_class_id := p_abc_class_id;
90 l_assignment_group_id := p_assignment_group_id;
91
92 IF ( l_assignment_group_id IS NULL ) THEN
93 IF ( l_debug = 1 ) THEN
94 Mydebug(l_api_name ||' Error : ABC Assignment Group id is null');
95 END IF;
96 fnd_message.Set_name('INV', 'INV_INVALID_PARAMETER_TYPE'); -- Need to create
97 fnd_message.set_token('VALUE1', 'Assignment Group id');
98 -- error when group id is null
99 fnd_msg_pub.ADD;
100 RAISE fnd_api.g_exc_error;
101 END IF;
102
103 IF ( l_item_id IS NULL ) THEN
104 IF ( l_debug = 1 ) THEN
105 Mydebug(l_api_name ||' Error : Inventory Item id is null');
106 END IF;
107 fnd_message.Set_name('INV', 'INV_INVALID_PARAMETER_TYPE');
108 fnd_message.set_token('VALUE1', 'Inventory item id');
109 -- error when item id is null
110 fnd_msg_pub.ADD;
111 RAISE fnd_api.g_exc_error;
112 END IF;
113
114 IF ( l_abc_class_id IS NULL ) THEN
115 IF ( l_debug = 1 ) THEN
116 Mydebug(l_api_name ||' Error : ABC Class id is null');
117 END IF;
118 fnd_message.Set_name('INV', 'INV_INVALID_PARAMETER_TYPE'); -- Need to create
119 fnd_message.set_token('VALUE1', 'ABC Class id');
120 -- error when class id is null
121 fnd_msg_pub.ADD;
122 RAISE fnd_api.g_exc_error;
123 END IF;
124
125
126 -- check_assignment_group : This cursor checks assignment_group_id
127 -- existing in the system or not
128 OPEN check_assignment_group(l_assignment_group_id);
129 FETCH check_assignment_group INTO l_assignment_group_id, l_org_id;
130
131 IF ( check_assignment_group%NOTFOUND ) THEN
132 CLOSE check_assignment_group;
133 IF ( l_debug = 1 ) THEN
134 Mydebug(l_api_name ||' Error : ABC Group id='|| l_assignment_group_id || ' not defined');
135 END IF;
136 fnd_message.Set_name('INV', 'INV_ABC_GROUP_NOT_EXISTS');
137 fnd_msg_pub.ADD;
138 RAISE fnd_api.g_exc_error;
139 END IF;
140
141 CLOSE check_assignment_group;
142
143
144 -- check_item_exists : This cursor checks whether the item exists in the
145 -- organization of the abc group or not
146 OPEN check_item_exists(l_item_id, l_org_id);
147 FETCH check_item_exists INTO l_item_id;
148
149 IF ( check_item_exists%NOTFOUND ) THEN
150 CLOSE check_item_exists;
151 IF ( l_debug = 1 ) THEN
152 Mydebug(l_api_name ||' Error : Inventory item id='|| l_item_id ||' not exists/active in the organization of ABC group ');
153 END IF;
154 fnd_message.Set_name('INV', 'INV_NO_ITEM_ORG');
155 fnd_msg_pub.ADD;
156 RAISE fnd_api.g_exc_error;
157 END IF;
158
159 CLOSE check_item_exists;
160
161
162 -- check_abc_group_class_id : This cursor checks whether assignment_group_id and
163 -- class_id combination exists in the system or not
164 OPEN check_abc_group_class_id(l_assignment_group_id, l_abc_class_id);
165 FETCH check_abc_group_class_id INTO l_abc_class_id;
166
167 IF ( check_abc_group_class_id%NOTFOUND ) THEN
168 CLOSE check_abc_group_class_id;
169 IF ( l_debug = 1 ) THEN
170 Mydebug(l_api_name ||' Error : ABC Class='|| l_abc_class_id || ' not defined in the ABC Assignment group');
171 END IF;
172 fnd_message.Set_name('INV', 'INV_ABC_ITEM_ASSGN_NO_CLASSES');
173 fnd_msg_pub.ADD;
174 RAISE fnd_api.g_exc_error;
175 END IF;
176
177 CLOSE check_abc_group_class_id;
178
179
180 l_last_updated_by := FND_GLOBAL.USER_ID; --- Needs to be updated
181 l_last_update_date := SYSDATE; -- Needs to be updated
182 l_created_by := FND_GLOBAL.USER_ID; -- Needs to be updated
183 l_creation_date := SYSDATE; -- Needs to be updated
184
185 -- chk_abc_assignments : This cursor checks whether the item is already defined in abc group or not
186 OPEN chk_abc_assignments(l_item_id, l_assignment_group_id);
187 FETCH chk_abc_assignments INTO l_abc_classid_old;
188 IF ( chk_abc_assignments%rowcount = 1 ) THEN
189 IF ( l_debug = 1 ) THEN
190 Mydebug(l_api_name
191 ||' : Item_id :'
192 ||l_item_id
193 ||' AssignmentGroupID :'
194 || l_assignment_group_id
195 ||'combination existing in the system');
196 END IF;
197 IF ( l_abc_classid_old = l_abc_class_id ) THEN
198 IF ( l_debug = 1 ) THEN
199 Mydebug(l_api_name ||' Itemid, ABC group id, ABC class id combination already exists');
200 Mydebug(l_api_name ||' Hence proceeding to process next record');
201 END IF;
202 fnd_message.Set_name('INV', 'INV_DUP');
203 -- this combination is already defined
204 fnd_message.set_token('VALUE1','This combination');
205 fnd_msg_pub.ADD;
206 ELSE
207 IF ( l_debug = 1 ) THEN
208 Mydebug(l_api_name || ' Updating existing record in the system with new class_id :'
209 || l_abc_class_id);
210 END IF;
211
212 update_abc_assignments ( p_assignment_group_id => l_assignment_group_id
213 , p_inventory_item_id => l_item_id
214 , p_abc_class_id => l_abc_class_id
215 , p_last_updated_by => l_last_updated_by
216 , p_last_update_date => l_last_update_date);
217 END IF;
218 ELSIF ( chk_abc_assignments%rowcount = 0 ) THEN
219 IF ( l_debug = 1 ) THEN
220 Mydebug(l_api_name
221 || ' Inserting new record with details '
222 || ' Item_id : ' || l_item_id
223 || ' AssignmentGroupID : ' || l_assignment_group_id
224 || ' Class Id: ' || l_abc_class_id);
225 END IF;
226
227 insert_abc_assignments ( p_assignment_group_id => l_assignment_group_id
228 , p_inventory_item_id => l_item_id
229 , p_abc_class_id => l_abc_class_id
230 , p_last_updated_by => l_last_updated_by
231 , p_last_update_date => l_last_update_date
232 , p_created_by => l_created_by
233 , p_creation_date => l_creation_date);
234 END IF;
235
236 CLOSE chk_abc_assignments;
237
238 FND_MSG_PUB.Count_And_Get
239 ( p_count => x_msg_count,
240 p_data => x_msg_data,
241 p_encoded => FND_API.G_FALSE );
242
243 EXCEPTION
244
245 WHEN fnd_api.g_exc_error THEN
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 FND_MSG_PUB.Count_And_Get
248 (
249 p_count => x_msg_count,
250 p_data => x_msg_data,
251 p_encoded => FND_API.G_FALSE
252 );
253 WHEN OTHERS THEN
254 IF fnd_msg_pub.Check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
255 fnd_msg_pub.Add_exc_msg (g_pkg_name, 'create_abc_assignments');
256 END IF;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
258 FND_MSG_PUB.Count_And_Get
259 (
260 p_count => x_msg_count,
261 p_data => x_msg_data,
262 p_encoded => FND_API.G_FALSE
263 );
264
265 END create_abc_assignments;
266
267 /* **
268 Procedure : Insert_abc_assignments
269 This procedure is to insert the new abc assignment for the input values
270 assignment_group_id ,abc_class_id and item_id combination
271 ** */
272
273 PROCEDURE Insert_abc_assignments ( p_assignment_group_id IN NUMBER
274 , p_inventory_item_id IN NUMBER
275 , p_abc_class_id IN NUMBER
276 , p_last_updated_by IN VARCHAR2
277 , p_last_update_date IN DATE
278 , p_created_by IN VARCHAR2
279 , p_creation_date IN DATE
280 )
281 IS
282 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ABC_ASSIGNMENTS';
283 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'), 0);
284 BEGIN
285 IF ( l_debug = 1 ) THEN
286 Mydebug(l_api_name ||' Inserting new record into MTL_ABC_ASSIGNMENTS ');
287 Mydebug(l_api_name ||' p_inventory_item_id : ' || p_inventory_item_id);
288 Mydebug(l_api_name ||' P_assignment_group_id : ' || p_assignment_group_id);
289 Mydebug(l_api_name ||' p_abc_class_id : ' || p_abc_class_id);
290 END IF;
291
292 INSERT INTO mtl_abc_assignments
293 ( inventory_item_id
294 , assignment_group_id
295 , abc_class_id
296 , last_update_date
297 , last_updated_by
298 , creation_date
299 , created_by )
300 VALUES ( p_inventory_item_id
301 , p_assignment_group_id
302 , p_abc_class_id
303 , p_last_update_date
304 , p_last_updated_by
305 , p_creation_date
306 , p_created_by);
307 EXCEPTION
308
309 WHEN OTHERS THEN
310 IF fnd_msg_pub.Check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
311 fnd_msg_pub.Add_exc_msg (g_pkg_name, 'insert_abc_assignments');
312 END IF;
313
314 END insert_abc_assignments;
315
316
317 /* **
318 Procedure : Update_abc_assignments
319 This procedure is to update the new class id for the existing assignment with
320 the given item and abc group combination
321 ** */
322
323 PROCEDURE Update_abc_assignments ( p_assignment_group_id IN NUMBER
324 , p_inventory_item_id IN NUMBER
325 , p_abc_class_id IN NUMBER
326 , p_last_updated_by IN VARCHAR2
327 , p_last_update_date IN DATE)
328 IS
329 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ABC_ASSIGNMENTS';
330 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'), 0);
331 BEGIN
332 IF ( l_debug = 1 ) THEN
333 Mydebug(l_api_name || ' Updating MTL_ABC_ASSIGNMENTS ');
334 Mydebug(l_api_name || ' inventory_item_id : ' || p_inventory_item_id);
335 Mydebug(l_api_name || ' assignment_group_id : ' || p_assignment_group_id);
336 Mydebug(l_api_name || ' with p_abc_class_id_new : ' || p_abc_class_id);
337 END IF;
338
339 UPDATE mtl_abc_assignments
340 SET abc_class_id = p_abc_class_id,
341 last_updated_by = p_last_updated_by,
342 last_update_date = p_last_update_date
343 WHERE assignment_group_id = p_assignment_group_id
344 AND inventory_item_id = p_inventory_item_id;
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF fnd_msg_pub.Check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
349 fnd_msg_pub.Add_exc_msg (g_pkg_name, 'update_abc_assignments');
350 END IF;
351
352 END update_abc_assignments;
353
354 END INV_ABC_Assignments_PVT;