[Home] [Help]
PACKAGE BODY: APPS.GMI_TRAN_PND_DB_PVT
Source
1 PACKAGE BODY GMI_TRAN_PND_DB_PVT AS
2 /* $Header: GMIVPNDB.pls 115.12 2004/04/16 05:29:31 mkalyani ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVPNDB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private definitions For DML Actions |
13 | For IC_TRAN_PND |
14 | |
15 | HISTORY |
16 | 12-JAN-2000 H.Verdding |
17 | 24-AUG-2001 NC Added line_detail_id.BUG#1675561 |
18 | Piyush K. Mishra 6-Jun-2002 Bug#2385934 |
19 | Commented the line that updates the creation_date in the procedure |
20 | update_ic_tran_pnd as the creation date should not change during |
21 | updation. |
22 | 30-OCT-2002 J.DiIorio Bug#2643440 11.5.1J - added nocopy and |
23 | changed out to in out. |
24 | 15_AUG-2003 J.DiIorio Bug#3090255 11.5.10L |
25 | Added field intorder_posted_ind. |
26 | 2-APR-2004 V.Anitha BUG#3526733 |
30 | p_tran_row.tran_id to p_tran_row.reverse_id when |
27 | Added code to insert reverse_id into IC_TRAN_PND table |
28 | 14-APR-2004 V.Anita BUG#3526733 |
29 | Modified the value passed to the reverse_id from |
31 | inserting into IC_TRAN_PND table. |
32 +=========================================================================+
33 API Name : GMI_TRAN_PND_DB_PVT
34 Type : Public
35 Function : This package contains private procedures used to create
36 IC_TRAN_PND transactions
37 Pre-reqs : N/A
38 Parameters: Per function
39
40 Current Vers : 1.0
41
42 Previous Vers : 1.0
43
44 Initial Vers : 1.0
45 Notes
46
47 Body end of comments
48 */
49 /* Global variables */
50 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_TRAN_PND_DB_PVT';
51 /* Api start of comments */
52
53
54 FUNCTION INSERT_IC_TRAN_PND
55 (
56 p_tran_row IN IC_TRAN_PND%ROWTYPE,
57 x_tran_row IN OUT NOCOPY IC_TRAN_PND%ROWTYPE
58 )
59 RETURN BOOLEAN
60 IS
61 err_num NUMBER;
62 err_msg VARCHAR2(100);
63 l_trans_id NUMBER;
64
65 CURSOR C ( v_trans_id IN NUMBER) IS
66 SELECT trans_id FROM IC_TRAN_PND
67 WHERE trans_id = v_trans_id;
68
69
70 BEGIN
71
72 SELECT gem5_trans_id_s.nextval
73 INTO l_trans_id FROM dual;
74
75 INSERT INTO IC_TRAN_PND
76 (
77 trans_id
78 , item_id
79 , line_id
80 , co_code
81 , orgn_code
82 , whse_code
83 , lot_id
84 , location
85 , doc_id
86 , doc_type
87 , doc_line
88 , line_type
89 , reason_code
90 , creation_date
91 , trans_date
92 , trans_qty
93 , trans_qty2
94 , qc_grade
95 , lot_status
96 , trans_stat
97 , trans_um
98 , trans_um2
99 , op_code
100 , gl_posted_ind
101 , completed_ind
102 , delete_mark
103 , event_id
104 , staged_ind
105 , text_code
106 , last_update_date
107 , created_by
108 , last_updated_by
109 , line_detail_id
110 , intorder_posted_ind
111 , reverse_id --BUG#3526733
112 )
113 VALUES
114 ( l_trans_id
115 , p_tran_row.item_id
116 , p_tran_row.line_id
117 , p_tran_row.co_code
118 , p_tran_row.orgn_code
119 , p_tran_row.whse_code
120 , p_tran_row.lot_id
121 , p_tran_row.location
122 , p_tran_row.doc_id
123 , p_tran_row.doc_type
124 , p_tran_row.doc_line
125 , p_tran_row.line_type
126 , p_tran_row.reason_code
127 , p_tran_row.creation_date
128 , p_tran_row.trans_date
129 , p_tran_row.trans_qty
130 , p_tran_row.trans_qty2
131 , p_tran_row.qc_grade
132 , p_tran_row.lot_status
133 , p_tran_row.trans_stat
134 , p_tran_row.trans_um
135 , p_tran_row.trans_um2
136 , p_tran_row.op_code
137 , p_tran_row.gl_posted_ind
138 , p_tran_row.completed_ind
139 , p_tran_row.delete_mark
140 , p_tran_row.event_id
141 , p_tran_row.staged_ind
142 , p_tran_row.text_code
143 , p_tran_row.last_update_date
144 , p_tran_row.created_by
145 , p_tran_row.last_updated_by
146 , p_tran_row.line_detail_id
147 , p_tran_row.intorder_posted_ind
148 , p_tran_row.reverse_id --BUG#3526733
149 );
150
151 OPEN C(l_trans_id);
152 FETCH C into l_trans_id;
153
154 IF (C%NOTFOUND) THEN
155 CLOSE C;
156 RAISE NO_DATA_FOUND;
157 END IF;
158
159 CLOSE C;
160
161 x_tran_row := p_tran_row;
162 x_tran_row.trans_id := l_trans_id;
163 RETURN TRUE;
164
165 EXCEPTION
166
167 WHEN NO_DATA_FOUND THEN
168 RETURN FALSE;
169
170 WHEN OTHERS THEN
171
172 FND_MESSAGE.Set_Name('GMI','GMI_SQL_ERROR');
173 FND_MESSAGE.Set_Token('SQL_CODE', err_num);
174 FND_MESSAGE.Set_Token('SQL_ERRM', sqlerrm);
175 FND_MSG_PUB.Add;
176
177
178 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
179 , 'insert_ic_tran_pnd'
180 );
181 RETURN FALSE;
182
183 END INSERT_IC_TRAN_PND;
184
185 FUNCTION FETCH_IC_TRAN_PND
186 (
187 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
188 ,x_tran_fetch_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
189 )
190 RETURN BOOLEAN
191 IS
192 err_num NUMBER;
193 err_msg VARCHAR2(100);
194
195 BEGIN
196
197 IF ( ( p_tran_rec.trans_id =FND_API.G_MISS_NUM ) OR
198 ( p_tran_rec.trans_id is NULL ) ) THEN
199
200 /* Select by item_id, doc_type, line_id,lot_id , location */
201
202 SELECT
203 trans_id
204 , item_id
205 , line_id
206 , co_code
207 , orgn_code
208 , whse_code
209 , lot_id
210 , location
211 , doc_id
212 , doc_type
213 , doc_line
214 , line_type
215 , reason_code
216 , trans_date
217 , trans_qty
218 , trans_qty2
219 , qc_grade
220 , lot_status
221 , trans_stat
222 , trans_um
223 , trans_um2
224 , event_id
225 , staged_ind
226 , text_code
230 INTO
227 , op_code
228 , line_detail_id
229 , intorder_posted_ind
231 x_tran_fetch_rec.trans_id
232 , x_tran_fetch_rec.item_id
233 , x_tran_fetch_rec.line_id
234 , x_tran_fetch_rec.co_code
235 , x_tran_fetch_rec.orgn_code
236 , x_tran_fetch_rec.whse_code
237 , x_tran_fetch_rec.lot_id
238 , x_tran_fetch_rec.location
239 , x_tran_fetch_rec.doc_id
240 , x_tran_fetch_rec.doc_type
241 , x_tran_fetch_rec.doc_line
242 , x_tran_fetch_rec.line_type
243 , x_tran_fetch_rec.reason_code
244 , x_tran_fetch_rec.trans_date
245 , x_tran_fetch_rec.trans_qty
246 , x_tran_fetch_rec.trans_qty2
247 , x_tran_fetch_rec.qc_grade
248 , x_tran_fetch_rec.lot_status
249 , x_tran_fetch_rec.trans_stat
250 , x_tran_fetch_rec.trans_um
251 , x_tran_fetch_rec.trans_um2
252 , x_tran_fetch_rec.event_id
253 , x_tran_fetch_rec.staged_ind
254 , x_tran_fetch_rec.text_code
255 , x_tran_fetch_rec.user_id
256 , x_tran_fetch_rec.line_detail_id
257 , x_tran_fetch_rec.intorder_posted_ind
258 FROM IC_TRAN_PND
259 WHERE
260 doc_type = p_tran_rec.doc_type
261 AND doc_id = p_tran_rec.doc_id
262 AND line_id = p_tran_rec.line_id
263 AND item_id = p_tran_rec.item_id
264 AND lot_id = p_tran_rec.lot_id
265 AND location = p_tran_rec.location
266 AND completed_ind= 0
267 AND delete_mark = 0
268 FOR UPDATE NOWAIT;
269
270 ELSE
271
272 SELECT
273 trans_id
274 , item_id
275 , line_id
276 , co_code
277 , orgn_code
278 , whse_code
279 , lot_id
280 , location
281 , doc_id
282 , doc_type
283 , doc_line
284 , line_type
285 , reason_code
286 , trans_date
287 , trans_qty
288 , trans_qty2
289 , qc_grade
290 , lot_status
291 , trans_stat
292 , trans_um
293 , trans_um2
294 , event_id
295 , staged_ind
296 , text_code
297 , op_code
298 , line_detail_id
299 , intorder_posted_ind
300 INTO
301 x_tran_fetch_rec.trans_id
302 , x_tran_fetch_rec.item_id
303 , x_tran_fetch_rec.line_id
304 , x_tran_fetch_rec.co_code
305 , x_tran_fetch_rec.orgn_code
306 , x_tran_fetch_rec.whse_code
307 , x_tran_fetch_rec.lot_id
308 , x_tran_fetch_rec.location
309 , x_tran_fetch_rec.doc_id
310 , x_tran_fetch_rec.doc_type
311 , x_tran_fetch_rec.doc_line
312 , x_tran_fetch_rec.line_type
313 , x_tran_fetch_rec.reason_code
314 , x_tran_fetch_rec.trans_date
315 , x_tran_fetch_rec.trans_qty
316 , x_tran_fetch_rec.trans_qty2
317 , x_tran_fetch_rec.qc_grade
318 , x_tran_fetch_rec.lot_status
319 , x_tran_fetch_rec.trans_stat
320 , x_tran_fetch_rec.trans_um
321 , x_tran_fetch_rec.trans_um2
322 , x_tran_fetch_rec.event_id
323 , x_tran_fetch_rec.staged_ind
324 , x_tran_fetch_rec.text_code
325 , x_tran_fetch_rec.user_id
326 , x_tran_fetch_rec.line_detail_id
327 , x_tran_fetch_rec.intorder_posted_ind
328 FROM IC_TRAN_PND
329 WHERE
330 trans_id = p_tran_rec.trans_id
331 AND delete_mark =0
332 FOR UPDATE NOWAIT;
333
334 END IF;
335
336 IF (SQL%NOTFOUND) THEN
337 RAISE NO_DATA_FOUND;
338 ELSE
339 RETURN TRUE;
340 END IF;
341
342
343 EXCEPTION
344
345 WHEN NO_DATA_FOUND THEN
346
347 RETURN FALSE;
348
349 WHEN OTHERS THEN
350
351
352 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
353 , 'fetch_ic_tran_pnd'
354 );
355 RETURN FALSE;
356
357 END FETCH_IC_TRAN_PND;
358
359 FUNCTION DELETE_IC_TRAN_PND
360 (
361 p_tran_row IN IC_TRAN_PND%ROWTYPE
362 )
363 RETURN BOOLEAN
364 IS
365 err_num NUMBER;
366 err_msg VARCHAR2(100);
367
368 BEGIN
369
370 UPDATE IC_TRAN_PND
371 SET delete_mark = 1,
372 event_id = p_tran_row.event_id,
373 last_update_date = p_tran_row.last_update_date,
374 last_updated_by = p_tran_row.last_updated_by
375 WHERE trans_id = p_tran_row.trans_id;
376
377 IF (SQL%NOTFOUND) THEN
378 RAISE NO_DATA_FOUND;
379 ELSE
380 RETURN TRUE;
381 END IF;
382
383
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN
386 RETURN FALSE;
387
388 WHEN OTHERS THEN
389
390
391 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
392 , 'delete_ic_tran_pnd'
393 );
394 RETURN FALSE;
395
396 END DELETE_IC_TRAN_PND;
397
398 FUNCTION UPDATE_IC_TRAN_PND
399 (
400 p_tran_row IN IC_TRAN_PND%ROWTYPE
401 )
402 RETURN BOOLEAN
403 IS
404 err_num NUMBER;
405 err_msg VARCHAR2(100);
406
407 BEGIN
408
409 UPDATE IC_TRAN_PND
410 SET
411 item_id = p_tran_row.item_id,
412 line_id = p_tran_row.line_id,
413 co_code = p_tran_row.co_code,
414 orgn_code = p_tran_row.orgn_code,
415 whse_code = p_tran_row.whse_code,
416 lot_id = p_tran_row.lot_id,
417 location = p_tran_row.location,
418 doc_id = p_tran_row.doc_id,
419 doc_type = p_tran_row.doc_type,
420 doc_line = p_tran_row.doc_line,
421 line_type = p_tran_row.line_type,
422 reason_code = p_tran_row.reason_code,
423 --Begin Bug#2385934 Piyush K. Mishra.
424 --Commented the creation_date as updation of creation_date should not be done.
425 --creation_date = p_tran_row.creation_date,
426 --End Bug#2385934
427 trans_date = p_tran_row.trans_date,
428 trans_qty = p_tran_row.trans_qty,
429 trans_qty2 = p_tran_row.trans_qty2,
430 qc_grade = p_tran_row.qc_grade,
431 lot_status = p_tran_row.lot_status,
432 trans_stat = p_tran_row.trans_stat,
433 trans_um = p_tran_row.trans_um,
434 trans_um2 = p_tran_row.trans_um2,
435 gl_posted_ind = p_tran_row.gl_posted_ind,
436 completed_ind = p_tran_row.completed_ind,
437 delete_mark = p_tran_row.delete_mark,
438 event_id = p_tran_row.event_id,
439 staged_ind = p_tran_row.staged_ind,
440 text_code = p_tran_row.text_code,
441 last_update_date = p_tran_row.last_update_date,
442 last_updated_by = p_tran_row.last_updated_by,
443 op_code = p_tran_row.op_code,
444 line_detail_id = p_tran_row.line_detail_id,
445 intorder_posted_ind = p_tran_row.intorder_posted_ind
446 WHERE trans_id = p_tran_row.trans_id;
447
448 IF (SQL%NOTFOUND) THEN
449 RAISE NO_DATA_FOUND;
450 ELSE
451 RETURN TRUE;
452 END IF;
453
454 EXCEPTION
455
456 WHEN NO_DATA_FOUND THEN
457 RETURN FALSE;
458
459 WHEN OTHERS THEN
460
461 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
462 , 'UPDATE_ic_tran_pnd'
463 );
464 RETURN FALSE;
465
466 END UPDATE_IC_TRAN_PND;
467
468 END GMI_TRAN_PND_DB_PVT;