[Home] [Help]
PACKAGE BODY: APPS.PSP_EFFORT_REPORTS_PKG
Source
1 package body PSP_EFFORT_REPORTS_PKG as
2 /* $Header: PSPERREB.pls 115.6 2002/11/18 12:44:18 lveerubh ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_EFFORT_REPORT_ID in NUMBER,
6 X_VERSION_NUM in NUMBER,
7 X_PERSON_ID in NUMBER,
8 X_EFFECTIVE_START_DATE in DATE,
9 X_EFFECTIVE_END_DATE in DATE,
10 X_VERSION_CREATION_DATE in DATE,
11 X_VERSION_REASON_CODE in VARCHAR2,
12 X_MESSAGE_ID in NUMBER,
13 X_REPORT_COMMENT in VARCHAR2,
14 X_REPORT_CREATION_DATE in DATE,
15 X_STATUS_CODE in VARCHAR2,
16 X_TEMPLATE_ID in NUMBER,
17 X_PREV_EFFORT_REPORT_ID in NUMBER,
18 X_BUSINESS_GROUP_ID in NUMBER,
19 X_SET_OF_BOOKS_ID in NUMBER,
20 X_MODE in VARCHAR2 default 'R'
21 ) is
22 cursor C is select ROWID from PSP_EFFORT_REPORTS
23 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
24 and VERSION_NUM = X_VERSION_NUM;
25 X_LAST_UPDATE_DATE DATE;
26 X_LAST_UPDATED_BY NUMBER;
27 X_LAST_UPDATE_LOGIN NUMBER;
28 begin
29 X_LAST_UPDATE_DATE := SYSDATE;
30 if(X_MODE = 'I') then
31 X_LAST_UPDATED_BY := 1;
32 X_LAST_UPDATE_LOGIN := 0;
33 elsif (X_MODE = 'R') then
34 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
35 if X_LAST_UPDATED_BY is NULL then
36 X_LAST_UPDATED_BY := -1;
37 end if;
38 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
39 if X_LAST_UPDATE_LOGIN is NULL then
40 X_LAST_UPDATE_LOGIN := -1;
41 end if;
42 else
43 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
44 app_exception.raise_exception;
45 end if;
46 insert into PSP_EFFORT_REPORTS (
47 EFFORT_REPORT_ID,
48 VERSION_NUM,
49 PERSON_ID,
50 EFFECTIVE_START_DATE,
51 EFFECTIVE_END_DATE,
52 VERSION_CREATION_DATE,
53 VERSION_REASON_CODE,
54 MESSAGE_ID,
55 REPORT_COMMENT,
56 REPORT_CREATION_DATE,
57 STATUS_CODE,
58 TEMPLATE_ID,
59 PREV_EFFORT_REPORT_ID,
60 BUSINESS_GROUP_ID,
61 SET_OF_BOOKS_ID,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_EFFORT_REPORT_ID,
69 X_VERSION_NUM,
70 X_PERSON_ID,
71 X_EFFECTIVE_START_DATE,
72 X_EFFECTIVE_END_DATE,
73 X_VERSION_CREATION_DATE,
74 X_VERSION_REASON_CODE,
75 X_MESSAGE_ID,
76 X_REPORT_COMMENT,
77 X_REPORT_CREATION_DATE,
78 X_STATUS_CODE,
79 X_TEMPLATE_ID,
80 X_PREV_EFFORT_REPORT_ID,
81 X_BUSINESS_GROUP_ID,
82 X_SET_OF_BOOKS_ID,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_LAST_UPDATE_DATE,
86 X_LAST_UPDATED_BY,
87 X_LAST_UPDATE_LOGIN
88 );
89
90 open c;
91 fetch c into X_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 X_EFFORT_REPORT_ID in NUMBER,
102 X_VERSION_NUM in NUMBER,
103 X_PERSON_ID in NUMBER,
104 X_EFFECTIVE_START_DATE in DATE,
105 X_EFFECTIVE_END_DATE in DATE,
106 X_VERSION_CREATION_DATE in DATE,
107 X_VERSION_REASON_CODE in VARCHAR2,
108 X_MESSAGE_ID in NUMBER,
109 X_REPORT_COMMENT in VARCHAR2,
110 X_REPORT_CREATION_DATE in DATE,
111 X_STATUS_CODE in VARCHAR2,
112 X_TEMPLATE_ID in NUMBER,
113 X_BUSINESS_GROUP_ID in NUMBER,
114 X_SET_OF_BOOKS_ID in NUMBER,
115 X_PREV_EFFORT_REPORT_ID in NUMBER
116 ) is
117 cursor c1 is select
118 PERSON_ID,
119 EFFECTIVE_START_DATE,
120 EFFECTIVE_END_DATE,
121 VERSION_CREATION_DATE,
122 VERSION_REASON_CODE,
123 MESSAGE_ID,
124 REPORT_COMMENT,
125 REPORT_CREATION_DATE,
126 STATUS_CODE,
127 TEMPLATE_ID,
128 PREV_EFFORT_REPORT_ID
129 from PSP_EFFORT_REPORTS
130 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
131 and VERSION_NUM = X_VERSION_NUM
132 for update of EFFORT_REPORT_ID nowait;
133 tlinfo c1%rowtype;
134
135 begin
136 open c1;
137 fetch c1 into tlinfo;
138 if (c1%notfound) then
139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
140 app_exception.raise_exception;
141 close c1;
142 return;
143 end if;
144 close c1;
145
146 if ( (tlinfo.PERSON_ID = X_PERSON_ID)
147 AND ((tlinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
148 OR ((tlinfo.EFFECTIVE_START_DATE is null)
149 AND (X_EFFECTIVE_START_DATE is null)))
150 AND ((tlinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
151 OR ((tlinfo.EFFECTIVE_END_DATE is null)
152 AND (X_EFFECTIVE_END_DATE is null)))
153 AND (tlinfo.VERSION_CREATION_DATE = X_VERSION_CREATION_DATE)
154 AND ((tlinfo.VERSION_REASON_CODE = X_VERSION_REASON_CODE)
155 OR ((tlinfo.VERSION_REASON_CODE is null)
156 AND (X_VERSION_REASON_CODE is null)))
157 AND ((tlinfo.MESSAGE_ID = X_MESSAGE_ID)
158 OR ((tlinfo.MESSAGE_ID is null)
159 AND (X_MESSAGE_ID is null)))
160 AND ((tlinfo.REPORT_COMMENT = X_REPORT_COMMENT)
161 OR ((tlinfo.REPORT_COMMENT is null)
162 AND (X_REPORT_COMMENT is null)))
163 AND ((tlinfo.REPORT_CREATION_DATE = X_REPORT_CREATION_DATE)
164 OR ((tlinfo.REPORT_CREATION_DATE is null)
165 AND (X_REPORT_CREATION_DATE is null)))
166 AND (tlinfo.STATUS_CODE = X_STATUS_CODE)
167 AND ((tlinfo.TEMPLATE_ID = X_TEMPLATE_ID)
168 OR ((tlinfo.TEMPLATE_ID is null)
169 AND (X_TEMPLATE_ID is null)))
170 AND ((tlinfo.PREV_EFFORT_REPORT_ID = X_PREV_EFFORT_REPORT_ID)
171 OR ((tlinfo.PREV_EFFORT_REPORT_ID is null)
172 AND (X_PREV_EFFORT_REPORT_ID is null)))
173 ) then
174 null;
175 else
176 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
177 app_exception.raise_exception;
178 end if;
179 return;
180 end LOCK_ROW;
181
182 procedure UPDATE_ROW (
183 X_EFFORT_REPORT_ID in NUMBER,
184 X_VERSION_NUM in NUMBER,
185 X_PERSON_ID in NUMBER,
186 X_EFFECTIVE_START_DATE in DATE,
187 X_EFFECTIVE_END_DATE in DATE,
188 X_VERSION_CREATION_DATE in DATE,
189 X_VERSION_REASON_CODE in VARCHAR2,
190 X_MESSAGE_ID in NUMBER,
191 X_REPORT_COMMENT in VARCHAR2,
192 X_REPORT_CREATION_DATE in DATE,
193 X_STATUS_CODE in VARCHAR2,
194 X_TEMPLATE_ID in NUMBER,
195 X_PREV_EFFORT_REPORT_ID in NUMBER,
196 X_BUSINESS_GROUP_ID in NUMBER,
197 X_SET_OF_BOOKS_ID in NUMBER,
198 X_MODE in VARCHAR2 default 'R'
199 ) is
200 X_LAST_UPDATE_DATE DATE;
201 X_LAST_UPDATED_BY NUMBER;
202 X_LAST_UPDATE_LOGIN NUMBER;
203 begin
204 X_LAST_UPDATE_DATE := SYSDATE;
205 if(X_MODE = 'I') then
206 X_LAST_UPDATED_BY := 1;
207 X_LAST_UPDATE_LOGIN := 0;
208 elsif (X_MODE = 'R') then
209 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
210 if X_LAST_UPDATED_BY is NULL then
211 X_LAST_UPDATED_BY := -1;
212 end if;
213 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
214 if X_LAST_UPDATE_LOGIN is NULL then
215 X_LAST_UPDATE_LOGIN := -1;
216 end if;
217 else
218 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
219 app_exception.raise_exception;
220 end if;
221 update PSP_EFFORT_REPORTS set
222 PERSON_ID = X_PERSON_ID,
223 EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
224 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
225 VERSION_CREATION_DATE = X_VERSION_CREATION_DATE,
226 VERSION_REASON_CODE = X_VERSION_REASON_CODE,
227 MESSAGE_ID = X_MESSAGE_ID,
228 REPORT_COMMENT = X_REPORT_COMMENT,
229 REPORT_CREATION_DATE = X_REPORT_CREATION_DATE,
230 STATUS_CODE = X_STATUS_CODE,
231 TEMPLATE_ID = X_TEMPLATE_ID,
232 PREV_EFFORT_REPORT_ID = X_PREV_EFFORT_REPORT_ID,
233 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
234 SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
235 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
236 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
237 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
238 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
239 and VERSION_NUM = X_VERSION_NUM
240 ;
241 ---if (sql%notfound) then
242 ---raise no_data_found;
243 ---end if;
244 commit;
245 end UPDATE_ROW;
246
247 procedure ADD_ROW (
248 X_ROWID in out NOCOPY VARCHAR2,
249 X_EFFORT_REPORT_ID in NUMBER,
250 X_VERSION_NUM in NUMBER,
251 X_PERSON_ID in NUMBER,
252 X_EFFECTIVE_START_DATE in DATE,
253 X_EFFECTIVE_END_DATE in DATE,
254 X_VERSION_CREATION_DATE in DATE,
255 X_VERSION_REASON_CODE in VARCHAR2,
256 X_MESSAGE_ID in NUMBER,
257 X_REPORT_COMMENT in VARCHAR2,
258 X_REPORT_CREATION_DATE in DATE,
259 X_STATUS_CODE in VARCHAR2,
260 X_TEMPLATE_ID in NUMBER,
261 X_PREV_EFFORT_REPORT_ID in NUMBER,
262 X_BUSINESS_GROUP_ID in NUMBER,
263 X_SET_OF_BOOKS_ID in NUMBER,
264 X_MODE in VARCHAR2 default 'R'
265 ) is
266 cursor c1 is select rowid from PSP_EFFORT_REPORTS
267 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
268 and VERSION_NUM = X_VERSION_NUM
269 ;
270 dummy c1%rowtype;
271 begin
272 open c1;
273 fetch c1 into dummy;
274 if (c1%notfound) then
275 close c1;
276 INSERT_ROW (
277 X_ROWID,
278 X_EFFORT_REPORT_ID,
279 X_VERSION_NUM,
280 X_PERSON_ID,
281 X_EFFECTIVE_START_DATE,
282 X_EFFECTIVE_END_DATE,
283 X_VERSION_CREATION_DATE,
284 X_VERSION_REASON_CODE,
285 X_MESSAGE_ID,
286 X_REPORT_COMMENT,
287 X_REPORT_CREATION_DATE,
288 X_STATUS_CODE,
289 X_TEMPLATE_ID,
290 X_PREV_EFFORT_REPORT_ID,
291 X_BUSINESS_GROUP_ID,
292 X_SET_OF_BOOKS_ID,
293 X_MODE);
294 return;
295 end if;
296 close c1;
297 UPDATE_ROW (
298 X_EFFORT_REPORT_ID,
299 X_VERSION_NUM,
300 X_PERSON_ID,
301 X_EFFECTIVE_START_DATE,
302 X_EFFECTIVE_END_DATE,
303 X_VERSION_CREATION_DATE,
304 X_VERSION_REASON_CODE,
305 X_MESSAGE_ID,
306 X_REPORT_COMMENT,
307 X_REPORT_CREATION_DATE,
308 X_STATUS_CODE,
309 X_TEMPLATE_ID,
310 X_PREV_EFFORT_REPORT_ID,
311 X_BUSINESS_GROUP_ID,
312 X_SET_OF_BOOKS_ID,
313 X_MODE);
314 end ADD_ROW;
315
316 procedure DELETE_ROW (
317 X_EFFORT_REPORT_ID in NUMBER,
318 X_VERSION_NUM in NUMBER
319 ) is
320 begin
321 delete from PSP_EFFORT_REPORTS
322 where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
323 and VERSION_NUM = X_VERSION_NUM;
324 ---if (sql%notfound) then
325 ---raise no_data_found;
326 ---end if;
327 end DELETE_ROW;
328
329 end PSP_EFFORT_REPORTS_PKG;