[Home] [Help]
PACKAGE BODY: APPS.OZF_RESALE_BATCH_LINE_MAPS_PKG
Source
1 PACKAGE BODY OZF_RESALE_BATCH_LINE_MAPS_PKG as
2 /* $Header: ozftrblb.pls 120.1.12000000.2 2007/05/28 10:29:44 ateotia ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_RESALE_BATCH_LINE_MAPS_PKG
7 -- Purpose
8 --
9 -- History
10 -- Anuj Teotia 28/05/2007 bug # 5997978 fixed
11 --
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_RESALE_BATCH_LINE_MAPS_PKG';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftrblb.pls';
20
21
22 ----------------------------------------------------------
23 ---- MEDIA ----
24 ----------------------------------------------------------
25
26 -- ========================================================
27 --
28 -- NAME
29 -- createInsertBody
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PROCEDURE Insert_Row(
39 px_resale_batch_line_map_id IN OUT NOCOPY NUMBER,
40 p_resale_batch_id NUMBER,
41 p_resale_line_id NUMBER,
42 px_object_version_number IN OUT NOCOPY NUMBER,
43 p_last_update_date DATE,
44 p_last_updated_by NUMBER,
45 p_creation_date DATE,
46 p_request_id NUMBER,
47 p_created_by NUMBER,
48 p_created_from VARCHAR2,
49 p_last_update_login NUMBER,
50 p_program_application_id NUMBER,
51 p_program_update_date DATE,
52 p_program_id NUMBER,
53 p_attribute_category VARCHAR2,
54 p_attribute1 VARCHAR2,
55 p_attribute2 VARCHAR2,
56 p_attribute3 VARCHAR2,
57 p_attribute4 VARCHAR2,
58 p_attribute5 VARCHAR2,
59 p_attribute6 VARCHAR2,
60 p_attribute7 VARCHAR2,
61 p_attribute8 VARCHAR2,
62 p_attribute9 VARCHAR2,
63 p_attribute10 VARCHAR2,
64 p_attribute11 VARCHAR2,
65 p_attribute12 VARCHAR2,
66 p_attribute13 VARCHAR2,
67 p_attribute14 VARCHAR2,
68 p_attribute15 VARCHAR2,
69 px_org_id IN OUT NOCOPY NUMBER)
70
71 IS
72 x_rowid VARCHAR2(30);
73 l_batch_org_id NUMBER; -- bug # 5997978 fixed
74
75 BEGIN
76
77 -- Start: bug # 5997978 fixed
78 IF px_org_id IS NULL THEN
79 OPEN OZF_RESALE_COMMON_PVT.g_resale_batch_org_id_csr(p_resale_batch_id);
80 FETCH OZF_RESALE_COMMON_PVT.g_resale_batch_org_id_csr INTO l_batch_org_id;
81 CLOSE OZF_RESALE_COMMON_PVT.g_resale_batch_org_id_csr;
82 px_org_id := MO_GLOBAL.get_valid_org(l_batch_org_id);
83 IF (l_batch_org_id IS NULL OR px_org_id IS NULL) THEN
84 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_ORG_ID_NOTFOUND');
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87 /*IF (px_org_id IS NULL OR px_org_id = FND_API.G_MISS_NUM) THEN
88 SELECT NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
89 INTO px_org_id
90 FROM DUAL; */
91 END IF;
92 -- End: bug # 5997978 fixed
93
94 px_object_version_number := 1;
95
96
97 INSERT INTO OZF_RESALE_BATCH_LINE_MAPS_ALL(
98 resale_batch_line_map_id,
99 resale_batch_id,
100 resale_line_id,
101 object_version_number,
102 last_update_date,
103 last_updated_by,
104 creation_date,
105 request_id,
106 created_by,
107 created_from,
108 last_update_login,
109 program_application_id,
110 program_update_date,
111 program_id,
112 attribute_category,
113 attribute1,
114 attribute2,
115 attribute3,
116 attribute4,
117 attribute5,
118 attribute6,
119 attribute7,
120 attribute8,
121 attribute9,
122 attribute10,
123 attribute11,
124 attribute12,
125 attribute13,
126 attribute14,
127 attribute15,
128 org_id
129 ) VALUES (
130 px_resale_batch_line_map_id,
131 p_resale_batch_id,
132 p_resale_line_id,
133 px_object_version_number,
134 p_last_update_date,
135 p_last_updated_by,
136 p_creation_date,
137 p_request_id,
138 p_created_by,
139 p_created_from,
140 p_last_update_login,
141 p_program_application_id,
142 p_program_update_date,
143 p_program_id,
144 p_attribute_category,
145 p_attribute1,
146 p_attribute2,
147 p_attribute3,
148 p_attribute4,
149 p_attribute5,
150 p_attribute6,
151 p_attribute7,
152 p_attribute8,
153 p_attribute9,
154 p_attribute10,
155 p_attribute11,
156 p_attribute12,
157 p_attribute13,
158 p_attribute14,
159 p_attribute15,
160 px_org_id);
161 END Insert_Row;
162
163
164 ----------------------------------------------------------
165 ---- MEDIA ----
166 ----------------------------------------------------------
167
168 -- ========================================================
169 --
170 -- NAME
171 -- createUpdateBody
172 --
173 -- PURPOSE
174 --
175 -- NOTES
176 --
177 -- HISTORY
178 --
179 -- ========================================================
180 PROCEDURE Update_Row(
181 p_resale_batch_line_map_id NUMBER,
182 p_resale_batch_id NUMBER,
183 p_resale_line_id NUMBER,
184 p_object_version_number NUMBER,
185 p_last_update_date DATE,
186 p_last_updated_by NUMBER,
187 p_request_id NUMBER,
188 p_created_from VARCHAR2,
189 p_last_update_login NUMBER,
190 p_program_application_id NUMBER,
191 p_program_update_date DATE,
192 p_program_id NUMBER,
193 p_attribute_category VARCHAR2,
194 p_attribute1 VARCHAR2,
195 p_attribute2 VARCHAR2,
196 p_attribute3 VARCHAR2,
197 p_attribute4 VARCHAR2,
198 p_attribute5 VARCHAR2,
199 p_attribute6 VARCHAR2,
200 p_attribute7 VARCHAR2,
201 p_attribute8 VARCHAR2,
202 p_attribute9 VARCHAR2,
203 p_attribute10 VARCHAR2,
204 p_attribute11 VARCHAR2,
205 p_attribute12 VARCHAR2,
206 p_attribute13 VARCHAR2,
207 p_attribute14 VARCHAR2,
208 p_attribute15 VARCHAR2,
209 p_org_id NUMBER)
210
211 IS
212 BEGIN
213 Update OZF_RESALE_BATCH_LINE_MAPS_ALL
214 SET
215 resale_batch_line_map_id = p_resale_batch_line_map_id,
216 resale_batch_id = p_resale_batch_id,
217 resale_line_id = p_resale_line_id,
218 object_version_number = p_object_version_number,
219 last_update_date = p_last_update_date,
220 last_updated_by = p_last_updated_by,
221 request_id = p_request_id,
222 created_from = p_created_from,
223 last_update_login = p_last_update_login,
224 program_application_id = p_program_application_id,
225 program_update_date = p_program_update_date,
226 program_id = p_program_id,
227 attribute_category = p_attribute_category,
228 attribute1 = p_attribute1,
229 attribute2 = p_attribute2,
230 attribute3 = p_attribute3,
231 attribute4 = p_attribute4,
232 attribute5 = p_attribute5,
233 attribute6 = p_attribute6,
234 attribute7 = p_attribute7,
235 attribute8 = p_attribute8,
236 attribute9 = p_attribute9,
237 attribute10 = p_attribute10,
238 attribute11 = p_attribute11,
239 attribute12 = p_attribute12,
240 attribute13 = p_attribute13,
241 attribute14 = p_attribute14,
242 attribute15 = p_attribute15,
243 org_id = p_org_id
244 WHERE RESALE_BATCH_LINE_MAP_ID = p_RESALE_BATCH_LINE_MAP_ID
245 AND object_version_number = p_object_version_number;
246
247 IF (SQL%NOTFOUND) THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END IF;
250 END Update_Row;
251
252
253 ----------------------------------------------------------
254 ---- MEDIA ----
255 ----------------------------------------------------------
256
257 -- ========================================================
258 --
259 -- NAME
260 -- createDeleteBody
261 --
262 -- PURPOSE
263 --
264 -- NOTES
265 --
266 -- HISTORY
267 --
268 -- ========================================================
269 PROCEDURE Delete_Row(
270 p_RESALE_BATCH_LINE_MAP_ID NUMBER)
271 IS
272 BEGIN
273 DELETE FROM OZF_RESALE_BATCH_LINE_MAPS_ALL
274 WHERE RESALE_BATCH_LINE_MAP_ID = p_RESALE_BATCH_LINE_MAP_ID;
275 If (SQL%NOTFOUND) then
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 End If;
278 END Delete_Row ;
279
280
281
282 ----------------------------------------------------------
283 ---- MEDIA ----
284 ----------------------------------------------------------
285
286 -- ========================================================
287 --
288 -- NAME
289 -- createLockBody
290 --
291 -- PURPOSE
292 --
293 -- NOTES
294 --
295 -- HISTORY
296 --
297 -- ========================================================
298 PROCEDURE Lock_Row(
299 p_resale_batch_line_map_id NUMBER,
300 p_resale_batch_id NUMBER,
301 p_resale_line_id NUMBER,
302 p_object_version_number NUMBER,
303 p_last_update_date DATE,
304 p_last_updated_by NUMBER,
305 p_creation_date DATE,
306 p_request_id NUMBER,
307 p_created_by NUMBER,
308 p_created_from VARCHAR2,
309 p_last_update_login NUMBER,
310 p_program_application_id NUMBER,
311 p_program_update_date DATE,
312 p_program_id NUMBER,
313 p_attribute_category VARCHAR2,
314 p_attribute1 VARCHAR2,
315 p_attribute2 VARCHAR2,
316 p_attribute3 VARCHAR2,
317 p_attribute4 VARCHAR2,
318 p_attribute5 VARCHAR2,
319 p_attribute6 VARCHAR2,
320 p_attribute7 VARCHAR2,
321 p_attribute8 VARCHAR2,
322 p_attribute9 VARCHAR2,
323 p_attribute10 VARCHAR2,
324 p_attribute11 VARCHAR2,
325 p_attribute12 VARCHAR2,
326 p_attribute13 VARCHAR2,
327 p_attribute14 VARCHAR2,
328 p_attribute15 VARCHAR2,
329 p_org_id NUMBER)
330
331 IS
332 CURSOR C IS
333 SELECT *
334 FROM OZF_RESALE_BATCH_LINE_MAPS_ALL
335 WHERE RESALE_BATCH_LINE_MAP_ID = p_RESALE_BATCH_LINE_MAP_ID
336 FOR UPDATE of RESALE_BATCH_LINE_MAP_ID NOWAIT;
337 Recinfo C%ROWTYPE;
338 BEGIN
339 OPEN c;
340 FETCH c INTO Recinfo;
341 If (c%NOTFOUND) then
342 CLOSE c;
343 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
344 APP_EXCEPTION.RAISE_EXCEPTION;
345 END IF;
346 CLOSE C;
347 IF (
348 ( Recinfo.resale_batch_line_map_id = p_resale_batch_line_map_id)
349 AND ( ( Recinfo.resale_batch_id = p_resale_batch_id)
350 OR ( ( Recinfo.resale_batch_id IS NULL )
351 AND ( p_resale_batch_id IS NULL )))
352 AND ( ( Recinfo.resale_line_id = p_resale_line_id)
353 OR ( ( Recinfo.resale_line_id IS NULL )
354 AND ( p_resale_line_id IS NULL )))
355 AND ( ( Recinfo.object_version_number = p_object_version_number)
356 OR ( ( Recinfo.object_version_number IS NULL )
357 AND ( p_object_version_number IS NULL )))
358 AND ( ( Recinfo.last_update_date = p_last_update_date)
359 OR ( ( Recinfo.last_update_date IS NULL )
360 AND ( p_last_update_date IS NULL )))
361 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
362 OR ( ( Recinfo.last_updated_by IS NULL )
363 AND ( p_last_updated_by IS NULL )))
364 AND ( ( Recinfo.creation_date = p_creation_date)
365 OR ( ( Recinfo.creation_date IS NULL )
366 AND ( p_creation_date IS NULL )))
367 AND ( ( Recinfo.request_id = p_request_id)
368 OR ( ( Recinfo.request_id IS NULL )
369 AND ( p_request_id IS NULL )))
370 AND ( ( Recinfo.created_by = p_created_by)
371 OR ( ( Recinfo.created_by IS NULL )
372 AND ( p_created_by IS NULL )))
376 AND ( ( Recinfo.last_update_login = p_last_update_login)
373 AND ( ( Recinfo.created_from = p_created_from)
374 OR ( ( Recinfo.created_from IS NULL )
375 AND ( p_created_from IS NULL )))
377 OR ( ( Recinfo.last_update_login IS NULL )
378 AND ( p_last_update_login IS NULL )))
379 AND ( ( Recinfo.program_application_id = p_program_application_id)
380 OR ( ( Recinfo.program_application_id IS NULL )
381 AND ( p_program_application_id IS NULL )))
382 AND ( ( Recinfo.program_update_date = p_program_update_date)
383 OR ( ( Recinfo.program_update_date IS NULL )
384 AND ( p_program_update_date IS NULL )))
385 AND ( ( Recinfo.program_id = p_program_id)
386 OR ( ( Recinfo.program_id IS NULL )
387 AND ( p_program_id IS NULL )))
388 AND ( ( Recinfo.attribute_category = p_attribute_category)
389 OR ( ( Recinfo.attribute_category IS NULL )
390 AND ( p_attribute_category IS NULL )))
391 AND ( ( Recinfo.attribute1 = p_attribute1)
392 OR ( ( Recinfo.attribute1 IS NULL )
393 AND ( p_attribute1 IS NULL )))
394 AND ( ( Recinfo.attribute2 = p_attribute2)
395 OR ( ( Recinfo.attribute2 IS NULL )
396 AND ( p_attribute2 IS NULL )))
397 AND ( ( Recinfo.attribute3 = p_attribute3)
398 OR ( ( Recinfo.attribute3 IS NULL )
399 AND ( p_attribute3 IS NULL )))
400 AND ( ( Recinfo.attribute4 = p_attribute4)
401 OR ( ( Recinfo.attribute4 IS NULL )
402 AND ( p_attribute4 IS NULL )))
403 AND ( ( Recinfo.attribute5 = p_attribute5)
404 OR ( ( Recinfo.attribute5 IS NULL )
405 AND ( p_attribute5 IS NULL )))
406 AND ( ( Recinfo.attribute6 = p_attribute6)
407 OR ( ( Recinfo.attribute6 IS NULL )
408 AND ( p_attribute6 IS NULL )))
409 AND ( ( Recinfo.attribute7 = p_attribute7)
410 OR ( ( Recinfo.attribute7 IS NULL )
411 AND ( p_attribute7 IS NULL )))
412 AND ( ( Recinfo.attribute8 = p_attribute8)
413 OR ( ( Recinfo.attribute8 IS NULL )
414 AND ( p_attribute8 IS NULL )))
415 AND ( ( Recinfo.attribute9 = p_attribute9)
416 OR ( ( Recinfo.attribute9 IS NULL )
417 AND ( p_attribute9 IS NULL )))
418 AND ( ( Recinfo.attribute10 = p_attribute10)
419 OR ( ( Recinfo.attribute10 IS NULL )
420 AND ( p_attribute10 IS NULL )))
421 AND ( ( Recinfo.attribute11 = p_attribute11)
422 OR ( ( Recinfo.attribute11 IS NULL )
423 AND ( p_attribute11 IS NULL )))
424 AND ( ( Recinfo.attribute12 = p_attribute12)
425 OR ( ( Recinfo.attribute12 IS NULL )
426 AND ( p_attribute12 IS NULL )))
427 AND ( ( Recinfo.attribute13 = p_attribute13)
428 OR ( ( Recinfo.attribute13 IS NULL )
429 AND ( p_attribute13 IS NULL )))
430 AND ( ( Recinfo.attribute14 = p_attribute14)
431 OR ( ( Recinfo.attribute14 IS NULL )
432 AND ( p_attribute14 IS NULL )))
433 AND ( ( Recinfo.attribute15 = p_attribute15)
434 OR ( ( Recinfo.attribute15 IS NULL )
435 AND ( p_attribute15 IS NULL )))
436 AND ( ( Recinfo.org_id = p_org_id)
437 OR ( ( Recinfo.org_id IS NULL )
438 AND ( p_org_id IS NULL )))
439 ) THEN
440 RETURN;
441 ELSE
442 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
443 APP_EXCEPTION.RAISE_EXCEPTION;
444 END IF;
445 END Lock_Row;
446
447 END OZF_RESALE_BATCH_LINE_MAPS_PKG;