[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_TCH_RESP_OVRD_PKG
Source
1 package body IGS_PS_TCH_RESP_OVRD_PKG as
2 /* $Header: IGSPI71B.pls 120.0 2005/06/01 19:56:50 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_TCH_RESP_OVRD_ALL%RowType;
5 new_references IGS_PS_TCH_RESP_OVRD_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_cd IN VARCHAR2 DEFAULT NULL,
11 x_version_number IN NUMBER DEFAULT NULL,
12 x_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_location_cd IN VARCHAR2 DEFAULT NULL,
15 x_unit_class IN VARCHAR2 DEFAULT NULL,
16 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
17 x_ou_start_dt IN DATE DEFAULT NULL,
18 x_uoo_id IN NUMBER DEFAULT NULL,
19 x_percentage IN NUMBER DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL,
25 x_org_id IN NUMBER DEFAULT NULL
26 ) AS
27
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM IGS_PS_TCH_RESP_OVRD_ALL
31 WHERE rowid = x_rowid;
32
33 BEGIN
34
35 l_rowid := x_rowid;
36
37 -- Code for setting the Old and New Reference Values.
38 -- Populate Old Values.
39 Open cur_old_ref_values;
40 Fetch cur_old_ref_values INTO old_references;
41 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
42 Close cur_old_ref_values;
43 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 Return;
47 END IF;
48 Close cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.unit_cd := x_unit_cd;
52 new_references.version_number := x_version_number;
53 new_references.cal_type := x_cal_type;
54 new_references.ci_sequence_number := x_ci_sequence_number;
55 new_references.location_cd := x_location_cd;
56 new_references.unit_class := x_unit_class;
57 new_references.org_unit_cd := x_org_unit_cd;
58 new_references.ou_start_dt := x_ou_start_dt;
59 new_references.uoo_id := x_uoo_id;
60 new_references.percentage := x_percentage;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71 new_references.org_id := x_org_id;
72
73 END Set_Column_Values;
74
75 PROCEDURE BeforeRowInsert1(
76 p_inserting IN BOOLEAN DEFAULT FALSE,
77 p_updating IN BOOLEAN DEFAULT FALSE,
78 p_deleting IN BOOLEAN DEFAULT FALSE
79 ) AS
80 BEGIN
81 -- Set either the uoo_id or the primary key based on the other fields.
82 IGS_PS_GEN_006.CRSP_GET_UOO_KEY( new_references.unit_cd,
83 new_references.version_number,
84 new_references.cal_type,
85 new_references.ci_sequence_number,
86 new_references.location_cd,
87 new_references.unit_class,
88 new_references.uoo_id);
89
90 END BeforeRowInsert1;
91
92 PROCEDURE BeforeRowInsertUpdateDelete2(
93 p_inserting IN BOOLEAN DEFAULT FALSE,
94 p_updating IN BOOLEAN DEFAULT FALSE,
95 p_deleting IN BOOLEAN DEFAULT FALSE
96 ) AS
97 v_unit_cd IGS_PS_TCH_RESP_OVRD_ALL.unit_cd%TYPE;
98 v_version_number IGS_PS_TCH_RESP_OVRD_ALL.version_number%TYPE;
99 v_message_name VARCHAR2(30);
100 BEGIN
101 -- Set variables.
102 IF p_deleting THEN
103 v_unit_cd := old_references.unit_cd;
104 v_version_number := old_references.version_number;
105 ELSE -- p_inserting or p_updating
106 v_unit_cd := new_references.unit_cd;
107 v_version_number := new_references.version_number;
108 END IF;
109 -- Validate the insert/update/delete.
110 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
111 v_unit_cd,
112 v_version_number,
113 v_message_name) = FALSE THEN
114 Fnd_Message.Set_Name('IGS', v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 -- Validate the org IGS_PS_UNIT is not inactive.
119 IF p_inserting THEN
120 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_TRo.crsp_val_ou_sys_sts
121 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts (
122 new_references.org_unit_cd,
123 new_references.ou_start_dt,
124 v_message_name) = FALSE THEN
125 Fnd_Message.Set_Name('IGS', v_message_name);
126 IGS_GE_MSG_STACK.ADD;
127 App_Exception.Raise_Exception;
128 END IF;
129 END IF;
130
131
132 END BeforeRowInsertUpdateDelete2;
133
134 PROCEDURE AfterRowUpdateDelete3(
135 p_inserting IN BOOLEAN DEFAULT FALSE,
136 p_updating IN BOOLEAN DEFAULT FALSE,
137 p_deleting IN BOOLEAN DEFAULT FALSE
138 ) AS
139 BEGIN
140 IF p_updating THEN
141 -- Create history record.
142 IGS_PS_GEN_005.CRSP_INS_TRO_HIST (
143 new_references.unit_cd,
144 new_references.version_number,
145 new_references.cal_type,
146 new_references.ci_sequence_number,
147 new_references.location_cd,
148 new_references.unit_class,
149 new_references.org_unit_cd,
150 new_references.ou_start_dt,
151 new_references.percentage,
152 old_references.percentage,
153 new_references.last_updated_by,
154 old_references.last_updated_by,
155 new_references.last_update_date,
156 old_references.last_update_date);
157 END IF;
158 IF p_deleting THEN
159 -- Create history record.
160 IGS_PS_GEN_005.CRSP_INS_TRO_HIST (
161 old_references.unit_cd,
162 old_references.version_number,
163 old_references.cal_type,
164 old_references.ci_sequence_number,
165 old_references.location_cd,
166 old_references.unit_class,
167 old_references.org_unit_cd,
168 old_references.ou_start_dt,
169 -1,
170 old_references.percentage,
171 old_references.last_updated_by,
172 old_references.last_updated_by,
173 old_references.last_update_date,
174 old_references.last_update_date);
175 END IF;
176
177
178 END AfterRowUpdateDelete3;
179
180 PROCEDURE Check_Constraints (
181 Column_Name IN VARCHAR2 DEFAULT NULL,
182 Column_Value IN VARCHAR2 DEFAULT NULL
183 )
184 AS
185 BEGIN
186
187 IF column_name is null then
188 NULL;
189 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
190 new_references.ci_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
191 ELSIF upper(Column_name) = 'PERCENTAGE' then
192 new_references.percentage := IGS_GE_NUMBER.TO_NUM(column_value);
193 ELSIF upper(Column_name) = 'UOO_ID' then
194 new_references.uoo_id := IGS_GE_NUMBER.TO_NUM(column_value);
195 ELSIF upper(Column_name) = 'CAL_TYPE' then
196 new_references.cal_type := column_value;
197 ELSIF upper(Column_name) = 'LOCATION_CD' then
198 new_references.location_cd := column_value;
199 ELSIF upper(Column_name) = 'UNIT_CLASS' then
200 new_references.unit_class:= column_value;
201 ELSIF upper(Column_name) = 'UNIT_CD' then
202 new_references.unit_cd:= column_value;
203 END IF;
204
205 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
206 column_name is null Then
207 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
208 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214 IF upper(column_name) = 'PERCENTAGE' OR
215 column_name is null Then
216 IF new_references.percentage < 000.01 OR new_references.percentage > 100.00 Then
217 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception;
220 END IF;
221 END IF;
222
223 IF upper(column_name) = 'UOO_ID' OR
224 column_name is null Then
225 IF new_references.uoo_id < 1 OR new_references.uoo_id > 999999 Then
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END IF;
230 END IF;
231
232 IF upper(column_name) = 'CAL_TYPE' OR
233 column_name is null Then
234 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
235 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240
241 IF upper(column_name) = 'LOCATION_CD' OR
242 column_name is null Then
243 IF new_references.location_cd <> UPPER(new_references.location_cd) Then
244 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END IF;
248 END IF;
249
250 IF upper(column_name) = 'UNIT_CLASS' OR
251 column_name is null Then
252 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
253 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257 END IF;
258
259 IF upper(column_name) = 'UNIT_CD' OR
260 column_name is null Then
261 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
262 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IF;
266 END IF;
267
268 END check_constraints;
269
270 PROCEDURE Check_Parent_Existance AS
271 BEGIN
272
273 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
274 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
275 ((new_references.org_unit_cd IS NULL) OR
276 (new_references.ou_start_dt IS NULL))) THEN
277 NULL;
278 ELSE
279 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
280 new_references.org_unit_cd,
281 new_references.ou_start_dt
282 ) THEN
283 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END IF;
287 END IF;
288
289 IF (((old_references.unit_cd = new_references.unit_cd) AND
290 (old_references.version_number = new_references.version_number) AND
291 (old_references.cal_type = new_references.cal_type) AND
292 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
293 (old_references.location_cd = new_references.location_cd) AND
294 (old_references.unit_class = new_references.unit_class)) OR
295 ((new_references.unit_cd IS NULL) OR
296 (new_references.version_number IS NULL) OR
297 (new_references.cal_type IS NULL) OR
298 (new_references.ci_sequence_number IS NULL) OR
299 (new_references.location_cd IS NULL) OR
300 (new_references.unit_class IS NULL))) THEN
301 NULL;
302 ELSE
303 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_PK_For_Validation (
304 new_references.unit_cd,
305 new_references.version_number,
306 new_references.cal_type,
307 new_references.ci_sequence_number,
308 new_references.location_cd,
309 new_references.unit_class
310 ) THEN
311 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315 END IF;
316
317 IF (((old_references.uoo_id = new_references.uoo_id)) OR
318 ((new_references.uoo_id IS NULL))) THEN
319 NULL;
320 ELSE
321 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_UK_For_Validation (
322 new_references.uoo_id
323 ) THEN
324 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 END IF;
328 END IF;
329 END Check_Parent_Existance;
330
331 FUNCTION Get_PK_For_Validation (
332 x_unit_cd IN VARCHAR2,
333 x_version_number IN NUMBER,
334 x_cal_type IN VARCHAR2,
335 x_ci_sequence_number IN NUMBER,
336 x_location_cd IN VARCHAR2,
337 x_unit_class IN VARCHAR2,
338 x_org_unit_cd IN VARCHAR2,
339 x_ou_start_dt IN DATE
340 ) RETURN BOOLEAN AS
341
342 CURSOR cur_rowid IS
343 SELECT rowid
344 FROM IGS_PS_TCH_RESP_OVRD_ALL
345 WHERE unit_cd = x_unit_cd
346 AND version_number = x_version_number
347 AND cal_type = x_cal_type
348 AND ci_sequence_number = x_ci_sequence_number
349 AND location_cd = x_location_cd
350 AND unit_class = x_unit_class
351 AND org_unit_cd = x_org_unit_cd
352 AND ou_start_dt = x_ou_start_dt
353 FOR UPDATE NOWAIT;
354
355 lv_rowid cur_rowid%RowType;
356
357 BEGIN
358
359 Open cur_rowid;
360 Fetch cur_rowid INTO lv_rowid;
361 IF (cur_rowid%FOUND) THEN
362 Close cur_rowid;
363 Return (TRUE);
364 ELSE
365 Close cur_rowid;
366 Return (FALSE);
367 END IF;
368 END Get_PK_For_Validation;
369
370 PROCEDURE GET_FK_IGS_OR_UNIT (
371 x_org_unit_cd IN VARCHAR2,
372 x_start_dt IN VARCHAR2
373 ) AS
374
375 CURSOR cur_rowid IS
376 SELECT rowid
377 FROM IGS_PS_TCH_RESP_OVRD_ALL
378 WHERE org_unit_cd = x_org_unit_cd
379 AND ou_start_dt = x_start_dt ;
380
381 lv_rowid cur_rowid%RowType;
382
383 BEGIN
384
385 Open cur_rowid;
386 Fetch cur_rowid INTO lv_rowid;
387 IF (cur_rowid%FOUND) THEN
388 Close cur_rowid;
389 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TRO_OU_FK');
390 IGS_GE_MSG_STACK.ADD;
391 App_Exception.Raise_Exception;
392 Return;
393 END IF;
394 Close cur_rowid;
395
396 END GET_FK_IGS_OR_UNIT;
397
398 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_OPT (
399 x_unit_cd IN VARCHAR2,
400 x_version_number IN NUMBER,
401 x_cal_type IN VARCHAR2,
402 x_ci_sequence_number IN NUMBER,
403 x_location_cd IN VARCHAR2,
404 x_unit_class IN VARCHAR2
405 ) AS
406
407 CURSOR cur_rowid IS
408 SELECT rowid
409 FROM IGS_PS_TCH_RESP_OVRD_ALL
410 WHERE unit_cd = x_unit_cd
411 AND version_number = x_version_number
412 AND cal_type = x_cal_type
413 AND ci_sequence_number = x_ci_sequence_number
414 AND location_cd = x_location_cd
415 AND unit_class = x_unit_class ;
416
417 lv_rowid cur_rowid%RowType;
418
419 BEGIN
420
421 Open cur_rowid;
422 Fetch cur_rowid INTO lv_rowid;
423 IF (cur_rowid%FOUND) THEN
424 Close cur_rowid;
425 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TRO_UOO_FK');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 Return;
429 END IF;
430 Close cur_rowid;
431
432 END GET_FK_IGS_PS_UNIT_OFR_OPT;
433
434 PROCEDURE GET_UFK_IGS_PS_UNIT_OFR_OPT (
435 x_uoo_id IN NUMBER
436 ) AS
437
438 CURSOR cur_rowid IS
439 SELECT rowid
440 FROM IGS_PS_TCH_RESP_OVRD_ALL
441 WHERE uoo_id = x_uoo_id ;
442
443 lv_rowid cur_rowid%RowType;
444
445 BEGIN
446
447 Open cur_rowid;
448 Fetch cur_rowid INTO lv_rowid;
449 IF (cur_rowid%FOUND) THEN
450 Close cur_rowid;
451 Fnd_Message.Set_Name ('IGS', 'IGS_PS_TRO_UOO_FK');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 Return;
455 END IF;
456 Close cur_rowid;
457
458 END GET_UFK_IGS_PS_UNIT_OFR_OPT;
459
460 PROCEDURE Before_DML (
461 p_action IN VARCHAR2,
462 x_rowid IN VARCHAR2 DEFAULT NULL,
463 x_unit_cd IN VARCHAR2 DEFAULT NULL,
464 x_version_number IN NUMBER DEFAULT NULL,
465 x_cal_type IN VARCHAR2 DEFAULT NULL,
466 x_ci_sequence_number IN NUMBER DEFAULT NULL,
467 x_location_cd IN VARCHAR2 DEFAULT NULL,
468 x_unit_class IN VARCHAR2 DEFAULT NULL,
469 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
470 x_ou_start_dt IN DATE DEFAULT NULL,
471 x_uoo_id IN NUMBER DEFAULT NULL,
472 x_percentage IN NUMBER DEFAULT NULL,
473 x_creation_date IN DATE DEFAULT NULL,
474 x_created_by IN NUMBER DEFAULT NULL,
475 x_last_update_date IN DATE DEFAULT NULL,
476 x_last_updated_by IN NUMBER DEFAULT NULL,
477 x_last_update_login IN NUMBER DEFAULT NULL,
478 x_org_id IN NUMBER DEFAULT NULL
479 ) AS
480 BEGIN
481
482 Set_Column_Values (
483 p_action,
484 x_rowid,
485 x_unit_cd,
486 x_version_number,
487 x_cal_type,
488 x_ci_sequence_number,
489 x_location_cd,
490 x_unit_class,
491 x_org_unit_cd,
492 x_ou_start_dt,
493 x_uoo_id,
494 x_percentage,
495 x_creation_date,
496 x_created_by,
497 x_last_update_date,
498 x_last_updated_by,
499 x_last_update_login ,
500 x_org_id
501 );
502
503 IF (p_action = 'INSERT') THEN
504 -- Call all the procedures related to Before Insert.
505 BeforeRowInsert1 ( p_inserting => TRUE );
506 BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE );
507 IF Get_PK_For_Validation (
508 new_references.unit_cd,
509 new_references.version_number,
510 new_references.cal_type,
511 new_references.ci_sequence_number,
512 new_references.location_cd,
513 new_references.unit_class,
514 new_references.org_unit_cd,
515 new_references.ou_start_dt
516 ) THEN
517 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
518 IGS_GE_MSG_STACK.ADD;
519 App_Exception.Raise_Exception;
520 END IF;
521 Check_Constraints;
522 Check_Parent_Existance;
523 ELSIF (p_action = 'UPDATE') THEN
524 -- Call all the procedures related to Before Update.
525 BeforeRowInsertUpdateDelete2 ( p_updating => TRUE );
526 Check_Constraints;
527 Check_Parent_Existance;
528 ELSIF (p_action = 'DELETE') THEN
529 -- Call all the procedures related to Before Delete.
530 BeforeRowInsertUpdateDelete2 ( p_deleting => TRUE );
531 ELSIF (p_action = 'VALIDATE_INSERT') THEN
532 IF Get_PK_For_Validation (
533 new_references.unit_cd,
534 new_references.version_number,
535 new_references.cal_type,
536 new_references.ci_sequence_number,
537 new_references.location_cd,
538 new_references.unit_class,
539 new_references.org_unit_cd,
540 new_references.ou_start_dt
541 ) THEN
542 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
543 IGS_GE_MSG_STACK.ADD;
544 App_Exception.Raise_Exception;
545 END IF;
546 Check_Constraints;
547 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
548 Check_Constraints;
549 END IF;
550 END Before_DML;
551
552 PROCEDURE After_DML (
553 p_action IN VARCHAR2,
554 x_rowid IN VARCHAR2
555 ) AS
556 BEGIN
557
558 l_rowid := x_rowid;
559
560
561 IF (p_action = 'UPDATE') THEN
562 -- Call all the procedures related to After Update.
563 AfterRowUpdateDelete3 ( p_updating => TRUE );
564 ELSIF (p_action = 'DELETE') THEN
565 -- Call all the procedures related to After Delete.
566 AfterRowUpdateDelete3 ( p_deleting => TRUE );
567 END IF;
568
569 END After_DML;
570
571 procedure INSERT_ROW (
572 X_ROWID in out NOCOPY VARCHAR2,
573 X_UNIT_CD in VARCHAR2,
574 X_VERSION_NUMBER in NUMBER,
575 X_LOCATION_CD in VARCHAR2,
576 X_CI_SEQUENCE_NUMBER in NUMBER,
577 X_CAL_TYPE in VARCHAR2,
578 X_UNIT_CLASS in VARCHAR2,
579 X_OU_START_DT in DATE,
580 X_ORG_UNIT_CD in VARCHAR2,
581 X_UOO_ID in NUMBER,
582 X_PERCENTAGE in NUMBER,
583 X_MODE in VARCHAR2 default 'R',
584 X_ORG_ID in NUMBER
585 ) as
586 cursor C is select ROWID from IGS_PS_TCH_RESP_OVRD_ALL
587 where UNIT_CD = X_UNIT_CD
588 and VERSION_NUMBER = X_VERSION_NUMBER
589 and LOCATION_CD = X_LOCATION_CD
590 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
591 and CAL_TYPE = X_CAL_TYPE
592 and UNIT_CLASS = X_UNIT_CLASS
593 and OU_START_DT = X_OU_START_DT
594 and ORG_UNIT_CD = X_ORG_UNIT_CD;
595 X_LAST_UPDATE_DATE DATE;
596 X_LAST_UPDATED_BY NUMBER;
597 X_LAST_UPDATE_LOGIN NUMBER;
598 X_REQUEST_ID NUMBER;
599 X_PROGRAM_ID NUMBER;
600 X_PROGRAM_APPLICATION_ID NUMBER;
601 X_PROGRAM_UPDATE_DATE DATE;
602
603 begin
604 X_LAST_UPDATE_DATE := SYSDATE;
605 if(X_MODE = 'I') then
606 X_LAST_UPDATED_BY := 1;
607 X_LAST_UPDATE_LOGIN := 0;
608 elsif (X_MODE = 'R') then
609 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
610 if X_LAST_UPDATED_BY is NULL then
611 X_LAST_UPDATED_BY := -1;
612 end if;
613 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
614 if X_LAST_UPDATE_LOGIN is NULL then
615 X_LAST_UPDATE_LOGIN := -1;
616 end if;
617
618 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
619 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
620 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
621 IF (X_REQUEST_ID = -1) THEN
622 X_REQUEST_ID := NULL;
623 X_PROGRAM_ID := NULL;
624 X_PROGRAM_APPLICATION_ID := NULL;
625 X_PROGRAM_UPDATE_DATE := NULL;
626 ELSE
627 X_PROGRAM_UPDATE_DATE := SYSDATE;
628 END IF;
629
630 else
631 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
632 IGS_GE_MSG_STACK.ADD;
633 app_exception.raise_exception;
634 end if;
635
636 Before_DML(
637 p_action => 'INSERT',
638 x_rowid => X_ROWID,
639 x_unit_cd => X_UNIT_CD,
640 x_version_number => X_VERSION_NUMBER,
641 x_cal_type => X_CAL_TYPE,
642 x_location_cd => X_LOCATION_CD,
643 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
644 x_unit_class => X_UNIT_CLASS,
645 x_org_unit_cd => X_ORG_UNIT_CD,
646 x_ou_start_dt => X_OU_START_DT,
647 x_uoo_id => X_UOO_ID,
648 x_percentage => X_PERCENTAGE,
649 x_creation_date => X_LAST_UPDATE_DATE,
650 x_created_by => X_LAST_UPDATED_BY,
651 x_last_update_date => X_LAST_UPDATE_DATE,
652 x_last_updated_by => X_LAST_UPDATED_BY,
653 x_last_update_login => X_LAST_UPDATE_LOGIN,
654 x_org_id => igs_ge_gen_003.get_org_id
655 );
656 insert into IGS_PS_TCH_RESP_OVRD_ALL (
657 UNIT_CD,
658 VERSION_NUMBER,
659 CAL_TYPE,
660 CI_SEQUENCE_NUMBER,
661 LOCATION_CD,
662 UNIT_CLASS,
663 ORG_UNIT_CD,
664 OU_START_DT,
665 UOO_ID,
666 PERCENTAGE,
667 CREATION_DATE,
668 CREATED_BY,
669 LAST_UPDATE_DATE,
670 LAST_UPDATED_BY,
671 LAST_UPDATE_LOGIN,
672 REQUEST_ID,
673 PROGRAM_ID,
674 PROGRAM_APPLICATION_ID,
675 PROGRAM_UPDATE_DATE,
676 ORG_ID
677 ) values (
678 NEW_REFERENCES.UNIT_CD,
679 NEW_REFERENCES.VERSION_NUMBER,
680 NEW_REFERENCES.CAL_TYPE,
681 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
682 NEW_REFERENCES.LOCATION_CD,
683 NEW_REFERENCES.UNIT_CLASS,
684 NEW_REFERENCES.ORG_UNIT_CD,
685 NEW_REFERENCES.OU_START_DT,
686 NEW_REFERENCES.UOO_ID,
687 NEW_REFERENCES.PERCENTAGE,
688 X_LAST_UPDATE_DATE,
689 X_LAST_UPDATED_BY,
690 X_LAST_UPDATE_DATE,
691 X_LAST_UPDATED_BY,
692 X_LAST_UPDATE_LOGIN,
693 X_REQUEST_ID,
694 X_PROGRAM_ID,
695 X_PROGRAM_APPLICATION_ID,
696 X_PROGRAM_UPDATE_DATE,
697 NEW_REFERENCES.ORG_ID
698 );
699
700 open c;
701 fetch c into X_ROWID;
702 if (c%notfound) then
703 close c;
704 raise no_data_found;
705 end if;
706 close c;
707 After_DML (
708 p_action => 'INSERT',
709 x_rowid => X_ROWID
710 );
711
712 end INSERT_ROW;
713
714 procedure LOCK_ROW (
715 X_ROWID IN VARCHAR2,
716 X_UNIT_CD in VARCHAR2,
717 X_VERSION_NUMBER in NUMBER,
718 X_LOCATION_CD in VARCHAR2,
719 X_CI_SEQUENCE_NUMBER in NUMBER,
720 X_CAL_TYPE in VARCHAR2,
721 X_UNIT_CLASS in VARCHAR2,
722 X_OU_START_DT in DATE,
723 X_ORG_UNIT_CD in VARCHAR2,
724 X_UOO_ID in NUMBER,
725 X_PERCENTAGE in NUMBER
726
727 ) as
728 cursor c1 is select
729 UOO_ID,
730 PERCENTAGE
731
732 from IGS_PS_TCH_RESP_OVRD_ALL
733 where ROWID = X_ROWID
734 for update nowait;
735 tlinfo c1%rowtype;
736
737 begin
738 open c1;
739 fetch c1 into tlinfo;
740 if (c1%notfound) then
741 close c1;
742 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
743 IGS_GE_MSG_STACK.ADD;
744 app_exception.raise_exception;
745 return;
746 end if;
747 close c1;
748
749 if ( (tlinfo.UOO_ID = X_UOO_ID)
750 AND (tlinfo.PERCENTAGE = X_PERCENTAGE)
751
752 ) then
753 null;
754 else
755 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
756 IGS_GE_MSG_STACK.ADD;
757 app_exception.raise_exception;
758 end if;
759 return;
760 end LOCK_ROW;
761
762 procedure UPDATE_ROW (
763 X_ROWID IN VARCHAR2,
764 X_UNIT_CD in VARCHAR2,
765 X_VERSION_NUMBER in NUMBER,
766 X_LOCATION_CD in VARCHAR2,
767 X_CI_SEQUENCE_NUMBER in NUMBER,
768 X_CAL_TYPE in VARCHAR2,
769 X_UNIT_CLASS in VARCHAR2,
770 X_OU_START_DT in DATE,
771 X_ORG_UNIT_CD in VARCHAR2,
772 X_UOO_ID in NUMBER,
773 X_PERCENTAGE in NUMBER,
774 X_MODE in VARCHAR2 default 'R'
775
776 ) as
777 X_LAST_UPDATE_DATE DATE;
778 X_LAST_UPDATED_BY NUMBER;
779 X_LAST_UPDATE_LOGIN NUMBER;
780 X_REQUEST_ID NUMBER;
781 X_PROGRAM_ID NUMBER;
782 X_PROGRAM_APPLICATION_ID NUMBER;
783 X_PROGRAM_UPDATE_DATE DATE;
784
785 begin
786 X_LAST_UPDATE_DATE := SYSDATE;
787 if(X_MODE = 'I') then
788 X_LAST_UPDATED_BY := 1;
789 X_LAST_UPDATE_LOGIN := 0;
790 elsif (X_MODE = 'R') then
791 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
792 if X_LAST_UPDATED_BY is NULL then
793 X_LAST_UPDATED_BY := -1;
794 end if;
795 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
796 if X_LAST_UPDATE_LOGIN is NULL then
797 X_LAST_UPDATE_LOGIN := -1;
798 end if;
799 else
800 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
801 IGS_GE_MSG_STACK.ADD;
802 app_exception.raise_exception;
803 end if;
804 Before_DML(
805 p_action => 'UPDATE',
806 x_rowid => X_ROWID,
807 x_unit_cd => X_UNIT_CD,
808 x_version_number => X_VERSION_NUMBER,
809 x_cal_type => X_CAL_TYPE,
810 x_location_cd => X_LOCATION_CD,
811 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
812 x_unit_class => X_UNIT_CLASS,
813 x_org_unit_cd => X_ORG_UNIT_CD,
814 x_ou_start_dt => X_OU_START_DT,
815 x_uoo_id => X_UOO_ID,
816 x_percentage => X_PERCENTAGE,
817 x_creation_date => X_LAST_UPDATE_DATE,
818 x_created_by => X_LAST_UPDATED_BY,
819 x_last_update_date => X_LAST_UPDATE_DATE,
820 x_last_updated_by => X_LAST_UPDATED_BY,
821 x_last_update_login => X_LAST_UPDATE_LOGIN
822
823 );
824
825
826 if (X_MODE = 'R') then
827 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
828 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
829 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
830 if (X_REQUEST_ID = -1) then
831 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
832 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
833 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
834 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
835 else
836 X_PROGRAM_UPDATE_DATE := SYSDATE;
837 end if;
838 end if;
839
840 update IGS_PS_TCH_RESP_OVRD_ALL set
841 UOO_ID = NEW_REFERENCES.UOO_ID,
842 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
843 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
844 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
845 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
846 REQUEST_ID = X_REQUEST_ID,
847 PROGRAM_ID = X_PROGRAM_ID,
848 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
849 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
850
851 where ROWID = X_ROWID
852 ;
853 if (sql%notfound) then
854 raise no_data_found;
855 end if;
856 After_DML (
857 p_action => 'UPDATE',
858 x_rowid => X_ROWID
859 );
860
861 end UPDATE_ROW;
862
863 procedure ADD_ROW (
864 X_ROWID in out NOCOPY VARCHAR2,
865 X_UNIT_CD in VARCHAR2,
866 X_VERSION_NUMBER in NUMBER,
867 X_LOCATION_CD in VARCHAR2,
868 X_CI_SEQUENCE_NUMBER in NUMBER,
869 X_CAL_TYPE in VARCHAR2,
870 X_UNIT_CLASS in VARCHAR2,
871 X_OU_START_DT in DATE,
872 X_ORG_UNIT_CD in VARCHAR2,
873 X_UOO_ID in NUMBER,
874 X_PERCENTAGE in NUMBER,
875 X_MODE in VARCHAR2 default 'R',
876 X_ORG_ID in NUMBER
877 ) as
878 cursor c1 is select rowid from IGS_PS_TCH_RESP_OVRD_ALL
879 where UNIT_CD = X_UNIT_CD
880 and VERSION_NUMBER = X_VERSION_NUMBER
881 and LOCATION_CD = X_LOCATION_CD
882 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
883 and CAL_TYPE = X_CAL_TYPE
884 and UNIT_CLASS = X_UNIT_CLASS
885 and OU_START_DT = X_OU_START_DT
886 and ORG_UNIT_CD = X_ORG_UNIT_CD ;
887
888 begin
889 open c1;
890 fetch c1 into X_ROWID;
891 if (c1%notfound) then
892 close c1;
893 INSERT_ROW (
894 X_ROWID,
895 X_UNIT_CD,
896 X_VERSION_NUMBER,
897 X_LOCATION_CD,
898 X_CI_SEQUENCE_NUMBER,
899 X_CAL_TYPE,
900 X_UNIT_CLASS,
901 X_OU_START_DT,
902 X_ORG_UNIT_CD,
903 X_UOO_ID,
904 X_PERCENTAGE,
905 X_MODE,
906 X_ORG_ID);
907 return;
908 end if;
909 close c1;
910 UPDATE_ROW (
911 X_ROWID,
912 X_UNIT_CD,
913 X_VERSION_NUMBER,
914 X_LOCATION_CD,
915 X_CI_SEQUENCE_NUMBER,
916 X_CAL_TYPE,
917 X_UNIT_CLASS,
918 X_OU_START_DT,
919 X_ORG_UNIT_CD,
920 X_UOO_ID,
921 X_PERCENTAGE,
922 X_MODE
923 );
924 end ADD_ROW;
925
926 procedure DELETE_ROW (
927 X_ROWID in VARCHAR2
928 ) as
929 begin
930 Before_DML (
931 p_action => 'DELETE',
932 x_rowid => X_ROWID
933 );
934
935 delete from IGS_PS_TCH_RESP_OVRD_ALL
936 where ROWID = X_ROWID;
937 if (sql%notfound) then
938 raise no_data_found;
939 end if;
940 After_DML (
941 p_action => 'DELETE',
942 x_rowid => X_ROWID
943 );
944
945 end DELETE_ROW;
946
947 end IGS_PS_TCH_RESP_OVRD_PKG;