[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_DUE_DT_SUMRY_PKG
Source
1 package body IGS_AS_DUE_DT_SUMRY_PKG AS
2 /* $Header: IGSDI50B.pls 115.5 2002/11/28 23:23:14 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_DUE_DT_SUMRY%RowType;
6 new_references IGS_AS_DUE_DT_SUMRY%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_session_id IN NUMBER DEFAULT NULL,
12 x_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_owner_org_unit_cd IN VARCHAR2 DEFAULT NULL,
17 x_owner_ou_start_dt IN DATE DEFAULT NULL,
18 x_unit_mode IN VARCHAR2 DEFAULT NULL,
19 x_ass_id IN NUMBER DEFAULT NULL,
20 x_week_ending_dt IN DATE DEFAULT NULL,
21 x_base_count IN NUMBER DEFAULT NULL,
22 x_expected_overdue_count IN NUMBER DEFAULT NULL,
23 x_one_week_extension_count IN NUMBER DEFAULT NULL,
24 x_two_week_extension_count IN NUMBER DEFAULT NULL,
25 x_three_week_plus_extnsn_count IN NUMBER DEFAULT NULL,
26 x_received_count IN NUMBER DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL
32 ) AS
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_AS_DUE_DT_SUMRY
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 Open cur_old_ref_values;
46 Fetch cur_old_ref_values INTO old_references;
47 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 Igs_Ge_Msg_Stack.Add;
50 Close cur_old_ref_values;
51 App_Exception.Raise_Exception;
52 Return;
53 END IF;
54 Close cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.session_id := x_session_id;
58 new_references.unit_cd := x_unit_cd;
59 new_references.version_number := x_version_number;
60 new_references.cal_type := x_cal_type;
61 new_references.ci_sequence_number := x_ci_sequence_number;
62 new_references.owner_org_unit_cd := x_owner_org_unit_cd;
63 new_references.owner_ou_start_dt := x_owner_ou_start_dt;
64 new_references.unit_mode := x_unit_mode;
65 new_references.ass_id := x_ass_id;
66 new_references.week_ending_dt := x_week_ending_dt;
67 new_references.base_count := x_base_count;
68 new_references.expected_overdue_count := x_expected_overdue_count;
69 new_references.one_week_extension_count := x_one_week_extension_count;
70 new_references.two_week_extension_count := x_two_week_extension_count;
71 new_references.three_week_plus_extnsn_count := x_three_week_plus_extnsn_count;
72 new_references.received_count := x_received_count;
73 IF (p_action = 'UPDATE') THEN
74 new_references.creation_date := old_references.creation_date;
75 new_references.created_by := old_references.created_by;
76 ELSE
77 new_references.creation_date := x_creation_date;
78 new_references.created_by := x_created_by;
79 END IF;
80 new_references.last_update_date := x_last_update_date;
81 new_references.last_updated_by := x_last_updated_by;
82 new_references.last_update_login := x_last_update_login;
83
84 END Set_Column_Values;
85
86 FUNCTION Get_PK_For_Validation (
87 x_at_id IN NUMBER
88 ) RETURN BOOLEAN AS
89
90 CURSOR cur_rowid IS
91 SELECT rowid
92 FROM IGS_AS_DUE_DT_SUMRY
93 WHERE at_id = x_at_id
94 FOR UPDATE NOWAIT;
95
96 lv_rowid cur_rowid%RowType;
97
98 BEGIN
99
100 Open cur_rowid;
101 Fetch cur_rowid INTO lv_rowid;
102 IF (cur_rowid%FOUND) THEN
103 Close cur_rowid;
104 Return (TRUE);
105 ELSE
106 Close cur_rowid;
107 Return (FALSE);
108 END IF;
109
110 END Get_PK_For_Validation;
111 PROCEDURE Before_DML (
112 p_action IN VARCHAR2,
113 x_rowid IN VARCHAR2 DEFAULT NULL,
114 X_AT_ID IN NUMBER DEFAULT NULL,
115 x_session_id IN NUMBER DEFAULT NULL,
116 x_unit_cd IN VARCHAR2 DEFAULT NULL,
117 x_version_number IN NUMBER DEFAULT NULL,
118 x_cal_type IN VARCHAR2 DEFAULT NULL,
119 x_ci_sequence_number IN NUMBER DEFAULT NULL,
120 x_owner_org_unit_cd IN VARCHAR2 DEFAULT NULL,
121 x_owner_ou_start_dt IN DATE DEFAULT NULL,
122 x_unit_mode IN VARCHAR2 DEFAULT NULL,
123 x_ass_id IN NUMBER DEFAULT NULL,
124 x_week_ending_dt IN DATE DEFAULT NULL,
125 x_base_count IN NUMBER DEFAULT NULL,
126 x_expected_overdue_count IN NUMBER DEFAULT NULL,
127 x_one_week_extension_count IN NUMBER DEFAULT NULL,
128 x_two_week_extension_count IN NUMBER DEFAULT NULL,
129 x_three_week_plus_extnsn_count IN NUMBER DEFAULT NULL,
130 x_received_count IN NUMBER DEFAULT NULL,
131 x_creation_date IN DATE DEFAULT NULL,
132 x_created_by IN NUMBER DEFAULT NULL,
133 x_last_update_date IN DATE DEFAULT NULL,
134 x_last_updated_by IN NUMBER DEFAULT NULL,
135 x_last_update_login IN NUMBER DEFAULT NULL
136 ) AS
137 BEGIN
138
139 Set_Column_Values (
140 p_action,
141 x_rowid,
142 x_session_id,
143 x_unit_cd,
144 x_version_number,
145 x_cal_type,
146 x_ci_sequence_number,
147 x_owner_org_unit_cd,
148 x_owner_ou_start_dt,
149 x_unit_mode,
150 x_ass_id,
151 x_week_ending_dt,
152 x_base_count,
153 x_expected_overdue_count,
154 x_one_week_extension_count,
155 x_two_week_extension_count,
156 x_three_week_plus_extnsn_count,
157 x_received_count,
158 x_creation_date,
159 x_created_by,
160 x_last_update_date,
161 x_last_updated_by,
162 x_last_update_login
163 );
164
165 IF (p_action = 'INSERT') THEN
166 -- Call all the procedures related to Before Insert.
167
168 IF Get_PK_For_Validation (
169 new_references.at_id ) THEN
170 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
171 Igs_Ge_Msg_Stack.Add;
172 App_Exception.Raise_Exception;
173 END IF;
174 NULL;
175 ELSIF (p_action = 'UPDATE') THEN
176 -- Call all the procedures related to Before Update.
177 NULL;
178 ELSIF (p_action = 'DELETE') THEN
179 -- Call all the procedures related to Before Delete.
180 NULL;
181
182 ELSIF (p_action = 'VALIDATE_INSERT') THEN
183 IF Get_PK_For_Validation (
184 new_references.at_id ) THEN
185 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
186 Igs_Ge_Msg_Stack.Add;
187 App_Exception.Raise_Exception;
188 END IF;
189 NULL;
190 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
191 NULL;
192 ELSIF (p_action = 'VALIDATE_DELETE') THEN
193 NULL;
194 END IF;
195
196 END Before_DML;
197
198
199 procedure INSERT_ROW (
200 X_ROWID in out NOCOPY VARCHAR2,
201 X_AT_ID in out NOCOPY NUMBER,
202 X_SESSION_ID in NUMBER,
203 X_UNIT_CD in VARCHAR2,
204 X_VERSION_NUMBER in NUMBER,
205 X_CAL_TYPE in VARCHAR2,
206 X_CI_SEQUENCE_NUMBER in NUMBER,
207 X_OWNER_ORG_UNIT_CD in VARCHAR2,
208 X_OWNER_OU_START_DT in DATE,
209 X_UNIT_MODE in VARCHAR2,
210 X_ASS_ID in NUMBER,
211 X_WEEK_ENDING_DT in DATE,
212 X_BASE_COUNT in NUMBER,
213 X_EXPECTED_OVERDUE_COUNT in NUMBER,
214 X_ONE_WEEK_EXTENSION_COUNT in NUMBER,
215 X_TWO_WEEK_EXTENSION_COUNT in NUMBER,
216 X_THREE_WEEK_PLUS_EXTNSN_COUNT in NUMBER,
217 X_RECEIVED_COUNT in NUMBER,
218 X_MODE in VARCHAR2 default 'R'
219 ) AS
220 cursor C is select ROWID from IGS_AS_DUE_DT_SUMRY
221 where AT_ID = X_AT_ID;
222 X_LAST_UPDATE_DATE DATE;
223 X_LAST_UPDATED_BY NUMBER;
224 X_LAST_UPDATE_LOGIN NUMBER;
225 X_REQUEST_ID NUMBER;
226 X_PROGRAM_ID NUMBER;
227 X_PROGRAM_APPLICATION_ID NUMBER;
228 X_PROGRAM_UPDATE_DATE DATE;
229
230 begin
231 X_LAST_UPDATE_DATE := SYSDATE;
232 if(X_MODE = 'I') then
233 X_LAST_UPDATED_BY := 1;
234 X_LAST_UPDATE_LOGIN := 0;
235 elsif (X_MODE = 'R') then
236 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
237 if X_LAST_UPDATED_BY is NULL then
238 X_LAST_UPDATED_BY := -1;
239 end if;
240 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
241 if X_LAST_UPDATE_LOGIN is NULL then
242 X_LAST_UPDATE_LOGIN := -1;
243 end if;
244 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
245 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
246
247 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
248 if (X_REQUEST_ID = -1) then
249 X_REQUEST_ID := NULL;
250 X_PROGRAM_ID := NULL;
251 X_PROGRAM_APPLICATION_ID := NULL;
252 X_PROGRAM_UPDATE_DATE := NULL;
253 else
254 X_PROGRAM_UPDATE_DATE := SYSDATE;
255 end if;
256
257 else
258 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
259 Igs_Ge_Msg_Stack.Add;
260 app_exception.raise_exception;
261 end if;
262
263 select IGS_AS_ASSR1020_TMP_AT_ID_S.nextval
264 INTO X_AT_ID
265 FROM DUAL;
266
267 Before_DML(
268 p_action=>'INSERT',
269 x_rowid=>X_ROWID,
270 x_ass_id=>X_ASS_ID,
271 x_base_count=>X_BASE_COUNT,
272 x_cal_type=>X_CAL_TYPE,
273 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
274 x_expected_overdue_count=>X_EXPECTED_OVERDUE_COUNT,
275 x_one_week_extension_count=>X_ONE_WEEK_EXTENSION_COUNT,
276 x_owner_org_unit_cd=>X_OWNER_ORG_UNIT_CD,
277 x_owner_ou_start_dt=>X_OWNER_OU_START_DT,
278 x_received_count=>X_RECEIVED_COUNT,
279 x_session_id=>X_SESSION_ID,
280 x_AT_id=>X_AT_ID,
281 x_three_week_plus_extnsn_count=>X_THREE_WEEK_PLUS_EXTNSN_COUNT,
282 x_two_week_extension_count=>X_TWO_WEEK_EXTENSION_COUNT,
283 x_unit_cd=>X_UNIT_CD,
284 x_unit_mode=>X_UNIT_MODE,
285 x_version_number=>X_VERSION_NUMBER,
286 x_week_ending_dt=>X_WEEK_ENDING_DT,
287 x_creation_date=>X_LAST_UPDATE_DATE,
288 x_created_by=>X_LAST_UPDATED_BY,
289 x_last_update_date=>X_LAST_UPDATE_DATE,
290 x_last_updated_by=>X_LAST_UPDATED_BY,
294 insert into IGS_AS_DUE_DT_SUMRY (
291 x_last_update_login=>X_LAST_UPDATE_LOGIN
292 );
293
295 SESSION_ID,
296 AT_ID,
297 UNIT_CD,
298 VERSION_NUMBER,
299 CAL_TYPE,
300 CI_SEQUENCE_NUMBER,
301 OWNER_ORG_UNIT_CD,
302 OWNER_OU_START_DT,
303 UNIT_MODE,
304 ASS_ID,
305 WEEK_ENDING_DT,
306 BASE_COUNT,
307 EXPECTED_OVERDUE_COUNT,
308 ONE_WEEK_EXTENSION_COUNT,
309 TWO_WEEK_EXTENSION_COUNT,
310 THREE_WEEK_PLUS_EXTNSN_COUNT,
311 RECEIVED_COUNT,
312 CREATION_DATE,
313 CREATED_BY,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY,
316 LAST_UPDATE_LOGIN,
317 REQUEST_ID,
318 PROGRAM_ID,
319 PROGRAM_APPLICATION_ID,
320 PROGRAM_UPDATE_DATE
321 ) values (
322 NEW_REFERENCES.SESSION_ID,
323 NEW_REFERENCES.AT_ID,
324 NEW_REFERENCES.UNIT_CD,
325 NEW_REFERENCES.VERSION_NUMBER,
326 NEW_REFERENCES.CAL_TYPE,
327 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
328 NEW_REFERENCES.OWNER_ORG_UNIT_CD,
329 NEW_REFERENCES.OWNER_OU_START_DT,
330 NEW_REFERENCES.UNIT_MODE,
331 NEW_REFERENCES.ASS_ID,
332 NEW_REFERENCES.WEEK_ENDING_DT,
333 NEW_REFERENCES.BASE_COUNT,
334 NEW_REFERENCES.EXPECTED_OVERDUE_COUNT,
335 NEW_REFERENCES.ONE_WEEK_EXTENSION_COUNT,
336 NEW_REFERENCES.TWO_WEEK_EXTENSION_COUNT,
337 NEW_REFERENCES.THREE_WEEK_PLUS_EXTNSN_COUNT,
338 NEW_REFERENCES.RECEIVED_COUNT,
339 X_LAST_UPDATE_DATE,
340 X_LAST_UPDATED_BY,
341 X_LAST_UPDATE_DATE,
342 X_LAST_UPDATED_BY,
343 X_LAST_UPDATE_LOGIN,
344 X_REQUEST_ID,
345 X_PROGRAM_ID,
346 X_PROGRAM_APPLICATION_ID,
347 X_PROGRAM_UPDATE_DATE
348 );
349
350 open c;
351 fetch c into X_ROWID;
352 if (c%notfound) then
353 close c;
354 raise no_data_found;
355 end if;
356 close c;
357
358 end INSERT_ROW;
359
360 procedure LOCK_ROW (
361 X_ROWID in VARCHAR2,
362 X_AT_ID in NUMBER,
363 X_SESSION_ID in NUMBER,
364 X_UNIT_CD in VARCHAR2,
365 X_VERSION_NUMBER in NUMBER,
366 X_CAL_TYPE in VARCHAR2,
367 X_CI_SEQUENCE_NUMBER in NUMBER,
368 X_OWNER_ORG_UNIT_CD in VARCHAR2,
369 X_OWNER_OU_START_DT in DATE,
370 X_UNIT_MODE in VARCHAR2,
371 X_ASS_ID in NUMBER,
372 X_WEEK_ENDING_DT in DATE,
373 X_BASE_COUNT in NUMBER,
374 X_EXPECTED_OVERDUE_COUNT in NUMBER,
375 X_ONE_WEEK_EXTENSION_COUNT in NUMBER,
376 X_TWO_WEEK_EXTENSION_COUNT in NUMBER,
377 X_THREE_WEEK_PLUS_EXTNSN_COUNT in NUMBER,
378 X_RECEIVED_COUNT in NUMBER
379 ) AS
380 cursor c1 is select
381 SESSION_ID,
382 UNIT_CD,
383 VERSION_NUMBER,
384 CAL_TYPE,
385 CI_SEQUENCE_NUMBER,
386 OWNER_ORG_UNIT_CD,
387 OWNER_OU_START_DT,
388 UNIT_MODE,
389 ASS_ID,
390 WEEK_ENDING_DT,
391 BASE_COUNT,
392 EXPECTED_OVERDUE_COUNT,
393 ONE_WEEK_EXTENSION_COUNT,
394 TWO_WEEK_EXTENSION_COUNT,
395 THREE_WEEK_PLUS_EXTNSN_COUNT,
396 RECEIVED_COUNT
397 from IGS_AS_DUE_DT_SUMRY
398 where ROWID =X_ROWID
399 for update nowait;
400 tlinfo c1%rowtype;
401
402 begin
403 open c1;
404 fetch c1 into tlinfo;
405 if (c1%notfound) then
406 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
407 Igs_Ge_Msg_Stack.Add;
408 close c1;
409 app_exception.raise_exception;
410 return;
411 end if;
412 close c1;
413
414 if ( (tlinfo.SESSION_ID = X_SESSION_ID)
415 AND (tlinfo.UNIT_CD = X_UNIT_CD)
416 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
417 AND (tlinfo.CAL_TYPE = X_CAL_TYPE)
421 OR ((tlinfo.OWNER_OU_START_DT is null)
418 AND (tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
419 AND (tlinfo.OWNER_ORG_UNIT_CD = X_OWNER_ORG_UNIT_CD)
420 AND ((tlinfo.OWNER_OU_START_DT = X_OWNER_OU_START_DT)
422 AND (X_OWNER_OU_START_DT is null)))
423 AND (tlinfo.UNIT_MODE = X_UNIT_MODE)
424 AND (tlinfo.ASS_ID = X_ASS_ID)
425 AND (tlinfo.WEEK_ENDING_DT = X_WEEK_ENDING_DT)
426 AND ((tlinfo.BASE_COUNT = X_BASE_COUNT)
427 OR ((tlinfo.BASE_COUNT is null)
428 AND (X_BASE_COUNT is null)))
429 AND ((tlinfo.EXPECTED_OVERDUE_COUNT = X_EXPECTED_OVERDUE_COUNT)
430 OR ((tlinfo.EXPECTED_OVERDUE_COUNT is null)
431 AND (X_EXPECTED_OVERDUE_COUNT is null)))
432 AND ((tlinfo.ONE_WEEK_EXTENSION_COUNT = X_ONE_WEEK_EXTENSION_COUNT)
433 OR ((tlinfo.ONE_WEEK_EXTENSION_COUNT is null)
434 AND (X_ONE_WEEK_EXTENSION_COUNT is null)))
435 AND ((tlinfo.TWO_WEEK_EXTENSION_COUNT = X_TWO_WEEK_EXTENSION_COUNT)
436 OR ((tlinfo.TWO_WEEK_EXTENSION_COUNT is null)
437 AND (X_TWO_WEEK_EXTENSION_COUNT is null)))
438 AND ((tlinfo.THREE_WEEK_PLUS_EXTNSN_COUNT = X_THREE_WEEK_PLUS_EXTNSN_COUNT)
439 OR ((tlinfo.THREE_WEEK_PLUS_EXTNSN_COUNT is null)
440 AND (X_THREE_WEEK_PLUS_EXTNSN_COUNT is null)))
441 AND ((tlinfo.RECEIVED_COUNT = X_RECEIVED_COUNT)
442 OR ((tlinfo.RECEIVED_COUNT is null)
443 AND (X_RECEIVED_COUNT is null)))
444 ) then
445 null;
446 else
447 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
448 Igs_Ge_Msg_Stack.Add;
449 app_exception.raise_exception;
450 end if;
451 return;
452 end LOCK_ROW;
453
454 procedure UPDATE_ROW (
455 X_ROWID in VARCHAR2,
456 X_AT_ID in NUMBER,
457 X_SESSION_ID in NUMBER,
458 X_UNIT_CD in VARCHAR2,
459 X_VERSION_NUMBER in NUMBER,
460 X_CAL_TYPE in VARCHAR2,
461 X_CI_SEQUENCE_NUMBER in NUMBER,
462 X_OWNER_ORG_UNIT_CD in VARCHAR2,
463 X_OWNER_OU_START_DT in DATE,
464 X_UNIT_MODE in VARCHAR2,
465 X_ASS_ID in NUMBER,
466 X_WEEK_ENDING_DT in DATE,
467 X_BASE_COUNT in NUMBER,
468 X_EXPECTED_OVERDUE_COUNT in NUMBER,
469 X_ONE_WEEK_EXTENSION_COUNT in NUMBER,
470 X_TWO_WEEK_EXTENSION_COUNT in NUMBER,
471 X_THREE_WEEK_PLUS_EXTNSN_COUNT in NUMBER,
472 X_RECEIVED_COUNT in NUMBER,
473 X_MODE in VARCHAR2 default 'R'
474 ) AS
475 X_LAST_UPDATE_DATE DATE;
476 X_LAST_UPDATED_BY NUMBER;
477 X_LAST_UPDATE_LOGIN NUMBER;
478 X_REQUEST_ID NUMBER;
479 X_PROGRAM_ID NUMBER;
480 X_PROGRAM_APPLICATION_ID NUMBER;
481 X_PROGRAM_UPDATE_DATE DATE;
482 begin
483 X_LAST_UPDATE_DATE := SYSDATE;
484 if(X_MODE = 'I') then
485 X_LAST_UPDATED_BY := 1;
486 X_LAST_UPDATE_LOGIN := 0;
487 elsif (X_MODE = 'R') then
488 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
489 if X_LAST_UPDATED_BY is NULL then
490 X_LAST_UPDATED_BY := -1;
491 end if;
492 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
493 if X_LAST_UPDATE_LOGIN is NULL then
494 X_LAST_UPDATE_LOGIN := -1;
495 end if;
496 else
497 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
498 Igs_Ge_Msg_Stack.Add;
499 app_exception.raise_exception;
500 end if;
501 Before_DML(
502 p_action=>'UPDATE',
503 x_rowid=>X_ROWID,
504 x_ass_id=>X_ASS_ID,
505 x_base_count=>X_BASE_COUNT,
506 x_cal_type=>X_CAL_TYPE,
507 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
508 x_expected_overdue_count=>X_EXPECTED_OVERDUE_COUNT,
512 x_received_count=>X_RECEIVED_COUNT,
509 x_one_week_extension_count=>X_ONE_WEEK_EXTENSION_COUNT,
510 x_owner_org_unit_cd=>X_OWNER_ORG_UNIT_CD,
511 x_owner_ou_start_dt=>X_OWNER_OU_START_DT,
513 x_session_id=>X_SESSION_ID,
514 x_AT_id=>X_AT_ID,
515 x_three_week_plus_extnsn_count=>X_THREE_WEEK_PLUS_EXTNSN_COUNT,
516 x_two_week_extension_count=>X_TWO_WEEK_EXTENSION_COUNT,
517 x_unit_cd=>X_UNIT_CD,
518 x_unit_mode=>X_UNIT_MODE,
519 x_version_number=>X_VERSION_NUMBER,
520 x_week_ending_dt=>X_WEEK_ENDING_DT,
521 x_creation_date=>X_LAST_UPDATE_DATE,
522 x_created_by=>X_LAST_UPDATED_BY,
523 x_last_update_date=>X_LAST_UPDATE_DATE,
524 x_last_updated_by=>X_LAST_UPDATED_BY,
525 x_last_update_login=>X_LAST_UPDATE_LOGIN
526 );
527 if (X_MODE = 'R') then
528 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
529 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
530 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
531 if (X_REQUEST_ID = -1) then
532 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
533 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
534 X_PROGRAM_APPLICATION_ID :=
535 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
536 X_PROGRAM_UPDATE_DATE :=
537 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
538 else
539 X_PROGRAM_UPDATE_DATE := SYSDATE;
540 end if;
541 end if;
542
543 update IGS_AS_DUE_DT_SUMRY set
544 SESSION_ID = NEW_REFERENCES.SESSION_ID,
545 UNIT_CD = NEW_REFERENCES.UNIT_CD,
546 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
547 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
548 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
549 OWNER_ORG_UNIT_CD = NEW_REFERENCES.OWNER_ORG_UNIT_CD,
550 OWNER_OU_START_DT = NEW_REFERENCES.OWNER_OU_START_DT,
551 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
552 ASS_ID = NEW_REFERENCES.ASS_ID,
553 WEEK_ENDING_DT = NEW_REFERENCES.WEEK_ENDING_DT,
554 BASE_COUNT = NEW_REFERENCES.BASE_COUNT,
555 EXPECTED_OVERDUE_COUNT = NEW_REFERENCES.EXPECTED_OVERDUE_COUNT,
556 ONE_WEEK_EXTENSION_COUNT = NEW_REFERENCES.ONE_WEEK_EXTENSION_COUNT,
557 TWO_WEEK_EXTENSION_COUNT = NEW_REFERENCES.TWO_WEEK_EXTENSION_COUNT,
558 THREE_WEEK_PLUS_EXTNSN_COUNT = NEW_REFERENCES.THREE_WEEK_PLUS_EXTNSN_COUNT,
559 RECEIVED_COUNT = NEW_REFERENCES.RECEIVED_COUNT,
560 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
561 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
562 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
563 REQUEST_ID = X_REQUEST_ID,
564 PROGRAM_ID = X_PROGRAM_ID,
565 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
566 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
567
568 where ROWID = X_ROWID;
569
570 if (sql%notfound) then
571 raise no_data_found;
572 end if;
573
574 end UPDATE_ROW;
575
576 procedure ADD_ROW (
577 X_ROWID in out NOCOPY VARCHAR2,
578 X_AT_ID in out NOCOPY NUMBER,
579 X_SESSION_ID in NUMBER,
580 X_UNIT_CD in VARCHAR2,
581 X_VERSION_NUMBER in NUMBER,
582 X_CAL_TYPE in VARCHAR2,
583 X_CI_SEQUENCE_NUMBER in NUMBER,
584 X_OWNER_ORG_UNIT_CD in VARCHAR2,
585 X_OWNER_OU_START_DT in DATE,
586 X_UNIT_MODE in VARCHAR2,
587 X_ASS_ID in NUMBER,
588 X_WEEK_ENDING_DT in DATE,
589 X_BASE_COUNT in NUMBER,
590 X_EXPECTED_OVERDUE_COUNT in NUMBER,
591 X_ONE_WEEK_EXTENSION_COUNT in NUMBER,
592 X_TWO_WEEK_EXTENSION_COUNT in NUMBER,
593 X_THREE_WEEK_PLUS_EXTNSN_COUNT in NUMBER,
594 X_RECEIVED_COUNT in NUMBER,
595 X_MODE in VARCHAR2 default 'R'
596 ) AS
597 cursor c1 is select rowid from IGS_AS_DUE_DT_SUMRY
598 where AT_ID = X_AT_ID
599 ;
600
601 begin
602 open c1;
603 fetch c1 into X_ROWID;
604 if (c1%notfound) then
605 close c1;
606 INSERT_ROW (
607 X_ROWID,
608 X_AT_ID,
609 X_SESSION_ID,
610 X_UNIT_CD,
611 X_VERSION_NUMBER,
612 X_CAL_TYPE,
613 X_CI_SEQUENCE_NUMBER,
614 X_OWNER_ORG_UNIT_CD,
615 X_OWNER_OU_START_DT,
616 X_UNIT_MODE,
617 X_ASS_ID,
618 X_WEEK_ENDING_DT,
619 X_BASE_COUNT,
620 X_EXPECTED_OVERDUE_COUNT,
621 X_ONE_WEEK_EXTENSION_COUNT,
622 X_TWO_WEEK_EXTENSION_COUNT,
623 X_THREE_WEEK_PLUS_EXTNSN_COUNT,
624 X_RECEIVED_COUNT,
625 X_MODE);
626 return;
627 end if;
628 close c1;
629 UPDATE_ROW (
630 X_ROWID,
631 X_AT_ID,
632 X_SESSION_ID,
633 X_UNIT_CD,
634 X_VERSION_NUMBER,
635 X_CAL_TYPE,
636 X_CI_SEQUENCE_NUMBER,
637 X_OWNER_ORG_UNIT_CD,
638 X_OWNER_OU_START_DT,
639 X_UNIT_MODE,
640 X_ASS_ID,
641 X_WEEK_ENDING_DT,
642 X_BASE_COUNT,
643 X_EXPECTED_OVERDUE_COUNT,
644 X_ONE_WEEK_EXTENSION_COUNT,
645 X_TWO_WEEK_EXTENSION_COUNT,
646 X_THREE_WEEK_PLUS_EXTNSN_COUNT,
647 X_RECEIVED_COUNT,
648 X_MODE);
649 end ADD_ROW;
650
651 procedure DELETE_ROW (
652 X_ROWID in VARCHAR2
653 ) AS
654 begin
655 Before_DML(
656 p_action => 'DELETE',
657 x_rowid => X_ROWID
658 );
659 delete from IGS_AS_DUE_DT_SUMRY
660 where ROWID = X_ROWID;
661 if (sql%notfound) then
662 raise no_data_found;
663 end if;
664 end DELETE_ROW;
665
666 end IGS_AS_DUE_DT_SUMRY_PKG;