[Home] [Help]
PACKAGE BODY: APPS.OZF_NA_DDN_RULE_PKG
Source
1 PACKAGE BODY OZF_Na_Ddn_Rule_PKG as
2 /* $Header: ozftdnrb.pls 120.1 2006/02/25 19:23:10 julou noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Na_Ddn_Rule_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_Ddn_Rule_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstam.b.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_deduction_rule_id IN OUT NOCOPY NUMBER,
40 p_transaction_source_code VARCHAR2,
41 p_transaction_type_code VARCHAR2,
42 p_deduction_identifier_id VARCHAR2,
43 p_deduction_identifier_org_id NUMBER,
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_deduction_rules_b(
65 na_deduction_rule_id,
66 transaction_source_code,
67 transaction_type_code,
68 deduction_identifier_id,
69 deduction_identifier_org_id,
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_deduction_rule_id, FND_API.G_MISS_NUM, NULL, px_na_deduction_rule_id),
78 DECODE( p_transaction_source_code, FND_API.g_miss_char, NULL, p_transaction_source_code),
79 DECODE( p_transaction_type_code, FND_API.g_miss_char, NULL, p_transaction_type_code),
80 DECODE( p_deduction_identifier_id, FND_API.G_MISS_CHAR, NULL, p_deduction_identifier_id),
81 DECODE( p_deduction_identifier_org_id, FND_API.G_MISS_NUM, NULL, p_deduction_identifier_org_id),
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_deduction_rules_tl(
90 na_deduction_rule_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_deduction_rule_id, FND_API.G_MISS_NUM, NULL, px_na_deduction_rule_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_deduction_rules_tl t
115 WHERE t.na_deduction_rule_id = DECODE( px_na_deduction_rule_id, FND_API.G_MISS_NUM, NULL, px_na_deduction_rule_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_deduction_rule_id NUMBER,
136 p_transaction_source_code VARCHAR2,
137 p_transaction_type_code VARCHAR2,
138 p_deduction_identifier_id VARCHAR2,
139 p_deduction_identifier_org_id NUMBER,
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_deduction_rules_b
151 SET
152 na_deduction_rule_id = DECODE( p_na_deduction_rule_id, null, na_deduction_rule_id, FND_API.G_MISS_NUM, null, p_na_deduction_rule_id),
153 transaction_source_code = DECODE( p_transaction_source_code, null, transaction_source_code, FND_API.g_miss_char, null, p_transaction_source_code),
154 transaction_type_code = DECODE( p_transaction_type_code, null, transaction_type_code, FND_API.g_miss_char, null, p_transaction_type_code),
155 deduction_identifier_id = DECODE( p_deduction_identifier_id, null, deduction_identifier_id, FND_API.G_MISS_CHAR, null, p_deduction_identifier_id),
156 deduction_identifier_org_id = DECODE( p_deduction_identifier_org_id, null, deduction_identifier_org_id, FND_API.G_MISS_NUM, null, p_deduction_identifier_org_id),
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_deduction_rule_id = p_na_deduction_rule_id
162 AND object_version_number = p_object_version_number;
163
164 UPDATE ozf_na_deduction_rules_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_deduction_rule_id = p_na_deduction_rule_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_deduction_rule_id NUMBER,
198 p_object_version_number NUMBER)
199 IS
200 BEGIN
201 DELETE FROM ozf_na_deduction_rules_b
202 WHERE na_deduction_rule_id = p_na_deduction_rule_id
203 AND object_version_number = p_object_version_number;
204 DELETE FROM ozf_na_deduction_rules_tl
205 WHERE na_deduction_rule_id = p_na_deduction_rule_id;
206
207 If (SQL%NOTFOUND) then
208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
209 End If;
210 END Delete_Row ;
211
212
213
214
215
216 -- ========================================================
217 --
218 -- NAME
219 -- Lock_Row
220 --
221 -- PURPOSE
222 --
223 -- NOTES
224 --
225 -- HISTORY
226 --
227 -- ========================================================
228 PROCEDURE Lock_Row(
229 p_na_deduction_rule_id NUMBER,
230 p_object_version_number NUMBER)
231 IS
232 CURSOR C IS
233 SELECT *
234 FROM ozf_na_deduction_rules_b
235 WHERE na_deduction_rule_id = p_na_deduction_rule_id
236 AND object_version_number = p_object_version_number
237 FOR UPDATE OF na_deduction_rule_id NOWAIT;
238 Recinfo C%ROWTYPE;
239 BEGIN
240
241 OPEN c;
242 FETCH c INTO Recinfo;
243 IF (c%NOTFOUND) THEN
244 CLOSE c;
245 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
246 RAISE FND_API.g_exc_error;
247 END IF;
248 CLOSE c;
249 END Lock_Row;
250
251 -- ==============================================================================
252 -- Start of Comments
253 -- ==============================================================================
254 -- API Name
255 -- add_language
256 -- Type
257 -- Private
258 -- History
259 --
260 -- NOTE
261 --
262 -- End of Comments
263 -- ===============================================================
264
265
266
267 PROCEDURE Add_Language
268 IS
269 BEGIN
270 delete from ozf_na_deduction_rules_tl T
271 where not exists
272 (select NULL
273 from ozf_na_deduction_rules_b b
274 where b.na_deduction_rule_id = t.na_deduction_rule_id
275 );
276
277 update ozf_na_deduction_rules_tl t set (
278 name
279 , description
280 ) = (select
281 b.name
282 , b.description
283 from ozf_na_deduction_rules_tl b
284 where b.na_deduction_rule_id = t.na_deduction_rule_id
285 and b.language = t.source_lang)
286 where (
287 t.na_deduction_rule_id,
288 t.language
289 ) in (select
290 subt.na_deduction_rule_id,
291 subt.language
292 from ozf_na_deduction_rules_tl subb, ozf_na_deduction_rules_tl subt
293 where subb.na_deduction_rule_id = subt.na_deduction_rule_id
294 and subb.language = subt.source_lang
295 and (subb.name <> subt.name
296 or subb.description <> subt.description
297 or (subb.description is null and subt.description is not null)
298 or (subb.description is not null and subt.description is null)
299 ));
300
301 insert into ozf_na_deduction_rules_tl (
302 na_deduction_rule_id,
303 creation_date,
304 created_by,
305 last_update_date,
306 last_updated_by,
307 last_update_login,
308 name,
309 description,
310 language,
311 source_lang
312 ) select
313 b.na_deduction_rule_id,
314 b.creation_date,
315 b.created_by,
316 b.last_update_date,
317 b.last_updated_by,
318 b.last_update_login,
319 b.name,
320 b.description,
321 l.language_code,
322 b.source_lang
323 from ozf_na_deduction_rules_tl b, fnd_languages l
324 where l.installed_flag in ('I', 'B')
325 and b.language = userenv('lang')
326 and not exists
327 (select null
328 from ozf_na_deduction_rules_tl t
329 where t.na_deduction_rule_id = b.na_deduction_rule_id
330 and t.language = l.language_code);
331 END ADD_LANGUAGE;
332
333
334
335
336 END OZF_Na_Ddn_Rule_PKG;