[Home] [Help]
PACKAGE BODY: APPS.OZF_NA_RULE_HEADER_PKG
Source
1 PACKAGE BODY OZF_Na_Rule_Header_PKG as
2 /* $Header: ozftnarb.pls 120.0 2005/06/01 01:58:18 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Na_Rule_Header_PKG
7 -- Purpose
8 --
9 -- History
10 -- Tue Dec 16 2003:2/13 PM RSSHARMA Fixed delete_row. Delete the row from tl table also
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Na_Rule_Header_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftdnrb.pls';
22
23
24
25
26 -- ========================================================
27 --
28 -- NAME
29 -- Insert_Row
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PROCEDURE Insert_Row(
39 px_na_rule_header_id IN OUT NOCOPY NUMBER,
40 p_user_status_id NUMBER,
41 p_status_code VARCHAR2,
42 p_start_date DATE,
43 p_end_date DATE,
44 px_object_version_number IN OUT NOCOPY NUMBER,
45 p_creation_date DATE,
46 p_created_by NUMBER,
47 p_last_update_date DATE,
48 p_last_updated_by NUMBER,
49 p_last_update_login NUMBER,
50 p_name VARCHAR2,
51 p_description VARCHAR2
52 )
53
54 IS
55 x_rowid VARCHAR2(30);
56
57
58 BEGIN
59
60
61 px_object_version_number := nvl(px_object_version_number, 1);
62
63
64 INSERT INTO ozf_na_rule_headers_b(
65 na_rule_header_id,
66 user_status_id,
67 status_code,
68 start_date,
69 end_date,
70 object_version_number,
71 creation_date,
72 created_by,
73 last_update_date,
74 last_updated_by,
75 last_update_login
76 ) VALUES (
77 DECODE( px_na_rule_header_id, FND_API.G_MISS_NUM, NULL, px_na_rule_header_id),
78 DECODE( p_user_status_id, FND_API.G_MISS_NUM, NULL, p_user_status_id),
79 DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
80 DECODE( p_start_date, FND_API.G_MISS_DATE, NULL, p_start_date),
81 DECODE( p_end_date, FND_API.G_MISS_DATE, NULL, p_end_date),
82 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
83 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
84 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
85 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
86 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
87 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login));
88
89 INSERT INTO ozf_na_rule_headers_tl(
90 na_rule_header_id ,
91 language ,
92 last_update_date ,
93 last_updated_by ,
94 creation_date ,
95 created_by ,
96 last_update_login ,
97 source_lang ,
98 name ,
99 description
100 )
101 SELECT
102 DECODE( px_na_rule_header_id, FND_API.G_MISS_NUM, NULL, px_na_rule_header_id),
103 l.language_code,
104 DECODE( p_last_update_date, to_date(NULL), SYSDATE, p_last_update_date),
105 DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
106 DECODE( p_creation_date, to_date(NULL), SYSDATE, p_creation_date),
107 DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
108 DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
109 USERENV('LANG'),
110 DECODE( p_name, FND_API.G_MISS_CHAR, NULL, p_name),
111 DECODE( p_description, FND_API.G_MISS_CHAR, NULL, p_description)
112 FROM fnd_languages l
113 WHERE l.installed_flag IN ('I','B')
114 AND NOT EXISTS(SELECT NULL FROM ozf_na_rule_headers_tl t
115 WHERE t.na_rule_header_id = DECODE( px_na_rule_header_id, FND_API.G_MISS_NUM, NULL, px_na_rule_header_id)
116 AND t.language = l.language_code);
117 END Insert_Row;
118
119
120
121
122 -- ========================================================
123 --
124 -- NAME
125 -- Update_Row
126 --
127 -- PURPOSE
128 --
129 -- NOTES
130 --
131 -- HISTORY
132 --
133 -- ========================================================
134 PROCEDURE Update_Row(
135 p_na_rule_header_id NUMBER,
136 p_user_status_id NUMBER,
137 p_status_code VARCHAR2,
138 p_start_date DATE,
139 p_end_date DATE,
140 p_object_version_number IN NUMBER,
141 p_last_update_date DATE,
142 p_last_updated_by NUMBER,
143 p_last_update_login NUMBER,
144 p_name VARCHAR2,
145 p_description VARCHAR2
146 )
147
148 IS
149 BEGIN
150 Update ozf_na_rule_headers_b
151 SET
152 na_rule_header_id = DECODE( p_na_rule_header_id, null, na_rule_header_id, FND_API.G_MISS_NUM, null, p_na_rule_header_id),
153 user_status_id = DECODE( p_user_status_id, null, user_status_id, FND_API.G_MISS_NUM, null, p_user_status_id),
154 status_code = DECODE( p_status_code, null, status_code, FND_API.g_miss_char, null, p_status_code),
155 start_date = DECODE( p_start_date, null, start_date, FND_API.G_MISS_DATE, null, p_start_date),
156 end_date = DECODE( p_end_date, null, end_date, FND_API.G_MISS_DATE, null, p_end_date),
157 object_version_number = nvl(p_object_version_number,0) + 1 ,
158 last_update_date = DECODE( p_last_update_date, to_date(NULL), last_update_date, FND_API.G_MISS_DATE, to_date(NULL), p_last_update_date),
159 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
160 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
161 WHERE na_rule_header_id = p_na_rule_header_id
162 AND object_version_number = p_object_version_number;
163
164 UPDATE ozf_na_rule_headers_tl
165 set name = DECODE( p_name, null, name, FND_API.g_miss_char, null, p_name),
166 description = DECODE( p_description, null, description, FND_API.g_miss_char, null, p_description),
167 last_update_date = DECODE( p_last_update_date, to_date(NULL), last_update_date, FND_API.G_MISS_DATE, to_date(NULL), p_last_update_date),
168 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
169 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
170 source_lang = USERENV('LANG')
171 WHERE na_rule_header_id = p_na_rule_header_id
172 AND USERENV('LANG') IN (language, source_lang);
173
174 IF (SQL%NOTFOUND) THEN
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END IF;
177
178
179 END Update_Row;
180
181
182
183
184 -- ========================================================
185 --
186 -- NAME
187 -- Delete_Row
188 --
189 -- PURPOSE
190 --
191 -- NOTES
192 --
193 -- HISTORY
194 --
195 -- ========================================================
196 PROCEDURE Delete_Row(
197 p_na_rule_header_id NUMBER,
198 p_object_version_number NUMBER)
199 IS
200 BEGIN
201 DELETE FROM ozf_na_rule_headers_b
202 WHERE na_rule_header_id = p_na_rule_header_id
203 AND object_version_number = p_object_version_number;
204 If (SQL%NOTFOUND) then
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 End If;
207
208 DELETE FROM ozf_na_rule_headers_tl
209 WHERE na_rule_header_id = p_na_rule_header_id;
210
211 If (SQL%NOTFOUND) then
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 End If;
214
215 END Delete_Row ;
216
217
218
219
220
221 -- ========================================================
222 --
223 -- NAME
224 -- Lock_Row
225 --
226 -- PURPOSE
227 --
228 -- NOTES
229 --
230 -- HISTORY
231 --
232 -- ========================================================
233 PROCEDURE Lock_Row(
234 p_na_rule_header_id NUMBER,
235 p_object_version_number NUMBER)
236 IS
237 CURSOR C IS
238 SELECT *
239 FROM ozf_na_rule_headers_b
240 WHERE na_rule_header_id = p_na_rule_header_id
241 AND object_version_number = p_object_version_number
242 FOR UPDATE OF na_rule_header_id NOWAIT;
243 Recinfo C%ROWTYPE;
244 BEGIN
245
246 OPEN c;
247 FETCH c INTO Recinfo;
248 IF (c%NOTFOUND) THEN
249 CLOSE c;
250 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
251 RAISE FND_API.g_exc_error;
252 END IF;
253 CLOSE c;
254 END Lock_Row;
255
256 -- ==============================================================================
257 -- Start of Comments
258 -- ==============================================================================
259 -- API Name
260 -- add_language
261 -- Type
262 -- Private
263 -- History
264 --
265 -- NOTE
266 --
267 -- End of Comments
268 -- ===============================================================
269
270
271
272 PROCEDURE Add_Language
273 IS
274 BEGIN
275 delete from ozf_na_rule_headers_tl T
276 where not exists
277 (select NULL
278 from ozf_na_rule_headers_b b
279 where b.na_rule_header_id = t.na_rule_header_id
280 );
281
282 update ozf_na_rule_headers_tl t set (
283 name
284 , description
285 ) = (select
286 b.name
287 , b.description
288 from ozf_na_rule_headers_tl b
289 where b.na_rule_header_id = t.na_rule_header_id
290 and b.language = t.source_lang)
291 where (
292 t.na_rule_header_id,
293 t.language
294 ) in (select
295 subt.na_rule_header_id,
296 subt.language
297 from ozf_na_rule_headers_tl subb, ozf_na_rule_headers_tl subt
298 where subb.na_rule_header_id = subt.na_rule_header_id
299 and subb.language = subt.source_lang
300 and (subb.name <> subt.name
301 or subb.description <> subt.description
302 or (subb.description is null and subt.description is not null)
303 or (subb.description is not null and subt.description is null)
304 ));
305
306 insert into ozf_na_rule_headers_tl (
307 na_rule_header_id,
308 creation_date,
309 created_by,
310 last_update_date,
311 last_updated_by,
312 last_update_login,
313 name,
314 description,
315 language,
316 source_lang
317 ) select
318 b.na_rule_header_id,
319 b.creation_date,
320 b.created_by,
321 b.last_update_date,
322 b.last_updated_by,
323 b.last_update_login,
324 b.name,
325 b.description,
326 l.language_code,
327 b.source_lang
328 from ozf_na_rule_headers_tl b, fnd_languages l
329 where l.installed_flag in ('I', 'B')
330 and b.language = userenv('lang')
331 and not exists
332 (select null
333 from ozf_na_rule_headers_tl t
334 where t.na_rule_header_id = b.na_rule_header_id
335 and t.language = l.language_code);
336 END ADD_LANGUAGE;
337
338
339
340
341 END OZF_Na_Rule_Header_PKG;