[Home] [Help]
PACKAGE BODY: APPS.CZ_MODEL_USAGES_PKG
Source
1 PACKAGE BODY CZ_MODEL_USAGES_PKG AS
2 /* $Header: czmdlugb.pls 120.4 2007/12/24 16:20:02 skudryav ship $ */
3
4 PROCEDURE INSERT_ROW(x_rowid IN OUT NOCOPY VARCHAR2,
5 p_model_usage_id IN NUMBER,
6 p_name IN VARCHAR2,
7 p_description IN VARCHAR2,
8 p_note IN VARCHAR2,
9 p_in_use IN VARCHAR2,
10 p_created_by IN NUMBER,
11 p_creation_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_date IN DATE,
14 p_last_update_login IN NUMBER) IS
15
16 CURSOR l_rowid_cursor IS
17 SELECT rowid, description
18 FROM cz_model_usages
19 WHERE model_usage_id = p_model_usage_id;
20 l_model_usage_inserted BOOLEAN := FALSE;
21 l_description CZ_MODEL_USAGES.description%TYPE := p_description;
22 BEGIN
23
24 OPEN l_rowid_cursor;
25 FETCH l_rowid_cursor INTO x_rowid,l_description;
26
27 IF l_description IS NULL THEN
28 l_description := p_name;
29 END IF;
30
31 IF l_rowid_cursor%NOTFOUND THEN
32 INSERT INTO CZ_MODEL_USAGES
33 (
34 MODEL_USAGE_ID
35 ,NAME
36 ,DESCRIPTION
37 ,NOTE
38 ,IN_USE
39 ,CREATED_BY
40 ,CREATION_DATE
41 ,LAST_UPDATED_BY
42 ,LAST_UPDATE_DATE
43 ,LAST_UPDATE_LOGIN
44 )
45 VALUES
46 (
47 p_model_usage_id
48 ,p_name
49 ,p_description
50 ,p_note
51 ,p_in_use
52 ,p_created_by
53 ,p_creation_date
54 ,p_last_updated_by
55 ,p_last_update_date
56 ,p_last_update_login
57 );
58 l_model_usage_inserted := TRUE;
59 END IF;
60
61 INSERT INTO cz_model_usages_tl(MODEL_USAGE_ID
62 ,LANGUAGE
63 ,SOURCE_LANG
64 ,DESCRIPTION
65 )
66 SELECT p_model_usage_id
67 ,L.LANGUAGE_CODE
68 ,userenv('LANG')
69 ,l_description
70 FROM FND_LANGUAGES L
71 WHERE L.INSTALLED_FLAG IN ('I', 'B')
72 AND NOT EXISTS (SELECT NULL
73 FROM cz_model_usages_tl T
74 WHERE T.model_usage_id = p_model_usage_id
75 AND T.LANGUAGE = L.LANGUAGE_CODE);
76
77 IF l_rowid_cursor%NOTFOUND AND l_model_usage_inserted=FALSE THEN
78 CLOSE l_rowid_cursor;
79 RAISE NO_DATA_FOUND;
80 END IF;
81 CLOSE l_rowid_cursor;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 IF l_rowid_cursor%ISOPEN THEN
86 CLOSE l_rowid_cursor;
87 END IF;
88 RAISE;
89 END INSERT_ROW;
90
91 --------------------------------------------------------------------------------
92 PROCEDURE UPDATE_ROW(p_model_usage_id IN NUMBER,
93 p_name IN VARCHAR2,
94 p_description IN VARCHAR2,
95 p_note IN VARCHAR2,
96 p_last_updated_by IN NUMBER,
97 p_last_update_date IN DATE,
98 p_last_update_login IN NUMBER)
99 IS
100 l_in_use CZ_MODEL_USAGES.in_use%TYPE;
101 BEGIN
102
103 SELECT NVL(in_use,'X') INTO l_in_use FROM CZ_MODEL_USAGES
104 WHERE model_usage_id = p_model_usage_id;
105
106 IF l_in_use = '1' THEN
107 UPDATE cz_model_usages_tl
108 SET description = p_description
109 WHERE model_usage_id = p_model_usage_id AND
110 source_lang <> language;
111 ELSE
112 UPDATE cz_model_usages
113 SET name = p_name,
114 note = p_note,
115 last_updated_by = p_last_updated_by,
116 last_update_date = p_last_update_date,
117 last_update_login = p_last_update_login
118 WHERE model_usage_id = p_model_usage_id;
119 UPDATE cz_model_usages_tl
120 SET description = p_description
121 WHERE model_usage_id = p_model_usage_id;
122
123 IF SQL%NOTFOUND THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 END IF;
128
129 END UPDATE_ROW;
130
131 --------------------------------------------------------------------------------
132 PROCEDURE DELETE_ROW(p_model_usage_id IN NUMBER)
133 IS
134 BEGIN
135 DELETE FROM cz_model_usages_tl
136 WHERE model_usage_id = p_model_usage_id;
137
138 IF SQL%NOTFOUND THEN
139 RAISE NO_DATA_FOUND;
140 END IF;
141 END DELETE_ROW;
142
143 --------------------------------------------------------------------------------
144 PROCEDURE ADD_LANGUAGE
145 IS
146
147 BEGIN
148 INSERT INTO cz_model_usages_tl(MODEL_USAGE_ID
149 ,LANGUAGE
150 ,SOURCE_LANG
151 ,DESCRIPTION
152 )
153 SELECT B.model_usage_id
154 ,L.LANGUAGE_CODE
155 ,B.SOURCE_LANG
156 ,B.DESCRIPTION
157 FROM cz_model_usages_tl B,
158 FND_LANGUAGES L
159 WHERE L.INSTALLED_FLAG in ('I', 'B')
160 AND B.LANGUAGE = userenv('LANG')
161 AND NOT EXISTS (SELECT NULL
162 FROM cz_model_usages_tl T
163 WHERE T.model_usage_id = B.model_usage_id
164 AND T.LANGUAGE = L.LANGUAGE_CODE);
165 COMMIT;
166 END ADD_LANGUAGE;
167
168 --------------------------------------------------------------------------------
169 -- PROCEDURE LOCK_ROW(p_model_usage_id IN NUMBER)
170 -- IS
171 -- BEGIN
172 -- NULL;
173 -- END LOCK_ROW;
174
175 --------------------------------------------------------------------------------
176 PROCEDURE TRANSLATE_ROW(p_model_usage_id IN NUMBER,
177 p_description IN VARCHAR2) IS
178 BEGIN
179 UPDATE cz_model_usages_tl
180 SET description = p_description,
181 source_lang = userenv('LANG')
182 WHERE model_usage_id = p_model_usage_id
183 AND userenv('LANG') IN (language, source_lang);
184
185 IF SQL%NOTFOUND THEN
186 RAISE NO_DATA_FOUND;
187 END IF;
188 END TRANSLATE_ROW;
189
190 --------------------------------------------------------------------------------
191 PROCEDURE LOAD_ROW
192 (
193 p_model_usage_id IN NUMBER,
194 p_name IN VARCHAR2,
195 p_description IN VARCHAR2,
196 p_note IN VARCHAR2,
197 p_in_use IN VARCHAR2,
198 p_owner IN VARCHAR2,
199 p_last_update_date IN VARCHAR2
200 ) IS
201 CURSOR l_exists_cursor IS
202 SELECT '1'
203 FROM cz_model_usages_tl
204 WHERE model_usage_id = p_model_usage_id
205 AND language = userenv('LANG');
206 l_row_exists VARCHAR2(1);
207 l_rowid VARCHAR2(64);
208 l_last_update_date DATE;
209 l_last_updated_by NUMBER;
210 BEGIN
211
212 -- Translate owner to file_last_updated_by
213 l_last_updated_by := fnd_load_util.owner_id(p_owner);
214
215 -- Translate char last_update_date to date
216 l_last_update_date := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
217
218 OPEN l_exists_cursor;
219 FETCH l_exists_cursor INTO l_row_exists;
220
221 IF l_exists_cursor%NOTFOUND THEN
222 INSERT_ROW(l_rowid,
223 p_model_usage_id,
224 p_name,
225 p_description,
226 p_note,
227 p_in_use,
228 l_last_updated_by,
229 l_last_update_date,
230 l_last_updated_by,
231 l_last_update_date,
232 0);
233
234 ELSE
235 UPDATE_ROW(p_model_usage_id,
236 p_name,
237 p_description,
238 p_note,
239 l_last_updated_by,
240 l_last_update_date,
241 0);
242
243 END IF;
244
245 CLOSE l_exists_cursor;
246
247 END LOAD_ROW;
248
249 END CZ_MODEL_USAGES_PKG;