[Home] [Help]
PACKAGE BODY: APPS.BOM_RESOURCES_PKG
Source
1 PACKAGE BODY BOM_RESOURCES_PKG as
2 /* $Header: bomporsb.pls 120.1.12020000.2 2012/07/05 09:43:01 ntungare ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Resource_Id IN OUT NOCOPY NUMBER,
6 X_Resource_Code VARCHAR2,
7 X_Organization_Id NUMBER,
8 X_Last_Update_Date DATE,
9 X_Last_Updated_By NUMBER,
10 X_Creation_Date DATE,
11 X_Created_By NUMBER,
12 X_Last_Update_Login NUMBER,
13 X_Description VARCHAR2,
14 X_Disable_Date DATE,
15 X_Cost_Element_Id NUMBER,
16 X_Purchase_Item_Id NUMBER,
17 X_Cost_Code_Type NUMBER,
18 X_Functional_Currency_Flag NUMBER,
19 X_Unit_Of_Measure VARCHAR2,
20 X_Default_Activity_Id NUMBER,
21 X_Resource_Type NUMBER,
22 X_Autocharge_Type NUMBER,
23 X_Standard_Rate_Flag NUMBER,
24 X_Default_Basis_Type NUMBER,
25 X_Absorption_Account NUMBER,
26 X_Allow_Costs_Flag NUMBER,
27 X_Rate_Variance_Account NUMBER,
28 X_Expenditure_Type VARCHAR2,
29 X_Attribute_Category VARCHAR2,
30 X_Attribute1 VARCHAR2,
31 X_Attribute2 VARCHAR2,
32 X_Attribute3 VARCHAR2,
33 X_Attribute4 VARCHAR2,
34 X_Attribute5 VARCHAR2,
35 X_Attribute6 VARCHAR2,
36 X_Attribute7 VARCHAR2,
37 X_Attribute8 VARCHAR2,
38 X_Attribute9 VARCHAR2,
39 X_Attribute10 VARCHAR2,
40 X_Attribute11 VARCHAR2,
41 X_Attribute12 VARCHAR2,
42 X_Attribute13 VARCHAR2,
43 X_Attribute14 VARCHAR2,
44 X_Attribute15 VARCHAR2,
45 X_REQUEST_ID NUMBER := NULL,
49 )
46 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
47 X_PROGRAM_ID NUMBER := NULL,
48 X_PROGRAM_UPDATE_DATE DATE := NULL
50 IS
51 BEGIN
52 Insert_Row(X_Rowid => X_Rowid,
53 X_Resource_Id => X_Resource_Id,
54 X_Resource_Code => X_Resource_Code,
55 X_Organization_Id => X_Organization_Id,
56 X_Last_Update_Date => X_Last_Update_Date,
57 X_Last_Updated_By => X_Last_Updated_By,
58 X_Creation_Date => X_Creation_Date,
59 X_Created_By => X_Created_By,
60 X_Last_Update_Login => X_Last_Update_Login,
61 X_Description => X_Description,
62 X_Disable_Date => X_Disable_Date,
63 X_Cost_Element_Id => X_Cost_Element_Id,
64 X_Purchase_Item_Id => X_Purchase_Item_Id,
65 X_Cost_Code_Type => X_Cost_Code_Type,
66 X_Functional_Currency_Flag => X_Functional_Currency_Flag,
67 X_Unit_Of_Measure => X_Unit_Of_Measure,
68 X_Default_Activity_Id => X_Default_Activity_Id,
69 X_Resource_Type => X_Resource_Type,
70 X_Autocharge_Type => X_Autocharge_Type,
71 X_Standard_Rate_Flag => X_Standard_Rate_Flag,
72 X_Default_Basis_Type => X_Default_Basis_Type,
73 X_Absorption_Account => X_Absorption_Account,
74 X_Allow_Costs_Flag => X_Allow_Costs_Flag,
75 X_Rate_Variance_Account => X_Rate_Variance_Account,
76 X_Expenditure_Type => X_Expenditure_Type,
77 X_Attribute_Category => X_Attribute_Category,
78 X_Attribute1 => X_Attribute1,
79 X_Attribute2 => X_Attribute2,
80 X_Attribute3 => X_Attribute3,
81 X_Attribute4 => X_Attribute4,
82 X_Attribute5 => X_Attribute5,
83 X_Attribute6 => X_Attribute6,
84 X_Attribute7 => X_Attribute7,
85 X_Attribute8 => X_Attribute8,
86 X_Attribute9 => X_Attribute9,
87 X_Attribute10 => X_Attribute10,
88 X_Attribute11 => X_Attribute11,
89 X_Attribute12 => X_Attribute12,
90 X_Attribute13 => X_Attribute13,
91 X_Attribute14 => X_Attribute14,
92 X_Attribute15 => X_Attribute15,
93 X_REQUEST_ID =>X_REQUEST_ID,
94 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
95 X_PROGRAM_ID => X_PROGRAM_ID,
96 X_PROGRAM_UPDATE_DATE =>X_PROGRAM_UPDATE_DATE,
97 X_BATCHABLE =>NULL,
98 X_MIN_BATCH_CAPACITY =>NULL,
99 X_MAX_BATCH_CAPACITY =>NULL,
100 X_BATCH_CAPACITY_UOM =>NULL,
101 X_BATCH_WINDOW =>NULL,
102 X_BATCH_WINDOW_UOM =>NULL,
103 X_COMPETENCE_ID =>NULL,
104 X_RATING_LEVEL_ID =>NULL,
105 X_QUALIFICATION_TYPE_ID => NULL,
106 X_BILLABLE_ITEM_ID => NULL,
107 X_SUPPLY_SUBINVENTORY => NULL,
108 X_SUPPLY_LOCATOR_ID => NULL,
109 X_BATCHING_PENALTY => NULL); --APS Enhancement for Routings
110 END;
111 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
112 X_Resource_Id IN OUT NOCOPY NUMBER,
113 X_Resource_Code VARCHAR2,
114 X_Organization_Id NUMBER,
115 X_Last_Update_Date DATE,
116 X_Last_Updated_By NUMBER,
117 X_Creation_Date DATE,
118 X_Created_By NUMBER,
119 X_Last_Update_Login NUMBER,
120 X_Description VARCHAR2,
121 X_Disable_Date DATE,
122 X_Cost_Element_Id NUMBER,
123 X_Purchase_Item_Id NUMBER,
124 X_Cost_Code_Type NUMBER,
125 X_Functional_Currency_Flag NUMBER,
126 X_Unit_Of_Measure VARCHAR2,
127 X_Default_Activity_Id NUMBER,
128 X_Resource_Type NUMBER,
129 X_Autocharge_Type NUMBER,
130 X_Standard_Rate_Flag NUMBER,
131 X_Default_Basis_Type NUMBER,
132 X_Absorption_Account NUMBER,
133 X_Allow_Costs_Flag NUMBER,
134 X_Rate_Variance_Account NUMBER,
135 X_Expenditure_Type VARCHAR2,
136 X_Attribute_Category VARCHAR2,
137 X_Attribute1 VARCHAR2,
138 X_Attribute2 VARCHAR2,
139 X_Attribute3 VARCHAR2,
143 X_Attribute7 VARCHAR2,
140 X_Attribute4 VARCHAR2,
141 X_Attribute5 VARCHAR2,
142 X_Attribute6 VARCHAR2,
144 X_Attribute8 VARCHAR2,
145 X_Attribute9 VARCHAR2,
146 X_Attribute10 VARCHAR2,
147 X_Attribute11 VARCHAR2,
148 X_Attribute12 VARCHAR2,
149 X_Attribute13 VARCHAR2,
150 X_Attribute14 VARCHAR2,
151 X_Attribute15 VARCHAR2,
152 X_REQUEST_ID NUMBER := NULL,
153 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
154 X_PROGRAM_ID NUMBER := NULL,
155 X_PROGRAM_UPDATE_DATE DATE := NULL,
156 X_BATCHABLE NUMBER ,
157 X_MIN_BATCH_CAPACITY NUMBER ,
158 X_MAX_BATCH_CAPACITY NUMBER ,
159 X_BATCH_CAPACITY_UOM VARCHAR2 ,
160 X_BATCH_WINDOW NUMBER ,
161 X_BATCH_WINDOW_UOM VARCHAR2,
162 X_COMPETENCE_ID NUMBER := NULL,
163 X_RATING_LEVEL_ID NUMBER := NULL,
164 X_QUALIFICATION_TYPE_ID NUMBER := NULL,
165 X_BILLABLE_ITEM_ID NUMBER := NULL,
166 X_SUPPLY_SUBINVENTORY VARCHAR2,
167 X_SUPPLY_LOCATOR_ID NUMBER :=NULL,
168 X_BATCHING_PENALTY NUMBER :=NULL --APS Enhancement for Routings
169 ) IS
170 CURSOR C IS SELECT rowid FROM BOM_RESOURCES
171 WHERE resource_id = X_Resource_Id;
172 CURSOR C2 IS SELECT bom_resources_s.nextval FROM sys.dual;
173 BEGIN
174 if (X_Resource_Id is NULL) then
175 OPEN C2;
176 FETCH C2 INTO X_Resource_Id;
177 CLOSE C2;
178 end if;
179
180 INSERT INTO BOM_RESOURCES(
181 resource_id,
182 resource_code,
183 organization_id,
184 last_update_date,
185 last_updated_by,
186 creation_date,
187 created_by,
188 last_update_login,
189 description,
190 disable_date,
191 cost_element_id,
192 purchase_item_id,
193 cost_code_type,
194 functional_currency_flag,
195 unit_of_measure,
196 default_activity_id,
197 resource_type,
198 autocharge_type,
199 standard_rate_flag,
200 default_basis_type,
201 absorption_account,
202 allow_costs_flag,
203 rate_variance_account,
204 expenditure_type,
205 attribute_category,
206 attribute1,
207 attribute2,
208 attribute3,
209 attribute4,
210 attribute5,
211 attribute6,
212 attribute7,
213 attribute8,
214 attribute9,
215 attribute10,
216 attribute11,
217 attribute12,
218 attribute13,
219 attribute14,
220 attribute15,
221 REQUEST_ID,
222 PROGRAM_APPLICATION_ID,
223 PROGRAM_ID,
224 PROGRAM_UPDATE_DATE,
225 BATCHABLE,
226 MIN_BATCH_CAPACITY,
227 MAX_BATCH_CAPACITY,
228 BATCH_CAPACITY_UOM,
229 BATCH_WINDOW,
230 BATCH_WINDOW_UOM,
231 COMPETENCE_ID,
232 RATING_LEVEL_ID,
233 QUALIFICATION_TYPE_ID,
234 BILLABLE_ITEM_ID,
235 SUPPLY_SUBINVENTORY,
236 SUPPLY_LOCATOR_ID,
237 BATCHING_PENALTY --APS Enhancement for Routings
238 ) VALUES (
239 X_Resource_Id,
240 X_Resource_Code,
241 X_Organization_Id,
242 X_Last_Update_Date,
243 X_Last_Updated_By,
244 X_Creation_Date,
245 X_Created_By,
246 X_Last_Update_Login,
247 X_Description,
248 X_Disable_Date,
249 X_Cost_Element_Id,
250 X_Purchase_Item_Id,
251 X_Cost_Code_Type,
252 X_Functional_Currency_Flag,
253 X_Unit_Of_Measure,
254 X_Default_Activity_Id,
255 X_Resource_Type,
256 X_Autocharge_Type,
257 X_Standard_Rate_Flag,
258 X_Default_Basis_Type,
259 X_Absorption_Account,
260 X_Allow_Costs_Flag,
261 X_Rate_Variance_Account,
262 X_Expenditure_Type,
263 X_Attribute_Category,
264 X_Attribute1,
265 X_Attribute2,
266 X_Attribute3,
267 X_Attribute4,
268 X_Attribute5,
269 X_Attribute6,
270 X_Attribute7,
274 X_Attribute11,
271 X_Attribute8,
272 X_Attribute9,
273 X_Attribute10,
275 X_Attribute12,
276 X_Attribute13,
277 X_Attribute14,
278 X_Attribute15,
279 X_REQUEST_ID,
280 X_PROGRAM_APPLICATION_ID,
281 X_PROGRAM_ID,
282 X_PROGRAM_UPDATE_DATE,
283 X_BATCHABLE,
284 X_MIN_BATCH_CAPACITY,
285 X_MAX_BATCH_CAPACITY,
286 X_BATCH_CAPACITY_UOM,
287 X_BATCH_WINDOW,
288 X_BATCH_WINDOW_UOM,
289 X_COMPETENCE_ID,
290 X_RATING_LEVEL_ID,
291 X_QUALIFICATION_TYPE_ID,
292 X_BILLABLE_ITEM_ID,
293 X_SUPPLY_SUBINVENTORY,
294 X_SUPPLY_LOCATOR_ID,
295 X_BATCHING_PENALTY --APS Enhancement for Routings
296 );
297
298 OPEN C;
299 FETCH C INTO X_Rowid;
300 if (C%NOTFOUND) then
301 CLOSE C;
302 Raise NO_DATA_FOUND;
303 end if;
304 CLOSE C;
305 END Insert_Row;
306
307 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
308 X_Resource_Id NUMBER,
309 X_Resource_Code VARCHAR2,
310 X_Organization_Id NUMBER,
311 X_Description VARCHAR2,
312 X_Disable_Date DATE,
313 X_Cost_Element_Id NUMBER,
314 X_Purchase_Item_Id NUMBER,
315 X_Cost_Code_Type NUMBER,
316 X_Functional_Currency_Flag NUMBER,
317 X_Unit_Of_Measure VARCHAR2,
318 X_Default_Activity_Id NUMBER,
319 X_Resource_Type NUMBER,
320 X_Autocharge_Type NUMBER,
321 X_Standard_Rate_Flag NUMBER,
322 X_Default_Basis_Type NUMBER,
323 X_Absorption_Account NUMBER,
324 X_Allow_Costs_Flag NUMBER,
325 X_Rate_Variance_Account NUMBER,
326 X_Expenditure_Type VARCHAR2,
327 X_Attribute_Category VARCHAR2,
328 X_Attribute1 VARCHAR2,
329 X_Attribute2 VARCHAR2,
330 X_Attribute3 VARCHAR2,
331 X_Attribute4 VARCHAR2,
332 X_Attribute5 VARCHAR2,
333 X_Attribute6 VARCHAR2,
334 X_Attribute7 VARCHAR2,
335 X_Attribute8 VARCHAR2,
336 X_Attribute9 VARCHAR2,
337 X_Attribute10 VARCHAR2,
338 X_Attribute11 VARCHAR2,
339 X_Attribute12 VARCHAR2,
340 X_Attribute13 VARCHAR2,
341 X_Attribute14 VARCHAR2,
342 X_Attribute15 VARCHAR2,
343 X_REQUEST_ID NUMBER := NULL,
344 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
345 X_PROGRAM_ID NUMBER := NULL,
346 X_PROGRAM_UPDATE_DATE DATE := NULL
347 )
348 IS BEGIN
349 Lock_Row(X_Rowid => X_Rowid,
350 X_Resource_Id => X_Resource_Id,
351 X_Resource_Code => X_Resource_Code,
352 X_Organization_Id => X_Organization_Id,
353 X_Description => X_Description,
354 X_Disable_Date => X_Disable_Date,
355 X_Cost_Element_Id => X_Cost_Element_Id,
356 X_Purchase_Item_Id => X_Purchase_Item_Id,
357 X_Cost_Code_Type => X_Cost_Code_Type,
358 X_Functional_Currency_Flag => X_Functional_Currency_Flag,
359 X_Unit_Of_Measure => X_Unit_Of_Measure,
360 X_Default_Activity_Id => X_Default_Activity_Id,
361 X_Resource_Type => X_Resource_Type,
362 X_Autocharge_Type => X_Autocharge_Type,
363 X_Standard_Rate_Flag => X_Standard_Rate_Flag,
364 X_Default_Basis_Type => X_Default_Basis_Type,
365 X_Absorption_Account => X_Absorption_Account,
366 X_Allow_Costs_Flag => X_Allow_Costs_Flag,
367 X_Rate_Variance_Account => X_Rate_Variance_Account,
368 X_Expenditure_Type => X_Expenditure_Type,
369 X_Attribute_Category => X_Attribute_Category,
370 X_Attribute1 => X_Attribute1,
371 X_Attribute2 => X_Attribute2,
372 X_Attribute3 => X_Attribute3,
373 X_Attribute4 => X_Attribute4,
374 X_Attribute5 => X_Attribute5,
375 X_Attribute6 => X_Attribute6,
379 X_Attribute10 => X_Attribute10,
376 X_Attribute7 => X_Attribute7,
377 X_Attribute8 => X_Attribute8,
378 X_Attribute9 => X_Attribute9,
380 X_Attribute11 => X_Attribute11,
381 X_Attribute12 => X_Attribute12,
382 X_Attribute13 => X_Attribute13,
383 X_Attribute14 => X_Attribute14,
384 X_Attribute15 => X_Attribute15,
385 X_REQUEST_ID =>X_REQUEST_ID,
386 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
387 X_PROGRAM_ID => X_PROGRAM_ID,
388 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
389 X_BATCHABLE =>NULL,
390 X_MIN_BATCH_CAPACITY =>NULL,
391 X_MAX_BATCH_CAPACITY =>NULL,
392 X_BATCH_CAPACITY_UOM =>NULL,
393 X_BATCH_WINDOW =>NULL,
394 X_BATCH_WINDOW_UOM =>NULL,
395 X_COMPETENCE_ID =>NULL,
396 X_RATING_LEVEL_ID =>NULL,
397 X_QUALIFICATION_TYPE_ID => NULL,
398 X_BILLABLE_ITEM_ID => NULL,
399 X_SUPPLY_SUBINVENTORY => NULL,
400 X_SUPPLY_LOCATOR_ID => NULL,
401 X_BATCHING_PENALTY => NULL); --APS Enhancement for Routings
402
403 END;
404 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
405 X_Resource_Id NUMBER,
406 X_Resource_Code VARCHAR2,
407 X_Organization_Id NUMBER,
408 X_Description VARCHAR2,
409 X_Disable_Date DATE,
410 X_Cost_Element_Id NUMBER,
411 X_Purchase_Item_Id NUMBER,
412 X_Cost_Code_Type NUMBER,
413 X_Functional_Currency_Flag NUMBER,
414 X_Unit_Of_Measure VARCHAR2,
415 X_Default_Activity_Id NUMBER,
416 X_Resource_Type NUMBER,
417 X_Autocharge_Type NUMBER,
418 X_Standard_Rate_Flag NUMBER,
419 X_Default_Basis_Type NUMBER,
420 X_Absorption_Account NUMBER,
421 X_Allow_Costs_Flag NUMBER,
422 X_Rate_Variance_Account NUMBER,
423 X_Expenditure_Type VARCHAR2,
424 X_Attribute_Category VARCHAR2,
425 X_Attribute1 VARCHAR2,
426 X_Attribute2 VARCHAR2,
427 X_Attribute3 VARCHAR2,
428 X_Attribute4 VARCHAR2,
429 X_Attribute5 VARCHAR2,
430 X_Attribute6 VARCHAR2,
431 X_Attribute7 VARCHAR2,
432 X_Attribute8 VARCHAR2,
433 X_Attribute9 VARCHAR2,
434 X_Attribute10 VARCHAR2,
435 X_Attribute11 VARCHAR2,
436 X_Attribute12 VARCHAR2,
437 X_Attribute13 VARCHAR2,
438 X_Attribute14 VARCHAR2,
439 X_Attribute15 VARCHAR2,
440 X_REQUEST_ID NUMBER := NULL,
441 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
442 X_PROGRAM_ID NUMBER := NULL,
443 X_PROGRAM_UPDATE_DATE DATE := NULL,
444 X_BATCHABLE NUMBER ,
445 X_MIN_BATCH_CAPACITY NUMBER ,
446 X_MAX_BATCH_CAPACITY NUMBER ,
447 X_BATCH_CAPACITY_UOM VARCHAR2 ,
448 X_BATCH_WINDOW NUMBER ,
449 X_BATCH_WINDOW_UOM VARCHAR2,
450 X_COMPETENCE_ID NUMBER := NULL,
451 X_RATING_LEVEL_ID NUMBER := NULL,
452 X_QUALIFICATION_TYPE_ID NUMBER := NULL,
453 X_BILLABLE_ITEM_ID NUMBER := NULL,
454 X_SUPPLY_SUBINVENTORY VARCHAR2,
455 X_SUPPLY_LOCATOR_ID NUMBER :=NULL,
456 X_BATCHING_PENALTY NUMBER :=NULL --APS Enhancement for Routings
457
458 ) IS
459 CURSOR C IS
460 SELECT *
461 FROM BOM_RESOURCES
462 WHERE rowid = X_Rowid
463 FOR UPDATE of Resource_Id NOWAIT;
464 Recinfo C%ROWTYPE;
465
466 BEGIN
467 OPEN C;
468 FETCH C INTO Recinfo;
469 if (C%NOTFOUND) then
470 CLOSE C;
471 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
472 APP_EXCEPTION.Raise_Exception;
473 end if;
474 CLOSE C;
475 if (
476 (Recinfo.resource_id = X_Resource_Id)
477 AND (Recinfo.resource_code = X_Resource_Code)
478 AND (Recinfo.organization_id = X_Organization_Id)
482 AND ( (Recinfo.disable_date = X_Disable_Date)
479 AND ( (Recinfo.description = X_Description)
480 OR ( (Recinfo.description IS NULL)
481 AND (X_Description IS NULL)))
483 OR ( (Recinfo.disable_date IS NULL)
484 AND (X_Disable_Date IS NULL)))
485 AND (Recinfo.cost_element_id = X_Cost_Element_Id)
486 AND ( (Recinfo.purchase_item_id = X_Purchase_Item_Id)
487 OR ( (Recinfo.purchase_item_id IS NULL)
488 AND (X_Purchase_Item_Id IS NULL)))
489 AND (Recinfo.cost_code_type = X_Cost_Code_Type)
490 AND (Recinfo.functional_currency_flag = X_Functional_Currency_Flag)
491 AND ( (Recinfo.unit_of_measure = X_Unit_Of_Measure)
492 OR ( (Recinfo.unit_of_measure IS NULL)
493 AND (X_Unit_Of_Measure IS NULL)))
494 AND ( (Recinfo.default_activity_id = X_Default_Activity_Id)
495 OR ( (Recinfo.default_activity_id IS NULL)
496 AND (X_Default_Activity_Id IS NULL)))
497 AND ( X_Resource_Type = 6 OR
498 (X_Resource_Type <> 6 AND
499 ((Recinfo.resource_type = X_Resource_Type)
500 OR ((Recinfo.resource_type IS NULL)
501 AND (X_Resource_Type IS NULL)))) OR
502 ((Recinfo.resource_type IS NULL) AND
503 (X_Resource_Type IS NULL)))
504 AND ( (Recinfo.autocharge_type = X_Autocharge_Type)
505 OR ( (Recinfo.autocharge_type IS NULL)
506 AND (X_Autocharge_Type IS NULL)))
507 AND ( (Recinfo.standard_rate_flag = X_Standard_Rate_Flag)
508 OR ( (Recinfo.standard_rate_flag IS NULL)
509 AND (X_Standard_Rate_Flag IS NULL)))
510 AND ( (Recinfo.default_basis_type = X_Default_Basis_Type)
511 OR ( (Recinfo.default_basis_type IS NULL)
512 AND (X_Default_Basis_Type IS NULL)))
513 AND ( (Recinfo.absorption_account = X_Absorption_Account)
514 OR ( (Recinfo.absorption_account IS NULL)
515 AND (X_Absorption_Account IS NULL)))
516 AND (Recinfo.allow_costs_flag = X_Allow_Costs_Flag)
517 AND ( (Recinfo.rate_variance_account = X_Rate_Variance_Account)
518 OR ( (Recinfo.rate_variance_account IS NULL)
519 AND (X_Rate_Variance_Account IS NULL)))
520 AND ( (Recinfo.expenditure_type = X_Expenditure_Type)
521 OR ( (Recinfo.expenditure_type IS NULL)
522 AND (X_Expenditure_Type IS NULL)))
523 AND ( (Recinfo.attribute_category = X_Attribute_Category)
524 OR ( (Recinfo.attribute_category IS NULL)
525 AND (X_Attribute_Category IS NULL)))
526 AND ( (Recinfo.attribute1 = X_Attribute1)
527 OR ( (Recinfo.attribute1 IS NULL)
528 AND (X_Attribute1 IS NULL)))
529 AND ( (Recinfo.attribute2 = X_Attribute2)
530 OR ( (Recinfo.attribute2 IS NULL)
531 AND (X_Attribute2 IS NULL)))
532 AND ( (Recinfo.attribute3 = X_Attribute3)
533 OR ( (Recinfo.attribute3 IS NULL)
534 AND (X_Attribute3 IS NULL)))
535 AND ( (Recinfo.attribute4 = X_Attribute4)
536 OR ( (Recinfo.attribute4 IS NULL)
537 AND (X_Attribute4 IS NULL)))
538 AND ( (Recinfo.attribute5 = X_Attribute5)
539 OR ( (Recinfo.attribute5 IS NULL)
540 AND (X_Attribute5 IS NULL)))
541 AND ( (Recinfo.attribute6 = X_Attribute6)
542 OR ( (Recinfo.attribute6 IS NULL)
543 AND (X_Attribute6 IS NULL)))
544 AND ( (Recinfo.attribute7 = X_Attribute7)
545 OR ( (Recinfo.attribute7 IS NULL)
546 AND (X_Attribute7 IS NULL)))
547 AND ( (Recinfo.attribute8 = X_Attribute8)
548 OR ( (Recinfo.attribute8 IS NULL)
549 AND (X_Attribute8 IS NULL)))
550 AND ( (Recinfo.attribute9 = X_Attribute9)
551 OR ( (Recinfo.attribute9 IS NULL)
552 AND (X_Attribute9 IS NULL)))
553 AND ( (Recinfo.attribute10 = X_Attribute10)
554 OR ( (Recinfo.attribute10 IS NULL)
555 AND (X_Attribute10 IS NULL)))
556 AND ( (Recinfo.attribute11 = X_Attribute11)
557 OR ( (Recinfo.attribute11 IS NULL)
558 AND (X_Attribute11 IS NULL)))
559 AND ( (Recinfo.attribute12 = X_Attribute12)
560 OR ( (Recinfo.attribute12 IS NULL)
561 AND (X_Attribute12 IS NULL)))
562 AND ( (Recinfo.attribute13 = X_Attribute13)
563 OR ( (Recinfo.attribute13 IS NULL)
564 AND (X_Attribute13 IS NULL)))
565 AND ( (Recinfo.attribute14 = X_Attribute14)
566 OR ( (Recinfo.attribute14 IS NULL)
567 AND (X_Attribute14 IS NULL)))
568 AND ( (Recinfo.attribute15 = X_Attribute15)
569 OR ( (Recinfo.attribute15 IS NULL)
570 AND (X_Attribute15 IS NULL)))
571 AND ( (Recinfo.REQUEST_ID = X_REQUEST_ID)
572 OR ( (Recinfo.REQUEST_ID IS NULL)
573 AND (X_REQUEST_ID IS NULL)))
574 AND ( (Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
575 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
576 AND (X_PROGRAM_APPLICATION_ID IS NULL)))
577 AND ( (Recinfo.PROGRAM_ID = X_PROGRAM_ID)
578 OR ( (Recinfo.PROGRAM_ID IS NULL)
582 AND (X_PROGRAM_UPDATE_DATE IS NULL)))
579 AND (X_PROGRAM_ID IS NULL)))
580 AND ( (Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
581 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
583 AND ( (Recinfo.BATCHABLE = X_BATCHABLE)
584 OR ( (Recinfo.BATCHABLE IS NULL)
585 AND (X_BATCHABLE IS NULL)))
586 AND ( (Recinfo.MIN_BATCH_CAPACITY = X_MIN_BATCH_CAPACITY)
587 OR ( (Recinfo.MIN_BATCH_CAPACITY IS NULL)
588 AND (X_MIN_BATCH_CAPACITY IS NULL)))
589 AND ( (Recinfo.MAX_BATCH_CAPACITY = X_MAX_BATCH_CAPACITY)
590 OR ( (Recinfo.MAX_BATCH_CAPACITY IS NULL)
591 AND (X_MAX_BATCH_CAPACITY IS NULL)))
592 AND ( (Recinfo.BATCH_CAPACITY_UOM = X_BATCH_CAPACITY_UOM)
593 OR ( (Recinfo.BATCH_CAPACITY_UOM IS NULL)
594 AND (X_BATCH_CAPACITY_UOM IS NULL)))
595 AND ( (Recinfo.BATCH_WINDOW = X_BATCH_WINDOW)
596 OR ( (Recinfo.BATCH_WINDOW IS NULL)
597 AND (X_BATCH_WINDOW IS NULL)))
598 AND ( (Recinfo.BATCH_WINDOW_UOM = X_BATCH_WINDOW_UOM)
599 OR ( (Recinfo.BATCH_WINDOW_UOM IS NULL)
600 AND (X_BATCH_WINDOW_UOM IS NULL)))
601 AND ( (Recinfo.COMPETENCE_ID = X_COMPETENCE_ID)
602 OR ( (Recinfo.COMPETENCE_ID IS NULL)
603 AND (X_COMPETENCE_ID IS NULL)))
604 AND ( (Recinfo.RATING_LEVEL_ID = X_RATING_LEVEL_ID)
605 OR ( (Recinfo.RATING_LEVEL_ID IS NULL)
606 AND (X_RATING_LEVEL_ID IS NULL)))
607 AND ( (Recinfo.QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID)
608 OR ( (Recinfo.QUALIFICATION_TYPE_ID IS NULL)
609 AND (X_QUALIFICATION_TYPE_ID IS NULL)))
610 AND ( (Recinfo.BILLABLE_ITEM_ID = X_BILLABLE_ITEM_ID)
611 OR ( (Recinfo.BILLABLE_ITEM_ID IS NULL)
612 AND (X_BILLABLE_ITEM_ID IS NULL)))
613 AND ( (Recinfo.SUPPLY_SUBINVENTORY = X_SUPPLY_SUBINVENTORY)
614 OR ( (Recinfo.SUPPLY_SUBINVENTORY IS NULL)
615 AND (X_SUPPLY_SUBINVENTORY IS NULL)))
616 AND ( (Recinfo.SUPPLY_LOCATOR_ID = X_SUPPLY_LOCATOR_ID)
617 OR ( (Recinfo.SUPPLY_LOCATOR_ID IS NULL)
618 AND (X_SUPPLY_LOCATOR_ID IS NULL)))
619 AND ( (Recinfo.BATCHING_PENALTY = X_BATCHING_PENALTY) --APS Enhancement for Routings
620 OR( (Recinfo.BATCHING_PENALTY IS NULL)
621 AND(X_BATCHING_PENALTY IS NULL)))
622
623
624
625 ) then
626 return;
627 else
628 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
629 APP_EXCEPTION.Raise_Exception;
630 end if;
631 END Lock_Row;
632 PROCEDURE Update_Row(X_Rowid VARCHAR2,
633 X_Resource_Id NUMBER,
634 X_Resource_Code VARCHAR2,
635 X_Organization_Id NUMBER,
636 X_Last_Update_Date DATE,
637 X_Last_Updated_By NUMBER,
638 X_Last_Update_Login NUMBER,
639 X_Description VARCHAR2,
640 X_Disable_Date DATE,
641 X_Cost_Element_Id NUMBER,
642 X_Purchase_Item_Id NUMBER,
643 X_Cost_Code_Type NUMBER,
644 X_Functional_Currency_Flag NUMBER,
645 X_Unit_Of_Measure VARCHAR2,
646 X_Default_Activity_Id NUMBER,
647 X_Resource_Type NUMBER,
648 X_Autocharge_Type NUMBER,
649 X_Standard_Rate_Flag NUMBER,
650 X_Default_Basis_Type NUMBER,
651 X_Absorption_Account NUMBER,
652 X_Allow_Costs_Flag NUMBER,
653 X_Rate_Variance_Account NUMBER,
654 X_Expenditure_Type VARCHAR2,
655 X_Attribute_Category VARCHAR2,
656 X_Attribute1 VARCHAR2,
657 X_Attribute2 VARCHAR2,
658 X_Attribute3 VARCHAR2,
659 X_Attribute4 VARCHAR2,
660 X_Attribute5 VARCHAR2,
661 X_Attribute6 VARCHAR2,
662 X_Attribute7 VARCHAR2,
663 X_Attribute8 VARCHAR2,
664 X_Attribute9 VARCHAR2,
665 X_Attribute10 VARCHAR2,
666 X_Attribute11 VARCHAR2,
667 X_Attribute12 VARCHAR2,
668 X_Attribute13 VARCHAR2,
669 X_Attribute14 VARCHAR2,
670 X_Attribute15 VARCHAR2,
671 X_REQUEST_ID NUMBER := NULL,
672 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
673 X_PROGRAM_ID NUMBER := NULL,
674 X_PROGRAM_UPDATE_DATE DATE := NULL
675 )
676 IS
677 BEGIN
678 Update_Row(X_Rowid => X_Rowid,
682 X_Last_Update_Date => X_Last_Update_Date,
679 X_Resource_Id => X_Resource_Id,
680 X_Resource_Code => X_Resource_Code,
681 X_Organization_Id => X_Organization_Id,
683 X_Last_Updated_By => X_Last_Updated_By,
684 X_Last_Update_Login => X_Last_Update_Login,
685 X_Description => X_Description,
686 X_Disable_Date => X_Disable_Date,
687 X_Cost_Element_Id => X_Cost_Element_Id,
688 X_Purchase_Item_Id => X_Purchase_Item_Id,
689 X_Cost_Code_Type => X_Cost_Code_Type,
690 X_Functional_Currency_Flag => X_Functional_Currency_Flag,
691 X_Unit_Of_Measure => X_Unit_Of_Measure,
692 X_Default_Activity_Id => X_Default_Activity_Id,
693 X_Resource_Type => X_Resource_Type,
694 X_Autocharge_Type => X_Autocharge_Type,
695 X_Standard_Rate_Flag => X_Standard_Rate_Flag,
696 X_Default_Basis_Type => X_Default_Basis_Type,
697 X_Absorption_Account => X_Absorption_Account,
698 X_Allow_Costs_Flag => X_Allow_Costs_Flag,
699 X_Rate_Variance_Account => X_Rate_Variance_Account,
700 X_Expenditure_Type => X_Expenditure_Type,
701 X_Attribute_Category => X_Attribute_Category,
702 X_Attribute1 => X_Attribute1,
703 X_Attribute2 => X_Attribute2,
704 X_Attribute3 => X_Attribute3,
705 X_Attribute4 => X_Attribute4,
706 X_Attribute5 => X_Attribute5,
707 X_Attribute6 => X_Attribute6,
708 X_Attribute7 => X_Attribute7,
709 X_Attribute8 => X_Attribute8,
710 X_Attribute9 => X_Attribute9,
711 X_Attribute10 => X_Attribute10,
712 X_Attribute11 => X_Attribute11,
713 X_Attribute12 => X_Attribute12,
714 X_Attribute13 => X_Attribute13,
715 X_Attribute14 => X_Attribute14,
716 X_Attribute15 => X_Attribute15,
717 X_REQUEST_ID =>X_REQUEST_ID,
718 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
719 X_PROGRAM_ID => X_PROGRAM_ID,
720 X_PROGRAM_UPDATE_DATE =>X_PROGRAM_UPDATE_DATE,
721 X_BATCHABLE =>NULL,
722 X_MIN_BATCH_CAPACITY =>NULL,
723 X_MAX_BATCH_CAPACITY =>NULL,
724 X_BATCH_CAPACITY_UOM =>NULL,
725 X_BATCH_WINDOW =>NULL,
726 X_BATCH_WINDOW_UOM =>NULL,
727 X_COMPETENCE_ID =>NULL,
728 X_RATING_LEVEL_ID =>NULL,
729 X_QUALIFICATION_TYPE_ID => NULL,
730 X_BILLABLE_ITEM_ID => NULL,
731 X_SUPPLY_SUBINVENTORY => NULL,
732 X_SUPPLY_LOCATOR_ID => NULL,
733 X_BATCHING_PENALTY => NULL); --APS Enhancement for Routings
734
735 END;
736 PROCEDURE Update_Row(X_Rowid VARCHAR2,
737 X_Resource_Id NUMBER,
738 X_Resource_Code VARCHAR2,
739 X_Organization_Id NUMBER,
740 X_Last_Update_Date DATE,
741 X_Last_Updated_By NUMBER,
742 X_Last_Update_Login NUMBER,
743 X_Description VARCHAR2,
744 X_Disable_Date DATE,
745 X_Cost_Element_Id NUMBER,
746 X_Purchase_Item_Id NUMBER,
747 X_Cost_Code_Type NUMBER,
748 X_Functional_Currency_Flag NUMBER,
749 X_Unit_Of_Measure VARCHAR2,
750 X_Default_Activity_Id NUMBER,
751 X_Resource_Type NUMBER,
752 X_Autocharge_Type NUMBER,
753 X_Standard_Rate_Flag NUMBER,
754 X_Default_Basis_Type NUMBER,
755 X_Absorption_Account NUMBER,
756 X_Allow_Costs_Flag NUMBER,
757 X_Rate_Variance_Account NUMBER,
758 X_Expenditure_Type VARCHAR2,
759 X_Attribute_Category VARCHAR2,
760 X_Attribute1 VARCHAR2,
761 X_Attribute2 VARCHAR2,
762 X_Attribute3 VARCHAR2,
763 X_Attribute4 VARCHAR2,
764 X_Attribute5 VARCHAR2,
765 X_Attribute6 VARCHAR2,
766 X_Attribute7 VARCHAR2,
767 X_Attribute8 VARCHAR2,
768 X_Attribute9 VARCHAR2,
769 X_Attribute10 VARCHAR2,
770 X_Attribute11 VARCHAR2,
771 X_Attribute12 VARCHAR2,
775 X_REQUEST_ID NUMBER := NULL,
772 X_Attribute13 VARCHAR2,
773 X_Attribute14 VARCHAR2,
774 X_Attribute15 VARCHAR2,
776 X_PROGRAM_APPLICATION_ID NUMBER := NULL,
777 X_PROGRAM_ID NUMBER := NULL,
778 X_PROGRAM_UPDATE_DATE DATE := NULL,
779 X_BATCHABLE NUMBER ,
780 X_MIN_BATCH_CAPACITY NUMBER ,
781 X_MAX_BATCH_CAPACITY NUMBER ,
782 X_BATCH_CAPACITY_UOM VARCHAR2 ,
783 X_BATCH_WINDOW NUMBER ,
784 X_BATCH_WINDOW_UOM VARCHAR2,
785 X_COMPETENCE_ID NUMBER := NULL,
786 X_RATING_LEVEL_ID NUMBER := NULL,
787 X_QUALIFICATION_TYPE_ID NUMBER := NULL,
788 X_BILLABLE_ITEM_ID NUMBER := NULL,
789 X_SUPPLY_SUBINVENTORY VARCHAR2,
790 X_SUPPLY_LOCATOR_ID NUMBER :=NULL,
791 X_BATCHING_PENALTY NUMBER := NULL --APS Enhancement for Routings
792 ) IS
793 BEGIN
794 UPDATE BOM_RESOURCES
795 SET
796 resource_id = X_Resource_Id,
797 resource_code = X_Resource_Code,
798 organization_id = X_Organization_Id,
799 last_update_date = X_Last_Update_Date,
800 last_updated_by = X_Last_Updated_By,
801 last_update_login = X_Last_Update_Login,
802 description = X_Description,
803 disable_date = X_Disable_Date,
804 cost_element_id = X_Cost_Element_Id,
805 purchase_item_id = X_Purchase_Item_Id,
806 cost_code_type = X_Cost_Code_Type,
807 functional_currency_flag = X_Functional_Currency_Flag,
808 unit_of_measure = X_Unit_Of_Measure,
809 default_activity_id = X_Default_Activity_Id,
810 resource_type = X_Resource_Type,
811 autocharge_type = X_Autocharge_Type,
812 standard_rate_flag = X_Standard_Rate_Flag,
813 default_basis_type = X_Default_Basis_Type,
814 absorption_account = X_Absorption_Account,
815 allow_costs_flag = X_Allow_Costs_Flag,
816 rate_variance_account = X_Rate_Variance_Account,
817 expenditure_type = X_Expenditure_Type,
818 attribute_category = X_Attribute_Category,
819 attribute1 = X_Attribute1,
820 attribute2 = X_Attribute2,
821 attribute3 = X_Attribute3,
822 attribute4 = X_Attribute4,
823 attribute5 = X_Attribute5,
824 attribute6 = X_Attribute6,
825 attribute7 = X_Attribute7,
826 attribute8 = X_Attribute8,
827 attribute9 = X_Attribute9,
828 attribute10 = X_Attribute10,
829 attribute11 = X_Attribute11,
830 attribute12 = X_Attribute12,
831 attribute13 = X_Attribute13,
832 attribute14 = X_Attribute14,
833 attribute15 = X_Attribute15,
834 request_id = x_request_id,
835 program_application_id = x_program_application_id,
836 program_id = x_program_id,
837 program_update_date = x_program_update_date,
838 batchable = x_batchable,
839 min_batch_capacity = x_min_batch_capacity,
840 max_batch_capacity = x_max_batch_capacity,
841 batch_capacity_uom = x_batch_capacity_uom,
842 batch_window = x_batch_window,
843 batch_window_uom = x_batch_window_uom,
844 competence_id = x_competence_id,
845 rating_level_id = x_rating_level_id,
846 qualification_type_id = x_qualification_type_id,
847 billable_item_id = x_billable_item_id,
848 supply_subinventory = x_supply_subinventory,
849 supply_locator_id = x_supply_locator_id,
850 batching_penalty = x_batching_penalty --APS Enhancement for Routings
851
852 WHERE rowid = X_Rowid;
853
854 if (SQL%NOTFOUND) then
855 Raise NO_DATA_FOUND;
856 end if;
857 END Update_Row;
858
859 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
860 X_Resource_Code VARCHAR2,
861 X_Organization_Id NUMBER,
862 X_unique_flag IN OUT NOCOPY NUMBER) IS
863 dummy NUMBER;
864 BEGIN
865 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
866 (SELECT 1 FROM BOM_RESOURCES
867 WHERE Organization_Id = X_Organization_Id
868 AND Resource_Code = X_Resource_Code
869 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
870 );
871 X_unique_flag := 1;
872
873 EXCEPTION
874 WHEN NO_DATA_FOUND THEN
878 FUNCTION Get_UOM_From_GL_Sets_Of_Books(X_Organization_Id NUMBER) RETURN VARCHAR2 IS
875 X_unique_flag := 0;
876 END Check_Unique;
877
879 currency_code VARCHAR2(3);
880 book_id NUMBER;
881 BEGIN
882 SELECT SET_OF_BOOKS_ID INTO book_id FROM ORG_ORGANIZATION_DEFINITIONS
883 WHERE ORGANIZATION_ID = X_Organization_Id;
884 SELECT SUBSTR(CURRENCY_CODE,1,3) INTO currency_code FROM GL_SETS_OF_BOOKS
885 WHERE SET_OF_BOOKS_ID = book_id;
886 RETURN(currency_code);
887
888 EXCEPTION
889 WHEN NO_DATA_FOUND THEN
890 FND_MESSAGE.SET_NAME('BOM', 'MFG_NOT_FOUND');
891 FND_MESSAGE.SET_TOKEN('ENTITY', 'CURRENCY CODE-CAP', TRUE);
892 APP_EXCEPTION.RAISE_EXCEPTION;
893 END Get_UOM_From_GL_Sets_Of_Books;
894
895 FUNCTION Check_Valid_UOM(X_Unit_Of_Measure VARCHAR2) RETURN NUMBER IS
896 uom_value NUMBER;
897 BEGIN
898 SELECT COUNT(*) INTO uom_value FROM MTL_UNITS_OF_MEASURE
899 WHERE UOM_CODE = SUBSTR(X_Unit_Of_Measure, 1, 3)
900 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE;
901 RETURN(uom_value);
902 END Check_Valid_UOM;
903
904 END BOM_RESOURCES_PKG;