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