[Home] [Help]
PACKAGE BODY: APPS.PSP_EFFORT_REPORT_PERIODS_PKG
Source
1 package body PSP_EFFORT_REPORT_PERIODS_PKG as
2 /* $Header: PSPERPEB.pls 115.4 2002/11/18 12:39:07 lveerubh ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
6 X_DESCRIPTION in VARCHAR2,
7 X_START_DATE_ACTIVE in DATE,
8 X_END_DATE_ACTIVE in DATE,
9 X_EFFORT_REPORT_PERIOD_PREFIX in VARCHAR2,
10 X_EFFORT_REPORT_PERIOD_TYPE in VARCHAR2,
11 X_EFFORT_REPORT_PERIOD_YEAR in NUMBER,
12 X_EFFORT_REPORT_PERIOD_NUMBER in NUMBER,
13 X_MODE in VARCHAR2 default 'R'
14 ) is
15 cursor C is select ROWID from PSP_EFFORT_REPORT_PERIODS
16 where EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME;
17 X_LAST_UPDATE_DATE DATE;
18 X_LAST_UPDATED_BY NUMBER;
19 X_LAST_UPDATE_LOGIN NUMBER;
20 begin
21 X_LAST_UPDATE_DATE := SYSDATE;
22 if(X_MODE = 'I') then
23 X_LAST_UPDATED_BY := 1;
24 X_LAST_UPDATE_LOGIN := 0;
25 elsif (X_MODE = 'R') then
26 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
27 if X_LAST_UPDATED_BY is NULL then
28 X_LAST_UPDATED_BY := -1;
29 end if;
30 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
31 if X_LAST_UPDATE_LOGIN is NULL then
32 X_LAST_UPDATE_LOGIN := -1;
33 end if;
34 else
35 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
36 app_exception.raise_exception;
37 end if;
38 insert into PSP_EFFORT_REPORT_PERIODS (
39 EFFORT_REPORT_PERIOD_NAME,
40 DESCRIPTION,
41 START_DATE_ACTIVE,
42 END_DATE_ACTIVE,
43 EFFORT_REPORT_PERIOD_PREFIX,
44 EFFORT_REPORT_PERIOD_TYPE,
45 EFFORT_REPORT_PERIOD_YEAR,
46 EFFORT_REPORT_PERIOD_NUMBER,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN
52 ) values (
53 X_EFFORT_REPORT_PERIOD_NAME,
54 X_DESCRIPTION,
55 X_START_DATE_ACTIVE,
56 X_END_DATE_ACTIVE,
57 X_EFFORT_REPORT_PERIOD_PREFIX,
58 X_EFFORT_REPORT_PERIOD_TYPE,
59 X_EFFORT_REPORT_PERIOD_YEAR,
60 X_EFFORT_REPORT_PERIOD_NUMBER,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_LOGIN
66 );
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78 procedure LOCK_ROW (
79 X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
80 X_DESCRIPTION in VARCHAR2,
81 X_START_DATE_ACTIVE in DATE,
82 X_END_DATE_ACTIVE in DATE,
83 X_EFFORT_REPORT_PERIOD_PREFIX in VARCHAR2,
84 X_EFFORT_REPORT_PERIOD_TYPE in VARCHAR2,
85 X_EFFORT_REPORT_PERIOD_YEAR in NUMBER,
86 X_EFFORT_REPORT_PERIOD_NUMBER in NUMBER
87 ) is
88 cursor c1 is select
89 DESCRIPTION,
90 START_DATE_ACTIVE,
91 END_DATE_ACTIVE,
92 EFFORT_REPORT_PERIOD_PREFIX,
93 EFFORT_REPORT_PERIOD_TYPE,
94 EFFORT_REPORT_PERIOD_YEAR,
95 EFFORT_REPORT_PERIOD_NUMBER
96 from PSP_EFFORT_REPORT_PERIODS
97 where EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME
98 for update of EFFORT_REPORT_PERIOD_NAME nowait;
99 tlinfo c1%rowtype;
100
101 begin
102 open c1;
103 fetch c1 into tlinfo;
104 if (c1%notfound) then
105 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
106 app_exception.raise_exception;
107 close c1;
108 return;
109 end if;
110 close c1;
111
112 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
113 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
114 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
115 OR ((tlinfo.END_DATE_ACTIVE is null)
116 AND (X_END_DATE_ACTIVE is null)))
117 AND ((tlinfo.EFFORT_REPORT_PERIOD_PREFIX = X_EFFORT_REPORT_PERIOD_PREFIX)
118 OR ((tlinfo.EFFORT_REPORT_PERIOD_PREFIX is null)
119 AND (X_EFFORT_REPORT_PERIOD_PREFIX is null)))
120 AND ((tlinfo.EFFORT_REPORT_PERIOD_TYPE = X_EFFORT_REPORT_PERIOD_TYPE)
121 OR ((tlinfo.EFFORT_REPORT_PERIOD_TYPE is null)
122 AND (X_EFFORT_REPORT_PERIOD_TYPE is null)))
123 AND (tlinfo.EFFORT_REPORT_PERIOD_YEAR = X_EFFORT_REPORT_PERIOD_YEAR)
124 AND (tlinfo.EFFORT_REPORT_PERIOD_NUMBER = X_EFFORT_REPORT_PERIOD_NUMBER)
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 return;
132 end LOCK_ROW;
133
134 procedure UPDATE_ROW (
135 X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
136 X_DESCRIPTION in VARCHAR2,
137 X_START_DATE_ACTIVE in DATE,
138 X_END_DATE_ACTIVE in DATE,
139 X_EFFORT_REPORT_PERIOD_PREFIX in VARCHAR2,
140 X_EFFORT_REPORT_PERIOD_TYPE in VARCHAR2,
141 X_EFFORT_REPORT_PERIOD_YEAR in NUMBER,
142 X_EFFORT_REPORT_PERIOD_NUMBER in NUMBER,
143 X_MODE in VARCHAR2 default 'R'
144 ) is
145 X_LAST_UPDATE_DATE DATE;
146 X_LAST_UPDATED_BY NUMBER;
147 X_LAST_UPDATE_LOGIN NUMBER;
148 begin
149 X_LAST_UPDATE_DATE := SYSDATE;
150 if(X_MODE = 'I') then
151 X_LAST_UPDATED_BY := 1;
152 X_LAST_UPDATE_LOGIN := 0;
153 elsif (X_MODE = 'R') then
154 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
155 if X_LAST_UPDATED_BY is NULL then
156 X_LAST_UPDATED_BY := -1;
157 end if;
158 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
159 if X_LAST_UPDATE_LOGIN is NULL then
160 X_LAST_UPDATE_LOGIN := -1;
161 end if;
162 else
163 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
164 app_exception.raise_exception;
165 end if;
166 update PSP_EFFORT_REPORT_PERIODS set
167 DESCRIPTION = X_DESCRIPTION,
168 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
169 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
170 EFFORT_REPORT_PERIOD_PREFIX = X_EFFORT_REPORT_PERIOD_PREFIX,
171 EFFORT_REPORT_PERIOD_TYPE = X_EFFORT_REPORT_PERIOD_TYPE,
172 EFFORT_REPORT_PERIOD_YEAR = X_EFFORT_REPORT_PERIOD_YEAR,
173 EFFORT_REPORT_PERIOD_NUMBER = X_EFFORT_REPORT_PERIOD_NUMBER,
174 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
175 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
177 where EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME
178 ;
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 end UPDATE_ROW;
183
184 procedure ADD_ROW (
185 X_ROWID in out NOCOPY VARCHAR2,
186 X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
187 X_DESCRIPTION in VARCHAR2,
188 X_START_DATE_ACTIVE in DATE,
189 X_END_DATE_ACTIVE in DATE,
190 X_EFFORT_REPORT_PERIOD_PREFIX in VARCHAR2,
191 X_EFFORT_REPORT_PERIOD_TYPE in VARCHAR2,
192 X_EFFORT_REPORT_PERIOD_YEAR in NUMBER,
193 X_EFFORT_REPORT_PERIOD_NUMBER in NUMBER,
194 X_MODE in VARCHAR2 default 'R'
195 ) is
196 cursor c1 is select rowid from PSP_EFFORT_REPORT_PERIODS
197 where EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME
198 ;
199 dummy c1%rowtype;
200 begin
201 open c1;
202 fetch c1 into dummy;
203 if (c1%notfound) then
204 close c1;
205 INSERT_ROW (
206 X_ROWID,
207 X_EFFORT_REPORT_PERIOD_NAME,
208 X_DESCRIPTION,
209 X_START_DATE_ACTIVE,
210 X_END_DATE_ACTIVE,
211 X_EFFORT_REPORT_PERIOD_PREFIX,
212 X_EFFORT_REPORT_PERIOD_TYPE,
213 X_EFFORT_REPORT_PERIOD_YEAR,
214 X_EFFORT_REPORT_PERIOD_NUMBER,
215 X_MODE);
216 return;
217 end if;
218 close c1;
219 UPDATE_ROW (
220 X_EFFORT_REPORT_PERIOD_NAME,
221 X_DESCRIPTION,
222 X_START_DATE_ACTIVE,
223 X_END_DATE_ACTIVE,
224 X_EFFORT_REPORT_PERIOD_PREFIX,
225 X_EFFORT_REPORT_PERIOD_TYPE,
226 X_EFFORT_REPORT_PERIOD_YEAR,
227 X_EFFORT_REPORT_PERIOD_NUMBER,
228 X_MODE);
229 end ADD_ROW;
230
231 procedure DELETE_ROW (
232 X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2
233 ) is
234 begin
235 delete from PSP_EFFORT_REPORT_PERIODS
236 where EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME;
237 ---if (sql%notfound) then
238 ---raise no_data_found;
239 ---end if;
240 commit;
241 end DELETE_ROW;
242
243 end PSP_EFFORT_REPORT_PERIODS_PKG;