[Home] [Help]
PACKAGE BODY: APPS.PV_GE_CHKLST_PKG
Source
1 PACKAGE BODY PV_Ge_Chklst_PKG as
2 /* $Header: pvxtgcib.pls 120.0 2005/05/27 16:03:10 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Ge_Chklst_PKG
7 -- Purpose
8 --
9 -- History
10 -- 15 Nov 2002 anubhavk created
11 -- 19 Nov 2002 anubhavk Updated - For NOCOPY by running nocopy.sh
12 --
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Ge_Chklst_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtgcib.pls';
24
25
26
27
28 -- ========================================================
29 --
30 -- NAME
31 -- Insert_Row
32 --
33 -- PURPOSE
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 -- ========================================================
40 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
41 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
42 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
43
44 PROCEDURE Insert_Row(
45 px_checklist_item_id IN OUT NOCOPY NUMBER,
46 px_object_version_number IN OUT NOCOPY NUMBER,
47 p_arc_used_by_entity_code VARCHAR2,
48 p_used_by_entity_id NUMBER,
49 p_sequence_num NUMBER,
50 p_is_required_flag VARCHAR2,
51 p_enabled_flag VARCHAR2,
52 p_created_by NUMBER,
53 p_creation_date DATE,
54 p_last_updated_by NUMBER,
55 p_last_update_date DATE,
56 p_last_update_login NUMBER,
57 p_checklist_item_name VARCHAR2
58 )
59
60 IS
61 x_rowid VARCHAR2(30);
62
63
64 BEGIN
65
66
67 px_object_version_number := nvl(px_object_version_number, 1);
68
69
70 INSERT INTO pv_ge_chklst_items_b(
71 checklist_item_id,
72 object_version_number,
73 arc_used_by_entity_code,
74 used_by_entity_id,
75 sequence_num,
76 is_required_flag,
77 enabled_flag,
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login
83 ) VALUES (
84 DECODE( px_checklist_item_id, FND_API.G_MISS_NUM, NULL, px_checklist_item_id),
85 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
86 DECODE( p_arc_used_by_entity_code, FND_API.g_miss_char, NULL, p_arc_used_by_entity_code),
87 DECODE( p_used_by_entity_id, FND_API.G_MISS_NUM, NULL, p_used_by_entity_id),
88 DECODE( p_sequence_num, FND_API.G_MISS_NUM, NULL, p_sequence_num),
89 DECODE( p_is_required_flag, FND_API.g_miss_char, NULL, p_is_required_flag),
90 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
91 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
92 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
93 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
94 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
95 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login));
96
97 INSERT INTO pv_ge_chklst_items_tl(
98 checklist_item_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 checklist_item_name
107 )
108 SELECT
109 DECODE( px_checklist_item_id, FND_API.G_MISS_NUM, NULL, px_checklist_item_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_checklist_item_name , FND_API.G_MISS_CHAR, NULL, p_checklist_item_name)
118 FROM fnd_languages l
119 WHERE l.installed_flag IN ('I','B')
120 AND NOT EXISTS(SELECT NULL FROM pv_ge_chklst_items_tl t
121 WHERE t.checklist_item_id = DECODE( px_checklist_item_id, FND_API.G_MISS_NUM, NULL, px_checklist_item_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_checklist_item_id NUMBER,
142 p_object_version_number IN NUMBER,
143 p_arc_used_by_entity_code VARCHAR2,
144 p_used_by_entity_id NUMBER,
145 p_sequence_num NUMBER,
146 p_is_required_flag VARCHAR2,
147 p_enabled_flag VARCHAR2,
148 p_last_updated_by NUMBER,
149 p_last_update_date DATE,
150 p_last_update_login NUMBER,
151 p_checklist_item_name VARCHAR2
152 )
153
154 IS
155 BEGIN
156 Update pv_ge_chklst_items_b
157 SET
158 checklist_item_id = DECODE( p_checklist_item_id, null, checklist_item_id, FND_API.G_MISS_NUM, null, p_checklist_item_id),
159 object_version_number = nvl(p_object_version_number,0) + 1 ,
160 arc_used_by_entity_code = DECODE( p_arc_used_by_entity_code, null, arc_used_by_entity_code, FND_API.g_miss_char, null, p_arc_used_by_entity_code),
161 used_by_entity_id = DECODE( p_used_by_entity_id, null, used_by_entity_id, FND_API.G_MISS_NUM, null, p_used_by_entity_id),
162 sequence_num = DECODE( p_sequence_num, null, sequence_num, FND_API.G_MISS_NUM, null, p_sequence_num),
163 is_required_flag = DECODE( p_is_required_flag, null, is_required_flag, FND_API.g_miss_char, null, p_is_required_flag),
164 enabled_flag = DECODE( p_enabled_flag, null, enabled_flag, FND_API.g_miss_char, null, p_enabled_flag),
165 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
166 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
167 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
168 WHERE checklist_item_id = p_checklist_item_id;
169 --AND object_version_number = p_object_version_number;
170
171 UPDATE pv_ge_chklst_items_tl
172 set checklist_item_name = DECODE( p_checklist_item_name, null, checklist_item_name, FND_API.g_miss_char, null, p_checklist_item_name),
173 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
174 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
175 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
176 source_lang = USERENV('LANG')
177 WHERE checklist_item_id = p_checklist_item_id
178 AND USERENV('LANG') IN (language, source_lang);
179
180 IF (SQL%NOTFOUND) THEN
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182 END IF;
183
184
185 END Update_Row;
186
187
188
189
190 -- ========================================================
191 --
192 -- NAME
193 -- Delete_Row
194 --
195 -- PURPOSE
196 --
197 -- NOTES
198 --
199 -- HISTORY
200 --
201 -- ========================================================
202 PROCEDURE Delete_Row(
203 p_checklist_item_id NUMBER,
204 p_object_version_number NUMBER)
205 IS
206 BEGIN
207 DELETE FROM pv_ge_chklst_items_b
208 WHERE checklist_item_id = p_checklist_item_id
209 AND object_version_number = p_object_version_number;
210 If (SQL%NOTFOUND) then
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 End If;
213 END Delete_Row ;
214
215
216
217
218
219 -- ========================================================
220 --
221 -- NAME
222 -- Lock_Row
223 --
224 -- PURPOSE
225 --
226 -- NOTES
227 --
228 -- HISTORY
229 --
230 -- ========================================================
231 PROCEDURE Lock_Row(
232 p_checklist_item_id NUMBER,
233 p_object_version_number NUMBER)
234 IS
235 CURSOR C IS
236 SELECT *
237 FROM pv_ge_chklst_items_b
238 WHERE checklist_item_id = p_checklist_item_id
239 AND object_version_number = p_object_version_number
240 FOR UPDATE OF checklist_item_id NOWAIT;
241 Recinfo C%ROWTYPE;
242 BEGIN
243
244 OPEN c;
245 FETCH c INTO Recinfo;
246 IF (c%NOTFOUND) THEN
247 CLOSE c;
248 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
249 RAISE FND_API.g_exc_error;
250 END IF;
251 CLOSE c;
252 END Lock_Row;
253
254 -- ==============================================================================
255 -- Start of Comments
256 -- ==============================================================================
257 -- API Name
258 -- add_language
259 -- Type
260 -- Private
261 -- History
262 --
263 -- NOTE
264 --
265 -- End of Comments
266 -- ===============================================================
267
268
269
270 PROCEDURE Add_Language
271 IS
272 BEGIN
273 -- changing by ktsao as per performance team guidelines to fix performance issue
274 -- as described in bug 3723612 (*** RTIKKU 03/24/05 12:46pm ***)
275 INSERT /*+ append parallel(tt) */ INTO pv_ge_chklst_items_tl tt (
276 checklist_item_id,
277 creation_date,
278 created_by,
279 last_update_date,
280 last_updated_by,
281 last_update_login,
282 checklist_item_name,
283 language,
284 source_lang
285 )
286 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
287 ( SELECT /*+ no_merge ordered parallel(b) */
288 b.checklist_item_id,
289 b.creation_date,
290 b.created_by,
291 b.last_update_date,
292 b.last_updated_by,
293 b.last_update_login,
294 b.checklist_item_name,
295 l.language_code,
296 b.source_lang
297 FROM pv_ge_chklst_items_tl B ,
298 FND_LANGUAGES L
299 WHERE L.INSTALLED_FLAG IN ( 'I','B' )
300 AND B.LANGUAGE = USERENV ( 'LANG' )
301 ) v, pv_ge_chklst_items_tl t
302 WHERE t.checklist_item_id(+) = v.checklist_item_id
303 AND t.language(+) = v.language_code
304 AND t.checklist_item_id IS NULL;
305
306 END ADD_LANGUAGE;
307
308
309
310
311 END PV_Ge_Chklst_PKG;