1 package body PN_SET_MILESTONES_PKG as
2 -- $Header: PNTSTMLB.pls 120.1 2005/08/05 06:24:44 appldev ship $
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_MILESTONES_SET_ID in out NOCOPY NUMBER,
7 X_SET_ID in NUMBER,
8 X_USER_ID in NUMBER,
9 X_NOTIFICATION_DATE in DATE,
10 X_LEAD_DAYS in NUMBER,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_ATTRIBUTE9 in VARCHAR2,
21 X_ATTRIBUTE10 in VARCHAR2,
22 X_ATTRIBUTE11 in VARCHAR2,
23 X_ATTRIBUTE12 in VARCHAR2,
24 X_ATTRIBUTE13 in VARCHAR2,
25 X_ATTRIBUTE14 in VARCHAR2,
26 X_ATTRIBUTE15 in VARCHAR2,
27 X_MILESTONE_TYPE_CODE in VARCHAR2,
28 X_FREQUENCY in NUMBER,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) IS
35 cursor C is
36 select ROWID
37 from PN_SET_MILESTONES
38 where MILESTONES_SET_ID = X_MILESTONES_SET_ID ;
39
40 begin
41
42 if X_MILESTONES_SET_ID is null then
43
44 select PN_SET_MILESTONES_S.nextval
45 into X_MILESTONES_SET_ID
46 from dual;
47
48 end if;
49
50 insert into PN_SET_MILESTONES (
51 MILESTONES_SET_ID,
52 SET_ID,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_LOGIN,
58 MILESTONE_TYPE_CODE,
59 USER_ID,
60 NOTIFICATION_DATE,
61 LEAD_DAYS,
62 FREQUENCY,
63 ATTRIBUTE_CATEGORY,
64 ATTRIBUTE1,
65 ATTRIBUTE2,
66 ATTRIBUTE3,
67 ATTRIBUTE4,
68 ATTRIBUTE5,
69 ATTRIBUTE6,
70 ATTRIBUTE7,
71 ATTRIBUTE8,
72 ATTRIBUTE9,
73 ATTRIBUTE10,
74 ATTRIBUTE11,
75 ATTRIBUTE12,
76 ATTRIBUTE13,
77 ATTRIBUTE14,
78 ATTRIBUTE15
79 )
80 values (
81 X_MILESTONES_SET_ID,
82 X_SET_ID,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_LOGIN,
88 X_MILESTONE_TYPE_CODE,
89 X_USER_ID,
90 X_NOTIFICATION_DATE,
91 X_LEAD_DAYS,
92 X_FREQUENCY,
93 X_ATTRIBUTE_CATEGORY,
94 X_ATTRIBUTE1,
95 X_ATTRIBUTE2,
96 X_ATTRIBUTE3,
97 X_ATTRIBUTE4,
98 X_ATTRIBUTE5,
99 X_ATTRIBUTE6,
100 X_ATTRIBUTE7,
101 X_ATTRIBUTE8,
102 X_ATTRIBUTE9,
103 X_ATTRIBUTE10,
104 X_ATTRIBUTE11,
105 X_ATTRIBUTE12,
106 X_ATTRIBUTE13,
107 X_ATTRIBUTE14,
108 X_ATTRIBUTE15
109 );
110
111 open c;
112 fetch c into X_ROWID;
113 if (c%notfound) then
114 close c;
115 raise no_data_found;
116 end if;
117 close c;
118
119 end INSERT_ROW;
120
121 procedure LOCK_ROW (
122 X_MILESTONES_SET_ID in NUMBER,
123 X_SET_ID in NUMBER,
124 X_USER_ID in NUMBER,
125 X_NOTIFICATION_DATE in DATE,
126 X_LEAD_DAYS in NUMBER,
127 X_ATTRIBUTE_CATEGORY in VARCHAR2,
128 X_ATTRIBUTE1 in VARCHAR2,
129 X_ATTRIBUTE2 in VARCHAR2,
130 X_ATTRIBUTE3 in VARCHAR2,
131 X_ATTRIBUTE4 in VARCHAR2,
132 X_ATTRIBUTE5 in VARCHAR2,
133 X_ATTRIBUTE6 in VARCHAR2,
134 X_ATTRIBUTE7 in VARCHAR2,
135 X_ATTRIBUTE8 in VARCHAR2,
136 X_ATTRIBUTE9 in VARCHAR2,
137 X_ATTRIBUTE10 in VARCHAR2,
138 X_ATTRIBUTE11 in VARCHAR2,
139 X_ATTRIBUTE12 in VARCHAR2,
140 X_ATTRIBUTE13 in VARCHAR2,
141 X_ATTRIBUTE14 in VARCHAR2,
142 X_ATTRIBUTE15 in VARCHAR2,
143 X_MILESTONE_TYPE_CODE in VARCHAR2,
144 X_FREQUENCY in NUMBER
145 ) IS
146 cursor c1 is
147 select *
148 from PN_SET_MILESTONES
149 where MILESTONES_SET_ID = X_MILESTONES_SET_ID
150 for update of MILESTONES_SET_ID nowait;
151
152 tlinfo c1%rowtype;
153
154 begin
155
156 open c1;
157 fetch c1 into tlinfo;
158 if (c1%notfound) then
159 close c1;
160 return;
161 end if;
162 close c1;
163
164 if ( (tlinfo.MILESTONE_TYPE_CODE = X_MILESTONE_TYPE_CODE)
165 AND ((tlinfo.FREQUENCY = X_FREQUENCY)
166 OR ((tlinfo.FREQUENCY is null) AND (X_FREQUENCY is null)))
167 AND (tlinfo.SET_ID = X_SET_ID)
168 AND (tlinfo.USER_ID = X_USER_ID)
169 AND ((tlinfo.NOTIFICATION_DATE = X_NOTIFICATION_DATE)
170 OR ((tlinfo.NOTIFICATION_DATE is null) AND (X_NOTIFICATION_DATE is null)))
171 AND ((tlinfo.LEAD_DAYS = X_LEAD_DAYS)
172 OR ((tlinfo.LEAD_DAYS is null) AND (X_LEAD_DAYS is null)))
173 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
174 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
175 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
176 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
177 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
178 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
179 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
180 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
181 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
182 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
183 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
184 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
185 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
186 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
187 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
188 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
189 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
190 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
191 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
192 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
193 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
194 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
195 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
196 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
197 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
198 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
199 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
200 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
201 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
202 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
203 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
204 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
205 ) then
206 null;
207 else
208 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
209 app_exception.raise_exception;
210 end if;
211 return;
212 end LOCK_ROW;
213
214 procedure UPDATE_ROW (
215 X_MILESTONES_SET_ID in NUMBER,
216 X_SET_ID in NUMBER,
217 X_USER_ID in NUMBER,
218 X_NOTIFICATION_DATE in DATE,
219 X_LEAD_DAYS in NUMBER,
220 X_ATTRIBUTE_CATEGORY in VARCHAR2,
221 X_ATTRIBUTE1 in VARCHAR2,
222 X_ATTRIBUTE2 in VARCHAR2,
223 X_ATTRIBUTE3 in VARCHAR2,
224 X_ATTRIBUTE4 in VARCHAR2,
225 X_ATTRIBUTE5 in VARCHAR2,
226 X_ATTRIBUTE6 in VARCHAR2,
227 X_ATTRIBUTE7 in VARCHAR2,
228 X_ATTRIBUTE8 in VARCHAR2,
229 X_ATTRIBUTE9 in VARCHAR2,
230 X_ATTRIBUTE10 in VARCHAR2,
231 X_ATTRIBUTE11 in VARCHAR2,
232 X_ATTRIBUTE12 in VARCHAR2,
233 X_ATTRIBUTE13 in VARCHAR2,
234 X_ATTRIBUTE14 in VARCHAR2,
235 X_ATTRIBUTE15 in VARCHAR2,
236 X_MILESTONE_TYPE_CODE in VARCHAR2,
237 X_FREQUENCY in NUMBER,
238 X_LAST_UPDATE_DATE in DATE,
239 X_LAST_UPDATED_BY in NUMBER,
240 X_LAST_UPDATE_LOGIN in NUMBER
241 ) IS
242 begin
243
244 update PN_SET_MILESTONES
245 set
246 USER_ID = X_USER_ID,
247 NOTIFICATION_DATE = X_NOTIFICATION_DATE,
248 LEAD_DAYS = X_LEAD_DAYS,
249 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
250 ATTRIBUTE1 = X_ATTRIBUTE1,
251 ATTRIBUTE2 = X_ATTRIBUTE2,
252 ATTRIBUTE3 = X_ATTRIBUTE3,
253 ATTRIBUTE4 = X_ATTRIBUTE4,
254 ATTRIBUTE5 = X_ATTRIBUTE5,
255 ATTRIBUTE6 = X_ATTRIBUTE6,
256 ATTRIBUTE7 = X_ATTRIBUTE7,
257 ATTRIBUTE8 = X_ATTRIBUTE8,
258 ATTRIBUTE9 = X_ATTRIBUTE9,
259 ATTRIBUTE10 = X_ATTRIBUTE10,
260 ATTRIBUTE11 = X_ATTRIBUTE11,
261 ATTRIBUTE12 = X_ATTRIBUTE12,
262 ATTRIBUTE13 = X_ATTRIBUTE13,
263 ATTRIBUTE14 = X_ATTRIBUTE14,
264 ATTRIBUTE15 = X_ATTRIBUTE15,
265 MILESTONE_TYPE_CODE = X_MILESTONE_TYPE_CODE,
266 FREQUENCY = X_FREQUENCY,
267 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
268 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
269 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
270 where MILESTONES_SET_ID = X_MILESTONES_SET_ID;
271
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275 end UPDATE_ROW;
276
277 procedure DELETE_ROW (
278 X_MILESTONES_SET_ID in NUMBER
279 ) IS
280 begin
281
282 delete from PN_SET_MILESTONES
283 where MILESTONES_SET_ID = X_MILESTONES_SET_ID;
284
285 if (sql%notfound) then
286 raise no_data_found;
287 end if;
288
289 end DELETE_ROW;
290
291
292 end PN_SET_MILESTONES_PKG;
[Home] [Help]