eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMD_WIP_SPEC_VRS_PVT

Source


1 PACKAGE BODY GMD_WIP_SPEC_VRS_PVT AS
2 /* $Header: GMDVWVRB.pls 120.3.12010000.2 2009/03/18 16:14:26 rnalla ship $ */
3 
4 -- Start of comments
5 --+==========================================================================+
6 --|                   Copyright (c) 1998 Oracle Corporation                  |
7 --|                          Redwood Shores, CA, USA                         |
8 --|                            All rights reserved.                          |
9 --+==========================================================================+
10 --| File Name          : GMDVWVRB.pls                                        |
11 --| Package Name       : GMD_WIP_SPEC_VRS_PVT                                |
12 --| Type               : Private                                             |
13 --|                                                                          |
14 --| Notes                                                                    |
15 --|    This package contains private layer APIs for WIP VR.                  |
16 --|                                                                          |
17 --| HISTORY                                                                  |
18 --|    Chetan Nagar     07-Aug-2002     Created.                             |
19 --|                                                                          |
20 --|SaiKiran Vankadari   13-Apr-2004     BUG# 3464772                         |
21 --|                     Added column AUTO_SAMPLE_IND in the INSERT statement |
22 --|                                                                          |
23 --|SaiKiran Vankadari   04-May-2004     ENHANCEMENT#3476560                  |
24 --|                     Added column DELAYED_LOT_ENTRY  in the INSERT        |
25 --|                     statement.                                           |
26 --|SaiKiran Vankadari   13-Apr-2005   Convergence Changes                    |
27 --|SaiKiran Vankadari   04-Oct-2005  Added migrated_ind to the insert statement|
28 --|S. Feinstein         18-Oct-2005  Added material_detail_id to samples and vr
29 --|RLNAGARA LPN ME 7027149 08-May-2008  Added Delayed_LPN_Entry in the INSERT   |
30 --+==========================================================================+
31 -- End of comments
32 
33 FUNCTION insert_row (
34   p_wip_spec_vrs IN  GMD_WIP_SPEC_VRS%ROWTYPE
35 , x_wip_spec_vrs OUT NOCOPY GMD_WIP_SPEC_VRS%ROWTYPE) RETURN BOOLEAN IS
36 BEGIN
37 
38     x_wip_spec_vrs := p_wip_spec_vrs;
39 
40     INSERT INTO GMD_WIP_SPEC_VRS
41      (
42       SPEC_VR_ID
43      ,SPEC_ID
44      ,ORGANIZATION_ID
45      ,BATCH_ID
46      ,RECIPE_ID
47      ,RECIPE_NO
48      ,RECIPE_VERSION
49      ,FORMULA_ID
50      ,FORMULALINE_ID
51      ,MATERIAL_DETAIL_ID
52      ,FORMULA_NO
53      ,FORMULA_VERS
54      ,ROUTING_ID
55      ,ROUTING_NO
56      ,ROUTING_VERS
57      ,STEP_ID
58      ,STEP_NO
59      ,OPRN_ID
60      ,OPRN_NO
61      ,OPRN_VERS
62      ,CHARGE
63      ,SPEC_VR_STATUS
64      ,START_DATE
65      ,END_DATE
66      ,SAMPLING_PLAN_ID
67      ,SAMPLE_INV_TRANS_IND
68      ,LOT_OPTIONAL_ON_SAMPLE
69      ,CONTROL_LOT_ATTRIB_IND
70      ,OUT_OF_SPEC_LOT_STATUS_ID
71      ,IN_SPEC_LOT_STATUS_ID
72      ,COA_TYPE
73      ,CONTROL_BATCH_STEP_IND
74      ,COA_AT_SHIP_IND
75      ,COA_AT_INVOICE_IND
76      ,COA_REQ_FROM_SUPL_IND
77      ,DELETE_MARK
78      ,TEXT_CODE
79      ,ATTRIBUTE_CATEGORY
80      ,ATTRIBUTE1
81      ,ATTRIBUTE2
82      ,ATTRIBUTE3
83      ,ATTRIBUTE4
84      ,ATTRIBUTE5
85      ,ATTRIBUTE6
86      ,ATTRIBUTE7
87      ,ATTRIBUTE8
88      ,ATTRIBUTE9
89      ,ATTRIBUTE10
90      ,ATTRIBUTE11
91      ,ATTRIBUTE12
92      ,ATTRIBUTE13
93      ,ATTRIBUTE14
94      ,ATTRIBUTE15
95      ,ATTRIBUTE16
96      ,ATTRIBUTE17
97      ,ATTRIBUTE18
98      ,ATTRIBUTE19
99      ,ATTRIBUTE20
100      ,ATTRIBUTE21
101      ,ATTRIBUTE22
102      ,ATTRIBUTE23
103      ,ATTRIBUTE24
104      ,ATTRIBUTE25
105      ,ATTRIBUTE26
106      ,ATTRIBUTE27
107      ,ATTRIBUTE28
108      ,ATTRIBUTE29
109      ,ATTRIBUTE30
110      ,CREATION_DATE
111      ,CREATED_BY
112      ,LAST_UPDATED_BY
113      ,LAST_UPDATE_DATE
114      ,LAST_UPDATE_LOGIN
115      ,AUTO_SAMPLE_IND
116      ,DELAYED_LOT_ENTRY
117      ,AUTO_COMPLETE_BATCH_STEP -- Bug# 5440347
118      ,MIGRATED_IND     --To differentiate R12 data from previous data during migration
119      ,DELAYED_LPN_ENTRY  --RLNAGARA LPN ME 7027149
120      )
121      VALUES
122      (
123       gmd_qc_spec_vr_id_s.NEXTVAL
124      ,x_wip_spec_vrs.SPEC_ID
125      ,x_wip_spec_vrs.ORGANIZATION_ID
126      ,x_wip_spec_vrs.BATCH_ID
127      ,x_wip_spec_vrs.RECIPE_ID
128      ,x_wip_spec_vrs.RECIPE_NO
129      ,x_wip_spec_vrs.RECIPE_VERSION
130      ,x_wip_spec_vrs.FORMULA_ID
131      ,x_wip_spec_vrs.FORMULALINE_ID
132      ,x_wip_spec_vrs.MATERIAL_DETAIL_ID
133      ,x_wip_spec_vrs.FORMULA_NO
134      ,x_wip_spec_vrs.FORMULA_VERS
135      ,x_wip_spec_vrs.ROUTING_ID
136      ,x_wip_spec_vrs.ROUTING_NO
137      ,x_wip_spec_vrs.ROUTING_VERS
138      ,x_wip_spec_vrs.STEP_ID
139      ,x_wip_spec_vrs.STEP_NO
140      ,x_wip_spec_vrs.OPRN_ID
141      ,x_wip_spec_vrs.OPRN_NO
142      ,x_wip_spec_vrs.OPRN_VERS
143      ,x_wip_spec_vrs.CHARGE
144      ,x_wip_spec_vrs.SPEC_VR_STATUS
145      ,x_wip_spec_vrs.START_DATE
146      ,x_wip_spec_vrs.END_DATE
147      ,x_wip_spec_vrs.SAMPLING_PLAN_ID
148      ,x_wip_spec_vrs.SAMPLE_INV_TRANS_IND
149      ,x_wip_spec_vrs.LOT_OPTIONAL_ON_SAMPLE
150      ,x_wip_spec_vrs.CONTROL_LOT_ATTRIB_IND
151      ,x_wip_spec_vrs.OUT_OF_SPEC_LOT_STATUS_ID
152      ,x_wip_spec_vrs.IN_SPEC_LOT_STATUS_ID
153      ,x_wip_spec_vrs.COA_TYPE
154      ,x_wip_spec_vrs.CONTROL_BATCH_STEP_IND
155      ,x_wip_spec_vrs.COA_AT_SHIP_IND
156      ,x_wip_spec_vrs.COA_AT_INVOICE_IND
157      ,x_wip_spec_vrs.COA_REQ_FROM_SUPL_IND
158      ,x_wip_spec_vrs.DELETE_MARK
159      ,x_wip_spec_vrs.TEXT_CODE
160      ,x_wip_spec_vrs.ATTRIBUTE_CATEGORY
161      ,x_wip_spec_vrs.ATTRIBUTE1
162      ,x_wip_spec_vrs.ATTRIBUTE2
163      ,x_wip_spec_vrs.ATTRIBUTE3
164      ,x_wip_spec_vrs.ATTRIBUTE4
165      ,x_wip_spec_vrs.ATTRIBUTE5
166      ,x_wip_spec_vrs.ATTRIBUTE6
167      ,x_wip_spec_vrs.ATTRIBUTE7
168      ,x_wip_spec_vrs.ATTRIBUTE8
169      ,x_wip_spec_vrs.ATTRIBUTE9
170      ,x_wip_spec_vrs.ATTRIBUTE10
171      ,x_wip_spec_vrs.ATTRIBUTE11
172      ,x_wip_spec_vrs.ATTRIBUTE12
173      ,x_wip_spec_vrs.ATTRIBUTE13
174      ,x_wip_spec_vrs.ATTRIBUTE14
175      ,x_wip_spec_vrs.ATTRIBUTE15
176      ,x_wip_spec_vrs.ATTRIBUTE16
177      ,x_wip_spec_vrs.ATTRIBUTE17
178      ,x_wip_spec_vrs.ATTRIBUTE18
179      ,x_wip_spec_vrs.ATTRIBUTE19
180      ,x_wip_spec_vrs.ATTRIBUTE20
181      ,x_wip_spec_vrs.ATTRIBUTE21
182      ,x_wip_spec_vrs.ATTRIBUTE22
183      ,x_wip_spec_vrs.ATTRIBUTE23
184      ,x_wip_spec_vrs.ATTRIBUTE24
185      ,x_wip_spec_vrs.ATTRIBUTE25
186      ,x_wip_spec_vrs.ATTRIBUTE26
187      ,x_wip_spec_vrs.ATTRIBUTE27
188      ,x_wip_spec_vrs.ATTRIBUTE28
189      ,x_wip_spec_vrs.ATTRIBUTE29
190      ,x_wip_spec_vrs.ATTRIBUTE30
191      ,x_wip_spec_vrs.CREATION_DATE
192      ,x_wip_spec_vrs.CREATED_BY
193      ,x_wip_spec_vrs.LAST_UPDATED_BY
194      ,x_wip_spec_vrs.LAST_UPDATE_DATE
195      ,x_wip_spec_vrs.LAST_UPDATE_LOGIN
196      ,x_wip_spec_vrs.AUTO_SAMPLE_IND
197      ,x_wip_spec_vrs.DELAYED_LOT_ENTRY
198      ,x_wip_spec_vrs.AUTO_COMPLETE_BATCH_STEP -- Bug# 5440347
199      ,0
200      ,x_wip_spec_vrs.DELAYED_LPN_ENTRY      --RLNAGARA LPN ME 7027149
201      )
202         RETURNING spec_vr_id INTO x_wip_spec_vrs.spec_vr_id
203      ;
204 
205     IF SQL%FOUND THEN
206       RETURN TRUE;
207     ELSE
208       RETURN FALSE;
209     END IF;
210 
211   EXCEPTION
212     WHEN OTHERS THEN
213       fnd_msg_pub.add_exc_msg ('GMD_WIP_SPEC_VRS_PVT', 'INSERT_ROW');
214       RETURN FALSE;
215 
216 END insert_row;
217 
218 
219 
220 
221 
222 FUNCTION delete_row (p_spec_vr_id        IN NUMBER,
223                      p_last_update_date  IN  DATE     ,
224                      p_last_updated_by 	 IN  NUMBER   ,
225                      p_last_update_login IN  NUMBER   )
226 RETURN BOOLEAN IS
227 
228   dummy       PLS_INTEGER;
229 
230   locked_by_other_user          EXCEPTION;
231   PRAGMA EXCEPTION_INIT         (locked_by_other_user,-54);
232 
233 BEGIN
234   IF p_spec_vr_id IS NOT NULL THEN
235     SELECT 1
236     INTO   dummy
237     FROM   gmd_wip_spec_vrs
238     WHERE  spec_vr_id = p_spec_vr_id
239     FOR UPDATE NOWAIT;
240 
241     UPDATE gmd_wip_spec_vrs
242     SET    delete_mark = 1,
243            last_update_date  = NVL(p_last_update_date,SYSDATE),
244            last_updated_by   = NVL(p_last_updated_by,FND_GLOBAL.USER_ID),
245            last_update_login = NVL(p_last_update_login,FND_GLOBAL.LOGIN_ID)
246     WHERE  spec_vr_id = p_spec_vr_id
247     ;
248   ELSE
249     GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_WIP_SPEC_VRS');
250     RETURN FALSE;
251   END IF;
252 
253   IF (SQL%FOUND) THEN
254     RETURN TRUE;
255   ELSE
256     GMD_API_PUB.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_WIP_SPEC_VRS');
257     RETURN FALSE;
258   END IF;
259 
260 EXCEPTION
261   WHEN locked_by_other_user THEN
262     GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
263                             'TABLE_NAME', 'GMD_WIP_SPEC_VRS',
264                             'RECORD','WIP Spec Validity Rule',
265                             'KEY', p_spec_vr_id);
266     RETURN FALSE;
267 
268   WHEN OTHERS THEN
269      fnd_msg_pub.add_exc_msg ('GMD_WIP_SPEC_VRS_PVT', 'DELETE_ROW');
270       RETURN FALSE;
271 
272 END delete_row;
273 
274 
275 
276 
277 FUNCTION lock_row (p_spec_vr_id IN NUMBER)
278 RETURN BOOLEAN IS
279 
280   dummy       PLS_INTEGER;
281 
282   locked_by_other_user          EXCEPTION;
283   PRAGMA EXCEPTION_INIT         (locked_by_other_user,-54);
284 
285 BEGIN
286   IF p_spec_vr_id IS NOT NULL THEN
287     SELECT 1
288     INTO   dummy
289     FROM   gmd_wip_spec_vrs
290     WHERE  spec_vr_id = p_spec_vr_id
291     FOR UPDATE NOWAIT;
292   ELSE
293     GMD_API_PUB.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_WIP_SPEC_VRS');
294     RETURN FALSE;
295   END IF;
296 
297   RETURN TRUE;
298 
299 EXCEPTION
300   WHEN locked_by_other_user THEN
301     GMD_API_PUB.log_message('GMD_RECORD_LOCKED',
302                             'TABLE_NAME', 'GMD_WIP_SPEC_VRS',
303                             'RECORD','WIP Spec Validity Rule',
304                             'KEY', p_spec_vr_id);
305     RETURN FALSE;
306 
307   WHEN OTHERS THEN
308      fnd_msg_pub.add_exc_msg ('GMD_WIP_SPEC_VRS_PVT', 'DELETE_ROW');
309       RETURN FALSE;
310 
311 END lock_row;
312 
313 
314 
315 FUNCTION fetch_row (
316   p_wip_spec_vrs IN  gmd_wip_spec_vrs%ROWTYPE
317 , x_wip_spec_vrs OUT NOCOPY gmd_wip_spec_vrs%ROWTYPE
318 )
319 RETURN BOOLEAN
320 IS
321 BEGIN
322 
323   IF (p_wip_spec_vrs.spec_vr_id IS NOT NULL) THEN
324     SELECT *
325     INTO   x_wip_spec_vrs
326     FROM   gmd_wip_spec_vrs
327     WHERE  spec_vr_id = p_wip_spec_vrs.spec_vr_id
328     ;
329   ELSE
330     gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_WIP_SPEC_VRS');
331     RETURN FALSE;
332   END IF;
333 
334   RETURN TRUE;
335 
336 EXCEPTION
337  WHEN NO_DATA_FOUND
338    THEN
339      gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_WIP_SPEC_VRS');
340      RETURN FALSE;
341  WHEN OTHERS
342    THEN
343      fnd_msg_pub.add_exc_msg ('GMD_WIP_SPEC_VRS_PVT', 'FETCH_ROW');
344      RETURN FALSE;
345 
346 END fetch_row;
347 
348 END GMD_WIP_SPEC_VRS_PVT;