[Home] [Help]
PACKAGE BODY: APPS.WIP_REPETITIVE_SCHEDULES_PKG
Source
1 PACKAGE BODY WIP_REPETITIVE_SCHEDULES_PKG as
2 /* $Header: wiprsvhb.pls 115.7 2002/11/29 15:32:54 rmahidha ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Repetitive_Schedule_Id NUMBER,
6 X_Organization_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Wip_Entity_Id NUMBER,
13 X_Line_Id NUMBER,
14 X_Daily_Production_Rate NUMBER,
15 X_Processing_Work_Days NUMBER,
16 X_Status_Type NUMBER,
17 X_Firm_Planned_Flag NUMBER,
18 X_Alternate_Bom_Designator VARCHAR2,
19 X_Common_Bom_Sequence_Id NUMBER,
20 X_Bom_Revision VARCHAR2,
21 X_Bom_Revision_Date DATE,
22 X_Alternate_Routing_Designator VARCHAR2,
23 X_Common_Routing_Sequence_Id NUMBER,
24 X_Routing_Revision VARCHAR2,
25 X_Routing_Revision_Date DATE,
26 X_First_Unit_Start_Date DATE,
27 X_First_Unit_Completion_Date DATE,
28 X_Last_Unit_Start_Date DATE,
29 X_Last_Unit_Completion_Date DATE,
30 X_Date_Released DATE,
31 X_Date_Closed DATE,
32 X_Quantity_Completed NUMBER,
33 X_Description VARCHAR2,
34 X_Demand_Class VARCHAR2,
35 X_Material_Account NUMBER,
36 X_Material_Overhead_Account NUMBER,
37 X_Resource_Account NUMBER,
38 X_Overhead_Account NUMBER,
39 X_Outside_Processing_Account NUMBER,
40 X_Material_Variance_Account NUMBER,
41 X_Overhead_Variance_Account NUMBER,
42 X_Resource_Variance_Account NUMBER,
43 X_O_Proc_Variance_Account NUMBER,
44 X_Attribute_Category VARCHAR2,
45 X_Attribute1 VARCHAR2,
46 X_Attribute2 VARCHAR2,
47 X_Attribute3 VARCHAR2,
48 X_Attribute4 VARCHAR2,
49 X_Attribute5 VARCHAR2,
50 X_Attribute6 VARCHAR2,
51 X_Attribute7 VARCHAR2,
52 X_Attribute8 VARCHAR2,
53 X_Attribute9 VARCHAR2,
54 X_Attribute10 VARCHAR2,
55 X_Attribute11 VARCHAR2,
56 X_Attribute12 VARCHAR2,
57 X_Attribute13 VARCHAR2,
58 X_Attribute14 VARCHAR2,
59 X_Attribute15 VARCHAR2,
60 X_PO_Creation_Time NUMBER
61 ) IS
62 CURSOR C IS SELECT rowid FROM WIP_REPETITIVE_SCHEDULES
63 WHERE repetitive_schedule_id = X_Repetitive_Schedule_Id;
64
65 BEGIN
66
67
68 INSERT INTO WIP_REPETITIVE_SCHEDULES(
69 repetitive_schedule_id,
70 organization_id,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by,
75 last_update_login,
76 wip_entity_id,
77 line_id,
78 daily_production_rate,
79 processing_work_days,
80 status_type,
81 firm_planned_flag,
82 alternate_bom_designator,
83 common_bom_sequence_id,
84 bom_revision,
85 bom_revision_date,
86 alternate_routing_designator,
87 common_routing_sequence_id,
88 routing_revision,
89 routing_revision_date,
90 first_unit_start_date,
91 first_unit_completion_date,
92 last_unit_start_date,
93 last_unit_completion_date,
94 date_released,
95 date_closed,
96 quantity_completed,
97 description,
98 demand_class,
99 material_account,
100 material_overhead_account,
101 resource_account,
102 overhead_account,
103 outside_processing_account,
104 material_variance_account,
105 overhead_variance_account,
106 resource_variance_account,
107 outside_proc_variance_account,
108 attribute_category,
109 attribute1,
110 attribute2,
111 attribute3,
112 attribute4,
113 attribute5,
114 attribute6,
115 attribute7,
116 attribute8,
117 attribute9,
118 attribute10,
119 attribute11,
120 attribute12,
121 attribute13,
122 attribute14,
123 attribute15,
124 po_creation_time
125 ) VALUES (
126
127 X_Repetitive_Schedule_Id,
128 X_Organization_Id,
129 X_Last_Update_Date,
130 X_Last_Updated_By,
131 X_Creation_Date,
132 X_Created_By,
133 X_Last_Update_Login,
134 X_Wip_Entity_Id,
135 X_Line_Id,
136 X_Daily_Production_Rate,
137 X_Processing_Work_Days,
138 X_Status_Type,
139 X_Firm_Planned_Flag,
140 X_Alternate_Bom_Designator,
141 X_Common_Bom_Sequence_Id,
142 X_Bom_Revision,
143 X_Bom_Revision_Date,
144 X_Alternate_Routing_Designator,
145 X_Common_Routing_Sequence_Id,
146 X_Routing_Revision,
147 X_Routing_Revision_Date,
148 X_First_Unit_Start_Date,
149 X_First_Unit_Completion_Date,
150 X_Last_Unit_Start_Date,
151 X_Last_Unit_Completion_Date,
152 X_Date_Released,
153 X_Date_Closed,
154 X_Quantity_Completed,
155 X_Description,
156 X_Demand_Class,
157 X_Material_Account,
158 X_Material_Overhead_Account,
159 X_Resource_Account,
160 X_Overhead_Account,
161 X_Outside_Processing_Account,
162 X_Material_Variance_Account,
163 X_Overhead_Variance_Account,
164 X_Resource_Variance_Account,
165 X_O_Proc_Variance_Account,
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_PO_Creation_Time
183 );
184
185 OPEN C;
186 FETCH C INTO X_Rowid;
187 if (C%NOTFOUND) then
188 CLOSE C;
189 Raise NO_DATA_FOUND;
190 end if;
191 CLOSE C;
192 END Insert_Row;
193
194
195 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
196 X_Repetitive_Schedule_Id NUMBER,
197 X_Organization_Id NUMBER,
198 X_Daily_Production_Rate NUMBER,
199 X_Processing_Work_Days NUMBER,
200 X_Status_Type NUMBER,
201 X_Firm_Planned_Flag NUMBER,
202 X_Common_Bom_Sequence_Id NUMBER,
203 X_Bom_Revision VARCHAR2,
204 X_Bom_Revision_Date DATE,
205 X_Common_Routing_Sequence_Id NUMBER,
206 X_Routing_Revision VARCHAR2,
207 X_Routing_Revision_Date DATE,
208 X_First_Unit_Start_Date DATE,
209 X_First_Unit_Completion_Date DATE,
210 X_Last_Unit_Start_Date DATE,
211 X_Last_Unit_Completion_Date DATE,
212 X_Date_Released DATE,
213 X_Date_Closed DATE,
214 X_Description VARCHAR2,
215 X_Demand_Class VARCHAR2,
216 X_Attribute_Category VARCHAR2,
217 X_Attribute1 VARCHAR2,
218 X_Attribute2 VARCHAR2,
219 X_Attribute3 VARCHAR2,
220 X_Attribute4 VARCHAR2,
221 X_Attribute5 VARCHAR2,
222 X_Attribute6 VARCHAR2,
223 X_Attribute7 VARCHAR2,
224 X_Attribute8 VARCHAR2,
225 X_Attribute9 VARCHAR2,
226 X_Attribute10 VARCHAR2,
227 X_Attribute11 VARCHAR2,
228 X_Attribute12 VARCHAR2,
229 X_Attribute13 VARCHAR2,
230 X_Attribute14 VARCHAR2,
231 X_Attribute15 VARCHAR2
232 ) IS
233 CURSOR C IS
234 SELECT *
235 FROM WIP_REPETITIVE_SCHEDULES
236 WHERE rowid = X_Rowid
237 FOR UPDATE of Repetitive_Schedule_Id NOWAIT;
238 Recinfo C%ROWTYPE;
239
240
241 BEGIN
242 OPEN C;
243 FETCH C INTO Recinfo;
244 if (C%NOTFOUND) then
245 CLOSE C;
246 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
247 APP_EXCEPTION.Raise_Exception;
248 end if;
249 CLOSE C;
250 if ( (Recinfo.repetitive_schedule_id = X_Repetitive_Schedule_Id)
251 AND (Recinfo.organization_id = X_Organization_Id)
252 AND (Recinfo.daily_production_rate = X_Daily_Production_Rate)
253 AND (Recinfo.processing_work_days = X_Processing_Work_Days)
254 AND (Recinfo.status_type = X_Status_Type)
258 AND (X_Common_Bom_Sequence_Id IS NULL)))
255 AND (Recinfo.firm_planned_flag = X_Firm_Planned_Flag)
256 AND ( (Recinfo.common_bom_sequence_id = X_Common_Bom_Sequence_Id)
257 OR ( (Recinfo.common_bom_sequence_id IS NULL)
259 AND ( (Recinfo.bom_revision = X_Bom_Revision)
260 OR ( (Recinfo.bom_revision IS NULL)
261 AND (X_Bom_Revision IS NULL)))
262 AND ( (Recinfo.bom_revision_date = X_Bom_Revision_Date)
263 OR ( (Recinfo.bom_revision_date IS NULL)
264 AND (X_Bom_Revision_Date IS NULL)))
265 AND ( (Recinfo.common_routing_sequence_id = X_Common_Routing_Sequence_Id)
266 OR ( (Recinfo.common_routing_sequence_id IS NULL)
267 AND (X_Common_Routing_Sequence_Id IS NULL)))
268 AND ( (Recinfo.routing_revision = X_Routing_Revision)
269 OR ( (Recinfo.routing_revision IS NULL)
270 AND (X_Routing_Revision IS NULL)))
271 AND ( (Recinfo.routing_revision_date = X_Routing_Revision_Date)
272 OR ( (Recinfo.routing_revision_date IS NULL)
273 AND (X_Routing_Revision_Date IS NULL)))
274 AND (Recinfo.first_unit_start_date = X_First_Unit_Start_Date)
275 AND (Recinfo.first_unit_completion_date = X_First_Unit_Completion_Date)
276 AND (Recinfo.last_unit_start_date = X_Last_Unit_Start_Date)
277 AND (Recinfo.last_unit_completion_date = X_Last_Unit_Completion_Date)
278 AND ( (Recinfo.date_released = X_Date_Released)
279 OR ( (Recinfo.date_released IS NULL)
280 AND (X_Date_Released IS NULL)))
281 AND ( (Recinfo.date_closed = X_Date_Closed)
282 OR ( (Recinfo.date_closed IS NULL)
283 AND (X_Date_Closed IS NULL)))
284 AND ( (Recinfo.description = X_Description)
285 OR ( (Recinfo.description IS NULL)
286 AND (X_Description IS NULL)))
287 AND ( (Recinfo.demand_class = X_Demand_Class)
288 OR ( (Recinfo.demand_class IS NULL)
289 AND (X_Demand_Class IS NULL)))
290 AND ( (Recinfo.attribute_category = X_Attribute_Category)
291 OR ( (Recinfo.attribute_category IS NULL)
292 AND (X_Attribute_Category IS NULL)))
293 AND ( (Recinfo.attribute1 = X_Attribute1)
294 OR ( (Recinfo.attribute1 IS NULL)
295 AND (X_Attribute1 IS NULL)))
296 AND ( (Recinfo.attribute2 = X_Attribute2)
297 OR ( (Recinfo.attribute2 IS NULL)
298 AND (X_Attribute2 IS NULL)))
299 AND ( (Recinfo.attribute3 = X_Attribute3)
300 OR ( (Recinfo.attribute3 IS NULL)
301 AND (X_Attribute3 IS NULL)))
302 AND ( (Recinfo.attribute4 = X_Attribute4)
303 OR ( (Recinfo.attribute4 IS NULL)
304 AND (X_Attribute4 IS NULL)))
305 AND ( (Recinfo.attribute5 = X_Attribute5)
306 OR ( (Recinfo.attribute5 IS NULL)
307 AND (X_Attribute5 IS NULL)))
308 AND ( (Recinfo.attribute6 = X_Attribute6)
309 OR ( (Recinfo.attribute6 IS NULL)
310 AND (X_Attribute6 IS NULL)))
311 AND ( (Recinfo.attribute7 = X_Attribute7)
312 OR ( (Recinfo.attribute7 IS NULL)
313 AND (X_Attribute7 IS NULL)))
314 AND ( (Recinfo.attribute8 = X_Attribute8)
315 OR ( (Recinfo.attribute8 IS NULL)
316 AND (X_Attribute8 IS NULL)))
317 AND ( (Recinfo.attribute9 = X_Attribute9)
318 OR ( (Recinfo.attribute9 IS NULL)
319 AND (X_Attribute9 IS NULL)))
320 AND ( (Recinfo.attribute10 = X_Attribute10)
321 OR ( (Recinfo.attribute10 IS NULL)
322 AND (X_Attribute10 IS NULL)))
323 AND ( (Recinfo.attribute11 = X_Attribute11)
324 OR ( (Recinfo.attribute11 IS NULL)
325 AND (X_Attribute11 IS NULL)))
326 AND ( (Recinfo.attribute12 = X_Attribute12)
327 OR ( (Recinfo.attribute12 IS NULL)
328 AND (X_Attribute12 IS NULL)))
329 AND ( (Recinfo.attribute13 = X_Attribute13)
330 OR ( (Recinfo.attribute13 IS NULL)
331 AND (X_Attribute13 IS NULL)))
332 AND ( (Recinfo.attribute14 = X_Attribute14)
333 OR ( (Recinfo.attribute14 IS NULL)
334 AND (X_Attribute14 IS NULL)))
335 AND ( (Recinfo.attribute15 = X_Attribute15)
336 OR ( (Recinfo.attribute15 IS NULL)
337 AND (X_Attribute15 IS NULL)))
338 ) then
339 return;
340 else
341 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
342 APP_EXCEPTION.Raise_Exception;
343 end if;
344 END Lock_Row;
345
346
347
348 PROCEDURE Update_Row(X_Rowid VARCHAR2,
349 X_Repetitive_Schedule_Id NUMBER,
350 X_Organization_Id NUMBER,
351 X_Last_Update_Date DATE,
352 X_Last_Updated_By NUMBER,
353 X_Last_Update_Login NUMBER,
354 X_Wip_Entity_Id NUMBER,
355 X_Line_Id NUMBER,
356 X_Daily_Production_Rate NUMBER,
357 X_Processing_Work_Days NUMBER,
358 X_Status_Type NUMBER,
359 X_Firm_Planned_Flag NUMBER,
363 X_Bom_Revision_Date DATE,
360 X_Alternate_Bom_Designator VARCHAR2,
361 X_Common_Bom_Sequence_Id NUMBER,
362 X_Bom_Revision VARCHAR2,
364 X_Alternate_Routing_Designator VARCHAR2,
365 X_Common_Routing_Sequence_Id NUMBER,
366 X_Routing_Revision VARCHAR2,
367 X_Routing_Revision_Date DATE,
368 X_First_Unit_Start_Date DATE,
369 X_First_Unit_Completion_Date DATE,
370 X_Last_Unit_Start_Date DATE,
371 X_Last_Unit_Completion_Date DATE,
372 X_Date_Released DATE,
373 X_Date_Closed DATE,
374 X_Description VARCHAR2,
375 X_Demand_Class VARCHAR2,
376 X_Material_Account NUMBER,
377 X_Material_Overhead_Account NUMBER,
378 X_Resource_Account NUMBER,
379 X_Overhead_Account NUMBER,
380 X_Outside_Processing_Account NUMBER,
381 X_Material_Variance_Account NUMBER,
382 X_Overhead_Variance_Account NUMBER,
383 X_Resource_Variance_Account NUMBER,
384 X_O_Proc_Variance_Account NUMBER,
385 X_Attribute_Category VARCHAR2,
386 X_Attribute1 VARCHAR2,
387 X_Attribute2 VARCHAR2,
388 X_Attribute3 VARCHAR2,
389 X_Attribute4 VARCHAR2,
390 X_Attribute5 VARCHAR2,
391 X_Attribute6 VARCHAR2,
392 X_Attribute7 VARCHAR2,
393 X_Attribute8 VARCHAR2,
394 X_Attribute9 VARCHAR2,
395 X_Attribute10 VARCHAR2,
396 X_Attribute11 VARCHAR2,
397 X_Attribute12 VARCHAR2,
398 X_Attribute13 VARCHAR2,
399 X_Attribute14 VARCHAR2,
400 X_Attribute15 VARCHAR2
401
402 ) IS
403 BEGIN
404 UPDATE WIP_REPETITIVE_SCHEDULES
405 SET
406 repetitive_schedule_id = X_Repetitive_Schedule_Id,
407 organization_id = X_Organization_Id,
408 last_update_date = X_Last_Update_Date,
409 last_updated_by = X_Last_Updated_By,
410 last_update_login = X_Last_Update_Login,
411 wip_entity_id = X_Wip_Entity_Id,
412 line_id = X_Line_Id,
413 daily_production_rate = X_Daily_Production_Rate,
414 processing_work_days = X_Processing_Work_Days,
415 status_type = X_Status_Type,
416 firm_planned_flag = X_Firm_Planned_Flag,
417 alternate_bom_designator = X_Alternate_Bom_Designator,
418 common_bom_sequence_id = X_Common_Bom_Sequence_Id,
419 bom_revision = X_Bom_Revision,
420 bom_revision_date = X_Bom_Revision_Date,
421 alternate_routing_designator = X_Alternate_Routing_Designator,
422 common_routing_sequence_id = X_Common_Routing_Sequence_Id,
423 routing_revision = X_Routing_Revision,
424 routing_revision_date = X_Routing_Revision_Date,
425 first_unit_start_date = X_First_Unit_Start_Date,
426 first_unit_completion_date = X_First_Unit_Completion_Date,
427 last_unit_start_date = X_Last_Unit_Start_Date,
428 last_unit_completion_date = X_Last_Unit_Completion_Date,
429 date_released = X_Date_Released,
430 date_closed = X_Date_Closed,
431 description = X_Description,
432 demand_class = X_Demand_Class,
433 material_account = X_Material_Account,
434 material_overhead_account = X_Material_Overhead_Account,
435 resource_account = X_Resource_Account,
436 overhead_account = X_Overhead_Account,
437 outside_processing_account = X_Outside_Processing_Account,
438 material_variance_account = X_Material_Variance_Account,
439 overhead_variance_account = X_Overhead_Variance_Account,
440 resource_variance_account = X_Resource_Variance_Account,
441 outside_proc_variance_account = X_O_Proc_Variance_Account,
442 attribute_category = X_Attribute_Category,
443 attribute1 = X_Attribute1,
444 attribute2 = X_Attribute2,
445 attribute3 = X_Attribute3,
446 attribute4 = X_Attribute4,
447 attribute5 = X_Attribute5,
448 attribute6 = X_Attribute6,
449 attribute7 = X_Attribute7,
450 attribute8 = X_Attribute8,
451 attribute9 = X_Attribute9,
452 attribute10 = X_Attribute10,
453 attribute11 = X_Attribute11,
457 attribute15 = X_Attribute15
454 attribute12 = X_Attribute12,
455 attribute13 = X_Attribute13,
456 attribute14 = X_Attribute14,
458 WHERE rowid = X_Rowid;
459
460 if (SQL%NOTFOUND) then
461 Raise NO_DATA_FOUND;
462 end if;
463 END Update_Row;
464 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
465 BEGIN
466 DELETE FROM WIP_REPETITIVE_SCHEDULES
467 WHERE rowid = X_Rowid;
468
469 if (SQL%NOTFOUND) then
470 Raise NO_DATA_FOUND;
471 end if;
472 END Delete_Row;
473
474
475 END WIP_REPETITIVE_SCHEDULES_PKG;