[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_SCIENCE_CODES_PKG
Source
1 package body IGW_PROP_SCIENCE_CODES_PKG as
2 /* $Header: igwpr70b.pls 115.7 2002/03/28 19:13:33 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_PROPOSAL_ID in NUMBER,
6 X_SCIENCE_CODE in VARCHAR2,
7 X_MODE in VARCHAR2 default 'R'
8 ) is
9 cursor C is select ROWID from IGW_PROP_SCIENCE_CODES
10 where PROPOSAL_ID = X_PROPOSAL_ID
11 and SCIENCE_CODE = X_SCIENCE_CODE;
12 X_LAST_UPDATE_DATE DATE;
13 X_LAST_UPDATED_BY NUMBER;
14 X_LAST_UPDATE_LOGIN NUMBER;
15 begin
16 X_LAST_UPDATE_DATE := SYSDATE;
17 if(X_MODE = 'I') then
18 X_LAST_UPDATED_BY := 1;
19 X_LAST_UPDATE_LOGIN := 0;
20 elsif (X_MODE = 'R') then
21 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
22 if X_LAST_UPDATED_BY is NULL then
23 X_LAST_UPDATED_BY := -1;
24 end if;
25 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
26 if X_LAST_UPDATE_LOGIN is NULL then
27 X_LAST_UPDATE_LOGIN := -1;
28 end if;
29 else
30 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
31 app_exception.raise_exception;
32 end if;
33 insert into IGW_PROP_SCIENCE_CODES (
34 PROPOSAL_ID,
35 SCIENCE_CODE,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_PROPOSAL_ID,
43 X_SCIENCE_CODE,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY,
48 X_LAST_UPDATE_LOGIN
49 );
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_ROWID in VARCHAR2,
63 X_PROPOSAL_ID in NUMBER,
64 X_SCIENCE_CODE in VARCHAR2
65 ) is
66 cursor c1 is select *
67 from IGW_PROP_SCIENCE_CODES
68 where ROWID = X_ROWID
69 for update of PROPOSAL_ID nowait;
70 tlinfo c1%rowtype;
71
72 begin
73 open c1;
74 fetch c1 into tlinfo;
75 if (c1%notfound) then
76 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
77 app_exception.raise_exception;
78 close c1;
79 return;
80 end if;
81 close c1;
82 if (
83 ( tlinfo.proposal_id = X_PROPOSAL_ID )
84 and
85 ( tlinfo.science_code = X_SCIENCE_CODE )
86 ) then
87 null;
88 else
89 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
90 app_exception.raise_exception;
91 end if;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_ROWID in VARCHAR2,
97 X_PROPOSAL_ID in NUMBER,
98 X_SCIENCE_CODE in VARCHAR2,
99 X_MODE in VARCHAR2 default 'R'
100 ) is
101 X_LAST_UPDATE_DATE DATE;
102 X_LAST_UPDATED_BY NUMBER;
103 X_LAST_UPDATE_LOGIN NUMBER;
104 begin
105 X_LAST_UPDATE_DATE := SYSDATE;
106 if(X_MODE = 'I') then
107 X_LAST_UPDATED_BY := 1;
108 X_LAST_UPDATE_LOGIN := 0;
109 elsif (X_MODE = 'R') then
110 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
111 if X_LAST_UPDATED_BY is NULL then
112 X_LAST_UPDATED_BY := -1;
113 end if;
114 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
115 if X_LAST_UPDATE_LOGIN is NULL then
116 X_LAST_UPDATE_LOGIN := -1;
117 end if;
118 else
119 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
120 app_exception.raise_exception;
121 end if;
122 update IGW_PROP_SCIENCE_CODES set
123 PROPOSAL_ID = X_PROPOSAL_ID,
124 SCIENCE_CODE = X_SCIENCE_CODE,
125 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
126 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
127 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
128 where ROWID = X_ROWID;
129
130 if (sql%notfound) then
131 raise no_data_found;
132 end if;
133 end UPDATE_ROW;
134
135 procedure ADD_ROW (
136 X_ROWID in out VARCHAR2,
137 X_PROPOSAL_ID in NUMBER,
138 X_SCIENCE_CODE in VARCHAR2,
139 X_MODE in VARCHAR2 default 'R'
140 ) is
141 CURSOR C1 IS SELECT 'X' FROM IGW_PROP_SCIENCE_CODES
142 where PROPOSAL_ID = X_PROPOSAL_ID
143 and SCIENCE_CODE = X_SCIENCE_CODE
144 ;
145 dummy c1%rowtype;
146 begin
147 open c1;
148 fetch c1 into dummy;
149 if (c1%notfound) then
150 close c1;
151 INSERT_ROW (
152 X_ROWID,
153 X_PROPOSAL_ID,
154 X_SCIENCE_CODE,
155 X_MODE);
156 return;
157 end if;
158 close c1;
159 UPDATE_ROW (
160 X_ROWID,
161 X_PROPOSAL_ID,
162 X_SCIENCE_CODE,
163 X_MODE);
164 end ADD_ROW;
165
166 procedure DELETE_ROW (
167 X_ROWID in VARCHAR2
168 ) is
169 begin
170 delete from IGW_PROP_SCIENCE_CODES
171 where ROWID = X_ROWID;
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175 end DELETE_ROW;
176
177 end IGW_PROP_SCIENCE_CODES_PKG;