[Home] [Help]
PACKAGE BODY: APPS.BOM_DEPT_RES_INSTANCES_PKG
Source
1 PACKAGE BODY BOM_DEPT_RES_INSTANCES_PKG as
2 /* $Header: bompdrib.pls 115.2 2002/11/19 03:13:43 lnarveka ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Instance_Id NUMBER,
6 X_Department_Id NUMBER,
7 X_Resource_Id NUMBER,
8 X_Serial_Number VARCHAR2,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_Last_Update_Login NUMBER DEFAULT NULL,
14 X_Attribute_Category VARCHAR2 DEFAULT NULL,
15 X_Attribute1 VARCHAR2 DEFAULT NULL,
16 X_Attribute2 VARCHAR2 DEFAULT NULL,
17 X_Attribute3 VARCHAR2 DEFAULT NULL,
18 X_Attribute4 VARCHAR2 DEFAULT NULL,
19 X_Attribute5 VARCHAR2 DEFAULT NULL,
20 X_Attribute6 VARCHAR2 DEFAULT NULL,
21 X_Attribute7 VARCHAR2 DEFAULT NULL,
22 X_Attribute8 VARCHAR2 DEFAULT NULL,
23 X_Attribute9 VARCHAR2 DEFAULT NULL,
24 X_Attribute10 VARCHAR2 DEFAULT NULL,
25 X_Attribute11 VARCHAR2 DEFAULT NULL,
26 X_Attribute12 VARCHAR2 DEFAULT NULL,
27 X_Attribute13 VARCHAR2 DEFAULT NULL,
28 X_Attribute14 VARCHAR2 DEFAULT NULL,
29 X_Attribute15 VARCHAR2 DEFAULT NULL
30 ) IS
31 CURSOR C IS SELECT rowid FROM BOM_DEPT_RES_INSTANCES
32 WHERE instance_id = X_Instance_Id
33 And department_id = X_Department_Id
34 And resource_id = X_Resource_Id
35 And nvl(serial_number,'x') = nvl(X_Serial_Number,'x');
36
37 BEGIN
38 INSERT INTO BOM_DEPT_RES_INSTANCES(
39 instance_id,
40 department_id,
41 resource_id,
42 serial_number,
43 last_update_date,
44 last_updated_by,
45 creation_date,
46 created_by,
47 last_update_login,
48 attribute_category,
49 attribute1,
50 attribute2,
51 attribute3,
52 attribute4,
53 attribute5,
54 attribute6,
55 attribute7,
56 attribute8,
57 attribute9,
58 attribute10,
59 attribute11,
60 attribute12,
61 attribute13,
62 attribute14,
63 attribute15
64 ) VALUES (
65 X_Instance_Id,
66 X_Department_Id,
67 X_Resource_Id,
68 X_Serial_Number,
69 X_Last_Update_Date,
70 X_Last_Updated_By,
71 X_Creation_Date,
72 X_Created_By,
73 X_Last_Update_Login,
74 X_Attribute_Category,
75 X_Attribute1,
76 X_Attribute2,
77 X_Attribute3,
78 X_Attribute4,
79 X_Attribute5,
80 X_Attribute6,
81 X_Attribute7,
82 X_Attribute8,
83 X_Attribute9,
84 X_Attribute10,
85 X_Attribute11,
86 X_Attribute12,
87 X_Attribute13,
88 X_Attribute14,
89 X_Attribute15
90 );
91 OPEN C;
92 FETCH C INTO X_Rowid;
93 if (C%NOTFOUND) then
94 CLOSE C;
95 Raise NO_DATA_FOUND;
96 end if;
97 CLOSE C;
98 END Insert_Row;
99
100 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
101 X_Instance_Id NUMBER,
102 X_Department_Id NUMBER,
103 X_Resource_Id NUMBER,
104 X_Serial_Number VARCHAR2,
105 X_Attribute_Category VARCHAR2 DEFAULT NULL,
106 X_Attribute1 VARCHAR2 DEFAULT NULL,
107 X_Attribute2 VARCHAR2 DEFAULT NULL,
108 X_Attribute3 VARCHAR2 DEFAULT NULL,
109 X_Attribute4 VARCHAR2 DEFAULT NULL,
110 X_Attribute5 VARCHAR2 DEFAULT NULL,
111 X_Attribute6 VARCHAR2 DEFAULT NULL,
112 X_Attribute7 VARCHAR2 DEFAULT NULL,
113 X_Attribute8 VARCHAR2 DEFAULT NULL,
114 X_Attribute9 VARCHAR2 DEFAULT NULL,
115 X_Attribute10 VARCHAR2 DEFAULT NULL,
116 X_Attribute11 VARCHAR2 DEFAULT NULL,
117 X_Attribute12 VARCHAR2 DEFAULT NULL,
118 X_Attribute13 VARCHAR2 DEFAULT NULL,
119 X_Attribute14 VARCHAR2 DEFAULT NULL,
120 X_Attribute15 VARCHAR2 DEFAULT NULL
121 ) IS
122 CURSOR C IS SELECT * FROM BOM_DEPT_RES_INSTANCES
123 WHERE rowid = X_Rowid FOR UPDATE of Instance_Id NOWAIT;
124 Recinfo C%ROWTYPE;
125 BEGIN
126 OPEN C;
127 FETCH C INTO Recinfo;
128 if (C%NOTFOUND) then
129 CLOSE C;
130 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
131 APP_EXCEPTION.Raise_Exception;
132 end if;
133 CLOSE C;
134 if (
135 (Recinfo.instance_id = X_Instance_Id)
136 AND (Recinfo.department_id = X_Department_Id)
137 AND (Recinfo.resource_id = X_Resource_Id)
138 AND ( (Recinfo.serial_number = X_Serial_Number)
139 OR ( (Recinfo.serial_number IS NULL)
140 AND (X_Serial_Number IS NULL)))
141 AND ( (Recinfo.attribute_category = X_Attribute_Category)
142 OR ( (Recinfo.attribute_category IS NULL)
143 AND (X_Attribute_Category IS NULL)))
144 AND ( (Recinfo.attribute1 = X_Attribute1)
145 OR ( (Recinfo.attribute1 IS NULL)
146 AND (X_Attribute1 IS NULL)))
147 AND ( (Recinfo.attribute2 = X_Attribute2)
148 OR ( (Recinfo.attribute2 IS NULL)
149 AND (X_Attribute2 IS NULL)))
150 AND ( (Recinfo.attribute3 = X_Attribute3)
151 OR ( (Recinfo.attribute3 IS NULL)
152 AND (X_Attribute3 IS NULL)))
153 AND ( (Recinfo.attribute4 = X_Attribute4)
154 OR ( (Recinfo.attribute4 IS NULL)
155 AND (X_Attribute4 IS NULL)))
156 AND ( (Recinfo.attribute5 = X_Attribute5)
157 OR ( (Recinfo.attribute5 IS NULL)
158 AND (X_Attribute5 IS NULL)))
159 AND ( (Recinfo.attribute6 = X_Attribute6)
160 OR ( (Recinfo.attribute6 IS NULL)
161 AND (X_Attribute6 IS NULL)))
162 AND ( (Recinfo.attribute7 = X_Attribute7)
163 OR ( (Recinfo.attribute7 IS NULL)
164 AND (X_Attribute7 IS NULL)))
165 AND ( (Recinfo.attribute8 = X_Attribute8)
166 OR ( (Recinfo.attribute8 IS NULL)
167 AND (X_Attribute8 IS NULL)))
168 AND ( (Recinfo.attribute9 = X_Attribute9)
169 OR ( (Recinfo.attribute9 IS NULL)
170 AND (X_Attribute9 IS NULL)))
171 AND ( (Recinfo.attribute10 = X_Attribute10)
172 OR ( (Recinfo.attribute10 IS NULL)
173 AND (X_Attribute10 IS NULL)))
174 AND ( (Recinfo.attribute11 = X_Attribute11)
175 OR ( (Recinfo.attribute11 IS NULL)
176 AND (X_Attribute11 IS NULL)))
177 AND ( (Recinfo.attribute12 = X_Attribute12)
178 OR ( (Recinfo.attribute12 IS NULL)
179 AND (X_Attribute12 IS NULL)))
180 AND ( (Recinfo.attribute13 = X_Attribute13)
181 OR ( (Recinfo.attribute13 IS NULL)
182 AND (X_Attribute13 IS NULL)))
183 AND ( (Recinfo.attribute14 = X_Attribute14)
184 OR ( (Recinfo.attribute14 IS NULL)
185 AND (X_Attribute14 IS NULL)))
186 AND ( (Recinfo.attribute15 = X_Attribute15)
187 OR ( (Recinfo.attribute15 IS NULL)
188 AND (X_Attribute15 IS NULL)))
189 ) then
190 return;
191 else
192 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 end if;
195 END Lock_Row;
196
197 PROCEDURE Update_Row(X_Rowid VARCHAR2,
198 X_Instance_Id NUMBER,
199 X_Department_Id NUMBER,
200 X_Resource_Id NUMBER,
201 X_Serial_Number VARCHAR2,
202 X_Last_Update_Date DATE,
203 X_Last_Updated_By NUMBER,
204 X_Last_Update_Login NUMBER DEFAULT NULL,
205 X_Attribute_Category VARCHAR2 DEFAULT NULL,
206 X_Attribute1 VARCHAR2 DEFAULT NULL,
207 X_Attribute2 VARCHAR2 DEFAULT NULL,
208 X_Attribute3 VARCHAR2 DEFAULT NULL,
209 X_Attribute4 VARCHAR2 DEFAULT NULL,
210 X_Attribute5 VARCHAR2 DEFAULT NULL,
211 X_Attribute6 VARCHAR2 DEFAULT NULL,
212 X_Attribute7 VARCHAR2 DEFAULT NULL,
213 X_Attribute8 VARCHAR2 DEFAULT NULL,
214 X_Attribute9 VARCHAR2 DEFAULT NULL,
215 X_Attribute10 VARCHAR2 DEFAULT NULL,
216 X_Attribute11 VARCHAR2 DEFAULT NULL,
217 X_Attribute12 VARCHAR2 DEFAULT NULL,
218 X_Attribute13 VARCHAR2 DEFAULT NULL,
219 X_Attribute14 VARCHAR2 DEFAULT NULL,
220 X_Attribute15 VARCHAR2 DEFAULT NULL
221 ) IS
222 BEGIN
223 UPDATE BOM_DEPT_RES_INSTANCES
224 SET
225 instance_id = X_Instance_Id,
226 department_id = X_Department_Id,
227 resource_id = X_Resource_Id,
228 serial_number = X_Serial_Number,
229 last_update_date = X_Last_Update_Date,
230 last_updated_by = X_Last_Updated_By,
231 last_update_login = X_Last_Update_Login,
232 attribute_category = X_Attribute_Category,
233 attribute1 = X_Attribute1,
234 attribute2 = X_Attribute2,
235 attribute3 = X_Attribute3,
236 attribute4 = X_Attribute4,
237 attribute5 = X_Attribute5,
238 attribute6 = X_Attribute6,
242 attribute10 = X_Attribute10,
239 attribute7 = X_Attribute7,
240 attribute8 = X_Attribute8,
241 attribute9 = X_Attribute9,
243 attribute11 = X_Attribute11,
244 attribute12 = X_Attribute12,
245 attribute13 = X_Attribute13,
246 attribute14 = X_Attribute14,
247 attribute15 = X_Attribute15
248 WHERE rowid = X_rowid;
249 if (SQL%NOTFOUND) then
250 Raise NO_DATA_FOUND;
251 end if;
252 END Update_Row;
253
254
255
256 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
257 BEGIN
258 DELETE FROM BOM_DEPT_RES_INSTANCES
259 WHERE rowid = X_Rowid;
260
261 if (SQL%NOTFOUND) then
262 Raise NO_DATA_FOUND;
263 end if;
264 END Delete_Row;
265
266
267
268 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
269 X_Instance_Id NUMBER,
270 X_Department_Id NUMBER,
271 X_Resource_Id NUMBER,
272 X_Serial_Number VARCHAR2) IS
273 dummy NUMBER;
274 BEGIN
275 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
276 (SELECT 1 FROM BOM_DEPT_RES_INSTANCES
277 WHERE instance_id = X_Instance_Id
278 And department_id = X_Department_Id
279 And resource_id = X_Resource_Id
280 And nvl(serial_number,'x') = nvl(X_Serial_Number,'x')
281 And ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
282 );
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 FND_MESSAGE.SET_NAME('BOM', 'BOM_CANNOT_ENTER_DEPT_RES_INS');
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 END Check_Unique;
288
289
290 /* GRANT EXECUTE ON BOM_DEPT_RES_INSTANCES_PKG TO MFG; */
291
292
293 END BOM_DEPT_RES_INSTANCES_PKG;