1 PACKAGE BODY CS_TM_LABOR_SCHEDULES_PKG as
2 /* $Header: csxttmlb.pls 120.1 2005/06/17 11:26:38 appldev $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_TM_LABOR_SCHEDULE_ID in NUMBER,
7 X_BUSINESS_PROCESS_ID in NUMBER,
8 X_START_TIME in DATE,
9 X_END_TIME in DATE,
10 X_MONDAY_FLAG in VARCHAR2,
11 X_TUESDAY_FLAG in VARCHAR2,
12 X_WEDNESDAY_FLAG in VARCHAR2,
13 X_THURSDAY_FLAG in VARCHAR2,
14 X_FRIDAY_FLAG in VARCHAR2,
15 X_SATURDAY_FLAG in VARCHAR2,
16 X_SUNDAY_FLAG in VARCHAR2,
17 X_HOLIDAY_FLAG in VARCHAR2,
18 X_INVENTORY_ITEM_ID in NUMBER,
19 X_LABOR_CODE in VARCHAR2,
20 X_START_HOUR in NUMBER,
21 X_START_MINUTE in NUMBER,
22 X_END_HOUR in NUMBER,
23 X_END_MINUTE in NUMBER,
24 X_ATTRIBUTE_CATEGORY in VARCHAR2,
25 X_ATTRIBUTE1 in VARCHAR2,
26 X_ATTRIBUTE2 in VARCHAR2,
27 X_ATTRIBUTE3 in VARCHAR2,
28 X_ATTRIBUTE4 in VARCHAR2,
29 X_ATTRIBUTE5 in VARCHAR2,
30 X_ATTRIBUTE6 in VARCHAR2,
31 X_ATTRIBUTE7 in VARCHAR2,
32 X_ATTRIBUTE8 in VARCHAR2,
33 X_ATTRIBUTE9 in VARCHAR2,
34 X_ATTRIBUTE10 in VARCHAR2,
35 X_ATTRIBUTE11 in VARCHAR2,
36 X_ATTRIBUTE12 in VARCHAR2,
37 X_ATTRIBUTE13 in VARCHAR2,
38 X_ATTRIBUTE14 in VARCHAR2,
39 X_ATTRIBUTE15 in VARCHAR2,
40 X_SECURITY_GROUP_ID in NUMBER,
41 X_CREATION_DATE in DATE,
42 X_CREATED_BY in NUMBER,
43 X_LAST_UPDATE_DATE in DATE,
44 X_LAST_UPDATED_BY in NUMBER,
45 X_LAST_UPDATE_LOGIN in NUMBER
46 ) is
47 cursor C is select ROWID from CS_TM_LABOR_SCHEDULES
48 where TM_LABOR_SCHEDULE_ID = X_TM_LABOR_SCHEDULE_ID
49 ;
50 begin
51 insert into CS_TM_LABOR_SCHEDULES (
52 TM_LABOR_SCHEDULE_ID,
53 BUSINESS_PROCESS_ID,
54 START_TIME,
55 END_TIME,
56 MONDAY_FLAG,
57 TUESDAY_FLAG,
58 WEDNESDAY_FLAG,
59 THURSDAY_FLAG,
60 FRIDAY_FLAG,
61 SATURDAY_FLAG,
62 SUNDAY_FLAG,
63 HOLIDAY_FLAG,
64 INVENTORY_ITEM_ID,
65 LABOR_CODE,
66 START_HOUR,
67 START_MINUTE,
68 END_HOUR,
69 END_MINUTE,
70 ATTRIBUTE_CATEGORY,
71 ATTRIBUTE1,
72 ATTRIBUTE2,
73 ATTRIBUTE3,
74 ATTRIBUTE4,
75 ATTRIBUTE5,
76 ATTRIBUTE6,
77 ATTRIBUTE7,
78 ATTRIBUTE8,
79 ATTRIBUTE9,
80 ATTRIBUTE10,
81 ATTRIBUTE11,
82 ATTRIBUTE12,
83 ATTRIBUTE13,
84 ATTRIBUTE14,
85 ATTRIBUTE15,
86 SECURITY_GROUP_ID,
87 CREATION_DATE,
88 CREATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN
92 ) values (
93 X_TM_LABOR_SCHEDULE_ID,
94 X_BUSINESS_PROCESS_ID,
95 X_START_TIME,
96 X_END_TIME,
97 X_MONDAY_FLAG,
98 X_TUESDAY_FLAG,
99 X_WEDNESDAY_FLAG,
100 X_THURSDAY_FLAG,
101 X_FRIDAY_FLAG,
102 X_SATURDAY_FLAG,
103 X_SUNDAY_FLAG,
104 X_HOLIDAY_FLAG,
105 X_INVENTORY_ITEM_ID,
106 X_LABOR_CODE,
107 X_START_HOUR,
108 X_START_MINUTE,
109 X_END_HOUR,
110 X_END_MINUTE,
111 X_ATTRIBUTE_CATEGORY,
112 X_ATTRIBUTE1,
113 X_ATTRIBUTE2,
114 X_ATTRIBUTE3,
115 X_ATTRIBUTE4,
116 X_ATTRIBUTE5,
117 X_ATTRIBUTE6,
118 X_ATTRIBUTE7,
119 X_ATTRIBUTE8,
120 X_ATTRIBUTE9,
121 X_ATTRIBUTE10,
122 X_ATTRIBUTE11,
123 X_ATTRIBUTE12,
124 X_ATTRIBUTE13,
125 X_ATTRIBUTE14,
126 X_ATTRIBUTE15,
127 X_SECURITY_GROUP_ID,
128 X_CREATION_DATE,
129 X_CREATED_BY,
130 X_LAST_UPDATE_DATE,
131 X_LAST_UPDATED_BY,
132 X_LAST_UPDATE_LOGIN
133 );
134
135 open c;
136 fetch c into X_ROWID;
137 if (c%notfound) then
138 close c;
139 raise no_data_found;
140 end if;
141 close c;
142
143 end INSERT_ROW;
144
145 procedure LOCK_ROW (
146 X_TM_LABOR_SCHEDULE_ID in NUMBER,
147 X_BUSINESS_PROCESS_ID in NUMBER,
148 X_MONDAY_FLAG in VARCHAR2,
149 X_TUESDAY_FLAG in VARCHAR2,
150 X_WEDNESDAY_FLAG in VARCHAR2,
151 X_THURSDAY_FLAG in VARCHAR2,
152 X_FRIDAY_FLAG in VARCHAR2,
153 X_SATURDAY_FLAG in VARCHAR2,
154 X_SUNDAY_FLAG in VARCHAR2,
155 X_HOLIDAY_FLAG in VARCHAR2,
156 X_INVENTORY_ITEM_ID in NUMBER,
157 X_LABOR_CODE in VARCHAR2,
158 X_ATTRIBUTE_CATEGORY in VARCHAR2,
159 X_ATTRIBUTE1 in VARCHAR2,
160 X_ATTRIBUTE2 in VARCHAR2,
161 X_ATTRIBUTE3 in VARCHAR2,
162 X_ATTRIBUTE4 in VARCHAR2,
163 X_ATTRIBUTE5 in VARCHAR2,
164 X_ATTRIBUTE6 in VARCHAR2,
165 X_ATTRIBUTE7 in VARCHAR2,
166 X_ATTRIBUTE8 in VARCHAR2,
167 X_ATTRIBUTE9 in VARCHAR2,
168 X_ATTRIBUTE10 in VARCHAR2,
169 X_ATTRIBUTE11 in VARCHAR2,
170 X_ATTRIBUTE12 in VARCHAR2,
171 X_ATTRIBUTE13 in VARCHAR2,
172 X_ATTRIBUTE14 in VARCHAR2,
173 X_ATTRIBUTE15 in VARCHAR2,
174 X_SECURITY_GROUP_ID in NUMBER
175 ) is
176 cursor c is select
177 BUSINESS_PROCESS_ID,
178 MONDAY_FLAG,
179 TUESDAY_FLAG,
180 WEDNESDAY_FLAG,
181 THURSDAY_FLAG,
182 FRIDAY_FLAG,
183 SATURDAY_FLAG,
184 SUNDAY_FLAG,
185 HOLIDAY_FLAG,
186 INVENTORY_ITEM_ID,
187 LABOR_CODE,
188 ATTRIBUTE_CATEGORY,
189 ATTRIBUTE1,
190 ATTRIBUTE2,
191 ATTRIBUTE3,
192 ATTRIBUTE4,
193 ATTRIBUTE5,
194 ATTRIBUTE6,
195 ATTRIBUTE7,
196 ATTRIBUTE8,
197 ATTRIBUTE9,
198 ATTRIBUTE10,
199 ATTRIBUTE11,
200 ATTRIBUTE12,
201 ATTRIBUTE13,
202 ATTRIBUTE14,
203 ATTRIBUTE15,
204 SECURITY_GROUP_ID
205 from CS_TM_LABOR_SCHEDULES
206 where TM_LABOR_SCHEDULE_ID = X_TM_LABOR_SCHEDULE_ID
207 for update of TM_LABOR_SCHEDULE_ID nowait;
208 recinfo c%rowtype;
209
210 begin
211 open c;
212 fetch c into recinfo;
213 if (c%notfound) then
214 close c;
215 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
216 app_exception.raise_exception;
217 end if;
218 close c;
219 if ( (recinfo.BUSINESS_PROCESS_ID = X_BUSINESS_PROCESS_ID)
220 AND ((recinfo.MONDAY_FLAG = X_MONDAY_FLAG)
221 OR ((recinfo.MONDAY_FLAG is null) AND (X_MONDAY_FLAG is null)))
222 AND ((recinfo.TUESDAY_FLAG = X_TUESDAY_FLAG)
223 OR ((recinfo.TUESDAY_FLAG is null) AND (X_TUESDAY_FLAG is null)))
224 AND ((recinfo.WEDNESDAY_FLAG = X_WEDNESDAY_FLAG)
225 OR ((recinfo.WEDNESDAY_FLAG is null) AND (X_WEDNESDAY_FLAG is null)))
226 AND ((recinfo.THURSDAY_FLAG = X_THURSDAY_FLAG)
227 OR ((recinfo.THURSDAY_FLAG is null) AND (X_THURSDAY_FLAG is null)))
228 AND ((recinfo.FRIDAY_FLAG = X_FRIDAY_FLAG)
229 OR ((recinfo.FRIDAY_FLAG is null) AND (X_FRIDAY_FLAG is null)))
230 AND ((recinfo.SATURDAY_FLAG = X_SATURDAY_FLAG)
231 OR ((recinfo.SATURDAY_FLAG is null) AND (X_SATURDAY_FLAG is null)))
232 AND ((recinfo.SUNDAY_FLAG = X_SUNDAY_FLAG)
233 OR ((recinfo.SUNDAY_FLAG is null) AND (X_SUNDAY_FLAG is null)))
234 AND ((recinfo.HOLIDAY_FLAG = X_HOLIDAY_FLAG)
235 OR ((recinfo.HOLIDAY_FLAG is null) AND (X_HOLIDAY_FLAG is null)))
236 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
237 AND ((recinfo.LABOR_CODE = X_LABOR_CODE)
238 OR ((recinfo.LABOR_CODE is null) AND (X_LABOR_CODE is null)))
239
240 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
241 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
242 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
243 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
244 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
245 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
246 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
247 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
248 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
249 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
250 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
251 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
252 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
253 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
254 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
255 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
256 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
257 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
258 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
259 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
260 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
261 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
262 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
263 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
264 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
265 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
266 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
267 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
268 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
269 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
270 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
271 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
272 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
273 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
274 ) then
275 null;
276 else
277 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
278 app_exception.raise_exception;
279 end if;
280
281 return;
282 end LOCK_ROW;
283
284 procedure UPDATE_ROW (
285 X_TM_LABOR_SCHEDULE_ID in NUMBER,
286 X_BUSINESS_PROCESS_ID in NUMBER,
287 X_START_TIME in DATE,
288 X_END_TIME in DATE,
289 X_MONDAY_FLAG in VARCHAR2,
290 X_TUESDAY_FLAG in VARCHAR2,
291 X_WEDNESDAY_FLAG in VARCHAR2,
292 X_THURSDAY_FLAG in VARCHAR2,
293 X_FRIDAY_FLAG in VARCHAR2,
294 X_SATURDAY_FLAG in VARCHAR2,
295 X_SUNDAY_FLAG in VARCHAR2,
296 X_HOLIDAY_FLAG in VARCHAR2,
297 X_INVENTORY_ITEM_ID in NUMBER,
298 X_LABOR_CODE in VARCHAR2,
299 X_START_HOUR in NUMBER,
300 X_START_MINUTE in NUMBER,
301 X_END_HOUR in NUMBER,
302 X_END_MINUTE in NUMBER,
303 X_ATTRIBUTE_CATEGORY in VARCHAR2,
304 X_ATTRIBUTE1 in VARCHAR2,
305 X_ATTRIBUTE2 in VARCHAR2,
306 X_ATTRIBUTE3 in VARCHAR2,
307 X_ATTRIBUTE4 in VARCHAR2,
308 X_ATTRIBUTE5 in VARCHAR2,
309 X_ATTRIBUTE6 in VARCHAR2,
310 X_ATTRIBUTE7 in VARCHAR2,
311 X_ATTRIBUTE8 in VARCHAR2,
312 X_ATTRIBUTE9 in VARCHAR2,
313 X_ATTRIBUTE10 in VARCHAR2,
314 X_ATTRIBUTE11 in VARCHAR2,
315 X_ATTRIBUTE12 in VARCHAR2,
316 X_ATTRIBUTE13 in VARCHAR2,
317 X_ATTRIBUTE14 in VARCHAR2,
318 X_ATTRIBUTE15 in VARCHAR2,
319 X_SECURITY_GROUP_ID in NUMBER,
320 X_LAST_UPDATE_DATE in DATE,
321 X_LAST_UPDATED_BY in NUMBER,
322 X_LAST_UPDATE_LOGIN in NUMBER
323 ) is
324 begin
325 update CS_TM_LABOR_SCHEDULES set
326 BUSINESS_PROCESS_ID = X_BUSINESS_PROCESS_ID,
327 START_TIME = X_START_TIME,
328 END_TIME = X_END_TIME,
329 MONDAY_FLAG = X_MONDAY_FLAG,
330 TUESDAY_FLAG = X_TUESDAY_FLAG,
331 WEDNESDAY_FLAG = X_WEDNESDAY_FLAG,
332 THURSDAY_FLAG = X_THURSDAY_FLAG,
333 FRIDAY_FLAG = X_FRIDAY_FLAG,
334 SATURDAY_FLAG = X_SATURDAY_FLAG,
335 SUNDAY_FLAG = X_SUNDAY_FLAG,
336 HOLIDAY_FLAG = X_HOLIDAY_FLAG,
337 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
338 LABOR_CODE = X_LABOR_CODE,
339 START_HOUR = X_START_HOUR,
340 START_MINUTE = X_START_MINUTE,
341 END_HOUR = X_END_HOUR,
342 END_MINUTE = X_END_MINUTE,
343 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
344 ATTRIBUTE1 = X_ATTRIBUTE1,
345 ATTRIBUTE2 = X_ATTRIBUTE2,
346 ATTRIBUTE3 = X_ATTRIBUTE3,
347 ATTRIBUTE4 = X_ATTRIBUTE4,
348 ATTRIBUTE5 = X_ATTRIBUTE5,
349 ATTRIBUTE6 = X_ATTRIBUTE6,
353 ATTRIBUTE10 = X_ATTRIBUTE10,
350 ATTRIBUTE7 = X_ATTRIBUTE7,
351 ATTRIBUTE8 = X_ATTRIBUTE8,
352 ATTRIBUTE9 = X_ATTRIBUTE9,
354 ATTRIBUTE11 = X_ATTRIBUTE11,
355 ATTRIBUTE12 = X_ATTRIBUTE12,
356 ATTRIBUTE13 = X_ATTRIBUTE13,
357 ATTRIBUTE14 = X_ATTRIBUTE14,
358 ATTRIBUTE15 = X_ATTRIBUTE15,
359 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
360 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
361 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
362 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
363 where TM_LABOR_SCHEDULE_ID = X_TM_LABOR_SCHEDULE_ID;
364
365 if (sql%notfound) then
366 raise no_data_found;
367 end if;
368
369 end UPDATE_ROW;
370
371 procedure DELETE_ROW (
372 X_TM_LABOR_SCHEDULE_ID in NUMBER
373 ) is
374 begin
375 delete from CS_TM_LABOR_SCHEDULES
376 where TM_LABOR_SCHEDULE_ID = X_TM_LABOR_SCHEDULE_ID;
377
378 if (sql%notfound) then
379 raise no_data_found;
380 end if;
381 end DELETE_ROW;
382
383 end CS_TM_LABOR_SCHEDULES_PKG;
384
[Home] [Help]