[Home] [Help]
PACKAGE BODY: APPS.IGW_CREATE_PROPOSAL_TBH
Source
1 package body IGW_CREATE_PROPOSAL_TBH as
2 /* $Header: igwtcprb.pls 115.4 2002/11/14 18:44:11 vmedikon ship $ */
3 procedure INSERT_ROW (
4 X_ROWID out NOCOPY rowid,
5 X_PROPOSAL_ID in NUMBER,
6 X_SPONSOR_ACTION_CODE in VARCHAR2,
7 X_SPONSOR_ACTION_DATE in DATE,
8 X_AWARD_AMOUNT in NUMBER,
9 X_PROPOSAL_NUMBER in VARCHAR2,
10 X_LEAD_ORGANIZATION_ID in NUMBER,
11 X_PROPOSAL_STATUS in VARCHAR2,
12 X_PROPOSAL_START_DATE in DATE,
13 X_PROPOSAL_END_DATE in DATE,
14 X_PROPOSAL_TITLE in VARCHAR2,
15 X_PROPOSAL_TYPE_CODE in VARCHAR2,
16 X_AWARD_NUMBER in VARCHAR2,
17 X_ORIGINAL_PROPOSAL_NUMBER in VARCHAR2,
18 X_ORIGINAL_AWARD_NUMBER in VARCHAR2,
19 X_ORIGINAL_PROPOSAL_START_DATE in DATE,
20 X_ORIGINAL_PROPOSAL_END_DATE in DATE,
21 X_ACTIVITY_TYPE_CODE in VARCHAR2,
22 X_SPONSOR_ID in NUMBER,
23 X_FUNDING_SPONSOR_UNIT in VARCHAR2,
24 X_ORIGINAL_SPONSOR_ID in NUMBER,
25 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
26 X_NOTICE_OF_OPPORTUNITY_CODE in VARCHAR2,
27 X_PROGRAM_NUMBER in VARCHAR2,
28 X_PROGRAM_TITLE in VARCHAR2,
29 X_PROGRAM_URL in VARCHAR2,
30 X_SUBMITTING_ORGANIZATION_ID in NUMBER,
31 X_SIGNING_OFFICIAL_ID in NUMBER,
32 X_ADMIN_OFFICIAL_ID in NUMBER,
33 X_DEADLINE_DATE in DATE,
34 X_DEADLINE_TYPE in VARCHAR2,
35 X_LETTER_OF_INTENT_DUE_DATE in DATE,
36 X_ATTRIBUTE_CATEGORY in VARCHAR2,
37 X_ATTRIBUTE1 in VARCHAR2,
38 X_ATTRIBUTE2 in VARCHAR2,
39 X_ATTRIBUTE3 in VARCHAR2,
40 X_ATTRIBUTE4 in VARCHAR2,
41 X_ATTRIBUTE5 in VARCHAR2,
42 X_ATTRIBUTE6 in VARCHAR2,
43 X_ATTRIBUTE7 in VARCHAR2,
44 X_ATTRIBUTE8 in VARCHAR2,
45 X_ATTRIBUTE9 in VARCHAR2,
46 X_ATTRIBUTE10 in VARCHAR2,
47 X_ATTRIBUTE11 in VARCHAR2,
48 X_ATTRIBUTE12 in VARCHAR2,
49 X_ATTRIBUTE13 in VARCHAR2,
50 X_ATTRIBUTE14 in VARCHAR2,
51 X_ATTRIBUTE15 in VARCHAR2,
52 X_MODE in VARCHAR2 default 'R',
53 X_RETURN_STATUS OUT NOCOPY VARCHAR2
54 ) is
55 cursor C is select ROWID from IGW_PROPOSALS_ALL
56 where PROPOSAL_ID = X_PROPOSAL_ID;
57 X_LAST_UPDATE_DATE DATE;
58 X_LAST_UPDATED_BY NUMBER;
59 X_LAST_UPDATE_LOGIN NUMBER;
60 begin
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62 X_LAST_UPDATE_DATE := SYSDATE;
63 if(X_MODE = 'I') then
64 X_LAST_UPDATED_BY := 1;
65 X_LAST_UPDATE_LOGIN := 0;
66 elsif (X_MODE = 'R') then
67 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
68 if X_LAST_UPDATED_BY is NULL then
69 X_LAST_UPDATED_BY := -1;
70 end if;
71 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
72 if X_LAST_UPDATE_LOGIN is NULL then
73 X_LAST_UPDATE_LOGIN := -1;
74 end if;
75 else
76 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
77 app_exception.raise_exception;
78 end if;
79 insert into IGW_PROPOSALS_ALL (
80 SPONSOR_ACTION_CODE,
81 SPONSOR_ACTION_DATE,
82 AWARD_AMOUNT,
83 PROPOSAL_ID,
84 PROPOSAL_NUMBER,
85 LEAD_ORGANIZATION_ID,
86 PROPOSAL_STATUS,
87 PROPOSAL_START_DATE,
88 PROPOSAL_END_DATE,
89 PROPOSAL_TITLE,
90 PROPOSAL_TYPE_CODE,
91 AWARD_NUMBER,
92 ORIGINAL_PROPOSAL_NUMBER,
93 ORIGINAL_AWARD_NUMBER,
94 ORIGINAL_PROPOSAL_START_DATE,
95 ORIGINAL_PROPOSAL_END_DATE,
96 ACTIVITY_TYPE_CODE,
97 SPONSOR_ID,
98 FUNDING_SPONSOR_UNIT,
99 ORIGINAL_SPONSOR_ID,
100 SPONSOR_PROPOSAL_NUMBER,
101 NOTICE_OF_OPPORTUNITY_CODE,
102 PROGRAM_NUMBER,
103 PROGRAM_TITLE,
104 PROGRAM_URL,
105 SUBMITTING_ORGANIZATION_ID,
106 SIGNING_OFFICIAL_ID,
107 ADMIN_OFFICIAL_ID,
108 DEADLINE_DATE,
109 DEADLINE_TYPE,
110 LETTER_OF_INTENT_DUE_DATE,
111 RECORD_VERSION_NUMBER,
112 ATTRIBUTE_CATEGORY,
113 ATTRIBUTE1,
114 ATTRIBUTE2,
115 ATTRIBUTE3,
116 ATTRIBUTE4,
117 ATTRIBUTE5,
118 ATTRIBUTE6,
119 ATTRIBUTE7,
120 ATTRIBUTE8,
121 ATTRIBUTE9,
122 ATTRIBUTE10,
123 ATTRIBUTE11,
124 ATTRIBUTE12,
125 ATTRIBUTE13,
126 ATTRIBUTE14,
127 ATTRIBUTE15,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN
133 ) values (
134 X_SPONSOR_ACTION_CODE,
135 X_SPONSOR_ACTION_DATE,
136 X_AWARD_AMOUNT,
137 X_PROPOSAL_ID,
138 X_PROPOSAL_NUMBER,
139 X_LEAD_ORGANIZATION_ID,
140 X_PROPOSAL_STATUS,
141 X_PROPOSAL_START_DATE,
142 X_PROPOSAL_END_DATE,
143 X_PROPOSAL_TITLE,
144 X_PROPOSAL_TYPE_CODE,
145 X_AWARD_NUMBER,
146 X_ORIGINAL_PROPOSAL_NUMBER,
147 X_ORIGINAL_AWARD_NUMBER,
148 X_ORIGINAL_PROPOSAL_START_DATE,
149 X_ORIGINAL_PROPOSAL_END_DATE,
150 X_ACTIVITY_TYPE_CODE,
151 X_SPONSOR_ID,
152 X_FUNDING_SPONSOR_UNIT,
153 X_ORIGINAL_SPONSOR_ID,
154 X_SPONSOR_PROPOSAL_NUMBER,
155 X_NOTICE_OF_OPPORTUNITY_CODE,
156 X_PROGRAM_NUMBER,
157 X_PROGRAM_TITLE,
158 X_PROGRAM_URL,
159 X_SUBMITTING_ORGANIZATION_ID,
160 X_SIGNING_OFFICIAL_ID,
161 X_ADMIN_OFFICIAL_ID,
162 X_DEADLINE_DATE,
163 X_DEADLINE_TYPE,
164 X_LETTER_OF_INTENT_DUE_DATE,
165 1,
166 X_ATTRIBUTE_CATEGORY,
167 X_ATTRIBUTE1,
168 X_ATTRIBUTE2,
169 X_ATTRIBUTE3,
170 X_ATTRIBUTE4,
171 X_ATTRIBUTE5,
172 X_ATTRIBUTE6,
173 X_ATTRIBUTE7,
174 X_ATTRIBUTE8,
175 X_ATTRIBUTE9,
176 X_ATTRIBUTE10,
177 X_ATTRIBUTE11,
178 X_ATTRIBUTE12,
179 X_ATTRIBUTE13,
180 X_ATTRIBUTE14,
181 X_ATTRIBUTE15,
182 X_LAST_UPDATE_DATE,
183 X_LAST_UPDATED_BY,
184 X_LAST_UPDATE_DATE,
185 X_LAST_UPDATED_BY,
186 X_LAST_UPDATE_LOGIN
187 );
188
189 open c;
190 fetch c into X_ROWID;
191 if (c%notfound) then
192 close c;
193 raise no_data_found;
194 end if;
195 close c;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_CREATE_PROPOSAL_TBH'
200 ,p_procedure_name => 'INSERT_ROW' );
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
202 RAISE;
203
204 end INSERT_ROW;
205
206 -----------------------------------------------------------------------
207
208 procedure DELETE_ROW (
209 x_rowid in rowid
210 ,p_proposal_id in number
211 ,p_record_version_number in number
212 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
213 ) is
214
215 l_row_id ROWID := x_rowid;
216 l_record_version_number NUMBER;
217
218 CURSOR get_row_id IS
219 SELECT rowid
220 FROM igw_proposals_all
221 WHERE proposal_id = p_proposal_id;
222
223 begin
224
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226
227 IF l_row_id IS NULL THEN
228 OPEN get_row_id;
229 FETCH get_row_id INTO l_row_id;
230 CLOSE get_row_id;
231 END IF;
232
233 delete from IGW_PROPOSALS_ALL
234 where rowid = l_row_id
235 and record_version_number = p_record_version_number;
236
237 if (sql%notfound) then
238 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
239 FND_MSG_PUB.Add;
240 end if;
241 EXCEPTION
242 WHEN OTHERS THEN
243 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_CREATE_PROPOSAL_TBH'
244 ,p_procedure_name => 'DELETE_ROW' );
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
246 RAISE;
247 end DELETE_ROW;
248
249 ---------------------------------------------------------------------------------
250
251 procedure UPDATE_SPONSOR_ACTION (
252 X_ROWID in rowid,
253 X_PROPOSAL_ID in NUMBER,
254 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
255 X_FUNDED_DIRECT_COST in NUMBER,
256 X_FUNDED_INDIRECT_COST in NUMBER,
257 X_RECORD_VERSION_NUMBER IN NUMBER,
258 X_MODE in VARCHAR2 default 'R',
259 X_RETURN_STATUS OUT NOCOPY VARCHAR2
260 ) is
261 X_LAST_UPDATE_DATE DATE;
262 X_LAST_UPDATED_BY NUMBER;
263 X_LAST_UPDATE_LOGIN NUMBER;
264
265 l_row_id ROWID := x_rowid;
266 l_record_version_number NUMBER;
267
268 CURSOR get_row_id IS
269 SELECT rowid
270 FROM igw_proposals_all
271 WHERE proposal_id = x_proposal_id;
272
273 begin
274
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276
277 IF l_row_id IS NULL THEN
278 OPEN get_row_id;
279 FETCH get_row_id INTO l_row_id;
280 CLOSE get_row_id;
281 END IF;
282
283 X_LAST_UPDATE_DATE := SYSDATE;
284 if(X_MODE = 'I') then
285 X_LAST_UPDATED_BY := 1;
286 X_LAST_UPDATE_LOGIN := 0;
287 elsif (X_MODE = 'R') then
288 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
289 if X_LAST_UPDATED_BY is NULL then
290 X_LAST_UPDATED_BY := -1;
291 end if;
292 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
293 if X_LAST_UPDATE_LOGIN is NULL then
294 X_LAST_UPDATE_LOGIN := -1;
295 end if;
296 else
297 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
298 app_exception.raise_exception;
299 end if;
300
301 update IGW_PROPOSALS_ALL set
302 SPONSOR_PROPOSAL_NUMBER = X_SPONSOR_PROPOSAL_NUMBER,
303 FUNDED_DIRECT_COST = X_FUNDED_DIRECT_COST,
304 FUNDED_INDIRECT_COST = X_FUNDED_INDIRECT_COST,
305 RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER +1,
306 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
307 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
308 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
309 where rowid = l_row_id
310 AND RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER;
311
312 if (sql%notfound) then
313 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
314 FND_MSG_PUB.Add;
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 end if;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_CREATE_PROPOSAL_TBH'
321 ,p_procedure_name => 'UPDATE_SPONSOR_ACTION' );
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
323 RAISE;
324
325 end UPDATE_SPONSOR_ACTION;
326
327 ---------------------------------------------------------------------------------
328
329 end IGW_CREATE_PROPOSAL_TBH;