[Home] [Help]
PACKAGE BODY: APPS.MRP_SCHEDULE_DATES_PKG
Source
1 PACKAGE BODY MRP_SCHEDULE_DATES_PKG AS
2 /* $Header: MRSDATEB.pls 115.0 99/07/16 12:44:32 porting ship $ */
3
4 PROCEDURE Insert_Row(
5 X_Rowid IN OUT VARCHAR2,
6 X_MPS_Transaction_Id NUMBER,
7 X_Schedule_Level NUMBER,
8 X_Supply_Demand_Type NUMBER,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_Last_Update_Login NUMBER DEFAULT NULL,
14 X_Inventory_Item_Id NUMBER,
15 X_Organization_Id NUMBER,
16 X_Schedule_Designator VARCHAR2,
17 X_Schedule_Date DATE,
18 X_Schedule_Workdate DATE,
19 X_Rate_End_Date DATE DEFAULT NULL,
20 X_Schedule_Quantity NUMBER DEFAULT NULL,
21 X_Original_Schedule_Quantity NUMBER DEFAULT NULL,
22 X_Repetitive_Daily_Rate NUMBER DEFAULT NULL,
23 X_Schedule_Origination_Type NUMBER,
24 X_Source_Forecast_Designator VARCHAR2 DEFAULT NULL,
25 X_Reference_Schedule_Id NUMBER DEFAULT NULL,
26 X_Schedule_Comments VARCHAR2 DEFAULT NULL,
27 X_Source_Organization_Id NUMBER DEFAULT NULL,
28 X_Source_Schedule_Designator VARCHAR2 DEFAULT NULL,
29 X_Source_Sales_Order_Id NUMBER DEFAULT NULL,
30 X_Source_Code VARCHAR2 DEFAULT NULL,
31 X_Source_Line_Id NUMBER DEFAULT NULL,
32 X_Reservation_Id NUMBER DEFAULT NULL,
33 X_Forecast_Id NUMBER DEFAULT NULL,
34 X_Request_Id NUMBER DEFAULT NULL,
35 X_Program_Application_Id NUMBER DEFAULT NULL,
36 X_Program_Id NUMBER DEFAULT NULL,
37 X_Program_Update_Date DATE DEFAULT NULL,
38 X_DDF_Context VARCHAR2 DEFAULT NULL,
39 X_Attribute_Category VARCHAR2 DEFAULT NULL,
40 X_Attribute1 VARCHAR2 DEFAULT NULL,
41 X_Attribute2 VARCHAR2 DEFAULT NULL,
42 X_Attribute3 VARCHAR2 DEFAULT NULL,
43 X_Attribute4 VARCHAR2 DEFAULT NULL,
44 X_Attribute5 VARCHAR2 DEFAULT NULL,
45 X_Attribute6 VARCHAR2 DEFAULT NULL,
46 X_Attribute7 VARCHAR2 DEFAULT NULL,
47 X_Attribute8 VARCHAR2 DEFAULT NULL,
48 X_Attribute9 VARCHAR2 DEFAULT NULL,
49 X_Attribute10 VARCHAR2 DEFAULT NULL,
50 X_Attribute11 VARCHAR2 DEFAULT NULL,
51 X_Attribute12 VARCHAR2 DEFAULT NULL,
52 X_Attribute13 VARCHAR2 DEFAULT NULL,
53 X_Attribute14 VARCHAR2 DEFAULT NULL,
54 X_Attribute15 VARCHAR2 DEFAULT NULL
55 ) IS
56
57 CURSOR C IS SELECT rowid
58 FROM MRP_SCHEDULE_DATES
59 WHERE mps_transaction_id = X_MPS_Transaction_Id
60 AND schedule_level = X_Schedule_Level
61 AND supply_demand_type = X_Supply_Demand_Type;
62
63 BEGIN
64
65 INSERT INTO MRP_SCHEDULE_dates(
66 mps_transaction_id,
67 schedule_level,
68 supply_demand_type,
69 last_update_date,
70 last_updated_by,
71 creation_date,
72 created_by,
73 last_update_login,
74 inventory_item_id,
75 organization_id,
76 schedule_designator,
77 schedule_date,
78 schedule_workdate,
79 rate_end_date,
80 schedule_quantity,
81 original_schedule_quantity,
82 repetitive_daily_rate,
83 schedule_origination_type,
84 source_forecast_designator,
85 reference_schedule_id,
86 schedule_comments,
87 source_organization_id,
88 source_schedule_designator,
89 source_sales_order_id,
90 source_code,
91 source_line_id,
92 reservation_id,
93 forecast_id,
94 request_id,
95 program_application_id,
96 program_id,
97 program_update_date,
98 ddf_context,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15
115 ) VALUES (
116 X_MPS_Transaction_Id,
117 X_Schedule_Level,
118 X_Supply_Demand_Type,
119 X_Last_Update_Date,
120 X_Last_Updated_By,
121 X_Creation_Date,
122 X_Created_By,
123 X_Last_Update_Login,
124 X_Inventory_Item_Id,
125 X_Organization_Id,
126 X_Schedule_Designator,
127 X_Schedule_Date,
128 X_Schedule_Workdate,
129 X_Rate_End_Date,
130 X_Schedule_Quantity,
131 X_Original_Schedule_Quantity,
132 X_Repetitive_Daily_Rate,
133 X_Schedule_Origination_Type,
134 X_Source_Forecast_Designator,
135 X_Reference_Schedule_Id,
136 X_Schedule_Comments,
137 X_Source_Organization_Id,
138 X_Source_Schedule_Designator,
139 X_Source_Sales_Order_Id,
140 X_Source_Code,
141 X_Source_Line_Id,
142 X_Reservation_Id,
143 X_Forecast_Id,
144 X_Request_Id,
145 X_Program_Application_Id,
146 X_Program_Id,
147 X_Program_Update_Date,
148 X_DDF_Context,
149 X_Attribute_Category,
150 X_Attribute1,
151 X_Attribute2,
152 X_Attribute3,
153 X_Attribute4,
154 X_Attribute5,
155 X_Attribute6,
156 X_Attribute7,
157 X_Attribute8,
158 X_Attribute9,
159 X_Attribute10,
160 X_Attribute11,
161 X_Attribute12,
162 X_Attribute13,
163 X_Attribute14,
164 X_Attribute15
165 );
166
167 OPEN C;
168 FETCH C INTO X_Rowid;
169 if (C%NOTFOUND) then
170 CLOSE C;
171 RAISE NO_DATA_FOUND;
172 end if;
173 CLOSE C;
174
175 END Insert_Row;
176
177
178 PROCEDURE Lock_Row(
179 X_Rowid VARCHAR2,
180 X_MPS_Transaction_Id NUMBER,
181 X_Schedule_Level NUMBER,
182 X_Supply_Demand_Type NUMBER,
183 X_Last_Update_Date DATE,
184 X_Last_Updated_By NUMBER,
185 X_Creation_Date DATE,
186 X_Created_By NUMBER,
187 X_Last_Update_Login NUMBER DEFAULT NULL,
188 X_Inventory_Item_Id NUMBER,
189 X_Organization_Id NUMBER,
190 X_Schedule_Designator VARCHAR2,
191 X_Schedule_Date DATE,
192 X_Schedule_Workdate DATE,
193 X_Rate_End_Date DATE DEFAULT NULL,
194 X_Schedule_Quantity NUMBER DEFAULT NULL,
195 X_Original_Schedule_Quantity NUMBER DEFAULT NULL,
196 X_Repetitive_Daily_Rate NUMBER DEFAULT NULL,
197 X_Schedule_Origination_Type NUMBER,
198 X_Source_Forecast_Designator VARCHAR2 DEFAULT NULL,
199 X_Reference_Schedule_Id NUMBER DEFAULT NULL,
200 X_Schedule_Comments VARCHAR2 DEFAULT NULL,
201 X_Source_Organization_Id NUMBER DEFAULT NULL,
202 X_Source_Schedule_Designator VARCHAR2 DEFAULT NULL,
203 X_Source_Sales_Order_Id NUMBER DEFAULT NULL,
204 X_Source_Code VARCHAR2 DEFAULT NULL,
205 X_Source_Line_Id NUMBER DEFAULT NULL,
206 X_Reservation_Id NUMBER DEFAULT NULL,
207 X_Forecast_Id NUMBER DEFAULT NULL,
208 X_Request_Id NUMBER DEFAULT NULL,
209 X_Program_Application_Id NUMBER DEFAULT NULL,
210 X_Program_Id NUMBER DEFAULT NULL,
211 X_Program_Update_Date DATE DEFAULT NULL,
212 X_DDF_Context VARCHAR2 DEFAULT NULL,
213 X_Attribute_Category VARCHAR2 DEFAULT NULL,
214 X_Attribute1 VARCHAR2 DEFAULT NULL,
215 X_Attribute2 VARCHAR2 DEFAULT NULL,
216 X_Attribute3 VARCHAR2 DEFAULT NULL,
217 X_Attribute4 VARCHAR2 DEFAULT NULL,
218 X_Attribute5 VARCHAR2 DEFAULT NULL,
219 X_Attribute6 VARCHAR2 DEFAULT NULL,
220 X_Attribute7 VARCHAR2 DEFAULT NULL,
221 X_Attribute8 VARCHAR2 DEFAULT NULL,
222 X_Attribute9 VARCHAR2 DEFAULT NULL,
223 X_Attribute10 VARCHAR2 DEFAULT NULL,
224 X_Attribute11 VARCHAR2 DEFAULT NULL,
225 X_Attribute12 VARCHAR2 DEFAULT NULL,
226 X_Attribute13 VARCHAR2 DEFAULT NULL,
227 X_Attribute14 VARCHAR2 DEFAULT NULL,
228 X_Attribute15 VARCHAR2 DEFAULT NULL
229 ) IS
230
231 CURSOR C IS
232 SELECT *
233 FROM MRP_SCHEDULE_DATES
234 WHERE rowid = X_Rowid
235 FOR UPDATE OF mps_transaction_id NOWAIT;
236
237 Recinfo C%ROWTYPE;
238
239 BEGIN
240
241 OPEN C;
242 FETCH C INTO Recinfo;
243
244 if (C%NOTFOUND) then
245 CLOSE C;
246 RAISE NO_DATA_FOUND;
247 end if;
248
249 CLOSE C;
250
251 if (
252 (Recinfo.mps_transaction_id = X_MPS_Transaction_Id)
253 AND (Recinfo.schedule_level = X_Schedule_Level)
254 AND (Recinfo.supply_demand_type = X_Supply_Demand_Type)
255 AND (Recinfo.inventory_item_id = X_Inventory_Item_Id)
256 AND (Recinfo.organization_id = X_Organization_Id)
257 AND (Recinfo.schedule_designator = X_Schedule_Designator)
258 AND (Recinfo.schedule_date = X_Schedule_Date)
259 AND (Recinfo.schedule_workdate = X_Schedule_Workdate)
260 AND ( (Recinfo.rate_end_date = X_Rate_End_Date)
261 OR ( (Recinfo.rate_end_date IS NULL)
262 AND (X_Rate_End_Date IS NULL)))
263 AND ( (Recinfo.schedule_quantity = X_Schedule_Quantity)
264 OR ( (Recinfo.schedule_quantity IS NULL)
265 AND (X_Schedule_Quantity IS NULL)))
266 AND ( (Recinfo.original_schedule_quantity = X_Original_Schedule_Quantity)
267 OR ( (Recinfo.original_schedule_quantity IS NULL)
268 AND (X_Original_Schedule_Quantity IS NULL)))
269 AND ( (Recinfo.repetitive_daily_rate = X_Repetitive_Daily_Rate)
270 OR ( (Recinfo.repetitive_daily_rate IS NULL)
271 AND (X_Repetitive_Daily_Rate IS NULL)))
272 AND (Recinfo.schedule_origination_type = X_Schedule_Origination_Type)
273 AND ( (Recinfo.source_forecast_designator = X_Source_Forecast_Designator)
274 OR ( (Recinfo.source_forecast_designator IS NULL)
275 AND (X_Source_Forecast_Designator IS NULL)))
276 AND ( (Recinfo.reference_schedule_id = X_Reference_Schedule_Id)
277 OR ( (Recinfo.reference_schedule_id IS NULL)
278 AND (X_Reference_Schedule_Id IS NULL)))
279 AND ( (Recinfo.schedule_comments = X_Schedule_Comments)
280 OR ( (Recinfo.schedule_comments IS NULL)
281 AND (X_Schedule_Comments IS NULL)))
282 AND ( (Recinfo.source_organization_id = X_Source_Organization_Id)
283 OR ( (Recinfo.source_organization_id IS NULL)
284 AND (X_Source_Organization_Id IS NULL)))
285 AND ( (Recinfo.source_schedule_designator = X_Source_Schedule_Designator)
286 OR ( (Recinfo.source_schedule_designator IS NULL)
287 AND (X_Source_Schedule_Designator IS NULL)))
288 AND ( (Recinfo.source_sales_order_id = X_Source_Sales_Order_Id)
289 OR ( (Recinfo.source_sales_order_id IS NULL)
290 AND (X_Source_Sales_Order_Id IS NULL)))
291 AND ( (Recinfo.source_code = X_Source_Code)
292 OR ( (Recinfo.source_code IS NULL)
293 AND (X_Source_Code IS NULL)))
294 AND ( (Recinfo.source_line_id = X_Source_Line_Id)
295 OR ( (Recinfo.source_line_id IS NULL)
296 AND (X_Source_Line_Id IS NULL)))
297 AND ( (Recinfo.reservation_id = X_Reservation_Id)
298 OR ( (Recinfo.reservation_id IS NULL)
299 AND (X_Reservation_Id IS NULL)))
300 AND ( (Recinfo.forecast_id = X_Forecast_Id)
301 OR ( (Recinfo.forecast_id IS NULL)
302 AND (X_Forecast_Id IS NULL)))
303 AND ( (Recinfo.ddf_context = X_DDF_Context)
307 OR ( (Recinfo.attribute_category IS NULL)
304 OR ( (Recinfo.ddf_context IS NULL)
305 AND (X_DDF_Context IS NULL)))
306 AND ( (Recinfo.attribute_category = X_Attribute_Category)
308 AND (X_Attribute_Category IS NULL)))
309 AND ( (Recinfo.attribute1 = X_Attribute1)
310 OR ( (Recinfo.attribute1 IS NULL)
311 AND (X_Attribute1 IS NULL)))
312 AND ( (Recinfo.attribute2 = X_Attribute2)
313 OR ( (Recinfo.attribute2 IS NULL)
314 AND (X_Attribute2 IS NULL)))
315 AND ( (Recinfo.attribute3 = X_Attribute3)
316 OR ( (Recinfo.attribute3 IS NULL)
317 AND (X_Attribute3 IS NULL)))
318 AND ( (Recinfo.attribute4 = X_Attribute4)
319 OR ( (Recinfo.attribute4 IS NULL)
320 AND (X_Attribute4 IS NULL)))
321 AND ( (Recinfo.attribute5 = X_Attribute5)
322 OR ( (Recinfo.attribute5 IS NULL)
323 AND (X_Attribute5 IS NULL)))
324 AND ( (Recinfo.attribute6 = X_Attribute6)
325 OR ( (Recinfo.attribute6 IS NULL)
326 AND (X_Attribute6 IS NULL)))
327 AND ( (Recinfo.attribute7 = X_Attribute7)
328 OR ( (Recinfo.attribute7 IS NULL)
329 AND (X_Attribute7 IS NULL)))
330 AND ( (Recinfo.attribute8 = X_Attribute8)
331 OR ( (Recinfo.attribute8 IS NULL)
332 AND (X_Attribute8 IS NULL)))
333 AND ( (Recinfo.attribute9 = X_Attribute9)
334 OR ( (Recinfo.attribute9 IS NULL)
335 AND (X_Attribute9 IS NULL)))
336 AND ( (Recinfo.attribute10 = X_Attribute10)
337 OR ( (Recinfo.attribute10 IS NULL)
338 AND (X_Attribute10 IS NULL)))
339 AND ( (Recinfo.attribute11 = X_Attribute11)
340 OR ( (Recinfo.attribute11 IS NULL)
341 AND (X_Attribute11 IS NULL)))
342 AND ( (Recinfo.attribute12 = X_Attribute12)
343 OR ( (Recinfo.attribute12 IS NULL)
344 AND (X_Attribute12 IS NULL)))
345 AND ( (Recinfo.attribute13 = X_Attribute13)
346 OR ( (Recinfo.attribute13 IS NULL)
347 AND (X_Attribute13 IS NULL)))
348 AND ( (Recinfo.attribute14 = X_Attribute14)
349 OR ( (Recinfo.attribute14 IS NULL)
350 AND (X_Attribute14 IS NULL)))
351 AND ( (Recinfo.attribute15 = X_Attribute15)
352 OR ( (Recinfo.attribute15 IS NULL)
353 AND (X_Attribute15 IS NULL)))
354 ) then
355 return;
356 else
357 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
358 APP_EXCEPTION.RAISE_EXCEPTION;
359 end if;
360 END Lock_Row;
361
362
363 PROCEDURE Update_Row(
364 X_Rowid VARCHAR2,
365 X_MPS_Transaction_Id NUMBER,
366 X_Schedule_Level NUMBER,
367 X_Supply_Demand_Type NUMBER,
368 X_Last_Update_Date DATE,
369 X_Last_Updated_By NUMBER,
370 X_Creation_Date DATE,
371 X_Created_By NUMBER,
372 X_Last_Update_Login NUMBER DEFAULT NULL,
373 X_Inventory_Item_Id NUMBER,
374 X_Organization_Id NUMBER,
375 X_Schedule_Designator VARCHAR2,
376 X_Schedule_Date DATE,
377 X_Schedule_Workdate DATE,
378 X_Rate_End_Date DATE DEFAULT NULL,
379 X_Schedule_Quantity NUMBER DEFAULT NULL,
380 X_Original_Schedule_Quantity NUMBER DEFAULT NULL,
381 X_Repetitive_Daily_Rate NUMBER DEFAULT NULL,
382 X_Schedule_Origination_Type NUMBER,
383 X_Source_Forecast_Designator VARCHAR2 DEFAULT NULL,
384 X_Reference_Schedule_Id NUMBER DEFAULT NULL,
385 X_Schedule_Comments VARCHAR2 DEFAULT NULL,
386 X_Source_Organization_Id NUMBER DEFAULT NULL,
387 X_Source_Schedule_Designator VARCHAR2 DEFAULT NULL,
388 X_Source_Sales_Order_Id NUMBER DEFAULT NULL,
389 X_Source_Code VARCHAR2 DEFAULT NULL,
390 X_Source_Line_Id NUMBER DEFAULT NULL,
391 X_Reservation_Id NUMBER DEFAULT NULL,
392 X_Forecast_Id NUMBER DEFAULT NULL,
393 X_Request_Id NUMBER DEFAULT NULL,
394 X_Program_Application_Id NUMBER DEFAULT NULL,
395 X_Program_Id NUMBER DEFAULT NULL,
396 X_Program_Update_Date DATE DEFAULT NULL,
397 X_DDF_Context VARCHAR2 DEFAULT NULL,
398 X_Attribute_Category VARCHAR2 DEFAULT NULL,
399 X_Attribute1 VARCHAR2 DEFAULT NULL,
400 X_Attribute2 VARCHAR2 DEFAULT NULL,
401 X_Attribute3 VARCHAR2 DEFAULT NULL,
402 X_Attribute4 VARCHAR2 DEFAULT NULL,
403 X_Attribute5 VARCHAR2 DEFAULT NULL,
404 X_Attribute6 VARCHAR2 DEFAULT NULL,
405 X_Attribute7 VARCHAR2 DEFAULT NULL,
406 X_Attribute8 VARCHAR2 DEFAULT NULL,
410 X_Attribute12 VARCHAR2 DEFAULT NULL,
407 X_Attribute9 VARCHAR2 DEFAULT NULL,
408 X_Attribute10 VARCHAR2 DEFAULT NULL,
409 X_Attribute11 VARCHAR2 DEFAULT NULL,
411 X_Attribute13 VARCHAR2 DEFAULT NULL,
412 X_Attribute14 VARCHAR2 DEFAULT NULL,
413 X_Attribute15 VARCHAR2 DEFAULT NULL
414 ) IS
415
416 BEGIN
417
418 UPDATE MRP_SCHEDULE_DATES
419 SET
420 mps_transaction_id = X_MPS_Transaction_Id,
421 schedule_level = X_Schedule_Level,
422 supply_demand_type = X_Supply_Demand_Type,
423 last_update_date = X_Last_Update_Date,
424 last_updated_by = X_Last_Updated_By,
425 creation_date = X_Creation_Date,
426 created_by = X_Created_By,
427 last_update_login = X_Last_Update_Login,
428 inventory_item_id = X_Inventory_Item_Id,
429 organization_id = X_Organization_Id,
430 schedule_designator = X_Schedule_Designator,
431 schedule_date = X_Schedule_Date,
432 schedule_workdate = X_Schedule_Workdate,
433 rate_end_date = X_Rate_End_Date,
434 schedule_quantity = X_Schedule_Quantity,
435 original_schedule_quantity = X_Original_Schedule_Quantity,
436 repetitive_daily_rate = X_Repetitive_Daily_Rate,
437 schedule_origination_type = X_Schedule_Origination_Type,
438 source_forecast_designator = X_Source_Forecast_Designator,
439 reference_schedule_id = X_Reference_Schedule_Id,
440 schedule_comments = X_Schedule_Comments,
441 source_organization_id = X_Source_Organization_Id,
442 source_schedule_designator = X_Source_Schedule_Designator,
443 source_sales_order_id = X_Source_Sales_Order_Id,
444 source_code = X_Source_Code,
445 source_line_id = X_Source_Line_Id,
446 reservation_id = X_Reservation_Id,
447 forecast_id = X_Forecast_Id,
448 request_id = X_Request_Id,
449 program_application_id = X_Program_Application_Id,
450 program_id = X_Program_Id,
451 program_update_date = X_Program_Update_Date,
452 ddf_context = X_DDF_Context,
453 attribute_category = X_Attribute_Category,
454 attribute1 = X_Attribute1,
455 attribute2 = X_Attribute2,
456 attribute3 = X_Attribute3,
457 attribute4 = X_Attribute4,
458 attribute5 = X_Attribute5,
459 attribute6 = X_Attribute6,
460 attribute7 = X_Attribute7,
461 attribute8 = X_Attribute8,
462 attribute9 = X_Attribute9,
463 attribute10 = X_Attribute10,
464 attribute11 = X_Attribute11,
465 attribute12 = X_Attribute12,
466 attribute13 = X_Attribute13,
467 attribute14 = X_Attribute14,
468 attribute15 = X_Attribute15
469 WHERE rowid = X_rowid;
470
471 if (SQL%NOTFOUND) then
472 RAISE NO_DATA_FOUND;
473 end if;
474
475 END Update_Row;
476
477
478 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
479
480 BEGIN
481 DELETE FROM MRP_SCHEDULE_DATES
482 WHERE rowid = X_Rowid;
483
484 if (SQL%NOTFOUND) then
485 RAISE NO_DATA_FOUND;
486 end if;
487 END Delete_Row;
488
489
490 PROCEDURE Get_Unique_Id(X_Unique_Id IN OUT NUMBER) IS
491
492 CURSOR C IS
493 SELECT mrp_schedule_dates_s.nextval
494 FROM DUAL;
495
496 BEGIN
497
498 OPEN C;
499
500 FETCH C INTO X_Unique_Id;
501
502 if (C%NOTFOUND) then
503 CLOSE C;
504 RAISE NO_DATA_FOUND;
505 end if;
506
507 CLOSE C;
508
509 END Get_Unique_Id;
510
511
512 END MRP_SCHEDULE_DATES_PKG;