[Home] [Help]
PACKAGE BODY: APPS.PV_GE_HIST_LOG_PKG
Source
1 PACKAGE BODY PV_Ge_Hist_Log_PKG as
2 /* $Header: pvxtghlb.pls 120.0 2005/05/27 16:20:38 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Ge_Hist_Log_PKG
7 -- Purpose
8 --
9 -- History
10 --
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):= 'PV_Ge_Hist_Log_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtghlb.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_entity_history_log_id IN OUT NOCOPY NUMBER,
40 px_object_version_number IN OUT NOCOPY NUMBER,
41 p_arc_history_for_entity_code VARCHAR2,
42 p_history_for_entity_id NUMBER,
43 p_message_code VARCHAR2,
44 p_history_category_code VARCHAR2,
45 p_created_by NUMBER,
46 p_creation_date DATE,
47 p_last_updated_by NUMBER,
48 p_last_update_date DATE,
49 p_last_update_login NUMBER,
50 p_partner_id NUMBER,
51 p_access_level_flag VARCHAR2,
52 p_interaction_level NUMBER,
53 p_COMMENTS VARCHAR2
54 )
55
56 IS
57 x_rowid VARCHAR2(30);
58
59
60 BEGIN
61
62
63 px_object_version_number := nvl(px_object_version_number, 1);
64
65
66 INSERT INTO pv_ge_history_log_b(
67 entity_history_log_id,
68 object_version_number,
69 arc_history_for_entity_code,
70 history_for_entity_id,
71 message_code,
72 history_category_code,
73 created_by,
74 creation_date,
75 last_updated_by,
76 last_update_date,
77 last_update_login,
78 partner_id,
79 access_level_flag,
80 interaction_level
81 ) VALUES (
82 DECODE( px_entity_history_log_id, FND_API.G_MISS_NUM, NULL, px_entity_history_log_id),
83 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
84 DECODE( p_arc_history_for_entity_code, FND_API.g_miss_char, NULL, p_arc_history_for_entity_code),
85 DECODE( p_history_for_entity_id, FND_API.G_MISS_NUM, NULL, p_history_for_entity_id),
86 DECODE( p_message_code, FND_API.g_miss_char, NULL, p_message_code),
87 DECODE( p_history_category_code, FND_API.g_miss_char, NULL, p_history_category_code),
88 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
89 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
90 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
91 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
92 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
93 DECODE( p_partner_id, FND_API.G_MISS_NUM, NULL, p_partner_id),
94 DECODE( p_access_level_flag, FND_API.g_miss_char, NULL, p_access_level_flag),
95 DECODE( p_interaction_level, FND_API.G_MISS_NUM, NULL, p_interaction_level));
96
97 INSERT INTO pv_ge_history_log_tl(
98 entity_history_log_id ,
99 language ,
100 last_update_date ,
101 last_updated_by ,
102 creation_date ,
103 created_by ,
104 last_update_login ,
105 source_lang ,
106 COMMENTS
107 )
108 SELECT
109 DECODE( px_entity_history_log_id, FND_API.G_MISS_NUM, NULL, px_entity_history_log_id),
110 l.language_code,
111 DECODE( p_last_update_date, NULL, SYSDATE, p_last_update_date),
112 DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
113 DECODE( p_creation_date, NULL, SYSDATE, p_creation_date),
114 DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
115 DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
116 USERENV('LANG'),
117 DECODE( p_COMMENTS , FND_API.G_MISS_CHAR, NULL, p_COMMENTS)
118 FROM fnd_languages l
119 WHERE l.installed_flag IN ('I','B')
120 AND NOT EXISTS(SELECT NULL FROM pv_ge_history_log_tl t
121 WHERE t.entity_history_log_id = DECODE( px_entity_history_log_id, FND_API.G_MISS_NUM, NULL, px_entity_history_log_id)
122 AND t.language = l.language_code);
123 END Insert_Row;
124
125
126
127
128 -- ========================================================
129 --
130 -- NAME
131 -- Update_Row
132 --
133 -- PURPOSE
134 --
135 -- NOTES
136 --
137 -- HISTORY
138 --
139 -- ========================================================
140 PROCEDURE Update_Row(
141 p_entity_history_log_id NUMBER,
142 p_object_version_number IN NUMBER,
143 p_arc_history_for_entity_code VARCHAR2,
144 p_history_for_entity_id NUMBER,
145 p_message_code VARCHAR2,
146 p_history_category_code VARCHAR2,
147 p_last_updated_by NUMBER,
148 p_last_update_date DATE,
149 p_last_update_login NUMBER,
150 p_partner_id NUMBER,
151 p_access_level_flag VARCHAR2,
152 p_interaction_level NUMBER,
153 p_COMMENTS VARCHAR2
154 )
155
156 IS
157 BEGIN
158 Update pv_ge_history_log_b
159 SET
160 entity_history_log_id = DECODE( p_entity_history_log_id, null, entity_history_log_id, FND_API.G_MISS_NUM, null, p_entity_history_log_id),
161 object_version_number = nvl(p_object_version_number,0) + 1 ,
162 arc_history_for_entity_code = DECODE( p_arc_history_for_entity_code, null, arc_history_for_entity_code, FND_API.g_miss_char, null, p_arc_history_for_entity_code),
163 history_for_entity_id = DECODE( p_history_for_entity_id, null, history_for_entity_id, FND_API.G_MISS_NUM, null, p_history_for_entity_id),
164 message_code = DECODE( p_message_code, null, message_code, FND_API.g_miss_char, null, p_message_code),
165 history_category_code = DECODE( p_history_category_code, null, history_category_code, FND_API.g_miss_char, null, p_history_category_code),
166 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
167 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
168 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
169 partner_id = DECODE( p_partner_id, null, partner_id, FND_API.G_MISS_NUM, null, p_partner_id),
170 access_level_flag = DECODE( p_access_level_flag, null, access_level_flag, FND_API.g_miss_char, null, p_access_level_flag),
171 interaction_level = DECODE( p_interaction_level, null, interaction_level, FND_API.G_MISS_NUM, null, p_interaction_level)
172 WHERE entity_history_log_id = p_entity_history_log_id
173 AND object_version_number = p_object_version_number;
174
175 UPDATE pv_ge_history_log_tl
176 set COMMENTS = DECODE( p_COMMENTS, null, COMMENTS, FND_API.g_miss_char, null, p_COMMENTS),
177 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
178 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
179 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
180 source_lang = USERENV('LANG')
181 WHERE entity_history_log_id = p_entity_history_log_id
182 AND USERENV('LANG') IN (language, source_lang);
183
184 IF (SQL%NOTFOUND) THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188
189 END Update_Row;
190
191
192
193
194 -- ========================================================
195 --
196 -- NAME
197 -- Delete_Row
198 --
199 -- PURPOSE
200 --
201 -- NOTES
202 --
203 -- HISTORY
204 --
205 -- ========================================================
206 PROCEDURE Delete_Row(
207 p_entity_history_log_id NUMBER,
208 p_object_version_number NUMBER)
209 IS
210 BEGIN
211 DELETE FROM pv_ge_history_log_b
212 WHERE entity_history_log_id = p_entity_history_log_id
213 AND object_version_number = p_object_version_number;
214 If (SQL%NOTFOUND) then
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 End If;
217 END Delete_Row ;
218
219
220
221
222
223 -- ========================================================
224 --
225 -- NAME
226 -- Lock_Row
227 --
228 -- PURPOSE
229 --
230 -- NOTES
231 --
232 -- HISTORY
233 --
234 -- ========================================================
235 PROCEDURE Lock_Row(
236 p_entity_history_log_id NUMBER,
237 p_object_version_number NUMBER)
238 IS
239 CURSOR C IS
240 SELECT *
241 FROM pv_ge_history_log_b
242 WHERE entity_history_log_id = p_entity_history_log_id
243 AND object_version_number = p_object_version_number
244 FOR UPDATE OF entity_history_log_id NOWAIT;
245 Recinfo C%ROWTYPE;
246 BEGIN
247
248 OPEN c;
249 FETCH c INTO Recinfo;
250 IF (c%NOTFOUND) THEN
251 CLOSE c;
252 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
253 RAISE FND_API.g_exc_error;
254 END IF;
255 CLOSE c;
256 END Lock_Row;
257
258 -- ==============================================================================
259 -- Start of Comments
260 -- ==============================================================================
261 -- API Name
262 -- add_language
263 -- Type
264 -- Private
265 -- History
266 --
267 -- NOTE
268 --
269 -- End of Comments
270 -- ===============================================================
271
272
273 PROCEDURE Add_Language
274 IS
275 BEGIN
276
277 -- changing by pukken as per performance team guidelines to fix performance issue
278 -- as described in bug 3723612 (*** RTIKKU 03/24/05 12:46pm ***)
279 INSERT /*+ append parallel(tt) */ INTO pv_ge_history_log_tl tt
280 (
281 entity_history_log_id,
282 creation_date,
283 created_by,
284 last_update_date,
285 last_updated_by,
286 last_update_login,
287 comments,
288 language,
289 source_lang
290 )
291 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.*
292 FROM
293 (
294 SELECT /*+ no_merge ordered parallel(b) */
295 b.entity_history_log_id,
296 b.creation_date,
297 b.created_by,
298 b.last_update_date,
299 b.last_updated_by,
300 b.last_update_login,
301 b.comments,
302 l.language_code,
303 b.source_lang
304 FROM pv_ge_history_log_tl B , FND_LANGUAGES L
305 WHERE L.INSTALLED_FLAG IN ( 'I','B' ) AND B.LANGUAGE = USERENV ( 'LANG' )
306 ) v
307 , pv_ge_history_log_tl t
308 WHERE t.entity_history_log_id(+) = v.entity_history_log_id
309 AND t.language(+) = v.language_code
310 AND t.entity_history_log_id IS NULL ;
311
312
313 END ADD_LANGUAGE;
314
315 END PV_Ge_Hist_Log_PKG;