[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_LOOPS_PKG
Source
1 PACKAGE BODY CSP_PARTS_LOOPS_PKG as
2 /* $Header: csptplpb.pls 115.9 2002/11/26 07:15:34 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_PARTS_LOOPS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PARTS_LOOPS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptplpb.pls';
13
14 PROCEDURE Insert_Row(
15 px_PARTS_LOOP_ID IN OUT NOCOPY NUMBER,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_ORGANIZATION_ID NUMBER,
22 p_PLANNER_CODE VARCHAR2,
23 p_CALCULATION_RULE_ID NUMBER,
24 p_FORECAST_RULE_ID NUMBER,
25 p_PARTS_LOOP_NAME VARCHAR2,
26 p_ATTRIBUTE_CATEGORY VARCHAR2,
27 p_ATTRIBUTE1 VARCHAR2,
28 p_ATTRIBUTE2 VARCHAR2,
29 p_ATTRIBUTE3 VARCHAR2,
30 p_ATTRIBUTE4 VARCHAR2,
31 p_ATTRIBUTE5 VARCHAR2,
32 p_ATTRIBUTE6 VARCHAR2,
33 p_ATTRIBUTE7 VARCHAR2,
34 p_ATTRIBUTE8 VARCHAR2,
35 p_ATTRIBUTE9 VARCHAR2,
36 p_ATTRIBUTE10 VARCHAR2,
37 p_ATTRIBUTE11 VARCHAR2,
38 p_ATTRIBUTE12 VARCHAR2,
39 p_ATTRIBUTE13 VARCHAR2,
40 p_ATTRIBUTE14 VARCHAR2,
41 p_ATTRIBUTE15 VARCHAR2,
42 p_DESCRIPTION VARCHAR2)
43
44 IS
45
46 CURSOR C2 IS SELECT CSP_PARTS_LOOPS_B_S1.nextval FROM sys.dual;
47 BEGIN
48 If (px_PARTS_LOOP_ID IS NULL) OR (px_PARTS_LOOP_ID = FND_API.G_MISS_NUM) then
49 OPEN C2;
50 FETCH C2 INTO px_PARTS_LOOP_ID;
51 CLOSE C2;
52 End If;
53 INSERT INTO CSP_PARTS_LOOPS_B(
54 PARTS_LOOP_ID,
55 CREATED_BY,
56 CREATION_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATE_LOGIN,
60 ORGANIZATION_ID,
61 PLANNER_CODE,
62 CALCULATION_RULE_ID,
63 FORECAST_RULE_ID,
64 PARTS_LOOP_NAME,
65 ATTRIBUTE_CATEGORY,
66 ATTRIBUTE1,
67 ATTRIBUTE2,
68 ATTRIBUTE3,
69 ATTRIBUTE4,
70 ATTRIBUTE5,
71 ATTRIBUTE6,
72 ATTRIBUTE7,
73 ATTRIBUTE8,
74 ATTRIBUTE9,
75 ATTRIBUTE10,
76 ATTRIBUTE11,
77 ATTRIBUTE12,
78 ATTRIBUTE13,
79 ATTRIBUTE14,
80 ATTRIBUTE15
81 ) VALUES (
82 px_PARTS_LOOP_ID,
83 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
84 decode(p_CREATION_DATE, fnd_api.g_miss_date,to_date(null),p_creation_date),
85 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
86 decode(p_LAST_UPDATE_DATE,fnd_api.g_miss_date,to_date(null),p_last_update_date),
87 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
88 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
89 decode( p_PLANNER_CODE, FND_API.G_MISS_CHAR, NULL, p_PLANNER_CODE),
90 decode( p_CALCULATION_RULE_ID, FND_API.G_MISS_NUM, NULL, p_CALCULATION_RULE_ID),
91 decode( p_FORECAST_RULE_ID, FND_API.G_MISS_NUM, NULL, p_FORECAST_RULE_ID),
92 decode( p_PARTS_LOOP_NAME, FND_API.G_MISS_CHAR, NULL, p_PARTS_LOOP_NAME),
93 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
94 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
95 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
96 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
97 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
98 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
99 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
100 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
101 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
102 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
103 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
104 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
105 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
106 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
107 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
108 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15));
109
110 insert into CSP_PARTS_LOOPS_TL (
111 LAST_UPDATE_DATE,
112 LAST_UPDATE_LOGIN,
113 DESCRIPTION,
114 PARTS_LOOP_ID,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LANGUAGE,
119 SOURCE_LANG)
120 select
121 P_LAST_UPDATE_DATE,
122 P_LAST_UPDATE_LOGIN,
123 P_DESCRIPTION,
124 px_PARTS_LOOP_ID,
125 P_CREATED_BY,
126 P_CREATION_DATE,
127 P_LAST_UPDATED_BY,
128 L.LANGUAGE_CODE,
129 userenv('LANG')
130 from FND_LANGUAGES L
131 where L.INSTALLED_FLAG in ('I', 'B')
132 and not exists
133 (select NULL
134 from CSP_PARTS_LOOPS_TL T
135 where T.PARTS_LOOP_ID = px_PARTS_LOOP_ID
136 and T.LANGUAGE = L.LANGUAGE_CODE);
137
138 End Insert_Row;
139
140 PROCEDURE Update_Row(
141 p_PARTS_LOOP_ID NUMBER,
142 p_CREATED_BY NUMBER,
143 p_CREATION_DATE DATE,
144 p_LAST_UPDATED_BY NUMBER,
145 p_LAST_UPDATE_DATE DATE,
146 p_LAST_UPDATE_LOGIN NUMBER,
147 p_ORGANIZATION_ID NUMBER,
148 p_PLANNER_CODE VARCHAR2,
149 p_CALCULATION_RULE_ID NUMBER,
150 p_FORECAST_RULE_ID NUMBER,
151 p_PARTS_LOOP_NAME VARCHAR2,
152 p_ATTRIBUTE_CATEGORY VARCHAR2,
153 p_ATTRIBUTE1 VARCHAR2,
154 p_ATTRIBUTE2 VARCHAR2,
155 p_ATTRIBUTE3 VARCHAR2,
156 p_ATTRIBUTE4 VARCHAR2,
157 p_ATTRIBUTE5 VARCHAR2,
158 p_ATTRIBUTE6 VARCHAR2,
159 p_ATTRIBUTE7 VARCHAR2,
160 p_ATTRIBUTE8 VARCHAR2,
161 p_ATTRIBUTE9 VARCHAR2,
162 p_ATTRIBUTE10 VARCHAR2,
163 p_ATTRIBUTE11 VARCHAR2,
164 p_ATTRIBUTE12 VARCHAR2,
165 p_ATTRIBUTE13 VARCHAR2,
166 p_ATTRIBUTE14 VARCHAR2,
167 p_ATTRIBUTE15 VARCHAR2,
168 p_DESCRIPTION VARCHAR2)
169
170 IS
171 BEGIN
172 Update CSP_PARTS_LOOPS_B
173 SET
174 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
175 CREATION_DATE = decode(p_CREATION_DATE, fnd_api.g_miss_date,creation_date,p_creation_date),
176 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
177 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE,fnd_api.g_miss_date,last_update_date,p_last_update_date),
178 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
179 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
180 PLANNER_CODE = decode( p_PLANNER_CODE, FND_API.G_MISS_CHAR, PLANNER_CODE, p_PLANNER_CODE),
181 CALCULATION_RULE_ID = decode( p_CALCULATION_RULE_ID, FND_API.G_MISS_NUM, CALCULATION_RULE_ID, p_CALCULATION_RULE_ID),
182 FORECAST_RULE_ID = decode( p_FORECAST_RULE_ID, FND_API.G_MISS_NUM, FORECAST_RULE_ID, p_FORECAST_RULE_ID),
183 PARTS_LOOP_NAME = decode( p_PARTS_LOOP_NAME, FND_API.G_MISS_CHAR, PARTS_LOOP_NAME, p_PARTS_LOOP_NAME),
184 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
185 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
186 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
187 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
188 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
189 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
190 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
191 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
192 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
193 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
194 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
195 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
196 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
197 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
198 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
199 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
200 where PARTS_LOOP_ID = p_PARTS_LOOP_ID;
201
202 If (SQL%NOTFOUND) then
203 RAISE NO_DATA_FOUND;
204 End If;
205
206 update CSP_PARTS_LOOPS_TL set
207 DESCRIPTION = P_DESCRIPTION,
208 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
211 SOURCE_LANG = userenv('LANG')
212 where PARTS_LOOP_ID = P_PARTS_LOOP_ID
213 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218
219 END Update_Row;
220
221 PROCEDURE Delete_Row(
222 p_PARTS_LOOP_ID NUMBER)
223 IS
224 BEGIN
225 DELETE FROM CSP_PARTS_LOOPS_B
226 WHERE PARTS_LOOP_ID = p_PARTS_LOOP_ID;
227 If (SQL%NOTFOUND) then
228 RAISE NO_DATA_FOUND;
229 End If;
230
231 delete from CSP_PARTS_LOOPS_TL
232 where PARTS_LOOP_ID = p_PARTS_LOOP_ID;
233
234 if (sql%notfound) then
235 raise no_data_found;
236 end if;
237
238 --- Remove Master Stocklist
239 delete from csp_mstrstck_lists_itms
240 where parts_loops_id = p_PARTS_LOOP_ID;
241
242 -- Update Subinventory
243 Update csp_sec_inventories
244 Set Parts_loop_id = NULL
245 Where Parts_loop_id = p_PARTS_LOOP_ID;
246
247 END Delete_Row;
248
249 PROCEDURE Lock_Row(
250 p_PARTS_LOOP_ID NUMBER,
251 p_CREATED_BY NUMBER,
252 p_CREATION_DATE DATE,
253 p_LAST_UPDATED_BY NUMBER,
254 p_LAST_UPDATE_DATE DATE,
255 p_LAST_UPDATE_LOGIN NUMBER,
256 p_ORGANIZATION_ID NUMBER,
257 p_PLANNER_CODE VARCHAR2,
258 p_CALCULATION_RULE_ID NUMBER,
259 p_FORECAST_RULE_ID NUMBER,
260 p_PARTS_LOOP_NAME VARCHAR2,
261 p_ATTRIBUTE_CATEGORY VARCHAR2,
262 p_ATTRIBUTE1 VARCHAR2,
263 p_ATTRIBUTE2 VARCHAR2,
264 p_ATTRIBUTE3 VARCHAR2,
265 p_ATTRIBUTE4 VARCHAR2,
266 p_ATTRIBUTE5 VARCHAR2,
267 p_ATTRIBUTE6 VARCHAR2,
268 p_ATTRIBUTE7 VARCHAR2,
269 p_ATTRIBUTE8 VARCHAR2,
270 p_ATTRIBUTE9 VARCHAR2,
271 p_ATTRIBUTE10 VARCHAR2,
272 p_ATTRIBUTE11 VARCHAR2,
273 p_ATTRIBUTE12 VARCHAR2,
274 p_ATTRIBUTE13 VARCHAR2,
275 p_ATTRIBUTE14 VARCHAR2,
276 p_ATTRIBUTE15 VARCHAR2,
277 p_DESCRIPTION VARCHAR2)
278
279 IS
280 CURSOR C IS
281 SELECT *
282 FROM CSP_PARTS_LOOPS_B
283 WHERE PARTS_LOOP_ID = p_PARTS_LOOP_ID
284 FOR UPDATE of PARTS_LOOP_ID NOWAIT;
285
286 cursor c1 is select
287 DESCRIPTION,
288 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
289 from CSP_PARTS_LOOPS_TL
290 where PARTS_LOOP_ID = p_PARTS_LOOP_ID
291 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
292 for update of PARTS_LOOP_ID nowait;
293
294 Recinfo C%ROWTYPE;
295 BEGIN
296 OPEN C;
297 FETCH C INTO Recinfo;
298 If (C%NOTFOUND) then
299 CLOSE C;
300 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
301 APP_EXCEPTION.RAISE_EXCEPTION;
302 End If;
303 CLOSE C;
304 if (
305 ( Recinfo.PARTS_LOOP_ID = p_PARTS_LOOP_ID)
306 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
307 OR ( ( Recinfo.CREATED_BY IS NULL )
308 AND ( p_CREATED_BY IS NULL )))
309 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
310 OR ( ( Recinfo.CREATION_DATE IS NULL )
311 AND ( p_CREATION_DATE IS NULL )))
312 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
313 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
314 AND ( p_LAST_UPDATED_BY IS NULL )))
315 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
316 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
317 AND ( p_LAST_UPDATE_DATE IS NULL )))
318 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
319 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
320 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
321 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
322 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
323 AND ( p_ORGANIZATION_ID IS NULL )))
324 AND ( ( Recinfo.PLANNER_CODE = p_PLANNER_CODE)
325 OR ( ( Recinfo.PLANNER_CODE IS NULL )
326 AND ( p_PLANNER_CODE IS NULL )))
327 AND ( ( Recinfo.CALCULATION_RULE_ID = p_CALCULATION_RULE_ID)
328 OR ( ( Recinfo.CALCULATION_RULE_ID IS NULL )
329 AND ( p_CALCULATION_RULE_ID IS NULL )))
330 AND ( ( Recinfo.FORECAST_RULE_ID = p_FORECAST_RULE_ID)
331 OR ( ( Recinfo.FORECAST_RULE_ID IS NULL )
332 AND ( p_FORECAST_RULE_ID IS NULL )))
333 AND ( ( Recinfo.PARTS_LOOP_NAME = p_PARTS_LOOP_NAME)
334 OR ( ( Recinfo.PARTS_LOOP_NAME IS NULL )
335 AND ( p_PARTS_LOOP_NAME IS NULL )))
336 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
337 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
338 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
339 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
340 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
341 AND ( p_ATTRIBUTE1 IS NULL )))
342 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
343 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
344 AND ( p_ATTRIBUTE2 IS NULL )))
345 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
346 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
347 AND ( p_ATTRIBUTE3 IS NULL )))
348 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
349 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
350 AND ( p_ATTRIBUTE4 IS NULL )))
351 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
352 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
353 AND ( p_ATTRIBUTE5 IS NULL )))
354 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
355 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
356 AND ( p_ATTRIBUTE6 IS NULL )))
357 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
358 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
359 AND ( p_ATTRIBUTE7 IS NULL )))
360 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
364 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
361 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
362 AND ( p_ATTRIBUTE8 IS NULL )))
363 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
365 AND ( p_ATTRIBUTE9 IS NULL )))
366 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
367 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
368 AND ( p_ATTRIBUTE10 IS NULL )))
369 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
370 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
371 AND ( p_ATTRIBUTE11 IS NULL )))
372 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
373 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
374 AND ( p_ATTRIBUTE12 IS NULL )))
375 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
376 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
377 AND ( p_ATTRIBUTE13 IS NULL )))
378 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
379 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
380 AND ( p_ATTRIBUTE14 IS NULL )))
381 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
382 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
383 AND ( p_ATTRIBUTE15 IS NULL )))
384 ) then
385 null;
386 else
387 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
388 APP_EXCEPTION.RAISE_EXCEPTION;
389 End If;
390
391 for tlinfo in c1 loop
392 if (tlinfo.BASELANG = 'Y') then
393 if ( ((tlinfo.DESCRIPTION = p_DESCRIPTION)
394 OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
395 ) then
396 null;
397 else
398 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
399 app_exception.raise_exception;
400 end if;
401 end if;
402 end loop;
403 return;
404 END Lock_Row;
405
406 procedure ADD_LANGUAGE
407 is
408 begin
409 delete from csp_parts_loops_tl T
410 where not exists
411 (select NULL
412 from csp_parts_loops_b B
413 where B.parts_loop_id = T.parts_loop_id
414 );
415
416 update csp_parts_loops_tl T set (
417 DESCRIPTION
418 ) = (select
419 B.DESCRIPTION
420 from csp_parts_loops_tl B
421 where B.parts_loop_id = T.parts_loop_id
422 and B.LANGUAGE = T.SOURCE_LANG)
423 where (
424 T.parts_loop_id,
425 T.LANGUAGE
426 ) in (select
427 SUBT.parts_loop_id,
428 SUBT.LANGUAGE
429 from csp_parts_loops_tl SUBB, csp_parts_loops_tl SUBT
430 where SUBB.parts_loop_id = SUBT.parts_loop_id
431 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
432 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
433 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
434 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
435 ));
436
437 insert into csp_parts_loops_tl (
438 parts_loop_id,
439 CREATED_BY,
440 CREATION_DATE,
441 LAST_UPDATED_BY,
445 LANGUAGE,
442 LAST_UPDATE_DATE,
443 LAST_UPDATE_LOGIN,
444 DESCRIPTION,
446 SOURCE_LANG
447 ) select
448 B.parts_loop_id,
449 B.CREATED_BY,
450 B.CREATION_DATE,
451 B.LAST_UPDATED_BY,
452 B.LAST_UPDATE_DATE,
453 B.LAST_UPDATE_LOGIN,
454 B.DESCRIPTION,
455 L.LANGUAGE_CODE,
456 B.SOURCE_LANG
457 from csp_parts_loops_tl B, FND_LANGUAGES L
458 where L.INSTALLED_FLAG in ('I', 'B')
459 and B.LANGUAGE = userenv('LANG')
460 and not exists
461 (select NULL
462 from csp_parts_loops_tl T
463 where T.parts_loop_id = B.parts_loop_id
464 and T.LANGUAGE = L.LANGUAGE_CODE);
465 end ADD_LANGUAGE;
466
467 PROCEDURE Translate_Row
468 ( p_parts_loop_id IN NUMBER
469 , p_description IN VARCHAR2
470 , p_owner IN VARCHAR2
471 )
472 IS
473 l_user_id NUMBER := 0;
474 BEGIN
475
476 if p_owner = 'SEED' then
477 l_user_id := 1;
478 end if;
479
480 UPDATE csp_parts_loops_tl
481 SET description = p_description
482 , last_update_date = SYSDATE
483 , last_updated_by = l_user_id
484 , last_update_login = 0
485 , source_lang = userenv('LANG')
486 WHERE parts_loop_id = p_parts_loop_id
487 AND userenv('LANG') IN (language, source_lang);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
492 THEN
493 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Translate_Row');
494 END IF;
495 RAISE;
496
497 END Translate_Row;
498
499 PROCEDURE Load_Row
500 ( p_parts_loop_id IN NUMBER
501 , p_description IN VARCHAR2
502 , p_owner IN VARCHAR2
503 )
504 IS
505
506 l_parts_loop_id NUMBER;
507 l_user_id NUMBER := 0;
508
509 BEGIN
510
511 -- assign user ID
512 if p_owner = 'SEED' then
513 l_user_id := 1; --SEED
514 end if;
515
516 BEGIN
517 -- update row if present
518 Update_Row(
519 p_parts_loop_id => p_parts_loop_id,
520 p_CREATED_BY => FND_API.G_MISS_NUM,
521 p_CREATION_DATE => FND_API.G_MISS_DATE,
522 p_LAST_UPDATED_BY => l_user_id,
523 p_LAST_UPDATE_DATE => SYSDATE,
524 p_LAST_UPDATE_LOGIN => 0,
525 p_organization_id => FND_API.G_MISS_NUM,
526 p_planner_code => FND_API.G_MISS_CHAR,
527 p_calculation_rule_id => FND_API.G_MISS_NUM,
528 p_forecast_rule_id => FND_API.G_MISS_NUM,
529 p_parts_loop_name => FND_API.G_MISS_CHAR,
530 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
531 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
532 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
533 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
534 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
535 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
536 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
537 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
538 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
539 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
540 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
541 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
542 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
543 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
544 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
545 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
546 p_DESCRIPTION => p_description);
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 -- insert row
550 Insert_Row(
551 px_parts_loop_id => l_parts_loop_id,
552 p_CREATED_BY => FND_API.G_MISS_NUM,
553 p_CREATION_DATE => FND_API.G_MISS_DATE,
554 p_LAST_UPDATED_BY => l_user_id,
555 p_LAST_UPDATE_DATE => SYSDATE,
556 p_LAST_UPDATE_LOGIN => 0,
557 p_organization_id => FND_API.G_MISS_NUM,
558 p_planner_code => FND_API.G_MISS_CHAR,
559 p_calculation_rule_id => FND_API.G_MISS_NUM,
560 p_forecast_rule_id => FND_API.G_MISS_NUM,
561 p_parts_loop_name => FND_API.G_MISS_CHAR,
562 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
563 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
564 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
565 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
566 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
567 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
568 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
572 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
569 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
570 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
571 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
573 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
574 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
575 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
576 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
577 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
578 p_DESCRIPTION => p_description);
579 END;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
584 THEN
585 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Load_Row');
586 END IF;
587 RAISE;
588
589 END Load_Row;
590
591 End CSP_PARTS_LOOPS_PKG;