[Home] [Help]
PACKAGE BODY: APPS.AR_CONS_BILL_CYCLE_PKG
Source
1 package body AR_CONS_BILL_CYCLE_PKG as
2 /* $Header: ARCBILCB.pls 120.3 2006/05/23 16:18:52 jypandey noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_BILLING_CYCLE_ID IN NUMBER,
6 X_BILL_CYCLE_TYPE IN VARCHAR2,
7 X_CYCLE_NAME IN VARCHAR2,
8 X_DESCRIPTION IN VARCHAR2,
9 X_CYCLE_FREQUENCY IN VARCHAR2,
10 X_START_DATE IN DATE,
11 X_LAST_DAY IN VARCHAR2,
12 X_DAY_1 IN VARCHAR2,
13 X_DAY_2 IN VARCHAR2,
14 X_DAY_3 IN VARCHAR2,
15 X_DAY_4 IN VARCHAR2,
16 X_DAY_5 IN VARCHAR2,
17 X_DAY_6 IN VARCHAR2,
18 X_DAY_7 IN VARCHAR2,
19 X_DAY_8 IN VARCHAR2,
20 X_DAY_9 IN VARCHAR2,
21 X_DAY_10 IN VARCHAR2,
22 X_DAY_11 IN VARCHAR2,
23 X_DAY_12 IN VARCHAR2,
24 X_DAY_13 IN VARCHAR2,
25 X_DAY_14 IN VARCHAR2,
26 X_DAY_15 IN VARCHAR2,
27 X_DAY_16 IN VARCHAR2,
28 X_DAY_17 IN VARCHAR2,
29 X_DAY_18 IN VARCHAR2,
30 X_DAY_19 IN VARCHAR2,
31 X_DAY_20 IN VARCHAR2,
32 X_DAY_21 IN VARCHAR2,
33 X_DAY_22 IN VARCHAR2,
34 X_DAY_23 IN VARCHAR2,
35 X_DAY_24 IN VARCHAR2,
36 X_DAY_25 IN VARCHAR2,
37 X_DAY_26 IN VARCHAR2,
38 X_DAY_27 IN VARCHAR2,
39 X_DAY_28 IN VARCHAR2,
40 X_DAY_29 IN VARCHAR2,
41 X_DAY_30 IN VARCHAR2,
42 X_DAY_31 IN VARCHAR2,
43 X_DAY_MONDAY IN VARCHAR2,
44 X_DAY_TUESDAY IN VARCHAR2,
45 X_DAY_WEDNESDAY IN VARCHAR2,
46 X_DAY_THURSDAY IN VARCHAR2,
47 X_DAY_FRIDAY IN VARCHAR2,
48 X_DAY_SATURDAY IN VARCHAR2,
49 X_DAY_SUNDAY IN VARCHAR2,
50 X_SKIP_WEEKENDS IN VARCHAR2,
51 X_SKIP_HOLIDAYS IN VARCHAR2,
52 X_REPEAT_DAILY IN NUMBER,
53 X_REPEAT_WEEKLY IN NUMBER,
54 X_REPEAT_MONTHLY IN NUMBER,
55 X_DAY_TYPE IN VARCHAR2,
56 X_CREATED_BY IN NUMBER,
57 X_CREATION_DATE IN DATE,
58 X_LAST_UPDATE_LOGIN IN NUMBER,
59 X_LAST_UPDATE_DATE IN DATE,
60 X_LAST_UPDATED_BY IN NUMBER,
61 X_OBJECT_VERSION_NUMBER IN NUMBER
62 ) IS
63 cursor C is select ROWID from AR_CONS_BILL_CYCLES_B
64 where BILLING_CYCLE_ID = X_BILLING_CYCLE_ID;
65
66 Begin
67
68 INSERT INTO AR_CONS_BILL_CYCLES_B(
69 BILLING_CYCLE_ID,
70 BILL_CYCLE_TYPE,
71 CYCLE_FREQUENCY,
72 START_DATE,
73 LAST_DAY,
74 DAY_1,
75 DAY_2,
76 DAY_3,
77 DAY_4,
78 DAY_5,
79 DAY_6,
80 DAY_7,
81 DAY_8,
82 DAY_9,
83 DAY_10,
84 DAY_11,
85 DAY_12,
86 DAY_13,
87 DAY_14,
88 DAY_15,
89 DAY_16,
90 DAY_17,
91 DAY_18,
92 DAY_19,
93 DAY_20,
94 DAY_21,
95 DAY_22,
96 DAY_23,
97 DAY_24,
98 DAY_25,
99 DAY_26,
100 DAY_27,
101 DAY_28,
102 DAY_29,
103 DAY_30,
104 DAY_31,
105 DAY_MONDAY,
106 DAY_TUESDAY,
107 DAY_WEDNESDAY,
108 DAY_THURSDAY,
109 DAY_FRIDAY,
110 DAY_SATURDAY ,
111 DAY_SUNDAY ,
112 SKIP_WEEKENDS,
113 SKIP_HOLIDAYS,
114 REPEAT_DAILY,
115 REPEAT_WEEKLY,
116 REPEAT_MONTHLY,
117 DAY_TYPE ,
118 CREATED_BY,
119 CREATION_DATE,
120 LAST_UPDATE_LOGIN,
121 LAST_UPDATE_DATE,
122 LAST_UPDATED_BY,
123 OBJECT_VERSION_NUMBER
124 ) VALUES(
125 X_BILLING_CYCLE_ID,
126 X_BILL_CYCLE_TYPE,
127 X_CYCLE_FREQUENCY,
128 X_START_DATE,
129 X_LAST_DAY,
130 X_DAY_1,
131 X_DAY_2,
132 X_DAY_3,
133 X_DAY_4,
134 X_DAY_5,
135 X_DAY_6,
136 X_DAY_7,
137 X_DAY_8,
138 X_DAY_9,
139 X_DAY_10,
140 X_DAY_11,
141 X_DAY_12,
142 X_DAY_13,
143 X_DAY_14,
144 X_DAY_15,
145 X_DAY_16,
146 X_DAY_17,
147 X_DAY_18,
148 X_DAY_19,
149 X_DAY_20,
150 X_DAY_21,
151 X_DAY_22,
152 X_DAY_23,
153 X_DAY_24,
154 X_DAY_25,
155 X_DAY_26,
156 X_DAY_27,
157 X_DAY_28,
158 X_DAY_29,
159 X_DAY_30,
160 X_DAY_31,
161 X_DAY_MONDAY,
162 X_DAY_TUESDAY,
163 X_DAY_WEDNESDAY,
164 X_DAY_THURSDAY,
165 X_DAY_FRIDAY,
166 X_DAY_SATURDAY,
167 X_DAY_SUNDAY,
168 X_SKIP_WEEKENDS,
169 X_SKIP_HOLIDAYS,
170 X_REPEAT_DAILY,
171 X_REPEAT_WEEKLY,
172 X_REPEAT_MONTHLY,
173 X_DAY_TYPE,
174 X_CREATED_BY,
175 X_CREATION_DATE,
176 X_LAST_UPDATE_LOGIN,
177 X_LAST_UPDATE_DATE,
178 X_LAST_UPDATED_BY,
179 X_OBJECT_VERSION_NUMBER
180 );
181
182 insert into AR_CONS_BILL_CYCLES_TL (
183 BILLING_CYCLE_ID,
184 DESCRIPTION,
185 CYCLE_NAME,
186 LAST_UPDATE_DATE,
187 CREATION_DATE,
188 CREATED_BY,
189 LAST_UPDATED_BY,
190 LAST_UPDATE_LOGIN,
191 LANGUAGE,
192 SOURCE_LANG
193 ) select
194 X_BILLING_CYCLE_ID,
195 X_DESCRIPTION,
196 X_CYCLE_NAME,
197 X_LAST_UPDATE_DATE,
198 X_CREATION_DATE,
199 X_CREATED_BY,
200 X_LAST_UPDATED_BY,
201 X_LAST_UPDATE_LOGIN,
202 L.LANGUAGE_CODE,
203 userenv('LANG')
204 from FND_LANGUAGES L
205 where L.INSTALLED_FLAG in ('I', 'B')
206 and not exists
207 (select NULL
208 from AR_CONS_BILL_CYCLES_TL T
209 where T.BILLING_CYCLE_ID = X_BILLING_CYCLE_ID
210 and T.LANGUAGE = L.LANGUAGE_CODE);
211
212 OPEN C;
213 FETCH C INTO X_ROWId;
214 IF (C%NOTFOUND) THEN
215 CLOSE C;
216 Raise NO_DATA_FOUND;
217 END IF;
218 CLOSE C;
219
220 END Insert_Row;
221
222 procedure UPDATE_ROW (
223 X_BILLING_CYCLE_ID IN NUMBER,
224 X_BILL_CYCLE_TYPE IN VARCHAR2,
225 X_CYCLE_NAME IN VARCHAR2,
226 X_DESCRIPTION IN VARCHAR2,
227 X_CYCLE_FREQUENCY IN VARCHAR2,
228 X_START_DATE IN DATE,
229 X_LAST_DAY IN VARCHAR2,
230 X_DAY_1 IN VARCHAR2,
231 X_DAY_2 IN VARCHAR2,
232 X_DAY_3 IN VARCHAR2,
233 X_DAY_4 IN VARCHAR2,
234 X_DAY_5 IN VARCHAR2,
235 X_DAY_6 IN VARCHAR2,
236 X_DAY_7 IN VARCHAR2,
237 X_DAY_8 IN VARCHAR2,
238 X_DAY_9 IN VARCHAR2,
239 X_DAY_10 IN VARCHAR2,
240 X_DAY_11 IN VARCHAR2,
241 X_DAY_12 IN VARCHAR2,
242 X_DAY_13 IN VARCHAR2,
243 X_DAY_14 IN VARCHAR2,
244 X_DAY_15 IN VARCHAR2,
245 X_DAY_16 IN VARCHAR2,
246 X_DAY_17 IN VARCHAR2,
247 X_DAY_18 IN VARCHAR2,
248 X_DAY_19 IN VARCHAR2,
249 X_DAY_20 IN VARCHAR2,
250 X_DAY_21 IN VARCHAR2,
251 X_DAY_22 IN VARCHAR2,
252 X_DAY_23 IN VARCHAR2,
253 X_DAY_24 IN VARCHAR2,
254 X_DAY_25 IN VARCHAR2,
255 X_DAY_26 IN VARCHAR2,
256 X_DAY_27 IN VARCHAR2,
257 X_DAY_28 IN VARCHAR2,
258 X_DAY_29 IN VARCHAR2,
259 X_DAY_30 IN VARCHAR2,
260 X_DAY_31 IN VARCHAR2,
261 X_DAY_MONDAY IN VARCHAR2,
262 X_DAY_TUESDAY IN VARCHAR2,
263 X_DAY_WEDNESDAY IN VARCHAR2,
264 X_DAY_THURSDAY IN VARCHAR2,
265 X_DAY_FRIDAY IN VARCHAR2,
266 X_DAY_SATURDAY IN VARCHAR2,
267 X_DAY_SUNDAY IN VARCHAR2,
268 X_SKIP_WEEKENDS IN VARCHAR2,
269 X_SKIP_HOLIDAYS IN VARCHAR2,
270 X_REPEAT_DAILY IN NUMBER,
271 X_REPEAT_WEEKLY IN NUMBER,
272 X_REPEAT_MONTHLY IN NUMBER,
273 X_DAY_TYPE IN VARCHAR2,
274 X_LAST_UPDATE_LOGIN IN NUMBER,
275 X_LAST_UPDATE_DATE IN DATE,
276 X_LAST_UPDATED_BY IN NUMBER,
277 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
278 ) IS
279 Begin
280
281 UPDATE AR_CONS_BILL_CYCLES_B SET
282 cycle_frequency = X_cycle_frequency,
283 BILL_CYCLE_TYPE = X_BILL_CYCLE_TYPE,
284 START_DATE = X_START_DATE,
285 --CYCLE_FREQUENCY = X_cycle_frequency,
286 LAST_DAY = X_LAST_DAY,
287 DAY_1 = X_day_1,
288 DAY_2 = X_day_2,
289 DAY_3 = X_day_3,
290 DAY_4 = X_day_4,
291 DAY_5 = X_day_5,
292 DAY_6 = X_day_6,
293 DAY_7 = X_day_7,
294 DAY_8 = X_day_8,
295 DAY_9 = X_day_9,
296 DAY_10 = X_day_10,
297 DAY_11 = X_day_11,
298 DAY_12 = X_day_12,
299 DAY_13 = X_day_13,
300 DAY_14 = X_day_14,
301 DAY_15 = X_day_15,
302 DAY_16 = X_day_16,
303 DAY_17 = X_day_17,
304 DAY_18 = X_day_18,
305 DAY_19 = X_day_19,
306 DAY_20 = X_day_20,
307 DAY_21 = X_day_21,
308 DAY_22 = X_day_22,
309 DAY_23 = X_day_23,
310 DAY_24 = X_day_24,
311 DAY_25 = X_day_25,
312 DAY_26 = X_day_26,
313 DAY_27 = X_day_27,
314 DAY_28 = X_day_28,
315 DAY_29 = X_day_29,
316 DAY_30 = X_day_30,
317 DAY_31 = X_day_31,
318 DAY_MONDAY = X_day_monday,
319 DAY_TUESDAY = X_day_tuesday,
320 DAY_WEDNESDAY = X_day_wednesday,
321 DAY_THURSDAY = X_day_thursday,
322 DAY_FRIDAY = X_day_friday,
323 DAY_SATURDAY= X_DAY_SATURDAY ,
324 DAY_SUNDAY = X_DAY_SUNDAY ,
325 SKIP_WEEKENDS = X_SKIP_WEEKENDS,
326 SKIP_HOLIDAYS = X_SKIP_HOLIDAYS,
327 REPEAT_DAILY = X_REPEAT_DAILY,
328 REPEAT_WEEKLY = X_REPEAT_WEEKLY,
329 REPEAT_MONTHLY = X_REPEAT_MONTHLY,
330 DAY_TYPE = X_DAY_TYPE,
331 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
332 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
333 LAST_UPDATED_BY = X_last_updated_by,
334 OBJECT_VERSION_NUMBER = DECODE(X_object_version_number,0,OBJECT_VERSION_NUMBER+1,X_object_version_number)
335 WHERE BILLING_CYCLE_Id = X_billing_cycle_id;
336
337 IF sql%notfound THEN
338 X_object_version_number:= X_object_version_number-1;
339 Raise NO_DATA_FOUND;
340 END IF;
341
342 UPDATE AR_CONS_BILL_CYCLES_TL SET
343 DESCRIPTION = X_DESCRIPTION,
344 CYCLE_NAME = X_CYCLE_NAME,
345 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
346 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
347 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
348 SOURCE_LANG = userenv('LANG')
349 where BILLING_CYCLE_ID = X_billing_cycle_id
350 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
351 IF sql%notfound THEN
352 Raise NO_DATA_FOUND;
353 END IF;
354
355 END Update_Row;
356
357 procedure DELETE_ROW (
358 X_BILLING_CYCLE_ID IN NUMBER
359 ) IS
360 BEGIN
361 delete from AR_CONS_BILL_CYCLES_TL
362 where BILLING_CYCLE_ID = X_billing_cycle_id;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367
368 delete from AR_CONS_BILL_CYCLES_B
369 where BILLING_CYCLE_ID = X_billing_cycle_id;
370
371 if (sql%notfound) then
372 raise no_data_found;
373 end if;
374
375 END Delete_Row;
376
377
378 procedure LOCK_ROW (
379 X_BILLING_CYCLE_ID IN NUMBER,
380 X_CYCLE_NAME IN VARCHAR2,
381 X_DESCRIPTION IN VARCHAR2,
382 X_CYCLE_FREQUENCY IN VARCHAR2,
383 X_START_DATE IN DATE,
384 X_LAST_DAY IN VARCHAR2,
385 X_DAY_1 IN VARCHAR2,
386 X_DAY_2 IN VARCHAR2,
387 X_DAY_3 IN VARCHAR2,
388 X_DAY_4 IN VARCHAR2,
389 X_DAY_5 IN VARCHAR2,
390 X_DAY_6 IN VARCHAR2,
391 X_DAY_7 IN VARCHAR2,
392 X_DAY_8 IN VARCHAR2,
393 X_DAY_9 IN VARCHAR2,
394 X_DAY_10 IN VARCHAR2,
395 X_DAY_11 IN VARCHAR2,
396 X_DAY_12 IN VARCHAR2,
397 X_DAY_13 IN VARCHAR2,
398 X_DAY_14 IN VARCHAR2,
399 X_DAY_15 IN VARCHAR2,
400 X_DAY_16 IN VARCHAR2,
401 X_DAY_17 IN VARCHAR2,
402 X_DAY_18 IN VARCHAR2,
403 X_DAY_19 IN VARCHAR2,
404 X_DAY_20 IN VARCHAR2,
405 X_DAY_21 IN VARCHAR2,
406 X_DAY_22 IN VARCHAR2,
407 X_DAY_23 IN VARCHAR2,
408 X_DAY_24 IN VARCHAR2,
409 X_DAY_25 IN VARCHAR2,
410 X_DAY_26 IN VARCHAR2,
411 X_DAY_27 IN VARCHAR2,
412 X_DAY_28 IN VARCHAR2,
413 X_DAY_29 IN VARCHAR2,
414 X_DAY_30 IN VARCHAR2,
415 X_DAY_31 IN VARCHAR2,
416 X_DAY_MONDAY IN VARCHAR2,
417 X_DAY_TUESDAY IN VARCHAR2,
418 X_DAY_WEDNESDAY IN VARCHAR2,
419 X_DAY_THURSDAY IN VARCHAR2,
420 X_DAY_FRIDAY IN VARCHAR2,
421 X_DAY_SATURDAY IN VARCHAR2 ,
422 X_DAY_SUNDAY IN VARCHAR2,
423 X_SKIP_WEEKENDS IN VARCHAR2,
424 X_SKIP_HOLIDAYS IN VARCHAR2,
425 X_REPEAT_DAILY IN NUMBER,
426 X_REPEAT_WEEKLY IN NUMBER,
427 X_REPEAT_MONTHLY IN NUMBER,
428 X_DAY_TYPE IN VARCHAR2
429 ) IS
430 CURSOR c is select
431 BILLING_CYCLE_ID, cycle_frequency, START_DATE, LAST_DAY, DAY_1, DAY_2, DAY_3, DAY_4, DAY_5, DAY_6, DAY_7,
432 DAY_8, DAY_9, DAY_10, DAY_11, DAY_12, DAY_13, DAY_14, DAY_15, DAY_16, DAY_17, DAY_18, DAY_19, DAY_20,
436 REPEAT_MONTHLY, DAY_TYPE
433 DAY_21, DAY_22, DAY_23, DAY_24, DAY_25, DAY_26, DAY_27, DAY_28, DAY_29, DAY_30, DAY_31, DAY_MONDAY, DAY_TUESDAY,
434 DAY_WEDNESDAY, DAY_THURSDAY, DAY_FRIDAY, DAY_SATURDAY, DAY_SUNDAY,
435 SKIP_WEEKENDS, SKIP_HOLIDAYS, REPEAT_DAILY, REPEAT_WEEKLY,
437 FROM AR_CONS_BILL_CYCLES_B
438 WHERE BILLING_CYCLE_ID = X_billing_cycle_id
439 FOR update of BILLING_CYCLE_ID nowait;
440
441
442 Recinfo c%rowtype;
443 CURSOR c1 is select
444 DESCRIPTION, CYCLE_NAME,
445 decode(LANGUAGE,userenv('LANG'),'Y','N') BASELANG
446 FROM AR_CONS_BILL_CYCLES_TL
447 WHERE BILLING_CYCLE_ID = X_billing_cycle_id
448 FOR UPDATE Of BILLING_CYCLE_ID nowait;
449
450 BEGIN
451 open c;
452 fetch c into recinfo;
453 if (c%notfound) then
454 close c;
455 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
456 app_exception.raise_exception;
457 end if;
458 close c;
459
460 IF( ((recinfo.cycle_frequency = X_cycle_frequency)
461 OR ((recinfo.cycle_frequency is NULL) AND (X_cycle_frequency is null)))
462 AND ((recinfo.START_DATE = X_START_DATE)
463 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
464 AND ((recinfo.LAST_DAY = X_LAST_DAY)
465 OR ((recinfo.LAST_DAY is null ) AND (X_LAST_DAY is null)))
466 AND ((recinfo.DAY_1 = X_day_1)
467 OR ((recinfo.DAY_1 is null) AND ( X_day_1 is null)))
468 AND ((recinfo.DAY_2 = X_day_2)
469 OR ((recinfo.DAY_2 is null) AND ( X_day_2 is null)))
470 AND ((recinfo.DAY_3 = X_day_3)
471 OR ((recinfo.DAY_3 is null) AND ( X_day_3 is null)))
472 AND ((recinfo.DAY_4 = X_day_4)
473 OR ((recinfo.DAY_4 is null) AND ( X_day_4 is null)))
474 AND ((recinfo.DAY_5 = X_day_5)
475 OR ((recinfo.DAY_5 is null) AND ( X_day_5 is null)))
476
477 AND ((recinfo.DAY_6 = X_day_6)
478 OR ((recinfo.DAY_6 is null) AND ( X_day_6 is null)))
479 AND ((recinfo.DAY_7 = X_day_7)
480 OR ((recinfo.DAY_7 is null) AND ( X_day_7 is null)))
481 AND ((recinfo.DAY_8 = X_day_8)
482 OR ((recinfo.DAY_8 is null) AND ( X_day_8 is null)))
483 AND ((recinfo.DAY_9 = X_day_9)
484 OR ((recinfo.DAY_9 is null) AND ( X_day_9 is null)))
485 AND ((recinfo.DAY_10 = X_day_10)
486 OR ((recinfo.DAY_10 is null) AND ( X_day_10 is null)))
487
488 AND ((recinfo.DAY_11 = X_day_11)
489 OR ((recinfo.DAY_11 is null) AND ( X_day_11 is null)))
490 AND ((recinfo.DAY_12 = X_day_12)
491 OR ((recinfo.DAY_12 is null) AND ( X_day_12 is null)))
492 AND ((recinfo.DAY_13 = X_day_13)
493 OR ((recinfo.DAY_13 is null) AND ( X_day_13 is null)))
494 AND ((recinfo.DAY_14 = X_day_14)
495 OR ((recinfo.DAY_14 is null) AND ( X_day_14 is null)))
496 AND ((recinfo.DAY_15 = X_day_15)
497 OR ((recinfo.DAY_15 is null) AND ( X_day_15 is null)))
498
499 AND ((recinfo.DAY_16 = X_day_16)
500 OR ((recinfo.DAY_16 is null) AND ( X_day_16 is null)))
501 AND ((recinfo.DAY_17 = X_day_17)
502 OR ((recinfo.DAY_17 is null) AND ( X_day_17 is null)))
503 AND ((recinfo.DAY_18 = X_day_18)
504 OR ((recinfo.DAY_18 is null) AND ( X_day_18 is null)))
505 AND ((recinfo.DAY_19 = X_day_19)
506 OR ((recinfo.DAY_19 is null) AND ( X_day_19 is null)))
507 AND ((recinfo.DAY_20 = X_day_20)
508 OR ((recinfo.DAY_20 is null) AND ( X_day_20 is null)))
509
510 AND ((recinfo.DAY_21 = X_day_21)
511 OR ((recinfo.DAY_21 is null) AND ( X_day_21 is null)))
512 AND ((recinfo.DAY_22 = X_day_22)
513 OR ((recinfo.DAY_22 is null) AND ( X_day_22 is null)))
514 AND ((recinfo.DAY_23 = X_day_23)
515 OR ((recinfo.DAY_23 is null) AND ( X_day_23 is null)))
516 AND ((recinfo.DAY_24 = X_day_24)
517 OR ((recinfo.DAY_24 is null) AND ( X_day_24 is null)))
518 AND ((recinfo.DAY_25 = X_day_25)
519 OR ((recinfo.DAY_25 is null) AND ( X_day_25 is null)))
520
521 AND ((recinfo.DAY_26 = X_day_26)
522 OR ((recinfo.DAY_26 is null) AND ( X_day_26 is null)))
523 AND ((recinfo.DAY_27 = X_day_27)
524 OR ((recinfo.DAY_27 is null) AND ( X_day_27 is null)))
525 AND ((recinfo.DAY_28 = X_day_28)
526 OR ((recinfo.DAY_28 is null) AND ( X_day_28 is null)))
527 AND ((recinfo.DAY_29 = X_day_29)
528 OR ((recinfo.DAY_29 is null) AND ( X_day_29 is null)))
529 AND ((recinfo.DAY_30 = X_day_30)
530 OR ((recinfo.DAY_30 is null) AND ( X_day_30 is null)))
531
532 AND ((recinfo.DAY_31 = X_day_31)
533 OR ((recinfo.DAY_31 is null) AND ( X_day_31 is null)))
534
535 AND ((recinfo.DAY_MONDAY = X_day_monday)
536 OR ((recinfo.DAY_MONDAY is null) AND ( X_day_monday is null)))
537 AND ((recinfo.DAY_TUESDAY = X_day_tuesday)
538 OR ((recinfo.DAY_TUESDAY is null) AND ( X_day_tuesday is null)))
539 AND ((recinfo.DAY_WEDNESDAY =X_day_wednesday)
540 OR ((recinfo.DAY_WEDNESDAY is null) AND ( X_day_wednesday is null)))
541 AND ((recinfo.DAY_thursday =X_day_thursday)
542 OR ((recinfo.DAY_thursday is null) AND ( X_day_thursday is null)))
543 AND ((recinfo.DAY_FRIDAY =X_day_friday)
544 OR ((recinfo.DAY_friday is null) AND ( X_day_friday is null)))
545 AND ((recinfo.DAY_SATURDAY =X_day_saturday)
546 OR ((recinfo.DAY_saturday is null) AND ( X_day_saturday is null)))
547 AND ((recinfo.DAY_SUNDAY =X_day_sunday)
548 OR ((recinfo.DAY_sunday is null) AND ( X_day_sunday is null)))
549 AND ((recinfo.SKIP_WEEKENDS =X_SKIP_WEEKENDS)
550 OR ((recinfo.SKIP_WEEKENDS is null) AND ( X_SKIP_WEEKENDS is null)))
551 AND ((recinfo.SKIP_HOLIDAYS =X_SKIP_HOLIDAYS)
552 OR ((recinfo.SKIP_HOLIDAYS is null) AND ( X_SKIP_HOLIDAYS is null)))
553
554 AND ((recinfo.REPEAT_DAILY =X_REPEAT_DAILY)
555 OR ((recinfo.REPEAT_DAILY is null) AND ( X_REPEAT_DAILY is null)))
559 OR ((recinfo.REPEAT_MONTHLY is null) AND ( X_REPEAT_MONTHLY is null)))
556 AND ((recinfo.REPEAT_WEEKLY =X_REPEAT_WEEKLY)
557 OR ((recinfo.REPEAT_WEEKLY is null) AND ( X_REPEAT_WEEKLY is null)))
558 AND ((recinfo.REPEAT_MONTHLY =X_REPEAT_MONTHLY)
560 AND ((recinfo.DAY_TYPE =X_DAY_TYPE)
561 OR ((recinfo.DAY_TYPE is null) AND ( X_DAY_TYPE is null)))
562
563 )
564 THEN
565 null;
566 ELSE
567 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
568 app_exception.raise_exception;
569 END IF;
570
571 for tlinfo in c1 loop
572 if (tlinfo.BASELANG = 'Y') then
573 if ( (tlinfo.CYCLE_NAME = X_cycle_name)
574 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
575 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
576 ) then
577 null;
578 else
579 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
580 app_exception.raise_exception;
581 end if;
582 end if;
583 end loop;
584 END Lock_Row;
585
586 procedure ADD_LANGUAGE
587 is
588 begin
589 delete from AR_CONS_BILL_CYCLES_TL T
590 where not exists
591 (select NULL
592 from AR_CONS_BILL_CYCLES_B B
593 where B.BILLING_CYCLE_ID = T.BILLING_CYCLE_ID
594 );
595
596 update AR_CONS_BILL_CYCLES_TL T set (
597 CYCLE_NAME,DESCRIPTION
598 ) = (select
599 B.CYCLE_NAME,B.DESCRIPTION
600 from AR_CONS_BILL_CYCLES_TL B
601 where B.BILLING_CYCLE_ID = T.BILLING_CYCLE_ID
602 and B.LANGUAGE = T.SOURCE_LANG)
603 where (
604 T.BILLING_CYCLE_ID,
605 T.LANGUAGE
606 ) in (select
607 SUBT.BILLING_CYCLE_ID,
608 SUBT.LANGUAGE
609 from AR_CONS_BILL_CYCLES_TL SUBB, AR_CONS_BILL_CYCLES_TL SUBT
610 where SUBB.BILLING_CYCLE_ID = SUBT.BILLING_CYCLE_ID
611 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
612 and (SUBB.CYCLE_NAME <> SUBT.CYCLE_NAME
613 ));
614 insert into AR_CONS_BILL_CYCLES_TL (
615 BILLING_CYCLE_ID,
616 CYCLE_NAME,
617 CREATED_BY,
618 CREATION_DATE,
619 LAST_UPDATED_BY,
620 LAST_UPDATE_DATE,
621 LAST_UPDATE_LOGIN,
622 LANGUAGE,
623 SOURCE_LANG
624 ) select
625 B.BILLING_CYCLE_ID,
626 B.CYCLE_NAME,
627 B.CREATED_BY,
628 B.CREATION_DATE,
629 B.LAST_UPDATED_BY,
630 B.LAST_UPDATE_DATE,
631 B.LAST_UPDATE_LOGIN,
632 L.LANGUAGE_CODE,
633 B.SOURCE_LANG
634 from AR_CONS_BILL_CYCLES_TL B, FND_LANGUAGES L
635 where L.INSTALLED_FLAG in ('I', 'B')
636 and B.LANGUAGE = userenv('LANG')
637 and not exists
638 (select NULL
639 from AR_CONS_BILL_CYCLES_TL T
640 where T.BILLING_CYCLE_ID = B.BILLING_CYCLE_ID
641 and T.LANGUAGE = L.LANGUAGE_CODE);
642 end ADD_LANGUAGE;
643
644 procedure TRANSLATE_ROW (
645 X_BILLING_CYCLE_ID in NUMBER,
646 X_CYCLE_NAME in VARCHAR2,
647 X_DESCRIPTION in VARCHAR2,
648 X_OWNER in VARCHAR2
649 ) IS
650 Begin
651 update AR_CONS_BILL_CYCLES_TL
652 set CYCLE_NAME = X_CYCLE_NAME,
653 DESCRIPTION = X_DESCRIPTION,
654 source_lang = userenv('LANG'),
655 last_update_date = sysdate,
656 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
657 last_update_login = 0
658 where BILLING_CYCLE_ID = X_BILLING_CYCLE_ID
659 and userenv('LANG') in (language, source_lang);
660 END TRANSLATE_ROW;
661
662 procedure LOAD_ROW (
663 X_BILLING_CYCLE_ID in NUMBER,
664 X_BILL_CYCLE_TYPE IN VARCHAR2,
665 X_CYCLE_NAME IN VARCHAR2,
666 X_DESCRIPTION IN VARCHAR2,
667 X_CYCLE_FREQUENCY IN VARCHAR2,
668 X_START_DATE IN DATE,
669 X_LAST_DAY IN VARCHAR2,
670 X_DAY_1 IN VARCHAR2,
671 X_DAY_2 IN VARCHAR2,
672 X_DAY_3 IN VARCHAR2,
673 X_DAY_4 IN VARCHAR2,
674 X_DAY_5 IN VARCHAR2,
675 X_DAY_6 IN VARCHAR2,
676 X_DAY_7 IN VARCHAR2,
677 X_DAY_8 IN VARCHAR2,
678 X_DAY_9 IN VARCHAR2,
679 X_DAY_10 IN VARCHAR2,
680 X_DAY_11 IN VARCHAR2,
681 X_DAY_12 IN VARCHAR2,
682 X_DAY_13 IN VARCHAR2,
683 X_DAY_14 IN VARCHAR2,
684 X_DAY_15 IN VARCHAR2,
685 X_DAY_16 IN VARCHAR2,
686 X_DAY_17 IN VARCHAR2,
687 X_DAY_18 IN VARCHAR2,
688 X_DAY_19 IN VARCHAR2,
689 X_DAY_20 IN VARCHAR2,
690 X_DAY_21 IN VARCHAR2,
691 X_DAY_22 IN VARCHAR2,
692 X_DAY_23 IN VARCHAR2,
693 X_DAY_24 IN VARCHAR2,
694 X_DAY_25 IN VARCHAR2,
695 X_DAY_26 IN VARCHAR2,
696 X_DAY_27 IN VARCHAR2,
697 X_DAY_28 IN VARCHAR2,
698 X_DAY_29 IN VARCHAR2,
699 X_DAY_30 IN VARCHAR2,
700 X_DAY_31 IN VARCHAR2,
701 X_DAY_MONDAY IN VARCHAR2,
702 X_DAY_TUESDAY IN VARCHAR2,
703 X_DAY_WEDNESDAY IN VARCHAR2,
704 X_DAY_THURSDAY IN VARCHAR2,
705 X_DAY_FRIDAY IN VARCHAR2,
706 X_DAY_SATURDAY IN VARCHAR2,
707 X_DAY_SUNDAY IN VARCHAR2,
708 X_SKIP_WEEKENDS IN VARCHAR2,
709 X_SKIP_HOLIDAYS IN VARCHAR2,
710 X_REPEAT_DAILY IN NUMBER,
711 X_REPEAT_WEEKLY IN NUMBER,
712 X_REPEAT_MONTHLY IN NUMBER,
713 X_DAY_TYPE IN VARCHAR2,
714 X_OWNER IN VARCHAR2
715 ) IS
716 begin
717 declare
718 user_id number := 0;
719 row_id varchar2(64);
720 ob_version number:= 0;
721 begin
722 if (X_OWNER = 'SEED') then
723 user_id := 1;
724 end if;
725
726 AR_CONS_BILL_CYCLE_PKG.UPDATE_ROW (
727 X_BILLING_CYCLE_ID => X_BILLING_CYCLE_ID,
728 X_BILL_CYCLE_TYPE => X_BILL_CYCLE_TYPE,
732 X_START_DATE => X_START_DATE,
729 X_CYCLE_NAME => X_CYCLE_NAME,
730 X_DESCRIPTION => X_DESCRIPTION,
731 X_CYCLE_FREQUENCY => X_CYCLE_FREQUENCY,
733 X_LAST_DAY => X_LAST_DAY,
734 X_DAY_1 => X_DAY_1,
735 X_DAY_2 => X_DAY_2,
736 X_DAY_3 => X_DAY_3,
737 X_DAY_4 => X_DAY_4,
738 X_DAY_5 => X_DAY_5,
739 X_DAY_6 => X_DAY_6,
740 X_DAY_7 => X_DAY_7,
741 X_DAY_8 => X_DAY_8,
742 X_DAY_9 => X_DAY_9,
743 X_DAY_10 => X_DAY_10,
744 X_DAY_11 => X_DAY_11,
745 X_DAY_12 => X_DAY_12,
746 X_DAY_13 => X_DAY_13,
747 X_DAY_14 => X_DAY_14,
748 X_DAY_15 => X_DAY_15,
749 X_DAY_16 => X_DAY_16,
750 X_DAY_17 => X_DAY_17,
751 X_DAY_18 => X_DAY_18,
752 X_DAY_19 => X_DAY_19,
753 X_DAY_20 => X_DAY_20,
754 X_DAY_21 => X_DAY_21,
755 X_DAY_22 => X_DAY_22,
756 X_DAY_23 => X_DAY_23,
757 X_DAY_24 => X_DAY_24,
758 X_DAY_25 => X_DAY_25,
759 X_DAY_26 => X_DAY_26,
760 X_DAY_27 => X_DAY_27,
761 X_DAY_28 => X_DAY_28,
762 X_DAY_29 => X_DAY_29,
763 X_DAY_30 => X_DAY_30,
764 X_DAY_31 => X_DAY_31,
765 X_DAY_MONDAY => X_DAY_MONDAY,
766 X_DAY_TUESDAY => X_DAY_TUESDAY,
767 X_DAY_WEDNESDAY => X_DAY_WEDNESDAY,
768 X_DAY_THURSDAY => X_DAY_THURSDAY,
769 X_DAY_FRIDAY => X_DAY_FRIDAY,
770 X_DAY_SATURDAY => X_DAY_SATURDAY,
771 X_DAY_SUNDAY => X_DAY_SUNDAY,
772 X_SKIP_WEEKENDS => X_SKIP_WEEKENDS,
773 X_SKIP_HOLIDAYS => X_SKIP_HOLIDAYS,
774 X_REPEAT_DAILY => X_REPEAT_DAILY,
775 X_REPEAT_WEEKLY => X_REPEAT_WEEKLY,
776 X_REPEAT_MONTHLY => X_REPEAT_MONTHLY,
777 X_DAY_TYPE => X_DAY_TYPE,
778 X_OBJECT_VERSION_NUMBER => ob_version,
779 X_LAST_UPDATE_DATE => sysdate,
780 X_LAST_UPDATED_BY => user_id,
781 X_LAST_UPDATE_LOGIN => 0);
782 exception
783 when NO_DATA_FOUND then
784 AR_CONS_BILL_CYCLE_PKG.INSERT_ROW (
785 X_ROWID => row_id,
786 X_BILLING_CYCLE_ID => X_BILLING_CYCLE_ID,
787 X_BILL_CYCLE_TYPE => X_BILL_CYCLE_TYPE,
788 X_CYCLE_NAME => X_CYCLE_NAME,
789 X_DESCRIPTION => X_DESCRIPTION,
790 X_CYCLE_FREQUENCY => X_CYCLE_FREQUENCY,
791 X_START_DATE => X_START_DATE,
792 X_LAST_DAY => X_LAST_DAY,
793 X_DAY_1 => X_DAY_1,
794 X_DAY_2 => X_DAY_2,
795 X_DAY_3 => X_DAY_3,
796 X_DAY_4 => X_DAY_4,
797 X_DAY_5 => X_DAY_5,
798 X_DAY_6 => X_DAY_6,
799 X_DAY_7 => X_DAY_7,
800 X_DAY_8 => X_DAY_8,
801 X_DAY_9 => X_DAY_9,
802 X_DAY_10 => X_DAY_10,
803 X_DAY_11 => X_DAY_11,
804 X_DAY_12 => X_DAY_12,
805 X_DAY_13 => X_DAY_13,
806 X_DAY_14 => X_DAY_14,
807 X_DAY_15 => X_DAY_15,
808 X_DAY_16 => X_DAY_16,
809 X_DAY_17 => X_DAY_17,
810 X_DAY_18 => X_DAY_18,
811 X_DAY_19 => X_DAY_19,
812 X_DAY_20 => X_DAY_20,
813 X_DAY_21 => X_DAY_21,
814 X_DAY_22 => X_DAY_22,
815 X_DAY_23 => X_DAY_23,
816 X_DAY_24 => X_DAY_24,
817 X_DAY_25 => X_DAY_25,
818 X_DAY_26 => X_DAY_26,
819 X_DAY_27 => X_DAY_27,
820 X_DAY_28 => X_DAY_28,
821 X_DAY_29 => X_DAY_29,
822 X_DAY_30 => X_DAY_30,
823 X_DAY_31 => X_DAY_31,
824 X_DAY_MONDAY => X_DAY_MONDAY,
825 X_DAY_TUESDAY => X_DAY_TUESDAY,
826 X_DAY_WEDNESDAY => X_DAY_WEDNESDAY,
827 X_DAY_THURSDAY => X_DAY_THURSDAY,
828 X_DAY_FRIDAY => X_DAY_FRIDAY,
829 X_DAY_SATURDAY => X_DAY_SATURDAY,
830 X_DAY_SUNDAY => X_DAY_SUNDAY,
831 X_SKIP_WEEKENDS => X_SKIP_WEEKENDS,
832 X_SKIP_HOLIDAYS => X_SKIP_HOLIDAYS,
833 X_REPEAT_DAILY => X_REPEAT_DAILY,
834 X_REPEAT_WEEKLY => X_REPEAT_WEEKLY,
835 X_REPEAT_MONTHLY => X_REPEAT_MONTHLY,
836 X_DAY_TYPE => X_DAY_TYPE,
837 X_CREATION_DATE => sysdate,
838 X_CREATED_BY => user_id,
839 X_LAST_UPDATE_DATE => sysdate,
840 X_LAST_UPDATED_BY => user_id,
841 X_LAST_UPDATE_LOGIN => 0,
842 X_OBJECT_VERSION_NUMBER => 1);
843 end;
844 end LOAD_ROW;
845
846
847
848 END AR_CONS_BILL_CYCLE_PKG;