1: PACKAGE BODY MTL_ITEM_CATALOG_GROUPS_UTIL as
2: /* $Header: INVICGUB.pls 120.1 2006/01/19 04:16:12 swshukla noship $ */
3:
4: PROCEDURE INSERT_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE
5: ,X_ROWID OUT NOCOPY ROWID) IS
1: PACKAGE BODY MTL_ITEM_CATALOG_GROUPS_UTIL as
2: /* $Header: INVICGUB.pls 120.1 2006/01/19 04:16:12 swshukla noship $ */
3:
4: PROCEDURE INSERT_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE
5: ,X_ROWID OUT NOCOPY ROWID) IS
6:
7: l_return_status VARCHAR2(1); --Bug 4639946
8: BEGIN
6:
7: l_return_status VARCHAR2(1); --Bug 4639946
8: BEGIN
9:
10: INSERT INTO MTL_ITEM_CATALOG_GROUPS_B (
11: PARENT_CATALOG_GROUP_ID,
12: ITEM_CREATION_ALLOWED_FLAG,
13: ITEM_CATALOG_GROUP_ID,
14: INACTIVE_DATE,
110: P_Catalog_Group_Rec.LAST_UPDATED_BY,
111: P_Catalog_Group_Rec.LAST_UPDATE_LOGIN
112: ) RETURNING ROWID INTO X_ROWID;
113:
114: INSERT INTO MTL_ITEM_CATALOG_GROUPS_TL (
115: ITEM_CATALOG_GROUP_ID,
116: DESCRIPTION,
117: CREATION_DATE,
118: CREATED_BY,
133: USERENV('LANG')
134: FROM FND_LANGUAGES L
135: WHERE L.INSTALLED_FLAG in ('I', 'B')
136: AND NOT EXISTS (SELECT NULL
137: FROM MTL_ITEM_CATALOG_GROUPS_TL T
138: WHERE T.ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
139: AND T.LANGUAGE = L.LANGUAGE_CODE);
140:
141: --Bug: 4639946
153: OUT l_return_status;
154:
155: END INSERT_ROW;
156:
157: PROCEDURE LOCK_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE) IS
158:
159: CURSOR c_get_item_catalog IS
160: SELECT
161: PARENT_CATALOG_GROUP_ID,
201: ATTRIBUTE13,
202: ATTRIBUTE14,
203: ATTRIBUTE15,
204: REQUEST_ID
205: FROM MTL_ITEM_CATALOG_GROUPS_B
206: WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
207: FOR UPDATE OF ITEM_CATALOG_GROUP_ID NOWAIT;
208:
209:
210: CURSOR c_get_description_rec IS
211: SELECT
212: DESCRIPTION,
213: DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
214: FROM MTL_ITEM_CATALOG_GROUPS_TL
215: WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID
216: AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
217: FOR UPDATE OF ITEM_CATALOG_GROUP_ID NOWAIT;
218:
340: app_exception.raise_exception;
341:
342: END LOCK_ROW;
343:
344: PROCEDURE UPDATE_ROW (P_Catalog_Group_Rec IN MTL_ITEM_CATALOG_GROUPS%ROWTYPE) IS
345:
346: l_old_parent_id NUMBER; --Bug: 4639946
347: l_return_status VARCHAR2(1); --Bug: 4639946
348: BEGIN
348: BEGIN
349:
350: --Bug: 4639946
351: Select PARENT_CATALOG_GROUP_ID into l_old_parent_id
352: From MTL_ITEM_CATALOG_GROUPS_B
353: WHERE ITEM_CATALOG_GROUP_ID = P_Catalog_Group_Rec.ITEM_CATALOG_GROUP_ID;
354:
355: IF (SQL%NOTFOUND) THEN
356: RAISE no_data_found;
355: IF (SQL%NOTFOUND) THEN
356: RAISE no_data_found;
357: END IF;
358:
359: UPDATE MTL_ITEM_CATALOG_GROUPS_B
360: SET
361: PARENT_CATALOG_GROUP_ID = P_Catalog_Group_Rec.PARENT_CATALOG_GROUP_ID,
362: ITEM_CREATION_ALLOWED_FLAG = NVL(P_Catalog_Group_Rec.ITEM_CREATION_ALLOWED_FLAG,ITEM_CREATION_ALLOWED_FLAG),
363: INACTIVE_DATE = P_Catalog_Group_Rec.INACTIVE_DATE,
411: RAISE no_data_found;
412: END IF;
413:
414:
415: UPDATE MTL_ITEM_CATALOG_GROUPS_TL
416: SET
417: DESCRIPTION = P_Catalog_Group_Rec.DESCRIPTION,
418: LAST_UPDATE_DATE = P_Catalog_Group_Rec.LAST_UPDATE_DATE,
419: LAST_UPDATED_BY = P_Catalog_Group_Rec.LAST_UPDATED_BY,
442: OUT l_return_status;
443:
444: END UPDATE_ROW;
445:
446: PROCEDURE DELETE_ROW (X_ITEM_CATALOG_GROUP_ID IN MTL_ITEM_CATALOG_GROUPS.ITEM_CATALOG_GROUP_ID%TYPE)
447: IS
448: BEGIN
449:
450: DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL
446: PROCEDURE DELETE_ROW (X_ITEM_CATALOG_GROUP_ID IN MTL_ITEM_CATALOG_GROUPS.ITEM_CATALOG_GROUP_ID%TYPE)
447: IS
448: BEGIN
449:
450: DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL
451: WHERE ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID;
452:
453: IF (SQL%NOTFOUND) THEN
454: RAISE no_data_found;
453: IF (SQL%NOTFOUND) THEN
454: RAISE no_data_found;
455: END IF;
456:
457: DELETE FROM MTL_ITEM_CATALOG_GROUPS_B
458: WHERE ITEM_CATALOG_GROUP_ID = X_ITEM_CATALOG_GROUP_ID;
459:
460: IF (SQL%NOTFOUND) THEN
461: RAISE no_data_found;
465:
466: PROCEDURE ADD_LANGUAGE IS
467: BEGIN
468:
469: DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL T
470: WHERE NOT EXISTS (SELECT NULL
471: FROM MTL_ITEM_CATALOG_GROUPS_B B
472: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID);
473:
467: BEGIN
468:
469: DELETE FROM MTL_ITEM_CATALOG_GROUPS_TL T
470: WHERE NOT EXISTS (SELECT NULL
471: FROM MTL_ITEM_CATALOG_GROUPS_B B
472: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID);
473:
474: UPDATE MTL_ITEM_CATALOG_GROUPS_TL T
475: SET (DESCRIPTION) = (SELECT B.DESCRIPTION
470: WHERE NOT EXISTS (SELECT NULL
471: FROM MTL_ITEM_CATALOG_GROUPS_B B
472: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID);
473:
474: UPDATE MTL_ITEM_CATALOG_GROUPS_TL T
475: SET (DESCRIPTION) = (SELECT B.DESCRIPTION
476: FROM MTL_ITEM_CATALOG_GROUPS_TL B
477: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID
478: AND B.LANGUAGE = T.SOURCE_LANG)
472: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID);
473:
474: UPDATE MTL_ITEM_CATALOG_GROUPS_TL T
475: SET (DESCRIPTION) = (SELECT B.DESCRIPTION
476: FROM MTL_ITEM_CATALOG_GROUPS_TL B
477: WHERE B.ITEM_CATALOG_GROUP_ID = T.ITEM_CATALOG_GROUP_ID
478: AND B.LANGUAGE = T.SOURCE_LANG)
479: WHERE ( T.ITEM_CATALOG_GROUP_ID,T.LANGUAGE)
480: IN (SELECT SUBT.ITEM_CATALOG_GROUP_ID,
478: AND B.LANGUAGE = T.SOURCE_LANG)
479: WHERE ( T.ITEM_CATALOG_GROUP_ID,T.LANGUAGE)
480: IN (SELECT SUBT.ITEM_CATALOG_GROUP_ID,
481: SUBT.LANGUAGE
482: FROM MTL_ITEM_CATALOG_GROUPS_TL SUBB,
483: MTL_ITEM_CATALOG_GROUPS_TL SUBT
484: WHERE SUBB.ITEM_CATALOG_GROUP_ID = SUBT.ITEM_CATALOG_GROUP_ID
485: AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
486: AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
479: WHERE ( T.ITEM_CATALOG_GROUP_ID,T.LANGUAGE)
480: IN (SELECT SUBT.ITEM_CATALOG_GROUP_ID,
481: SUBT.LANGUAGE
482: FROM MTL_ITEM_CATALOG_GROUPS_TL SUBB,
483: MTL_ITEM_CATALOG_GROUPS_TL SUBT
484: WHERE SUBB.ITEM_CATALOG_GROUP_ID = SUBT.ITEM_CATALOG_GROUP_ID
485: AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
486: AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
487: or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
486: AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
487: or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
488: or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)));
489:
490: INSERT INTO MTL_ITEM_CATALOG_GROUPS_TL (
491: ITEM_CATALOG_GROUP_ID,
492: DESCRIPTION,
493: CREATION_DATE,
494: CREATED_BY,
506: B.LAST_UPDATED_BY,
507: B.LAST_UPDATE_LOGIN,
508: L.LANGUAGE_CODE,
509: B.SOURCE_LANG
510: FROM MTL_ITEM_CATALOG_GROUPS_TL B,
511: FND_LANGUAGES L
512: WHERE L.INSTALLED_FLAG in ('I', 'B')
513: AND B.LANGUAGE = userenv('LANG')
514: AND NOT EXISTS (SELECT NULL
511: FND_LANGUAGES L
512: WHERE L.INSTALLED_FLAG in ('I', 'B')
513: AND B.LANGUAGE = userenv('LANG')
514: AND NOT EXISTS (SELECT NULL
515: FROM MTL_ITEM_CATALOG_GROUPS_TL T
516: WHERE T.ITEM_CATALOG_GROUP_ID = B.ITEM_CATALOG_GROUP_ID
517: AND T.LANGUAGE = L.LANGUAGE_CODE);
518:
519: END ADD_LANGUAGE;
517: AND T.LANGUAGE = L.LANGUAGE_CODE);
518:
519: END ADD_LANGUAGE;
520:
521: END MTL_ITEM_CATALOG_GROUPS_UTIL;