[Home] [Help]
PACKAGE BODY: APPS.BOM_DEPARTMENTS_PKG
Source
1 PACKAGE BODY BOM_DEPARTMENTS_PKG as
2 /* $Header: bompodpb.pls 115.9 2002/11/19 03:15:13 lnarveka ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Department_Id IN OUT NOCOPY NUMBER,
7 X_Department_Code VARCHAR2,
8 X_Organization_Id NUMBER,
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_Description VARCHAR2 DEFAULT NULL,
15 X_Disable_Date DATE DEFAULT NULL,
16 X_Department_Class_Code VARCHAR2 DEFAULT NULL,
17 X_Pa_Expenditure_Org_Id NUMBER DEFAULT NULL,
18 X_Attribute_Category VARCHAR2 DEFAULT NULL,
19 X_Attribute1 VARCHAR2 DEFAULT NULL,
20 X_Attribute2 VARCHAR2 DEFAULT NULL,
21 X_Attribute3 VARCHAR2 DEFAULT NULL,
22 X_Attribute4 VARCHAR2 DEFAULT NULL,
23 X_Attribute5 VARCHAR2 DEFAULT NULL,
24 X_Attribute6 VARCHAR2 DEFAULT NULL,
25 X_Attribute7 VARCHAR2 DEFAULT NULL,
26 X_Attribute8 VARCHAR2 DEFAULT NULL,
27 X_Attribute9 VARCHAR2 DEFAULT NULL,
28 X_Attribute10 VARCHAR2 DEFAULT NULL,
29 X_Attribute11 VARCHAR2 DEFAULT NULL,
30 X_Attribute12 VARCHAR2 DEFAULT NULL,
31 X_Attribute13 VARCHAR2 DEFAULT NULL,
32 X_Attribute14 VARCHAR2 DEFAULT NULL,
33 X_Attribute15 VARCHAR2 DEFAULT NULL,
34 X_Location_Id NUMBER DEFAULT NULL,
35 X_Scrap_Account NUMBER DEFAULT NULL,
36 X_Est_Absorption_Account NUMBER DEFAULT NULL,
37 X_Maint_Cost_Category VARCHAR2 DEFAULT NULL
38 ) IS
39 CURSOR C IS SELECT rowid FROM BOM_DEPARTMENTS
40 WHERE department_id = X_Department_Id;
41 CURSOR C2 IS SELECT bom_departments_s.nextval FROM sys.dual;
42 BEGIN
43 if (X_Department_Id is NULL) then
44 OPEN C2;
45 FETCH C2 INTO X_Department_Id;
46 CLOSE C2;
47 end if;
48 INSERT INTO BOM_DEPARTMENTS(
49 department_id,
50 department_code,
51 organization_id,
52 last_update_date,
53 last_updated_by,
54 creation_date,
55 created_by,
56 last_update_login,
57 description,
58 disable_date,
59 department_class_code,
60 pa_expenditure_org_id,
61 attribute_category,
62 attribute1,
63 attribute2,
64 attribute3,
65 attribute4,
66 attribute5,
67 attribute6,
68 attribute7,
69 attribute8,
70 attribute9,
71 attribute10,
72 attribute11,
73 attribute12,
74 attribute13,
75 attribute14,
76 attribute15,
77 location_id,
78 scrap_account,
79 est_absorption_account,
80 maint_cost_category
81 ) VALUES (
82 X_Department_Id,
83 X_Department_Code,
84 X_Organization_Id,
85 X_Last_Update_Date,
86 X_Last_Updated_By,
87 X_Creation_Date,
88 X_Created_By,
89 X_Last_Update_Login,
90 X_Description,
91 X_Disable_Date,
92 X_Department_Class_Code,
93 X_Pa_Expenditure_Org_Id,
94 X_Attribute_Category,
95 X_Attribute1,
96 X_Attribute2,
97 X_Attribute3,
98 X_Attribute4,
99 X_Attribute5,
100 X_Attribute6,
101 X_Attribute7,
102 X_Attribute8,
103 X_Attribute9,
104 X_Attribute10,
105 X_Attribute11,
106 X_Attribute12,
107 X_Attribute13,
108 X_Attribute14,
109 X_Attribute15,
110 X_Location_Id,
111 X_Scrap_Account,
112 X_Est_Absorption_Account,
113 X_Maint_Cost_Category
114 );
115 OPEN C;
116 FETCH C INTO X_Rowid;
117 if (C%NOTFOUND) then
118 CLOSE C;
119 Raise NO_DATA_FOUND;
120 end if;
121 CLOSE C;
122 END Insert_Row;
123
124
125
126 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
127 X_Department_Id NUMBER,
128 X_Department_Code VARCHAR2,
129 X_Organization_Id NUMBER,
130 X_Description VARCHAR2 DEFAULT NULL,
131 X_Disable_Date DATE DEFAULT NULL,
132 X_Department_Class_Code VARCHAR2 DEFAULT NULL,
133 X_Pa_Expenditure_Org_Id NUMBER DEFAULT NULL,
134 X_Attribute_Category VARCHAR2 DEFAULT NULL,
135 X_Attribute1 VARCHAR2 DEFAULT NULL,
136 X_Attribute2 VARCHAR2 DEFAULT NULL,
137 X_Attribute3 VARCHAR2 DEFAULT NULL,
138 X_Attribute4 VARCHAR2 DEFAULT NULL,
139 X_Attribute5 VARCHAR2 DEFAULT NULL,
140 X_Attribute6 VARCHAR2 DEFAULT NULL,
141 X_Attribute7 VARCHAR2 DEFAULT NULL,
142 X_Attribute8 VARCHAR2 DEFAULT NULL,
143 X_Attribute9 VARCHAR2 DEFAULT NULL,
144 X_Attribute10 VARCHAR2 DEFAULT NULL,
145 X_Attribute11 VARCHAR2 DEFAULT NULL,
146 X_Attribute12 VARCHAR2 DEFAULT NULL,
147 X_Attribute13 VARCHAR2 DEFAULT NULL,
148 X_Attribute14 VARCHAR2 DEFAULT NULL,
149 X_Attribute15 VARCHAR2 DEFAULT NULL,
150 X_Location_Id NUMBER DEFAULT NULL,
151 X_Scrap_Account NUMBER DEFAULT NULL,
152 X_Est_Absorption_Account NUMBER DEFAULT NULL,
153 X_Maint_Cost_Category VARCHAR2 DEFAULT NULL
154 ) IS
155 CURSOR C IS SELECT * FROM BOM_DEPARTMENTS
156 WHERE rowid = X_Rowid FOR UPDATE of Department_Id NOWAIT;
157 Recinfo C%ROWTYPE;
158 BEGIN
159 OPEN C;
160 FETCH C INTO Recinfo;
161 if (C%NOTFOUND) then
162 CLOSE C;
163 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
164 APP_EXCEPTION.Raise_Exception;
165 end if;
166 CLOSE C;
167 if (
168 (Recinfo.department_id = X_Department_Id)
169 AND (Recinfo.department_code = X_Department_Code)
170 AND (Recinfo.organization_id = X_Organization_Id)
171 AND ( (Recinfo.description = X_Description)
172 OR ( (Recinfo.description IS NULL)
173 AND (X_Description IS NULL)))
174 AND ( (Recinfo.disable_date = X_Disable_Date)
175 OR ( (Recinfo.disable_date IS NULL)
176 AND (X_Disable_Date IS NULL)))
177 AND ( (Recinfo.department_class_code = X_Department_Class_Code)
178 OR ( (Recinfo.department_class_code IS NULL)
179 AND (X_Department_Class_Code IS NULL)))
180 AND ( (Recinfo.pa_expenditure_org_id = X_pa_expenditure_org_id)
181 OR ( (Recinfo.pa_expenditure_org_id IS NULL)
182 AND (X_Pa_Expenditure_Org_Id IS NULL)))
183 AND ( (Recinfo.attribute_category = X_Attribute_Category)
184 OR ( (Recinfo.attribute_category IS NULL)
185 AND (X_Attribute_Category IS NULL)))
186 AND ( (Recinfo.attribute1 = X_Attribute1)
187 OR ( (Recinfo.attribute1 IS NULL)
188 AND (X_Attribute1 IS NULL)))
189 AND ( (Recinfo.attribute2 = X_Attribute2)
190 OR ( (Recinfo.attribute2 IS NULL)
191 AND (X_Attribute2 IS NULL)))
192 AND ( (Recinfo.attribute3 = X_Attribute3)
193 OR ( (Recinfo.attribute3 IS NULL)
194 AND (X_Attribute3 IS NULL)))
195 AND ( (Recinfo.attribute4 = X_Attribute4)
196 OR ( (Recinfo.attribute4 IS NULL)
197 AND (X_Attribute4 IS NULL)))
198 AND ( (Recinfo.attribute5 = X_Attribute5)
199 OR ( (Recinfo.attribute5 IS NULL)
200 AND (X_Attribute5 IS NULL)))
201 AND ( (Recinfo.attribute6 = X_Attribute6)
202 OR ( (Recinfo.attribute6 IS NULL)
203 AND (X_Attribute6 IS NULL)))
204 AND ( (Recinfo.attribute7 = X_Attribute7)
205 OR ( (Recinfo.attribute7 IS NULL)
206 AND (X_Attribute7 IS NULL)))
207 AND ( (Recinfo.attribute8 = X_Attribute8)
208 OR ( (Recinfo.attribute8 IS NULL)
209 AND (X_Attribute8 IS NULL)))
210 AND ( (Recinfo.attribute9 = X_Attribute9)
211 OR ( (Recinfo.attribute9 IS NULL)
212 AND (X_Attribute9 IS NULL)))
213 AND ( (Recinfo.attribute10 = X_Attribute10)
214 OR ( (Recinfo.attribute10 IS NULL)
215 AND (X_Attribute10 IS NULL)))
216 AND ( (Recinfo.attribute11 = X_Attribute11)
217 OR ( (Recinfo.attribute11 IS NULL)
218 AND (X_Attribute11 IS NULL)))
219 AND ( (Recinfo.attribute12 = X_Attribute12)
220 OR ( (Recinfo.attribute12 IS NULL)
221 AND (X_Attribute12 IS NULL)))
222 AND ( (Recinfo.attribute13 = X_Attribute13)
223 OR ( (Recinfo.attribute13 IS NULL)
224 AND (X_Attribute13 IS NULL)))
225 AND ( (Recinfo.attribute14 = X_Attribute14)
226 OR ( (Recinfo.attribute14 IS NULL)
227 AND (X_Attribute14 IS NULL)))
228 AND ( (Recinfo.attribute15 = X_Attribute15)
229 OR ( (Recinfo.attribute15 IS NULL)
230 AND (X_Attribute15 IS NULL)))
231 AND ( (Recinfo.location_id = X_Location_Id)
235 OR ( (Recinfo.Scrap_Account IS NULL)
232 OR ( (Recinfo.location_id IS NULL)
233 AND (X_Location_Id IS NULL)))
234 AND ( (Recinfo.Scrap_Account = X_Scrap_Account)
236 AND (X_Scrap_Account IS NULL)))
237 AND ( (Recinfo.Est_Absorption_Account = X_Est_Absorption_Account)
238 OR ( (Recinfo.Est_Absorption_Account IS NULL)
239 AND (X_Est_Absorption_Account IS NULL)))
240 AND ( (Recinfo.Maint_Cost_Category = X_Maint_Cost_Category )
241 OR ( (Recinfo.Maint_Cost_Category IS NULL)
242 AND (X_Maint_Cost_Category IS NULL)))
243 ) then
244 return;
245 else
246 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
247 APP_EXCEPTION.RAISE_EXCEPTION;
248 end if;
249 END Lock_Row;
250
251
252
253 PROCEDURE Update_Row(X_Rowid VARCHAR2,
254 X_Department_Id NUMBER,
255 X_Department_Code VARCHAR2,
256 X_Organization_Id NUMBER,
257 X_Last_Update_Date DATE,
258 X_Last_Updated_By NUMBER,
259 X_Last_Update_Login NUMBER DEFAULT NULL,
260 X_Description VARCHAR2 DEFAULT NULL,
261 X_Disable_Date DATE DEFAULT NULL,
262 X_Department_Class_Code VARCHAR2 DEFAULT NULL,
263 X_Pa_Expenditure_Org_Id NUMBER DEFAULT NULL,
264 X_Attribute_Category VARCHAR2 DEFAULT NULL,
265 X_Attribute1 VARCHAR2 DEFAULT NULL,
266 X_Attribute2 VARCHAR2 DEFAULT NULL,
267 X_Attribute3 VARCHAR2 DEFAULT NULL,
268 X_Attribute4 VARCHAR2 DEFAULT NULL,
269 X_Attribute5 VARCHAR2 DEFAULT NULL,
270 X_Attribute6 VARCHAR2 DEFAULT NULL,
271 X_Attribute7 VARCHAR2 DEFAULT NULL,
275 X_Attribute11 VARCHAR2 DEFAULT NULL,
272 X_Attribute8 VARCHAR2 DEFAULT NULL,
273 X_Attribute9 VARCHAR2 DEFAULT NULL,
274 X_Attribute10 VARCHAR2 DEFAULT NULL,
276 X_Attribute12 VARCHAR2 DEFAULT NULL,
277 X_Attribute13 VARCHAR2 DEFAULT NULL,
278 X_Attribute14 VARCHAR2 DEFAULT NULL,
279 X_Attribute15 VARCHAR2 DEFAULT NULL,
280 X_Location_Id NUMBER DEFAULT NULL,
281 X_Scrap_Account NUMBER DEFAULT NULL,
282 X_Est_Absorption_Account NUMBER DEFAULT NULL,
283 X_Maint_Cost_Category VARCHAR2 DEFAULT NULL
284
285 ) IS
286 BEGIN
287 UPDATE BOM_DEPARTMENTS
288 SET
289 department_id = X_Department_Id,
290 department_code = X_Department_Code,
291 organization_id = X_Organization_Id,
292 last_update_date = X_Last_Update_Date,
293 last_updated_by = X_Last_Updated_By,
294 last_update_login = X_Last_Update_Login,
295 description = X_Description,
296 disable_date = X_Disable_Date,
297 department_class_code = X_Department_Class_Code,
298 pa_expenditure_org_id = X_Pa_Expenditure_Org_Id,
299 attribute_category = X_Attribute_Category,
300 attribute1 = X_Attribute1,
301 attribute2 = X_Attribute2,
302 attribute3 = X_Attribute3,
303 attribute4 = X_Attribute4,
304 attribute5 = X_Attribute5,
305 attribute6 = X_Attribute6,
306 attribute7 = X_Attribute7,
307 attribute8 = X_Attribute8,
308 attribute9 = X_Attribute9,
309 attribute10 = X_Attribute10,
310 attribute11 = X_Attribute11,
311 attribute12 = X_Attribute12,
312 attribute13 = X_Attribute13,
313 attribute14 = X_Attribute14,
314 attribute15 = X_Attribute15,
315 location_id = X_Location_Id,
316 scrap_account = X_Scrap_Account,
317 est_absorption_account = X_Est_Absorption_Account,
318 maint_cost_category = X_Maint_Cost_Category
319 WHERE rowid = X_rowid;
320 if (SQL%NOTFOUND) then
321 Raise NO_DATA_FOUND;
322 end if;
323 END Update_Row;
324
325
326
327 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
328 BEGIN
329 DELETE FROM BOM_DEPARTMENTS
330 WHERE rowid = X_Rowid;
331
332 if (SQL%NOTFOUND) then
333 Raise NO_DATA_FOUND;
334 end if;
335 END Delete_Row;
336
337
338
339 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
340 X_Organization_Id NUMBER,
341 X_Department_Code VARCHAR2) IS
342 dummy NUMBER;
343 BEGIN
344 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
345 (SELECT 1 FROM BOM_DEPARTMENTS
346 WHERE Organization_Id = X_Organization_Id
347 AND Department_Code = X_Department_Code
348 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
349 );
350
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALREADY_EXISTS');
354 FND_MESSAGE.SET_TOKEN('ENTITY1', 'THIS_CAP', TRUE);
355 FND_MESSAGE.SET_TOKEN('ENTITY2', 'DEPARTMENT', TRUE);
356 APP_EXCEPTION.RAISE_EXCEPTION;
357 END Check_Unique;
358
359
360
361 FUNCTION Resources_OSP_POReceipt(X_Department_Id NUMBER,
362 X_Organization_Id NUMBER) RETURN NUMBER IS
363 resources_exist NUMBER;
364 BEGIN
365 SELECT COUNT(RESOURCE_ID) INTO resources_exist FROM BOM_DEPARTMENT_RESOURCES
366 WHERE DEPARTMENT_ID = X_Department_Id
367 AND RESOURCE_ID IN
368 (SELECT RESOURCE_ID FROM BOM_RESOURCES
369 WHERE COST_CODE_TYPE = 4
370 AND AUTOCHARGE_TYPE = 3
371 AND ORGANIZATION_ID = Organization_Id);
372 RETURN(resources_exist);
373 END Resources_OSP_POReceipt;
374
375
376 /* GRANT EXECUTE ON BOM_DEPARTMENTS_PKG TO MFG; */
377
378
379
380 END BOM_DEPARTMENTS_PKG;