[Home] [Help]
PACKAGE BODY: APPS.WIP_REPETITIVE_ENTITY
Source
1 PACKAGE BODY WIP_REPETITIVE_ENTITY as
2 /* $Header: wiprentb.pls 115.9 2002/11/29 15:27:32 rmahidha ship $ */
3
4
5 procedure check_discrete(p_org_id NUMBER,
6 p_entity_name VARCHAR2) is
7
8 temp VARCHAR2(1);
9
10 cursor chck_discrete is
11 select 'x'
12 from WIP_ENTITIES
13 where organization_id = p_org_id
14 and wip_entity_name = p_entity_name
15 and entity_type <> WIP_CONSTANTS.REPETITIVE;
16
17 begin
18 open chck_discrete;
19 fetch chck_discrete into temp;
20 close chck_discrete;
21 if (temp = 'x') then
22 fnd_message.set_name('WIP', 'WIP_DUP_NAME_DISC_REP');
23 app_exception.raise_exception;
24 end if;
25 end check_discrete;
26
27 procedure insert_entity (p_entity_id IN OUT NOCOPY NUMBER,
28 p_org_id NUMBER,
29 p_entity_name VARCHAR2,
30 p_description VARCHAR2,
31 p_primary_id NUMBER,
32 p_user_id NUMBER,
33 p_login_id NUMBER) is
34
35
36 cursor check_entity is
37 select wip_entity_id
38 from WIP_REPETITIVE_ITEMS
39 where organization_id = p_org_id
40 and primary_item_id = p_primary_id;
41
42 CURSOR C4 IS SELECT MTL_GEN_OBJECT_ID_S.nextval FROM DUAL;
43
44 X_Gen_Object_Id NUMBER;
45
46 begin
47
48 open check_entity;
49 fetch check_entity into p_entity_id;
50
51 if check_entity%NOTFOUND then
52 -- create new entry in wip_entities
53 select wip_entities_s.nextval
54 into p_entity_id
55 from dual;
56
57 OPEN C4;
58 FETCH C4 INTO X_Gen_Object_Id;
59 if (C4%NOTFOUND) then
60 CLOSE C4;
61 Raise NO_DATA_FOUND;
62 end if;
63 CLOSE C4;
64
65 insert into wip_entities
66 (wip_entity_id, organization_id,
67 last_update_date, last_updated_by,
68 creation_date, created_by, last_update_login,
69 wip_entity_name, entity_type, description,
70 primary_item_id, gen_object_id)
71 values
72 (p_entity_id, p_org_id,
73 SYSDATE, p_user_id, SYSDATE, p_user_id, p_login_id,
74 substr(p_entity_name, 1, 240),
75 WIP_CONSTANTS.REPETITIVE,
76 p_description, p_primary_id, X_Gen_Object_Id);
77 end if;
78 close check_entity;
79 end insert_entity;
80
81 procedure validate_primary_line (p_entity_id NUMBER,
82 p_line_id NUMBER,
83 p_org_id NUMBER) is
84
85 temp NUMBER;
86 cursor C is
87 select count(primary_line_flag)
88 from WIP_REPETITIVE_ITEMS
89 where wip_entity_id = nvl(p_entity_id, -1)
90 and organization_id = p_org_id
91 and primary_line_flag = 1
92 and line_id <> p_line_id;
93
94 begin
95 temp := 0;
96 open C;
97 fetch C into temp;
98 close C;
99 if (temp <> 0) then
100 fnd_message.set_name('WIP', 'WIP_ONE_LEADTIME_LINE');
101 app_exception.raise_exception;
102 end if;
103
104 end validate_primary_line;
105
106 procedure insert_rep_item(p_rowid IN OUT NOCOPY VARCHAR2,
107 p_wip_entity_id NUMBER,
108 p_line_Id NUMBER,
109 p_organization_id NUMBER,
110 p_primary_item_id NUMBER,
111 p_alternate_bom_designator VARCHAR2,
112 p_alternate_routing_designator VARCHAR2,
113 p_class_code VARCHAR2,
114 p_wip_supply_type NUMBER,
115 p_completion_subinventory VARCHAR2,
116 p_completion_locator_id NUMBER,
117 p_load_distribution_priority NUMBER,
118 p_primary_line_flag NUMBER,
119 p_production_line_rate NUMBER,
120 p_overcompletion_toleran_type NUMBER,
121 p_overcompletion_toleran_value NUMBER,
122 p_attribute_category VARCHAR2,
123 p_attribute1 VARCHAR2,
124 p_attribute2 VARCHAR2,
125 p_attribute3 VARCHAR2,
126 p_attribute4 VARCHAR2,
127 p_attribute5 VARCHAR2,
128 p_attribute6 VARCHAR2,
129 p_attribute7 VARCHAR2,
130 p_attribute8 VARCHAR2,
131 p_attribute9 VARCHAR2,
132 p_attribute10 VARCHAR2,
133 p_attribute11 VARCHAR2,
134 p_attribute12 VARCHAR2,
135 p_attribute13 VARCHAR2,
136 p_attribute14 VARCHAR2,
137 p_attribute15 VARCHAR2,
138 p_user_id NUMBER,
139 p_login_id NUMBER) is
140 CURSOR C IS
141 SELECT rowid
142 FROM WIP_REPETITIVE_ITEMS
143 WHERE WIP_ENTITY_ID = p_wip_entity_id
144 AND organization_id = p_organization_id
145 AND line_id = p_line_id;
146
147
148 begin
149
150 INSERT INTO WIP_REPETITIVE_ITEMS (
151 Wip_Entity_id,
152 Line_Id,
153 Organization_Id,
154 Creation_Date,
155 Created_By,
156 Last_Update_Date,
157 Last_Updated_By,
158 Last_Update_Login,
159 Primary_Item_Id,
160 Alternate_Bom_Designator,
161 Alternate_Routing_Designator,
162 Class_Code,
163 Wip_Supply_Type,
164 Completion_Subinventory,
165 Completion_Locator_Id,
166 Load_Distribution_Priority,
167 Primary_Line_Flag,
168 Production_Line_Rate,
169 Overcompletion_Tolerance_Type,
170 Overcompletion_Tolerance_Value,
171 Attribute_Category,
172 attribute1,
173 attribute2,
174 attribute3,
175 attribute4,
176 attribute5,
177 attribute6,
178 attribute7,
179 attribute8,
180 attribute9,
181 attribute10,
182 attribute11,
183 attribute12,
184 attribute13,
185 attribute14,
186 attribute15
187 ) VALUES ( p_wip_Entity_Id,
188 p_line_Id,
189 p_organization_Id,
190 SYSDATE,
191 p_user_id,
192 SYSDATE,
193 p_user_id,
194 p_login_id,
195 p_primary_item_Id,
196 p_alternate_bom_designator,
197 p_alternate_routing_designator,
198 p_class_code,
199 p_wip_supply_type,
200 p_completion_subinventory,
201 p_completion_locator_id,
202 p_load_distribution_priority,
203 p_primary_line_flag,
204 p_production_line_rate,
205 p_overcompletion_toleran_type,
206 p_overcompletion_toleran_value,
207 p_attribute_category,
208 p_attribute1,
209 p_attribute2,
210 p_attribute3,
211 p_attribute4,
212 p_attribute5,
213 p_attribute6,
214 p_attribute7,
215 p_attribute8,
216 p_attribute9,
217 p_attribute10,
218 p_attribute11,
219 p_attribute12,
220 p_attribute13,
221 p_attribute14,
222 p_attribute15);
223 OPEN C;
224 FETCH C INTO p_rowid;
225 if (C%NOTFOUND) then
226 CLOSE C;
227 RAISE NO_DATA_FOUND;
228 end if;
229 CLOSE C;
230
231 end insert_rep_item;
232
233
234 procedure create_entity(p_rowid IN OUT NOCOPY VARCHAR2,
235 p_wip_entity_id IN OUT NOCOPY NUMBER,
236 p_wip_entity_name VARCHAR2,
237 p_description VARCHAR2,
238 p_line_Id NUMBER,
239 p_organization_id NUMBER,
240 p_primary_item_id NUMBER,
241 p_alternate_bom_designator VARCHAR2,
242 p_alternate_routing_designator VARCHAR2,
243 p_class_code VARCHAR2,
244 p_wip_supply_type NUMBER,
245 p_completion_subinventory VARCHAR2,
246 p_completion_locator_id NUMBER,
247 p_load_distribution_priority NUMBER,
248 p_primary_line_flag NUMBER,
249 p_production_line_rate NUMBER,
250 p_overcompletion_toleran_type NUMBER,
251 p_overcompletion_toleran_value NUMBER,
252 p_attribute_category VARCHAR2,
253 p_attribute1 VARCHAR2,
254 p_attribute2 VARCHAR2,
255 p_attribute3 VARCHAR2,
256 p_attribute4 VARCHAR2,
257 p_attribute5 VARCHAR2,
258 p_attribute6 VARCHAR2,
259 p_attribute7 VARCHAR2,
260 p_attribute8 VARCHAR2,
261 p_attribute9 VARCHAR2,
262 p_attribute10 VARCHAR2,
263 p_attribute11 VARCHAR2,
264 p_attribute12 VARCHAR2,
265 p_attribute13 VARCHAR2,
266 p_attribute14 VARCHAR2,
267 p_attribute15 VARCHAR2) is
268
269 x_user_id NUMBER;
270 x_login_id NUMBER;
271 err_msg VARCHAR(200);
272
273 begin
274 x_user_id := fnd_global.user_id;
275 x_login_id := fnd_global.login_id;
276
277 --- check discrete
278 check_discrete(p_organization_id, p_wip_entity_name);
279
280 -- check if wip_entity exists, if not insert in wip_entities
281 insert_entity(p_wip_entity_id, p_organization_id,
282 p_wip_entity_name, p_description,
283 p_primary_item_id, x_user_id, x_login_id);
284
285 -- validate primary line, now that we are assured of a wip_entity_id
286 if (p_primary_line_flag = 1) then
287 validate_primary_line(p_wip_entity_id, p_line_id,
288 p_organization_id);
289 end if;
290
291 -- insert into wip_rep_items
292 insert_rep_item(p_rowid,
293 p_wip_entity_id,
294 p_line_id,
295 p_organization_id,
296 p_primary_item_id,
297 p_alternate_bom_designator,
298 p_alternate_routing_designator,
299 p_class_code,
300 p_wip_supply_type,
301 p_completion_subinventory,
302 p_completion_locator_id,
303 p_load_distribution_priority,
304 p_primary_line_flag,
305 p_production_line_rate,
306 p_overcompletion_toleran_type,
307 p_overcompletion_toleran_value,
308 p_attribute_category,
309 p_attribute1,
310 p_attribute2,
311 p_attribute3,
312 p_attribute4,
313 p_attribute5,
314 p_attribute6,
315 p_attribute7,
316 p_attribute8,
317 p_attribute9,
318 p_attribute10,
319 p_attribute11,
320 p_attribute12,
321 p_attribute13,
322 p_attribute14,
323 p_attribute15,
324 x_user_id,
325 x_login_id);
326
327 end create_entity;
328
329 procedure delete_entity(p_wip_entity_id IN OUT NOCOPY NUMBER,
330 p_org_id NUMBER,
331 p_rowid VARCHAR2) is
332
333 temp NUMBER;
334 cursor check_last_entity is
335 select count(wip_entity_id)
336 from WIP_REPETITIVE_ITEMS
337 where organization_id = p_org_id
338 and wip_entity_id = p_wip_entity_id;
339
340 begin
341 -- delete from wip_repetitive_items
342 delete from WIP_REPETITIVE_ITEMS
343 where rowid = p_rowid;
344
345 if (SQL%NOTFOUND) then
346 raise NO_DATA_FOUND;
347 end if;
348
349 -- delete from wip_entities if last record
350 open check_last_entity;
351 fetch check_last_entity into temp;
352 close check_last_entity;
353
354 if (temp = 0) then
355 delete from WIP_ENTITIES
356 where organization_id = p_org_id
357 and wip_entity_id = p_wip_entity_id;
358 end if;
359 end delete_entity;
360
361 procedure update_entity(p_rowid VARCHAR2,
362 p_wip_entity_id NUMBER,
363 p_line_Id NUMBER,
364 p_organization_id NUMBER,
365 p_primary_item_id NUMBER,
366 p_alternate_bom_designator VARCHAR2,
367 p_alternate_routing_designator VARCHAR2,
368 p_class_code VARCHAR2,
369 p_wip_supply_type NUMBER,
370 p_completion_subinventory VARCHAR2,
371 p_completion_locator_id NUMBER,
372 p_load_distribution_priority NUMBER,
373 p_primary_line_flag NUMBER,
374 p_production_line_rate NUMBER,
375 p_overcompletion_toleran_type NUMBER,
376 p_overcompletion_toleran_value NUMBER,
377 p_attribute_category VARCHAR2,
378 p_attribute1 VARCHAR2,
379 p_attribute2 VARCHAR2,
380 p_attribute3 VARCHAR2,
381 p_attribute4 VARCHAR2,
382 p_attribute5 VARCHAR2,
383 p_attribute6 VARCHAR2,
384 p_attribute7 VARCHAR2,
385 p_attribute8 VARCHAR2,
386 p_attribute9 VARCHAR2,
387 p_attribute10 VARCHAR2,
388 p_attribute11 VARCHAR2,
389 p_attribute12 VARCHAR2,
390 p_attribute13 VARCHAR2,
391 p_attribute14 VARCHAR2,
392 p_attribute15 VARCHAR2) is
393
394 x_userid NUMBER;
395 x_loginid NUMBER;
396 begin
397
398 x_userid := FND_GLOBAL.USER_ID;
399 x_loginid := FND_GLOBAL.LOGIN_ID;
400
401 -- validate primary line
402 if (p_primary_line_flag = 1) then
403 validate_primary_line(p_wip_entity_id, p_line_id,
404 p_organization_id);
405 end if;
406
407 UPDATE WIP_REPETITIVE_ITEMS
408 SET
409 organization_id = p_organization_id,
410 last_update_date = SYSDATE,
411 last_updated_by = x_userid,
412 last_update_login = x_loginid,
413 primary_item_id = p_primary_item_id,
414 alternate_bom_designator = p_alternate_bom_designator,
415 alternate_routing_designator = p_alternate_routing_designator,
416 class_code = p_class_code,
417 wip_supply_type = p_wip_supply_type,
418 completion_subinventory = p_completion_subinventory,
419 completion_locator_id = p_completion_locator_id,
420 load_distribution_priority = p_load_distribution_priority,
421 primary_line_flag = p_primary_line_flag,
422 production_line_rate = p_production_line_rate,
423 overcompletion_tolerance_type = p_overcompletion_toleran_type,
424 overcompletion_tolerance_value= p_overcompletion_toleran_value,
425 attribute_category = p_attribute_category,
426 attribute1 = p_attribute1,
427 attribute2 = p_attribute2,
428 attribute3 = p_attribute3,
429 attribute4 = p_attribute4,
430 attribute5 = p_attribute5,
431 attribute6 = p_attribute6,
432 attribute7 = p_attribute7,
433 attribute8 = p_attribute8,
434 attribute9 = p_attribute9,
435 attribute10 = p_attribute10,
436 attribute11 = p_attribute11,
437 attribute12 = p_attribute12,
438 attribute13 = p_attribute13,
439 attribute14 = p_attribute14,
440 attribute15 = p_attribute15
441 WHERE rowid = p_rowid;
442
443 if (SQL%NOTFOUND) then
444 Raise NO_DATA_FOUND;
445 end if;
446
447 end update_entity;
448 END WIP_REPETITIVE_ENTITY;