[Home] [Help]
PACKAGE BODY: APPS.CSD_MASS_RO_SN_PKG
Source
1 PACKAGE BODY CSD_MASS_RO_SN_PKG as
2 /* $Header: csdtmrsb.pls 115.4 2003/10/28 18:53:43 vparvath noship $ */
3 -- Start of Comments
4 -- Package name : CSD_MASS_RO_SN_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_MASS_RO_SN_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtmrsb.pls';
13
14 PROCEDURE Insert_Row(
15 px_MASS_RO_SN_ID IN OUT NOCOPY NUMBER
16 ,p_REPAIR_LINE_ID NUMBER
17 ,p_SERIAL_NUMBER VARCHAR2
18 ,p_INSTANCE_ID NUMBER
19 ,p_PROCESS_FLAG VARCHAR2
20 ,p_CREATED_BY NUMBER
21 ,p_CREATION_DATE DATE
22 ,p_LAST_UPDATED_BY NUMBER
23 ,p_LAST_UPDATE_DATE DATE
24 ,p_LAST_UPDATE_LOGIN NUMBER
25 ,p_OBJECT_VERSION_NUMBER NUMBER
26 ,p_ATTRIBUTE_CATEGORY VARCHAR2
27 ,p_ATTRIBUTE1 VARCHAR2
28 ,p_ATTRIBUTE2 VARCHAR2
29 ,p_ATTRIBUTE3 VARCHAR2
30 ,p_ATTRIBUTE4 VARCHAR2
31 ,p_ATTRIBUTE5 VARCHAR2
32 ,p_ATTRIBUTE6 VARCHAR2
33 ,p_ATTRIBUTE7 VARCHAR2
34 ,p_ATTRIBUTE8 VARCHAR2
35 ,p_ATTRIBUTE9 VARCHAR2
36 ,p_ATTRIBUTE10 VARCHAR2
37 ,p_ATTRIBUTE11 VARCHAR2
38 ,p_ATTRIBUTE12 VARCHAR2
39 ,p_ATTRIBUTE13 VARCHAR2
40 ,p_ATTRIBUTE14 VARCHAR2
41 ,p_ATTRIBUTE15 VARCHAR2)
42
43 IS
44 CURSOR C2 IS SELECT CSD_MASS_RO_SN_S1.nextval FROM sys.dual;
45 BEGIN
46 If (px_MASS_RO_SN_ID IS NULL) OR (px_MASS_RO_SN_ID = FND_API.G_MISS_NUM) then
47 OPEN C2;
48 FETCH C2 INTO px_MASS_RO_SN_ID;
49 CLOSE C2;
50 End If;
51 INSERT INTO CSD_MASS_RO_SN(
52 MASS_RO_SN_ID
53 ,REPAIR_LINE_ID
54 ,SERIAL_NUMBER
55 ,INSTANCE_ID
56 ,PROCESS_FLAG
57 ,CREATED_BY
58 ,CREATION_DATE
59 ,LAST_UPDATED_BY
60 ,LAST_UPDATE_DATE
61 ,LAST_UPDATE_LOGIN
62 ,OBJECT_VERSION_NUMBER
63 ,ATTRIBUTE_GROUP
64 ,ATTRIBUTE1
65 ,ATTRIBUTE2
66 ,ATTRIBUTE3
67 ,ATTRIBUTE4
68 ,ATTRIBUTE5
69 ,ATTRIBUTE6
70 ,ATTRIBUTE7
71 ,ATTRIBUTE8
72 ,ATTRIBUTE9
73 ,ATTRIBUTE10
74 ,ATTRIBUTE11
75 ,ATTRIBUTE12
76 ,ATTRIBUTE13
77 ,ATTRIBUTE14
78 ,ATTRIBUTE15
79 ) VALUES (
80 px_MASS_RO_SN_ID
81 ,decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_LINE_ID)
82 ,decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, p_SERIAL_NUMBER)
83 ,decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, NULL, p_INSTANCE_ID)
84 ,decode( p_PROCESS_FLAG, FND_API.G_MISS_CHAR, NULL, p_PROCESS_FLAG)
85 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
86 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
87 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
88 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
89 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
90 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
91 ,decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
92 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
93 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
94 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
95 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
96 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
97 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
98 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
99 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
100 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
101 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
102 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
103 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
104 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
105 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
106 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15));
107 End Insert_Row;
108
109 PROCEDURE Update_Row(
110 p_MASS_RO_SN_ID NUMBER
111 ,p_REPAIR_LINE_ID NUMBER
112 ,p_SERIAL_NUMBER VARCHAR2
113 ,p_INSTANCE_ID NUMBER
114 ,p_PROCESS_FLAG VARCHAR2
115 ,p_CREATED_BY NUMBER
116 ,p_CREATION_DATE DATE
117 ,p_LAST_UPDATED_BY NUMBER
118 ,p_LAST_UPDATE_DATE DATE
119 ,p_LAST_UPDATE_LOGIN NUMBER
120 ,p_OBJECT_VERSION_NUMBER NUMBER
121 ,p_ATTRIBUTE_CATEGORY VARCHAR2
122 ,p_ATTRIBUTE1 VARCHAR2
123 ,p_ATTRIBUTE2 VARCHAR2
124 ,p_ATTRIBUTE3 VARCHAR2
125 ,p_ATTRIBUTE4 VARCHAR2
126 ,p_ATTRIBUTE5 VARCHAR2
127 ,p_ATTRIBUTE6 VARCHAR2
128 ,p_ATTRIBUTE7 VARCHAR2
129 ,p_ATTRIBUTE8 VARCHAR2
130 ,p_ATTRIBUTE9 VARCHAR2
131 ,p_ATTRIBUTE10 VARCHAR2
132 ,p_ATTRIBUTE11 VARCHAR2
133 ,p_ATTRIBUTE12 VARCHAR2
134 ,p_ATTRIBUTE13 VARCHAR2
135 ,p_ATTRIBUTE14 VARCHAR2
136 ,p_ATTRIBUTE15 VARCHAR2)
137
138 IS
139 BEGIN
140 Update CSD_MASS_RO_SN
141 SET
142 REPAIR_LINE_ID = decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, NULL, REPAIR_LINE_ID, p_REPAIR_LINE_ID)
143 ,SERIAL_NUMBER = decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, NULL, SERIAL_NUMBER, p_SERIAL_NUMBER)
144 ,INSTANCE_ID = decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, NULL, NULL, INSTANCE_ID, p_INSTANCE_ID)
145 ,PROCESS_FLAG = decode( p_PROCESS_FLAG, FND_API.G_MISS_CHAR, NULL, NULL, PROCESS_FLAG, p_PROCESS_FLAG)
146 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, p_CREATED_BY)
147 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, p_CREATION_DATE)
148 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
149 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
150 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
151 ,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
152 ,ATTRIBUTE_GROUP = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_GROUP, p_ATTRIBUTE_CATEGORY)
153 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1)
154 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2)
155 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3)
156 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4)
157 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5)
158 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6)
159 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7)
160 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8)
161 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9)
162 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10)
163 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11)
164 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12)
165 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13)
166 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14)
167 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
168 where MASS_RO_SN_ID = p_MASS_RO_SN_ID;
169
170 If (SQL%NOTFOUND) then
171 RAISE NO_DATA_FOUND;
172 End If;
173 END Update_Row;
174
175 PROCEDURE Delete_Row(
176 p_MASS_RO_SN_ID NUMBER)
177 IS
178 BEGIN
179 DELETE FROM CSD_MASS_RO_SN
180 WHERE MASS_RO_SN_ID = p_MASS_RO_SN_ID;
181 If (SQL%NOTFOUND) then
182 RAISE NO_DATA_FOUND;
183 End If;
184 END Delete_Row;
185
186 PROCEDURE Lock_Row(
187 p_MASS_RO_SN_ID NUMBER
188 ,p_OBJECT_VERSION_NUMBER NUMBER)
189
190 IS
191 CURSOR C IS
192 SELECT *
193 FROM CSD_MASS_RO_SN
194 WHERE MASS_RO_SN_ID = p_MASS_RO_SN_ID
195 FOR UPDATE of MASS_RO_SN_ID NOWAIT;
196 Recinfo C%ROWTYPE;
197 BEGIN
198 OPEN C;
199 FETCH C INTO Recinfo;
200 If (C%NOTFOUND) then
201 CLOSE C;
202 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
203 APP_EXCEPTION.RAISE_EXCEPTION;
204 End If;
205 CLOSE C;
206 if ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER ) then
207 return;
208 else
209 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
210 APP_EXCEPTION.RAISE_EXCEPTION;
211 End If;
212 END Lock_Row;
213
214 End CSD_MASS_RO_SN_PKG;