[Home] [Help]
PACKAGE BODY: APPS.GHG_ASSETS_PKG
Source
1 PACKAGE BODY ghg_assets_pkg AS
2 /*$Header: ghgasetb.pls 120.1 2011/10/31 09:03:13 pwaghmar noship $ */
3
4
5 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
6 x_ghg_asset_id NUMBER,
7 x_asset_id NUMBER,
8 x_asset_type_lookup_code VARCHAR2,
9 x_book_type_code VARCHAR2,
10 x_asset_number VARCHAR2,
11 x_description VARCHAR2,
12 x_tag_number VARCHAR2,
13 x_serial_number VARCHAR2,
14 x_transport_type_lookup_code VARCHAR2,
15 x_vendor_id NUMBER,
16 x_vendor_site_id NUMBER,
17 x_start_date DATE,
18 x_end_date DATE,
19 x_created_by NUMBER,
20 x_creation_date DATE,
21 x_last_updated_by NUMBER,
22 x_last_update_date date,
23 x_last_update_login number,
24 x_ghg_organization_id number) IS
25
26 v_debug_info VARCHAR2(100);
27
28 CURSOR c IS
29 SELECT ROWID
30 FROM GHG_ASSETS
31 WHERE ghg_asset_id = x_ghg_asset_id;
32
33 BEGIN
34
35 v_debug_info := 'Inserting into GHG_ASSETS';
36
37 INSERT INTO GHG_ASSETS (ghg_asset_id,
38 asset_id,
39 asset_type_lookup_code,
40 book_type_code,
41 asset_number,
42 description,
43 tag_number,
44 serial_number,
45 transport_type_lookup_code,
46 vendor_id,
47 vendor_site_id,
48 start_date,
49 end_date,
50 created_by,
51 creation_date,
52 last_updated_by,
53 last_update_date,
54 last_update_login,
55 ghg_organization_id )
56 VALUES (x_ghg_asset_id,
57 x_asset_id,
58 x_asset_type_lookup_code,
59 x_book_type_code,
60 x_asset_number,
61 x_description,
62 x_tag_number,
63 x_serial_number,
64 x_transport_type_lookup_code,
65 x_vendor_id,
66 x_vendor_site_id,
67 x_start_date,
68 x_end_date,
69 x_created_by,
70 x_creation_date,
71 x_last_updated_by,
72 x_last_update_date,
73 x_last_update_login,
74 x_ghg_organization_id);
75
76 v_debug_info := 'Open cursor c';
77 OPEN c;
78
79 v_debug_info := 'Fetch cursor c';
80 FETCH c INTO x_rowid;
81
82 IF (c%notfound) THEN
83 v_debug_info := 'Close cursor c - ROW NOT FOUND';
84 CLOSE c;
85 RAISE no_data_found;
86 END IF;
87
88 v_debug_info := 'Close cursor c';
89 CLOSE c;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF (SQLCODE <> -20001) THEN
94 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
95 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
96 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
97 END IF;
98 APP_EXCEPTION.RAISE_EXCEPTION;
99
100 END insert_row;
101
102 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
103 x_ghg_asset_id NUMBER,
104 x_asset_id NUMBER,
105 x_asset_type_lookup_code VARCHAR2,
106 x_book_type_code VARCHAR2,
107 x_asset_number VARCHAR2,
108 x_description VARCHAR2,
109 x_tag_number VARCHAR2,
110 x_serial_number VARCHAR2,
111 x_transport_type_lookup_code VARCHAR2,
112 x_vendor_id NUMBER,
113 x_vendor_site_id NUMBER,
114 x_start_date DATE,
115 x_end_date DATE,
116 x_created_by NUMBER,
117 x_creation_date DATE,
118 x_last_updated_by NUMBER,
119 x_last_update_date date,
120 x_last_update_login NUMBER,
121 x_ghg_organization_id number ) IS
122
123 v_debug_info VARCHAR2(100);
124
125 BEGIN
126
127 v_debug_info := 'Updating GHG_ASSETS';
128
129 UPDATE GHG_ASSETS
130 SET ghg_asset_id = x_ghg_asset_id,
131 asset_id = x_asset_id,
132 asset_type_lookup_code = x_asset_type_lookup_code,
133 book_type_code = x_book_type_code,
134 asset_number = x_asset_number,
135 description = x_description,
136 tag_number = x_tag_number,
137 serial_number = x_serial_number,
138 transport_type_lookup_code = x_transport_type_lookup_code,
139 vendor_id = x_vendor_id,
140 vendor_site_id = x_vendor_site_id,
141 start_date = x_start_date,
142 end_date = x_end_date,
143 created_by = x_created_by,
144 creation_date = x_creation_date,
145 last_updated_by = x_last_updated_by,
146 last_update_date = x_last_update_date,
147 last_update_login = x_last_update_login,
148 ghg_organization_id = x_ghg_organization_id
149 WHERE rowid = x_rowid;
150
151 IF (SQL%NOTFOUND) THEN
152 RAISE NO_DATA_FOUND;
153 END IF;
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 IF (SQLCODE <> -20001) THEN
158 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
159 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
160 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
161 END IF;
162 APP_EXCEPTION.RAISE_EXCEPTION;
163
164 END update_row;
165
166 PROCEDURE delete_row (x_ghg_asset_id NUMBER) IS
167
168 v_row_count NUMBER(15);
169
170 BEGIN
171
172 DELETE FROM GHG_ASSETS
173 WHERE ghg_asset_id = x_ghg_asset_id;
174
175 IF (sql%notfound) THEN
176 RAISE no_data_found;
177 END IF;
178
179 END delete_row;
180
181 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
182 x_ghg_asset_id NUMBER,
183 x_asset_id NUMBER,
184 x_asset_type_lookup_code VARCHAR2,
185 x_book_type_code VARCHAR2,
186 x_asset_number VARCHAR2,
187 x_description VARCHAR2,
188 x_tag_number VARCHAR2,
189 x_serial_number VARCHAR2,
190 x_transport_type_lookup_code VARCHAR2,
191 x_vendor_id NUMBER,
192 x_vendor_site_id NUMBER,
193 x_start_date DATE,
194 x_end_date DATE,
195 x_created_by NUMBER,
196 x_creation_date DATE,
197 x_last_updated_by NUMBER,
198 x_last_update_date DATE,
199 x_last_update_login number,
200 x_ghg_organization_id number ) IS
201
202 v_debug_info VARCHAR2(100);
203
204 CURSOR c IS
205 SELECT ghg_asset_id,
206 asset_id,
207 asset_type_lookup_code,
208 book_type_code,
209 asset_number,
210 description,
211 tag_number,
212 serial_number,
213 transport_type_lookup_code,
214 vendor_id,
215 vendor_site_id,
216 start_date,
217 end_date,
218 created_by,
219 creation_date,
220 last_updated_by,
221 last_update_date,
222 last_update_login,
223 ghg_organization_id
224 FROM GHG_ASSETS
225 WHERE rowid = x_rowid
226 FOR UPDATE of ghg_asset_id NOWAIT;
227 recinfo C%ROWTYPE;
228
229 BEGIN
230
231 v_debug_info := 'Open cursor C';
232
233 OPEN C;
234
235 v_debug_info := 'Fetch cursor C';
236
237 FETCH C INTO recinfo;
238
239 IF (C%NOTFOUND) THEN
240 v_debug_info := 'Close cursor C - ROW NOT FOUND';
241 CLOSE C;
242 RAISE NO_DATA_FOUND;
243 END IF;
244
245 v_debug_info := 'Close cursor C';
246
247 CLOSE C;
248
249 IF ( ((recinfo.ghg_asset_id = x_ghg_asset_id) OR
250 (recinfo.ghg_asset_id IS NULL AND x_ghg_asset_id IS NULL))
251 AND ((recinfo.asset_id = x_asset_id) OR
252 (recinfo.asset_id IS NULL AND x_asset_id IS NULL))
253 AND ((recinfo.asset_type_lookup_code = x_asset_type_lookup_code) OR
254 (recinfo.asset_type_lookup_code IS NULL AND
255 x_asset_type_lookup_code IS NULL))
256 AND ((recinfo.book_type_code = x_book_type_code) OR
257 (recinfo.book_type_code IS NULL AND x_book_type_code IS NULL))
258 AND ((recinfo.asset_number = x_asset_number) OR
259 (recinfo.asset_number IS NULL AND x_asset_number IS NULL))
260 AND ((recinfo.description = x_description) OR
261 (recinfo.description IS NULL AND x_description IS NULL))
262 AND ((recinfo.tag_number = x_tag_number) OR
263 (recinfo.tag_number IS NULL AND x_tag_number IS NULL))
264 AND ((recinfo.serial_number = x_serial_number) OR
265 (recinfo.serial_number IS NULL AND x_serial_number IS NULL))
266 AND ((recinfo.transport_type_lookup_code = x_transport_type_lookup_code)
267 OR
268 (recinfo.transport_type_lookup_code IS NULL AND
269 x_transport_type_lookup_code IS NULL))
270 AND ((recinfo.vendor_id = x_vendor_id) OR
271 (recinfo.vendor_id IS NULL AND x_vendor_id IS NULL))
272 and ((recinfo.ghg_organization_id = x_ghg_organization_id) or
273 (recinfo.ghg_organization_id is null and
274 x_ghg_organization_id is null))
275 AND ((recinfo.vendor_site_id = x_vendor_site_id) OR
276 (recinfo.vendor_site_id IS NULL AND x_vendor_site_id IS NULL))
277 AND ((recinfo.start_date = x_start_date) OR
278 (recinfo.start_date IS NULL AND x_start_date IS NULL))
279 AND ((recinfo.end_date = x_end_date) OR
280 (recinfo.end_date IS NULL AND x_end_date IS NULL))
281 AND ((recinfo.created_by = x_created_by) OR
282 (recinfo.created_by IS NULL AND x_created_by IS NULL))
283 AND ((recinfo.creation_date = x_creation_date) OR
284 (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
285 AND ((recinfo.last_updated_by = x_last_updated_by) OR
286 (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
287 AND ((recinfo.last_update_date = x_last_update_date) OR
288 (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
289 AND ((recinfo.last_update_login = x_last_update_login) OR
290 (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)))
291 THEN
292 NULL;
293 ELSE
294 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
295 APP_EXCEPTION.RAISE_EXCEPTION;
296 END IF;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF (SQLCODE <> -20001) THEN
301 IF (SQLCODE = -54) THEN
302 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
303 ELSE
304 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
305 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
306 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
307 END IF;
308 END IF;
309 APP_EXCEPTION.RAISE_EXCEPTION;
310
311 END lock_row;
312
313 END GHG_ASSETS_pkg;