1 package body PA_ALLOC_RULES_ALL_PKG AS
2 /* $Header: PAXATRLB.pls 120.2 2005/06/20 12:58:54 dlanka noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RULE_ID in out NOCOPY NUMBER,
6 X_RULE_NAME in VARCHAR2,
10 X_SOURCE_AMOUNT_TYPE in VARCHAR2,
7 X_DESCRIPTION in VARCHAR2,
8 X_POOL_PERCENT in NUMBER,
9 X_PERIOD_TYPE in VARCHAR2,
11 X_SOURCE_BALANCE_CATEGORY in VARCHAR2,
12 X_SOURCE_BALANCE_TYPE in VARCHAR2,
13 X_ALLOC_RESOURCE_LIST_ID in NUMBER,
14 X_AUTO_RELEASE_FLAG in VARCHAR2,
15 X_ALLOCATION_METHOD in VARCHAR2,
16 X_IMP_WITH_EXCEPTION in VARCHAR2,
17 X_DUP_TARGETS_FLAG in VARCHAR2,
18 X_TARGET_EXP_TYPE_CLASS in VARCHAR2,
19 X_TARGET_EXP_ORG_ID in NUMBER,
20 X_TARGET_EXP_TYPE in VARCHAR2,
21 X_TARGET_COST_TYPE in VARCHAR2,
22 X_OFFSET_EXP_TYPE_CLASS in VARCHAR2,
23 X_OFFSET_EXP_ORG_ID in NUMBER,
24 X_OFFSET_EXP_TYPE in VARCHAR2,
25 X_OFFSET_COST_TYPE in VARCHAR2,
26 X_OFFSET_METHOD in VARCHAR2,
27 X_OFFSET_PROJECT_ID in NUMBER,
28 X_OFFSET_TASK_ID in NUMBER,
29 X_BASIS_METHOD in VARCHAR2,
30 X_BASIS_RELATIVE_PERIOD in NUMBER,
31 X_BASIS_AMOUNT_TYPE in VARCHAR2,
32 X_BASIS_BALANCE_CATEGORY in VARCHAR2,
33 X_BASIS_BUDGET_TYPE_CODE in VARCHAR2,
34 X_BAS_BUDGET_ENTRY_METHOD_CODE in VARCHAR2,
35 X_BASIS_BALANCE_TYPE in VARCHAR2,
36 X_BASIS_RESOURCE_LIST_ID in NUMBER,
37 X_SOURCE_EXTN_FLAG in VARCHAR2,
38 X_TARGET_EXTN_FLAG in VARCHAR2,
39 X_FIXED_AMOUNT in NUMBER,
40 X_START_DATE_ACTIVE in DATE,
41 X_END_DATE_ACTIVE in DATE,
42 X_ATTRIBUTE_CATEGORY in VARCHAR2,
43 X_ATTRIBUTE1 in VARCHAR2,
44 X_ATTRIBUTE2 in VARCHAR2,
45 X_ATTRIBUTE3 in VARCHAR2,
46 X_ATTRIBUTE4 in VARCHAR2,
47 X_ATTRIBUTE5 in VARCHAR2,
48 X_ATTRIBUTE6 in VARCHAR2,
49 X_ATTRIBUTE7 in VARCHAR2,
50 X_ATTRIBUTE8 in VARCHAR2,
51 X_ATTRIBUTE9 in VARCHAR2,
52 X_ATTRIBUTE10 in VARCHAR2,
53 X_CREATION_DATE in DATE,
54 X_CREATED_BY in NUMBER,
55 X_LAST_UPDATE_DATE in DATE,
56 X_LAST_UPDATED_BY in NUMBER,
57 X_LAST_UPDATE_LOGIN in NUMBER,
58 X_LIMIT_TARGET_PROJECTS_CODE in varchar2,
59 X_BASIS_FIN_PLAN_TYPE_ID in NUMBER /* Bug 2619977 */ ,
60 /* FP.M : Allocation Impact : 3512552 */
61 X_ALLOC_RESOURCE_STRUCT_TYPE In Varchar2 ,
62 X_BASIS_RESOURCE_STRUCT_TYPE In Varchar2 ,
63 X_ALLOC_RBS_VERSION In Number ,
64 X_BASIS_RBS_VERSION In Number ,
65 X_ORG_ID in number
66
67 ) is
68 cursor C is select ROWID from PA_ALLOC_RULES_ALL
69 where RULE_ID = X_RULE_ID;
70 CURSOR C1 is Select pa_alloc_rules_s.nextval from sys.dual;
71 --X_LAST_UPDATE_DATE DATE;
72 --X_LAST_UPDATED_BY NUMBER;
73 --X_LAST_UPDATE_LOGIN NUMBER;
74 begin
75 --X_LAST_UPDATE_DATE := SYSDATE;
76
77 if X_RULE_ID is null then
78 open C1;
79 fetch C1 into X_RULE_ID;
80 close C1;
81 end if;
82 insert into PA_ALLOC_RULES_ALL (
83 RULE_ID,
84 RULE_NAME,
85 DESCRIPTION,
86 POOL_PERCENT,
87 PERIOD_TYPE,
88 SOURCE_AMOUNT_TYPE,
89 SOURCE_BALANCE_CATEGORY,
90 SOURCE_BALANCE_TYPE,
91 ALLOC_RESOURCE_LIST_ID,
92 AUTO_RELEASE_FLAG,
93 ALLOCATION_METHOD,
94 IMP_WITH_EXCEPTION,
95 DUP_TARGETS_FLAG,
96 TARGET_EXP_TYPE_CLASS,
97 TARGET_EXP_ORG_ID,
98 TARGET_EXP_TYPE,
99 TARGET_COST_TYPE,
100 OFFSET_EXP_TYPE_CLASS,
101 OFFSET_EXP_ORG_ID,
102 OFFSET_EXP_TYPE,
103 OFFSET_COST_TYPE,
104 OFFSET_METHOD,
105 OFFSET_PROJECT_ID,
106 OFFSET_TASK_ID,
107 BASIS_METHOD,
108 BASIS_RELATIVE_PERIOD,
109 BASIS_AMOUNT_TYPE,
110 BASIS_BALANCE_CATEGORY,
111 BASIS_BUDGET_TYPE_CODE,
112 BASIS_FIN_PLAN_TYPE_ID, /* Bug 2619977 */
113 BASIS_BUDGET_ENTRY_METHOD_CODE,
114 BASIS_BALANCE_TYPE,
115 BASIS_RESOURCE_LIST_ID,
116 SOURCE_EXTN_FLAG,
117 TARGET_EXTN_FLAG,
118 FIXED_AMOUNT,
119 START_DATE_ACTIVE,
120 END_DATE_ACTIVE,
121 ATTRIBUTE_CATEGORY,
122 ATTRIBUTE1,
123 ATTRIBUTE2,
124 ATTRIBUTE3,
125 ATTRIBUTE4,
126 ATTRIBUTE5,
127 ATTRIBUTE6,
128 ATTRIBUTE7,
129 ATTRIBUTE8,
130 ATTRIBUTE9,
131 ATTRIBUTE10,
132 CREATION_DATE,
133 CREATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATED_BY,
136 LAST_UPDATE_LOGIN,
137 LIMIT_TARGET_PROJECTS_CODE ,
138
139 /* FP.M : Allocation Impact : 3512552 */
140 ALLOC_RESOURCE_STRUCT_TYPE ,
141 BASIS_RESOURCE_STRUCT_TYPE ,
142 ALLOC_RBS_VERSION ,
143 BASIS_RBS_VERSION ,
144 ORG_ID
145
146 ) values (
147 X_RULE_ID,
148 X_RULE_NAME,
149 X_DESCRIPTION,
150 X_POOL_PERCENT,
151 X_PERIOD_TYPE,
152 X_SOURCE_AMOUNT_TYPE,
153 X_SOURCE_BALANCE_CATEGORY,
154 X_SOURCE_BALANCE_TYPE,
155 X_ALLOC_RESOURCE_LIST_ID,
156 X_AUTO_RELEASE_FLAG,
157 X_ALLOCATION_METHOD,
158 X_IMP_WITH_EXCEPTION,
159 X_DUP_TARGETS_FLAG,
160 X_TARGET_EXP_TYPE_CLASS,
161 X_TARGET_EXP_ORG_ID,
162 X_TARGET_EXP_TYPE,
163 X_TARGET_COST_TYPE,
164 X_OFFSET_EXP_TYPE_CLASS,
165 X_OFFSET_EXP_ORG_ID,
166 X_OFFSET_EXP_TYPE,
167 X_OFFSET_COST_TYPE,
168 X_OFFSET_METHOD,
169 X_OFFSET_PROJECT_ID,
170 X_OFFSET_TASK_ID,
171 X_BASIS_METHOD,
172 X_BASIS_RELATIVE_PERIOD,
173 X_BASIS_AMOUNT_TYPE,
174 X_BASIS_BALANCE_CATEGORY,
175 X_BASIS_BUDGET_TYPE_CODE,
176 X_BASIS_FIN_PLAN_TYPE_ID, /* Bug 2619977 */
177 X_BAS_BUDGET_ENTRY_METHOD_CODE,
178 X_BASIS_BALANCE_TYPE,
179 X_BASIS_RESOURCE_LIST_ID,
180 X_SOURCE_EXTN_FLAG,
181 X_TARGET_EXTN_FLAG,
182 X_FIXED_AMOUNT,
183 X_START_DATE_ACTIVE,
184 X_END_DATE_ACTIVE,
185 X_ATTRIBUTE_CATEGORY,
186 X_ATTRIBUTE1,
187 X_ATTRIBUTE2,
188 X_ATTRIBUTE3,
189 X_ATTRIBUTE4,
190 X_ATTRIBUTE5,
191 X_ATTRIBUTE6,
192 X_ATTRIBUTE7,
193 X_ATTRIBUTE8,
194 X_ATTRIBUTE9,
195 X_ATTRIBUTE10,
196 X_CREATION_DATE,
197 X_CREATED_BY,
198 X_LAST_UPDATE_DATE,
199 X_LAST_UPDATED_BY,
200 X_LAST_UPDATE_LOGIN,
201 X_LIMIT_TARGET_PROJECTS_CODE ,
202
203 /* FP.M : Allocation Impact : 3512552 */
204 X_ALLOC_RESOURCE_STRUCT_TYPE ,
205 X_BASIS_RESOURCE_STRUCT_TYPE ,
206 X_ALLOC_RBS_VERSION ,
207 X_BASIS_RBS_VERSION ,
208 X_ORG_ID
209 );
210
211 open c;
212 fetch c into X_ROWID;
213 if (c%notfound) then
214 close c;
215 raise no_data_found;
216 end if;
217 close c;
218
219 end INSERT_ROW;
220
221 procedure LOCK_ROW (
222 X_RULE_ID in NUMBER,
223 X_RULE_NAME in VARCHAR2,
224 X_DESCRIPTION in VARCHAR2,
225 X_POOL_PERCENT in NUMBER,
226 X_PERIOD_TYPE in VARCHAR2,
227 X_SOURCE_AMOUNT_TYPE in VARCHAR2,
228 X_SOURCE_BALANCE_CATEGORY in VARCHAR2,
229 X_SOURCE_BALANCE_TYPE in VARCHAR2,
230 X_ALLOC_RESOURCE_LIST_ID in NUMBER,
231 X_AUTO_RELEASE_FLAG in VARCHAR2,
232 X_ALLOCATION_METHOD in VARCHAR2,
233 X_IMP_WITH_EXCEPTION in VARCHAR2,
234 X_DUP_TARGETS_FLAG in VARCHAR2,
235 X_TARGET_EXP_TYPE_CLASS in VARCHAR2,
236 X_TARGET_EXP_ORG_ID in NUMBER,
237 X_TARGET_EXP_TYPE in VARCHAR2,
238 X_TARGET_COST_TYPE in VARCHAR2,
239 X_OFFSET_EXP_TYPE_CLASS in VARCHAR2,
240 X_OFFSET_EXP_ORG_ID in NUMBER,
241 X_OFFSET_EXP_TYPE in VARCHAR2,
242 X_OFFSET_COST_TYPE in VARCHAR2,
243 X_OFFSET_METHOD in VARCHAR2,
244 X_OFFSET_PROJECT_ID in NUMBER,
245 X_OFFSET_TASK_ID in NUMBER,
246 X_BASIS_METHOD in VARCHAR2,
247 X_BASIS_RELATIVE_PERIOD in NUMBER,
248 X_BASIS_AMOUNT_TYPE in VARCHAR2,
249 X_BASIS_BALANCE_CATEGORY in VARCHAR2,
250 X_BASIS_BUDGET_TYPE_CODE in VARCHAR2,
251 X_BAS_BUDGET_ENTRY_METHOD_CODE in VARCHAR2,
252 X_BASIS_BALANCE_TYPE in VARCHAR2,
253 X_BASIS_RESOURCE_LIST_ID in NUMBER,
254 X_SOURCE_EXTN_FLAG in VARCHAR2,
255 X_TARGET_EXTN_FLAG in VARCHAR2,
256 X_FIXED_AMOUNT in NUMBER,
257 X_START_DATE_ACTIVE in DATE,
258 X_END_DATE_ACTIVE in DATE,
259 X_ATTRIBUTE_CATEGORY in VARCHAR2,
260 X_ATTRIBUTE1 in VARCHAR2,
261 X_ATTRIBUTE2 in VARCHAR2,
262 X_ATTRIBUTE3 in VARCHAR2,
263 X_ATTRIBUTE4 in VARCHAR2,
264 X_ATTRIBUTE5 in VARCHAR2,
265 X_ATTRIBUTE6 in VARCHAR2,
266 X_ATTRIBUTE7 in VARCHAR2,
267 X_ATTRIBUTE8 in VARCHAR2,
268 X_ATTRIBUTE9 in VARCHAR2,
269 X_ATTRIBUTE10 in VARCHAR2,
270 X_LIMIT_TARGET_PROJECTS_CODE varchar2,
271 X_BASIS_FIN_PLAN_TYPE_ID in NUMBER /* Bug 2619977 */ ,
272
273 /* FP.M : Allocation Impact : 3512552 */
274 X_ALLOC_RESOURCE_STRUCT_TYPE In Varchar2 ,
275 X_BASIS_RESOURCE_STRUCT_TYPE In Varchar2 ,
276 X_ALLOC_RBS_VERSION In Number ,
277 X_BASIS_RBS_VERSION In Number
278
279
280 ) is
281 cursor c1 is select
282 RULE_NAME,
283 DESCRIPTION,
284 POOL_PERCENT,
285 PERIOD_TYPE,
286 SOURCE_AMOUNT_TYPE,
287 SOURCE_BALANCE_CATEGORY,
288 SOURCE_BALANCE_TYPE,
289 ALLOC_RESOURCE_LIST_ID,
290 AUTO_RELEASE_FLAG,
291 ALLOCATION_METHOD,
292 IMP_WITH_EXCEPTION,
293 DUP_TARGETS_FLAG,
294 TARGET_EXP_TYPE_CLASS,
295 TARGET_EXP_ORG_ID,
296 TARGET_EXP_TYPE,
297 TARGET_COST_TYPE,
298 OFFSET_EXP_TYPE_CLASS,
299 OFFSET_EXP_ORG_ID,
300 OFFSET_EXP_TYPE,
301 OFFSET_COST_TYPE,
302 OFFSET_METHOD,
303 OFFSET_PROJECT_ID,
304 OFFSET_TASK_ID,
305 BASIS_METHOD,
306 BASIS_RELATIVE_PERIOD,
307 BASIS_AMOUNT_TYPE,
308 BASIS_BALANCE_CATEGORY,
309 BASIS_BUDGET_TYPE_CODE,
310 BASIS_FIN_PLAN_TYPE_ID, /*Bug 2619977 */
311 BASIS_BUDGET_ENTRY_METHOD_CODE,
312 BASIS_BALANCE_TYPE,
313 BASIS_RESOURCE_LIST_ID,
314 SOURCE_EXTN_FLAG,
315 TARGET_EXTN_FLAG,
316 FIXED_AMOUNT,
317 START_DATE_ACTIVE,
318 END_DATE_ACTIVE,
319 ATTRIBUTE_CATEGORY,
320 ATTRIBUTE1,
321 ATTRIBUTE2,
322 ATTRIBUTE3,
323 ATTRIBUTE4,
324 ATTRIBUTE5,
325 ATTRIBUTE6,
326 ATTRIBUTE7,
327 ATTRIBUTE8,
328 ATTRIBUTE9,
329 ATTRIBUTE10,
330 LIMIT_TARGET_PROJECTS_CODE ,
331 /* FP.M : Allocation Impact : 3512552 */
332 ALLOC_RESOURCE_STRUCT_TYPE ,
333 BASIS_RESOURCE_STRUCT_TYPE ,
334 ALLOC_RBS_VERSION ,
335 BASIS_RBS_VERSION
336
337 from PA_ALLOC_RULES_ALL
338 where RULE_ID = X_RULE_ID
339 for update of RULE_ID nowait;
340 tlinfo c1%rowtype;
341
342 begin
343 open c1;
344 fetch c1 into tlinfo;
345 if (c1%notfound) then
346 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
347 app_exception.raise_exception;
348 close c1;
349 return;
350 end if;
351 close c1;
352
353 if ( (tlinfo.RULE_NAME = X_RULE_NAME)
354 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
355 OR ((tlinfo.DESCRIPTION is null)
356 AND (X_DESCRIPTION is null)))
357 AND ((tlinfo.POOL_PERCENT = X_POOL_PERCENT)
358 OR ((tlinfo.POOL_PERCENT is null)
359 AND (X_POOL_PERCENT is null)))
360 AND ((tlinfo.PERIOD_TYPE = X_PERIOD_TYPE)
361 OR ((tlinfo.PERIOD_TYPE is null)
362 AND (X_PERIOD_TYPE is null)))
363 AND ((tlinfo.SOURCE_AMOUNT_TYPE = X_SOURCE_AMOUNT_TYPE)
364 OR ((tlinfo.SOURCE_AMOUNT_TYPE is null)
365 AND (X_SOURCE_AMOUNT_TYPE is null)))
366 AND ((tlinfo.SOURCE_BALANCE_CATEGORY = X_SOURCE_BALANCE_CATEGORY)
367 OR ((tlinfo.SOURCE_BALANCE_CATEGORY is null)
368 AND (X_SOURCE_BALANCE_CATEGORY is null)))
369 AND ((tlinfo.SOURCE_BALANCE_TYPE = X_SOURCE_BALANCE_TYPE)
370 OR ((tlinfo.SOURCE_BALANCE_TYPE is null)
371 AND (X_SOURCE_BALANCE_TYPE is null)))
372 AND ((tlinfo.ALLOC_RESOURCE_LIST_ID = X_ALLOC_RESOURCE_LIST_ID)
373 OR ((tlinfo.ALLOC_RESOURCE_LIST_ID is null)
374 AND (X_ALLOC_RESOURCE_LIST_ID is null)))
375 AND ((tlinfo.AUTO_RELEASE_FLAG = X_AUTO_RELEASE_FLAG)
376 OR ((tlinfo.AUTO_RELEASE_FLAG is null)
377 AND (X_AUTO_RELEASE_FLAG is null)))
378 AND ((tlinfo.ALLOCATION_METHOD = X_ALLOCATION_METHOD)
379 OR ((tlinfo.ALLOCATION_METHOD is null)
380 AND (X_ALLOCATION_METHOD is null)))
381 AND ((tlinfo.IMP_WITH_EXCEPTION = X_IMP_WITH_EXCEPTION)
382 OR ((tlinfo.IMP_WITH_EXCEPTION is null)
383 AND (X_IMP_WITH_EXCEPTION is null)))
384 AND ((tlinfo.DUP_TARGETS_FLAG = X_DUP_TARGETS_FLAG)
385 OR ((tlinfo.DUP_TARGETS_FLAG is null)
386 AND (X_DUP_TARGETS_FLAG is null)))
387 AND ((tlinfo.TARGET_EXP_TYPE_CLASS = X_TARGET_EXP_TYPE_CLASS)
388 OR ((tlinfo.TARGET_EXP_TYPE_CLASS is null)
389 AND (X_TARGET_EXP_TYPE_CLASS is null)))
390 AND ((tlinfo.TARGET_EXP_ORG_ID = X_TARGET_EXP_ORG_ID)
391 OR ((tlinfo.TARGET_EXP_ORG_ID is null)
392 AND (X_TARGET_EXP_ORG_ID is null)))
393 AND ((tlinfo.TARGET_EXP_TYPE = X_TARGET_EXP_TYPE)
394 OR ((tlinfo.TARGET_EXP_TYPE is null)
395 AND (X_TARGET_EXP_TYPE is null)))
396 AND ((tlinfo.TARGET_COST_TYPE = X_TARGET_COST_TYPE)
397 OR ((tlinfo.TARGET_COST_TYPE is null)
398 AND (X_TARGET_COST_TYPE is null)))
399 AND ((tlinfo.OFFSET_EXP_TYPE_CLASS = X_OFFSET_EXP_TYPE_CLASS)
400 OR ((tlinfo.OFFSET_EXP_TYPE_CLASS is null)
401 AND (X_OFFSET_EXP_TYPE_CLASS is null)))
402 AND ((tlinfo.OFFSET_EXP_ORG_ID = X_OFFSET_EXP_ORG_ID)
403 OR ((tlinfo.OFFSET_EXP_ORG_ID is null)
404 AND (X_OFFSET_EXP_ORG_ID is null)))
405 AND ((tlinfo.OFFSET_EXP_TYPE = X_OFFSET_EXP_TYPE)
406 OR ((tlinfo.OFFSET_EXP_TYPE is null)
407 AND (X_OFFSET_EXP_TYPE is null)))
408 AND ((tlinfo.OFFSET_COST_TYPE = X_OFFSET_COST_TYPE)
409 OR ((tlinfo.OFFSET_COST_TYPE is null)
410 AND (X_OFFSET_COST_TYPE is null)))
411 AND ((tlinfo.OFFSET_METHOD = X_OFFSET_METHOD)
412 OR ((tlinfo.OFFSET_METHOD is null)
413 AND (X_OFFSET_METHOD is null)))
414 AND ((tlinfo.OFFSET_PROJECT_ID = X_OFFSET_PROJECT_ID)
415 OR ((tlinfo.OFFSET_PROJECT_ID is null)
416 AND (X_OFFSET_PROJECT_ID is null)))
417 AND ((tlinfo.OFFSET_TASK_ID = X_OFFSET_TASK_ID)
418 OR ((tlinfo.OFFSET_TASK_ID is null)
419 AND (X_OFFSET_TASK_ID is null)))
420 AND ((tlinfo.BASIS_METHOD = X_BASIS_METHOD)
421 OR ((tlinfo.BASIS_METHOD is null)
422 AND (X_BASIS_METHOD is null)))
423 AND ((tlinfo.BASIS_RELATIVE_PERIOD = X_BASIS_RELATIVE_PERIOD)
424 OR ((tlinfo.BASIS_RELATIVE_PERIOD is null)
425 AND (X_BASIS_RELATIVE_PERIOD is null)))
426 AND ((tlinfo.BASIS_AMOUNT_TYPE = X_BASIS_AMOUNT_TYPE)
427 OR ((tlinfo.BASIS_AMOUNT_TYPE is null)
428 AND (X_BASIS_AMOUNT_TYPE is null)))
429 AND ((tlinfo.BASIS_BALANCE_CATEGORY = X_BASIS_BALANCE_CATEGORY)
430 OR ((tlinfo.BASIS_BALANCE_CATEGORY is null)
431 AND (X_BASIS_BALANCE_CATEGORY is null)))
432 AND ((tlinfo.BASIS_BUDGET_TYPE_CODE = X_BASIS_BUDGET_TYPE_CODE)
433 OR ((tlinfo.BASIS_BUDGET_TYPE_CODE is null)
434 AND (X_BASIS_BUDGET_TYPE_CODE is null)))
435 /* Next 3 lines added for bug 2619977 */
436 AND ((tlinfo.BASIS_FIN_PLAN_TYPE_ID = X_BASIS_FIN_PLAN_TYPE_ID)
437 OR ((tlinfo.BASIS_FIN_PLAN_TYPE_ID is null)
438 AND (X_BASIS_FIN_PLAN_TYPE_ID is null)))
439 AND ((tlinfo.BASIS_BUDGET_ENTRY_METHOD_CODE = X_BAS_BUDGET_ENTRY_METHOD_CODE)
440 OR ((tlinfo.BASIS_BUDGET_ENTRY_METHOD_CODE is null)
441 AND (X_BAS_BUDGET_ENTRY_METHOD_CODE is null)))
442 AND ((tlinfo.BASIS_BALANCE_TYPE = X_BASIS_BALANCE_TYPE)
443 OR ((tlinfo.BASIS_BALANCE_TYPE is null)
444 AND (X_BASIS_BALANCE_TYPE is null)))
445 AND ((tlinfo.BASIS_RESOURCE_LIST_ID = X_BASIS_RESOURCE_LIST_ID)
446 OR ((tlinfo.BASIS_RESOURCE_LIST_ID is null)
447 AND (X_BASIS_RESOURCE_LIST_ID is null)))
448 AND ((tlinfo.SOURCE_EXTN_FLAG = X_SOURCE_EXTN_FLAG)
449 OR ((tlinfo.SOURCE_EXTN_FLAG is null)
450 AND (X_SOURCE_EXTN_FLAG is null)))
451 AND ((tlinfo.TARGET_EXTN_FLAG = X_TARGET_EXTN_FLAG)
452 OR ((tlinfo.TARGET_EXTN_FLAG is null)
453 AND (X_TARGET_EXTN_FLAG is null)))
454 AND ((tlinfo.FIXED_AMOUNT = X_FIXED_AMOUNT)
455 OR ((tlinfo.FIXED_AMOUNT is null)
456 AND (X_FIXED_AMOUNT is null)))
457 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
458 OR ((tlinfo.START_DATE_ACTIVE is null)
459 AND (X_START_DATE_ACTIVE is null)))
460 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
461 OR ((tlinfo.END_DATE_ACTIVE is null)
462 AND (X_END_DATE_ACTIVE is null)))
463 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
464 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
465 AND (X_ATTRIBUTE_CATEGORY is null)))
466 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
467 OR ((tlinfo.ATTRIBUTE1 is null)
468 AND (X_ATTRIBUTE1 is null)))
469 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
470 OR ((tlinfo.ATTRIBUTE2 is null)
471 AND (X_ATTRIBUTE2 is null)))
472 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
473 OR ((tlinfo.ATTRIBUTE3 is null)
474 AND (X_ATTRIBUTE3 is null)))
475 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
476 OR ((tlinfo.ATTRIBUTE4 is null)
477 AND (X_ATTRIBUTE4 is null)))
478 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
479 OR ((tlinfo.ATTRIBUTE5 is null)
480 AND (X_ATTRIBUTE5 is null)))
481 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
482 OR ((tlinfo.ATTRIBUTE6 is null)
483 AND (X_ATTRIBUTE6 is null)))
484 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
485 OR ((tlinfo.ATTRIBUTE7 is null)
486 AND (X_ATTRIBUTE7 is null)))
487 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
488 OR ((tlinfo.ATTRIBUTE8 is null)
489 AND (X_ATTRIBUTE8 is null)))
490 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
491 OR ((tlinfo.ATTRIBUTE9 is null)
492 AND (X_ATTRIBUTE9 is null)))
493 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
494 OR ((tlinfo.ATTRIBUTE10 is null)
495 AND (X_ATTRIBUTE10 is null)))
496 AND ((tlinfo.LIMIT_TARGET_PROJECTS_CODE = X_LIMIT_TARGET_PROJECTS_CODE)
497 OR ((tlinfo.LIMIT_TARGET_PROJECTS_CODE is null)
498 AND (X_LIMIT_TARGET_PROJECTS_CODE is null)))
499
500 /* FP.M : Allocation Impact : 3512552 */
501 AND ((tlinfo.ALLOC_RESOURCE_STRUCT_TYPE = X_ALLOC_RESOURCE_STRUCT_TYPE)
502 OR ((tlinfo.ALLOC_RESOURCE_STRUCT_TYPE is null)
503 AND (X_ALLOC_RESOURCE_STRUCT_TYPE is null)))
504 AND ((tlinfo.BASIS_RESOURCE_STRUCT_TYPE = X_BASIS_RESOURCE_STRUCT_TYPE)
505 OR ((tlinfo.BASIS_RESOURCE_STRUCT_TYPE is null)
506 AND (X_BASIS_RESOURCE_STRUCT_TYPE is null)))
507 AND ((tlinfo.ALLOC_RBS_VERSION = X_ALLOC_RBS_VERSION)
508 OR ((tlinfo.ALLOC_RBS_VERSION is null)
509 AND (X_ALLOC_RBS_VERSION is null)))
510 AND ((tlinfo.BASIS_RBS_VERSION = X_BASIS_RBS_VERSION)
511 OR ((tlinfo.BASIS_RBS_VERSION is null)
512 AND (X_BASIS_RBS_VERSION is null)))
513
514
515 ) then
516 null;
517 else
518 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
519 app_exception.raise_exception;
520 end if;
521 return;
522 end LOCK_ROW;
523
524 procedure UPDATE_ROW (
525 X_RULE_ID in NUMBER,
526 X_RULE_NAME in VARCHAR2,
527 X_DESCRIPTION in VARCHAR2,
528 X_POOL_PERCENT in NUMBER,
529 X_PERIOD_TYPE in VARCHAR2,
530 X_SOURCE_AMOUNT_TYPE in VARCHAR2,
531 X_SOURCE_BALANCE_CATEGORY in VARCHAR2,
532 X_SOURCE_BALANCE_TYPE in VARCHAR2,
533 X_ALLOC_RESOURCE_LIST_ID in NUMBER,
534 X_AUTO_RELEASE_FLAG in VARCHAR2,
535 X_ALLOCATION_METHOD in VARCHAR2,
536 X_IMP_WITH_EXCEPTION in VARCHAR2,
537 X_DUP_TARGETS_FLAG in VARCHAR2,
538 X_TARGET_EXP_TYPE_CLASS in VARCHAR2,
539 X_TARGET_EXP_ORG_ID in NUMBER,
540 X_TARGET_EXP_TYPE in VARCHAR2,
541 X_TARGET_COST_TYPE in VARCHAR2,
542 X_OFFSET_EXP_TYPE_CLASS in VARCHAR2,
543 X_OFFSET_EXP_ORG_ID in NUMBER,
544 X_OFFSET_EXP_TYPE in VARCHAR2,
545 X_OFFSET_COST_TYPE in VARCHAR2,
546 X_OFFSET_METHOD in VARCHAR2,
547 X_OFFSET_PROJECT_ID in NUMBER,
548 X_OFFSET_TASK_ID in NUMBER,
549 X_BASIS_METHOD in VARCHAR2,
550 X_BASIS_RELATIVE_PERIOD in NUMBER,
551 X_BASIS_AMOUNT_TYPE in VARCHAR2,
552 X_BASIS_BALANCE_CATEGORY in VARCHAR2,
553 X_BASIS_BUDGET_TYPE_CODE in VARCHAR2,
554 X_BAS_BUDGET_ENTRY_METHOD_CODE in VARCHAR2,
555 X_BASIS_BALANCE_TYPE in VARCHAR2,
556 X_BASIS_RESOURCE_LIST_ID in NUMBER,
557 X_SOURCE_EXTN_FLAG in VARCHAR2,
558 X_TARGET_EXTN_FLAG in VARCHAR2,
559 X_FIXED_AMOUNT in NUMBER,
560 X_START_DATE_ACTIVE in DATE,
561 X_END_DATE_ACTIVE in DATE,
562 X_ATTRIBUTE_CATEGORY in VARCHAR2,
563 X_ATTRIBUTE1 in VARCHAR2,
564 X_ATTRIBUTE2 in VARCHAR2,
565 X_ATTRIBUTE3 in VARCHAR2,
566 X_ATTRIBUTE4 in VARCHAR2,
567 X_ATTRIBUTE5 in VARCHAR2,
568 X_ATTRIBUTE6 in VARCHAR2,
569 X_ATTRIBUTE7 in VARCHAR2,
570 X_ATTRIBUTE8 in VARCHAR2,
571 X_ATTRIBUTE9 in VARCHAR2,
572 X_ATTRIBUTE10 in VARCHAR2,
573 X_LAST_UPDATE_DATE in DATE,
574 X_LAST_UPDATED_BY in NUMBER,
575 X_LAST_UPDATE_LOGIN in NUMBER,
576 X_LIMIT_TARGET_PROJECTS_CODE varchar2,
577 X_BASIS_FIN_PLAN_TYPE_ID in NUMBER /* Bug 2619977 */ ,
578
579 /* FP.M : Allocation Impact : 3512552 */
580 X_ALLOC_RESOURCE_STRUCT_TYPE In Varchar2 ,
581 X_BASIS_RESOURCE_STRUCT_TYPE In Varchar2 ,
582 X_ALLOC_RBS_VERSION In Number ,
583 X_BASIS_RBS_VERSION In Number
584
585
586 ) is
587 begin
588
589 update PA_ALLOC_RULES_ALL set
590 RULE_NAME = X_RULE_NAME,
591 DESCRIPTION = X_DESCRIPTION,
592 POOL_PERCENT = X_POOL_PERCENT,
593 PERIOD_TYPE = X_PERIOD_TYPE,
594 SOURCE_AMOUNT_TYPE = X_SOURCE_AMOUNT_TYPE,
595 SOURCE_BALANCE_CATEGORY = X_SOURCE_BALANCE_CATEGORY,
596 SOURCE_BALANCE_TYPE = X_SOURCE_BALANCE_TYPE,
597 ALLOC_RESOURCE_LIST_ID = X_ALLOC_RESOURCE_LIST_ID,
598 AUTO_RELEASE_FLAG = X_AUTO_RELEASE_FLAG,
599 ALLOCATION_METHOD = X_ALLOCATION_METHOD,
600 IMP_WITH_EXCEPTION = X_IMP_WITH_EXCEPTION,
601 DUP_TARGETS_FLAG = X_DUP_TARGETS_FLAG,
602 TARGET_EXP_TYPE_CLASS = X_TARGET_EXP_TYPE_CLASS,
603 TARGET_EXP_ORG_ID = X_TARGET_EXP_ORG_ID,
604 TARGET_EXP_TYPE = X_TARGET_EXP_TYPE,
605 TARGET_COST_TYPE = X_TARGET_COST_TYPE,
606 OFFSET_EXP_TYPE_CLASS = X_OFFSET_EXP_TYPE_CLASS,
607 OFFSET_EXP_ORG_ID = X_OFFSET_EXP_ORG_ID,
608 OFFSET_EXP_TYPE = X_OFFSET_EXP_TYPE,
609 OFFSET_COST_TYPE = X_OFFSET_COST_TYPE,
610 OFFSET_METHOD = X_OFFSET_METHOD,
611 OFFSET_PROJECT_ID = X_OFFSET_PROJECT_ID,
612 OFFSET_TASK_ID = X_OFFSET_TASK_ID,
613 BASIS_METHOD = X_BASIS_METHOD,
614 BASIS_RELATIVE_PERIOD = X_BASIS_RELATIVE_PERIOD,
615 BASIS_AMOUNT_TYPE = X_BASIS_AMOUNT_TYPE,
616 BASIS_BALANCE_CATEGORY = X_BASIS_BALANCE_CATEGORY,
617 BASIS_BUDGET_TYPE_CODE = X_BASIS_BUDGET_TYPE_CODE,
618 BASIS_FIN_PLAN_TYPE_ID = X_BASIS_FIN_PLAN_TYPE_ID, /* Bug 2619977 */
619 BASIS_BUDGET_ENTRY_METHOD_CODE = X_BAS_BUDGET_ENTRY_METHOD_CODE,
620 BASIS_BALANCE_TYPE = X_BASIS_BALANCE_TYPE,
621 BASIS_RESOURCE_LIST_ID = X_BASIS_RESOURCE_LIST_ID,
622 SOURCE_EXTN_FLAG = X_SOURCE_EXTN_FLAG,
623 TARGET_EXTN_FLAG = X_TARGET_EXTN_FLAG,
624 FIXED_AMOUNT = X_FIXED_AMOUNT,
625 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
626 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
627 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
628 ATTRIBUTE1 = X_ATTRIBUTE1,
629 ATTRIBUTE2 = X_ATTRIBUTE2,
630 ATTRIBUTE3 = X_ATTRIBUTE3,
631 ATTRIBUTE4 = X_ATTRIBUTE4,
632 ATTRIBUTE5 = X_ATTRIBUTE5,
633 ATTRIBUTE6 = X_ATTRIBUTE6,
634 ATTRIBUTE7 = X_ATTRIBUTE7,
635 ATTRIBUTE8 = X_ATTRIBUTE8,
636 ATTRIBUTE9 = X_ATTRIBUTE9,
637 ATTRIBUTE10 = X_ATTRIBUTE10,
638 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
639 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
640 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
641 LIMIT_TARGET_PROJECTS_CODE=X_LIMIT_TARGET_PROJECTS_CODE ,
642
646 BASIS_RESOURCE_STRUCT_TYPE = X_BASIS_RESOURCE_STRUCT_TYPE ,
643 /* FP.M : Allocation Impact : 3512552 */
644
645 ALLOC_RESOURCE_STRUCT_TYPE = X_ALLOC_RESOURCE_STRUCT_TYPE ,
647 ALLOC_RBS_VERSION = X_ALLOC_RBS_VERSION ,
648 BASIS_RBS_VERSION = X_BASIS_RBS_VERSION
649
650 where RULE_ID = X_RULE_ID;
651
652 if (sql%notfound) then
653 raise no_data_found;
654 end if;
655
656 end UPDATE_ROW;
657
658 procedure DELETE_ROW (
659 X_RULE_ID in NUMBER
660 ) is
661 begin
662 delete from PA_ALLOC_RULES_ALL
663 where RULE_ID = X_RULE_ID;
664 if (sql%notfound) then
665 raise no_data_found;
666 end if;
667
668 /* Bug 2573742 Begins */
669
670 delete from PA_ALLOC_SOURCE_LINES
671 where RULE_ID = X_RULE_ID;
672
673 delete from PA_ALLOC_TARGET_LINES
674 where RULE_ID = X_RULE_ID;
675
676 delete from PA_ALLOC_GL_LINES
677 where RULE_ID = X_RULE_ID;
678
679 delete from PA_ALLOC_RESOURCES
680 where RULE_ID = X_RULE_ID;
681
682 /* Bug 2573742 Ends */
683
684 end DELETE_ROW;
685
686 end PA_ALLOC_RULES_ALL_PKG;
[Home] [Help]