1 PACKAGE BODY HZ_EXT_DATA_RULES_PKG AS
2 /* $Header: ARHEDRTB.pls 120.2 2005/10/30 03:52:07 appldev noship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_RULE_ID IN NUMBER,
6 X_RULE_TYPE IN VARCHAR2,
7 X_RULE_NAME IN VARCHAR2
8 ) IS
9
10 l_rowid ROWID := NULL;
11
12 CURSOR c IS
13 SELECT ROWID FROM HZ_EXT_DATA_RULES_TL
14 WHERE RULE_ID = X_RULE_ID
15 AND LANGUAGE = USERENV('LANG');
16
17 BEGIN
18
19 INSERT INTO HZ_EXT_DATA_RULES_TL (
20 CREATION_DATE,
21 LAST_UPDATE_LOGIN,
22 LAST_UPDATE_DATE,
23 LAST_UPDATED_BY,
24 CREATED_BY,
25 RULE_NAME,
26 RULE_ID,
27 RULE_TYPE,
28 LANGUAGE,
29 SOURCE_LANG
30 ) SELECT
31 SYSDATE,
32 hz_utility_v2pub.last_update_login,
33 SYSDATE,
34 hz_utility_v2pub.last_updated_by,
35 hz_utility_v2pub.created_by,
36 X_RULE_NAME,
37 X_RULE_ID,
38 X_RULE_TYPE,
39 L.LANGUAGE_CODE,
40 USERENV('LANG')
41 FROM FND_LANGUAGES L
42 WHERE L.INSTALLED_FLAG IN ('I', 'B')
43 AND NOT EXISTS
44 (SELECT NULL
45 FROM HZ_EXT_DATA_RULES_TL T
46 WHERE T.RULE_ID = X_RULE_ID
47 AND T.LANGUAGE = L.LANGUAGE_CODE);
48
49 OPEN c;
50 FETCH c INTO l_rowid;
51 IF (c%NOTFOUND) THEN
52 CLOSE c;
53 RAISE NO_DATA_FOUND;
54 END IF;
55 CLOSE c;
56
57 END INSERT_ROW;
58
59 PROCEDURE LOCK_ROW (
60 X_RULE_ID IN NUMBER,
61 X_RULE_TYPE IN VARCHAR2,
62 X_RULE_NAME IN VARCHAR2
63 ) IS
64
65 CURSOR c1 IS
66 SELECT
67 RULE_TYPE,
68 RULE_NAME,
69 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
70 FROM HZ_EXT_DATA_RULES_TL
71 WHERE RULE_ID = X_RULE_ID
72 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
73 FOR UPDATE OF RULE_ID NOWAIT;
74
75 BEGIN
76
77 FOR tlinfo IN c1 LOOP
78 IF (tlinfo.BASELANG = 'Y') THEN
79 IF ( ((tlinfo.RULE_NAME = X_RULE_NAME)
80 OR ((tlinfo.RULE_NAME IS NULL) AND (X_RULE_NAME IS NULL)))
81 AND ((tlinfo.RULE_TYPE = X_RULE_TYPE)
82 OR ((tlinfo.RULE_TYPE IS NULL) AND (X_RULE_TYPE IS NULL)))
83 ) THEN
84 NULL;
85 ELSE
86 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
87 app_exception.raise_exception;
88 END IF;
89 END IF;
90 END LOOP;
91 RETURN;
92
93 END LOCK_ROW;
94
95 PROCEDURE UPDATE_ROW (
96 X_RULE_ID IN NUMBER,
97 X_RULE_NAME IN VARCHAR2
98 ) IS
99 BEGIN
100
101 UPDATE HZ_EXT_DATA_RULES_TL
102 SET RULE_NAME = X_RULE_NAME,
103 LAST_UPDATE_DATE = SYSDATE,
104 LAST_UPDATED_BY = hz_utility_v2pub.last_updated_by,
105 LAST_UPDATE_LOGIN = hz_utility_v2pub.last_update_login,
106 SOURCE_LANG = USERENV('LANG')
107 WHERE RULE_ID = X_RULE_ID
108 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
109
110 IF (SQL%NOTFOUND) THEN
111 RAISE NO_DATA_FOUND;
112 END IF;
113
114 END UPDATE_ROW;
115
116 PROCEDURE DELETE_ROW (
117 X_RULE_ID IN NUMBER
118 ) IS
119 BEGIN
120 DELETE FROM HZ_EXT_DATA_RULES_TL
121 WHERE RULE_ID = X_RULE_ID;
122
123 IF (SQL%NOTFOUND) THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 END DELETE_ROW;
128
129 PROCEDURE ADD_LANGUAGE IS
130 BEGIN
131 UPDATE HZ_EXT_DATA_RULES_TL T
132 SET (
133 RULE_NAME
134 ) = (SELECT
135 B.RULE_NAME
136 FROM HZ_EXT_DATA_RULES_TL B
137 WHERE B.RULE_ID = T.RULE_ID
138 AND B.LANGUAGE = T.SOURCE_LANG)
139 WHERE (
140 T.RULE_ID,
141 T.LANGUAGE
142 ) IN (SELECT
143 SUBT.RULE_ID,
144 SUBT.LANGUAGE
145 FROM HZ_EXT_DATA_RULES_TL SUBB, HZ_EXT_DATA_RULES_TL SUBT
146 WHERE SUBB.RULE_ID = SUBT.RULE_ID
147 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
148 AND (SUBB.RULE_NAME <> SUBT.RULE_NAME
149 OR (SUBB.RULE_NAME IS NULL AND SUBT.RULE_NAME IS NOT NULL)
150 OR (SUBB.RULE_NAME is NOT NULL AND SUBT.RULE_NAME IS NULL)
151 ));
152
153 INSERT INTO HZ_EXT_DATA_RULES_TL (
154 CREATION_DATE,
155 LAST_UPDATE_LOGIN,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 CREATED_BY,
159 RULE_NAME,
160 RULE_ID,
161 RULE_TYPE,
162 LANGUAGE,
163 SOURCE_LANG
164 ) SELECT
165 B.CREATION_DATE,
166 B.LAST_UPDATE_LOGIN,
167 B.LAST_UPDATE_DATE,
168 B.LAST_UPDATED_BY,
169 B.CREATED_BY,
170 B.RULE_NAME,
171 B.RULE_ID,
172 B.RULE_TYPE,
173 L.LANGUAGE_CODE,
174 B.SOURCE_LANG
175 FROM HZ_EXT_DATA_RULES_TL B, FND_LANGUAGES L
176 WHERE L.INSTALLED_FLAG in ('I', 'B')
177 AND B.LANGUAGE = USERENV('LANG')
178 AND NOT EXISTS
179 (SELECT NULL
180 FROM HZ_EXT_DATA_RULES_TL T
181 WHERE T.RULE_ID = B.RULE_ID
182 AND T.LANGUAGE = L.LANGUAGE_CODE);
183
184 END ADD_LANGUAGE;
185
186 END HZ_EXT_DATA_RULES_PKG;
[Home] [Help]