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