[Home] [Help]
PACKAGE BODY: APPS.MRP_SCHEDULE_ITEMS_PKG
Source
1 PACKAGE BODY MRP_SCHEDULE_ITEMS_PKG AS
2 /* $Header: MRSITEMB.pls 115.1 99/07/16 12:44:51 porting ship $ */
3
4 PROCEDURE Insert_Row(
5 X_Rowid IN OUT VARCHAR2,
6 X_Inventory_Item_Id NUMBER,
7 X_Organization_Id NUMBER,
8 X_Schedule_Designator VARCHAR2,
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_MPS_Explosion_Level NUMBER DEFAULT NULL,
15 X_Request_Id NUMBER DEFAULT NULL,
16 X_Program_Application_Id NUMBER DEFAULT NULL,
17 X_Program_Id NUMBER DEFAULT NULL,
18 X_Program_Update_Date DATE DEFAULT NULL,
19 X_Attribute_Category VARCHAR2 DEFAULT NULL,
20 X_Attribute1 VARCHAR2 DEFAULT NULL,
21 X_Attribute2 VARCHAR2 DEFAULT NULL,
22 X_Attribute3 VARCHAR2 DEFAULT NULL,
23 X_Attribute4 VARCHAR2 DEFAULT NULL,
24 X_Attribute5 VARCHAR2 DEFAULT NULL,
25 X_Attribute6 VARCHAR2 DEFAULT NULL,
26 X_Attribute7 VARCHAR2 DEFAULT NULL,
27 X_Attribute8 VARCHAR2 DEFAULT NULL,
28 X_Attribute9 VARCHAR2 DEFAULT NULL,
29 X_Attribute10 VARCHAR2 DEFAULT NULL,
30 X_Attribute11 VARCHAR2 DEFAULT NULL,
31 X_Attribute12 VARCHAR2 DEFAULT NULL,
32 X_Attribute13 VARCHAR2 DEFAULT NULL,
33 X_Attribute14 VARCHAR2 DEFAULT NULL,
34 X_Attribute15 VARCHAR2 DEFAULT NULL,
35 X_Capacity_Model_Id NUMBER DEFAULT NULL
36 ) IS
37
38 CURSOR C IS SELECT rowid
39 FROM MRP_SCHEDULE_ITEMS
40 WHERE inventory_item_id = X_Inventory_Item_Id
41 AND schedule_designator = X_Schedule_Designator
42 AND organization_id = X_Organization_Id;
43
44 BEGIN
45
46 INSERT INTO MRP_SCHEDULE_ITEMS(
47 inventory_item_id,
48 organization_id,
49 schedule_designator,
50 last_update_date,
51 last_updated_by,
52 creation_date,
53 created_by,
54 last_update_login,
55 mps_explosion_level,
56 request_id,
57 program_application_id,
58 program_id,
59 program_update_date,
60 attribute_category,
61 attribute1,
62 attribute2,
63 attribute3,
64 attribute4,
65 attribute5,
66 attribute6,
67 attribute7,
68 attribute8,
69 attribute9,
70 attribute10,
71 attribute11,
72 attribute12,
73 attribute13,
74 attribute14,
75 attribute15,
76 capacity_model_id
77 ) VALUES (
78 X_Inventory_Item_Id,
79 X_Organization_Id,
80 X_Schedule_Designator,
81 X_Last_Update_Date,
82 X_Last_Updated_By,
83 X_Creation_Date,
84 X_Created_By,
85 X_Last_Update_Login,
86 X_MPS_Explosion_Level,
87 X_Request_Id,
88 X_Program_Application_Id,
89 X_Program_Id,
90 X_Program_Update_Date,
91 X_Attribute_Category,
92 X_Attribute1,
93 X_Attribute2,
94 X_Attribute3,
95 X_Attribute4,
96 X_Attribute5,
97 X_Attribute6,
98 X_Attribute7,
99 X_Attribute8,
100 X_Attribute9,
101 X_Attribute10,
102 X_Attribute11,
103 X_Attribute12,
104 X_Attribute13,
105 X_Attribute14,
106 X_Attribute15,
107 X_Capacity_Model_Id
108 );
109
110 OPEN C;
111 FETCH C INTO X_Rowid;
112 if (C%NOTFOUND) then
113 CLOSE C;
114 RAISE NO_DATA_FOUND;
115 end if;
116 CLOSE C;
117
118 END Insert_Row;
119
120
121 PROCEDURE Lock_Row(
122 X_Rowid VARCHAR2,
123 X_Inventory_Item_Id NUMBER,
124 X_Organization_Id NUMBER,
125 X_Schedule_Designator VARCHAR2,
126 X_Last_Update_Date DATE,
127 X_Last_Updated_By NUMBER,
128 X_Creation_Date DATE,
129 X_Created_By NUMBER,
130 X_Last_Update_Login NUMBER DEFAULT NULL,
131 X_MPS_Explosion_Level NUMBER DEFAULT NULL,
132 X_Request_Id NUMBER DEFAULT NULL,
133 X_Program_Application_Id NUMBER DEFAULT NULL,
134 X_Program_Id NUMBER DEFAULT NULL,
135 X_Program_Update_Date DATE DEFAULT NULL,
136 X_Attribute_Category VARCHAR2 DEFAULT NULL,
137 X_Attribute1 VARCHAR2 DEFAULT NULL,
138 X_Attribute2 VARCHAR2 DEFAULT NULL,
139 X_Attribute3 VARCHAR2 DEFAULT NULL,
140 X_Attribute4 VARCHAR2 DEFAULT NULL,
141 X_Attribute5 VARCHAR2 DEFAULT NULL,
142 X_Attribute6 VARCHAR2 DEFAULT NULL,
143 X_Attribute7 VARCHAR2 DEFAULT NULL,
144 X_Attribute8 VARCHAR2 DEFAULT NULL,
145 X_Attribute9 VARCHAR2 DEFAULT NULL,
146 X_Attribute10 VARCHAR2 DEFAULT NULL,
147 X_Attribute11 VARCHAR2 DEFAULT NULL,
148 X_Attribute12 VARCHAR2 DEFAULT NULL,
149 X_Attribute13 VARCHAR2 DEFAULT NULL,
150 X_Attribute14 VARCHAR2 DEFAULT NULL,
151 X_Attribute15 VARCHAR2 DEFAULT NULL,
152 X_Capacity_Model_Id NUMBER DEFAULT NULL
153 ) IS
154
155 CURSOR C IS
156 SELECT *
157 FROM MRP_SCHEDULE_ITEMS
158 WHERE rowid = X_Rowid
159 FOR UPDATE of inventory_item_id NOWAIT;
160
161 Recinfo C%ROWTYPE;
162
163 BEGIN
164
165 OPEN C;
166 FETCH C INTO Recinfo;
167
168 if (C%NOTFOUND) then
169 CLOSE C;
170 RAISE NO_DATA_FOUND;
171 end if;
172
173 CLOSE C;
174
175 if (
176 (Recinfo.inventory_item_id = X_Inventory_Item_Id)
177 AND (Recinfo.organization_id = X_Organization_Id)
178 AND (Recinfo.schedule_designator = X_Schedule_Designator)
179 AND ( (Recinfo.mps_explosion_level = X_MPS_Explosion_Level)
180 OR ( (Recinfo.mps_explosion_level IS NULL)
181 AND (X_MPS_Explosion_Level IS NULL)))
182 AND ( (Recinfo.attribute_category = X_Attribute_Category)
183 OR ( (Recinfo.attribute_category IS NULL)
184 AND (X_Attribute_Category IS NULL)))
185 AND ( (Recinfo.attribute1 = X_Attribute1)
186 OR ( (Recinfo.attribute1 IS NULL)
187 AND (X_Attribute1 IS NULL)))
188 AND ( (Recinfo.attribute2 = X_Attribute2)
189 OR ( (Recinfo.attribute2 IS NULL)
190 AND (X_Attribute2 IS NULL)))
191 AND ( (Recinfo.attribute3 = X_Attribute3)
192 OR ( (Recinfo.attribute3 IS NULL)
193 AND (X_Attribute3 IS NULL)))
194 AND ( (Recinfo.attribute4 = X_Attribute4)
195 OR ( (Recinfo.attribute4 IS NULL)
196 AND (X_Attribute4 IS NULL)))
197 AND ( (Recinfo.attribute5 = X_Attribute5)
198 OR ( (Recinfo.attribute5 IS NULL)
199 AND (X_Attribute5 IS NULL)))
200 AND ( (Recinfo.attribute6 = X_Attribute6)
201 OR ( (Recinfo.attribute6 IS NULL)
202 AND (X_Attribute6 IS NULL)))
203 AND ( (Recinfo.attribute7 = X_Attribute7)
204 OR ( (Recinfo.attribute7 IS NULL)
205 AND (X_Attribute7 IS NULL)))
206 AND ( (Recinfo.attribute8 = X_Attribute8)
207 OR ( (Recinfo.attribute8 IS NULL)
208 AND (X_Attribute8 IS NULL)))
209 AND ( (Recinfo.attribute9 = X_Attribute9)
210 OR ( (Recinfo.attribute9 IS NULL)
211 AND (X_Attribute9 IS NULL)))
212 AND ( (Recinfo.attribute10 = X_Attribute10)
213 OR ( (Recinfo.attribute10 IS NULL)
214 AND (X_Attribute10 IS NULL)))
215 AND ( (Recinfo.attribute11 = X_Attribute11)
216 OR ( (Recinfo.attribute11 IS NULL)
217 AND (X_Attribute11 IS NULL)))
218 AND ( (Recinfo.attribute12 = X_Attribute12)
219 OR ( (Recinfo.attribute12 IS NULL)
220 AND (X_Attribute12 IS NULL)))
221 AND ( (Recinfo.attribute13 = X_Attribute13)
222 OR ( (Recinfo.attribute13 IS NULL)
223 AND (X_Attribute13 IS NULL)))
224 AND ( (Recinfo.attribute14 = X_Attribute14)
225 OR ( (Recinfo.attribute14 IS NULL)
226 AND (X_Attribute14 IS NULL)))
227 AND ( (Recinfo.attribute15 = X_Attribute15)
228 OR ( (Recinfo.attribute15 IS NULL)
229 AND (X_Attribute15 IS NULL)))
230 AND ( (Recinfo.capacity_model_id = X_Capacity_Model_Id)
231 OR ( (Recinfo.capacity_model_id IS NULL)
232 AND (X_Capacity_Model_Id IS NULL)))
233 ) then
234 return;
235 else
236 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 end if;
239 END Lock_Row;
240
241
242 PROCEDURE Update_Row(
243 X_Rowid VARCHAR2,
244 X_Inventory_Item_Id NUMBER,
245 X_Organization_Id NUMBER,
246 X_Schedule_Designator VARCHAR2,
247 X_Last_Update_Date DATE,
248 X_Last_Updated_By NUMBER,
249 X_Creation_Date DATE,
250 X_Created_By NUMBER,
251 X_Last_Update_Login NUMBER DEFAULT NULL,
252 X_MPS_Explosion_Level NUMBER DEFAULT NULL,
253 X_Request_Id NUMBER DEFAULT NULL,
254 X_Program_Application_Id NUMBER DEFAULT NULL,
255 X_Program_Id NUMBER DEFAULT NULL,
256 X_Program_Update_Date DATE DEFAULT NULL,
257 X_Attribute_Category VARCHAR2 DEFAULT NULL,
258 X_Attribute1 VARCHAR2 DEFAULT NULL,
259 X_Attribute2 VARCHAR2 DEFAULT NULL,
260 X_Attribute3 VARCHAR2 DEFAULT NULL,
261 X_Attribute4 VARCHAR2 DEFAULT NULL,
262 X_Attribute5 VARCHAR2 DEFAULT NULL,
263 X_Attribute6 VARCHAR2 DEFAULT NULL,
264 X_Attribute7 VARCHAR2 DEFAULT NULL,
265 X_Attribute8 VARCHAR2 DEFAULT NULL,
266 X_Attribute9 VARCHAR2 DEFAULT NULL,
267 X_Attribute10 VARCHAR2 DEFAULT NULL,
268 X_Attribute11 VARCHAR2 DEFAULT NULL,
269 X_Attribute12 VARCHAR2 DEFAULT NULL,
270 X_Attribute13 VARCHAR2 DEFAULT NULL,
271 X_Attribute14 VARCHAR2 DEFAULT NULL,
272 X_Attribute15 VARCHAR2 DEFAULT NULL,
273 X_Capacity_Model_Id NUMBER DEFAULT NULL
274 ) IS
275
276 BEGIN
277
278 UPDATE MRP_SCHEDULE_ITEMS
279 SET
280 inventory_item_id = X_Inventory_Item_Id,
281 organization_id = X_Organization_Id,
282 schedule_designator = X_Schedule_Designator,
283 last_update_date = X_Last_Update_Date,
284 last_updated_by = X_Last_Updated_By,
285 creation_date = X_Creation_Date,
286 created_by = X_Created_By,
287 last_update_login = X_Last_Update_Login,
288 mps_explosion_level = X_MPS_Explosion_Level,
289 request_id = X_Request_Id,
290 program_application_id = X_Program_Application_Id,
291 program_id = X_Program_Id,
292 program_update_date = X_Program_Update_Date,
293 attribute_category = X_Attribute_Category,
294 attribute1 = X_Attribute1,
295 attribute2 = X_Attribute2,
296 attribute3 = X_Attribute3,
297 attribute4 = X_Attribute4,
298 attribute5 = X_Attribute5,
299 attribute6 = X_Attribute6,
300 attribute7 = X_Attribute7,
301 attribute8 = X_Attribute8,
302 attribute9 = X_Attribute9,
306 attribute13 = X_Attribute13,
303 attribute10 = X_Attribute10,
304 attribute11 = X_Attribute11,
305 attribute12 = X_Attribute12,
307 attribute14 = X_Attribute14,
308 attribute15 = X_Attribute15,
309 capacity_model_id = X_Capacity_Model_Id
310 WHERE rowid = X_rowid;
311
312 if (SQL%NOTFOUND) then
313 RAISE NO_DATA_FOUND;
314 end if;
315
316 END Update_Row;
317
318
319 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
320
321 BEGIN
322 DELETE FROM MRP_SCHEDULE_ITEMS
323 WHERE rowid = X_Rowid;
324
325 if (SQL%NOTFOUND) then
326 RAISE NO_DATA_FOUND;
327 end if;
328
329 END Delete_Row;
330
331
332 PROCEDURE Get_Item_Info(X_organization_id NUMBER,
333 X_schedule_type NUMBER,
334 X_inventory_item_id NUMBER,
335 X_item_description IN OUT VARCHAR2,
336 X_primary_uom_code IN OUT VARCHAR2,
337 X_demand_time_fence_days IN OUT NUMBER,
338 X_planning_time_fence_days IN OUT NUMBER,
339 X_demand_time_fence_date IN OUT DATE,
340 X_planning_time_fence_date IN OUT DATE,
341 X_repetitive_planning_flag IN OUT VARCHAR2,
342 X_bom_item_type IN OUT NUMBER,
343 X_bom_item_type_text IN OUT VARCHAR2,
344 X_mrp_planning_code IN OUT NUMBER,
345 X_mrp_planning_code_text IN OUT VARCHAR2
346 ) IS
347
348 CURSOR C IS
349 SELECT msi.description,
350 msi.primary_uom_code,
351 NVL(msi.demand_time_fence_days,0),
352 NVL(msi.planning_time_fence_days,0),
353 DECODE(X_schedule_type,
354 1, DECODE(msi.demand_time_fence_days,
355 NULL, NULL,
356 cal2.calendar_date),
357 2, NULL),
358 DECODE(X_schedule_type,
359 1, NULL,
360 2, DECODE(msi.planning_time_fence_days,
361 NULL, NULL,
362 cal2.calendar_date)),
363 NVL(msi.repetitive_planning_flag,'N'),
364 msi.bom_item_type,
365 l1.meaning,
366 msi.mrp_planning_code,
367 l2.meaning
368 FROM MTL_SYSTEM_ITEMS msi,
369 BOM_CALENDAR_DATES cal2,
370 MTL_PARAMETERS param,
371 BOM_CALENDAR_DATES cal1,
372 MFG_LOOKUPS l1,
373 MFG_LOOKUPS l2
374 WHERE msi.organization_id = X_organization_id
375 AND msi.inventory_item_id = X_inventory_item_id
376 AND l1.lookup_type = 'BOM_ITEM_TYPE'
377 AND l1.lookup_code = msi.bom_item_type
378 AND l2.lookup_type(+) = 'MRP_PLANNING_CODE'
379 AND l2.lookup_code(+) = msi.mrp_planning_code
380 AND param.organization_id = msi.organization_id
381 AND cal2.exception_set_id = param.calendar_exception_set_id
382 AND cal2.calendar_code = param.calendar_code
383 AND cal2.seq_num = cal1.prior_seq_num +
384 DECODE(X_schedule_type,
385 1, NVL(msi.demand_time_fence_days, 0),
386 2, NVL(msi.planning_time_fence_days,0))
387 AND cal1.exception_set_id = param.calendar_exception_set_id
388 AND cal1.calendar_code = param.calendar_code
389 AND cal1.calendar_date = TRUNC(sysdate);
390
391 BEGIN
392
393 OPEN C;
394
395 FETCH C INTO X_item_description,
396 X_primary_uom_code,
397 X_demand_time_fence_days,
398 X_planning_time_fence_days,
399 X_demand_time_fence_date,
400 X_planning_time_fence_date,
401 X_repetitive_planning_flag,
402 X_bom_item_type,
403 X_bom_item_type_text,
404 X_mrp_planning_code,
405 X_mrp_planning_code_text;
406
407 if (C%NOTFOUND) then
408 CLOSE C;
409 RAISE NO_DATA_FOUND;
410 end if;
411
412 CLOSE C;
413
414 END Get_Item_Info;
415
416
417 PROCEDURE Delete_Details(X_organization_id NUMBER,
418 X_schedule_designator VARCHAR2,
419 X_inventory_item_id NUMBER
420 ) IS
421
422 BEGIN
423 DELETE
424 FROM mrp_schedule_dates
425 WHERE inventory_item_id = X_inventory_item_id
426 AND schedule_designator = X_schedule_designator
427 AND organization_id = X_organization_id;
428
429 IF (SQL%NOTFOUND) THEN
430 null;
431 END IF;
432
433 END Delete_Details;
434
435
436 FUNCTION Check_Unique(X_Rowid VARCHAR2,
437 X_organization_id NUMBER,
438 X_schedule_designator VARCHAR2,
439 X_inventory_item_id NUMBER)
440 RETURN BOOLEAN IS
441
442 dummy NUMBER;
443
444 BEGIN
445
446 SELECT 1
447 INTO dummy
448 FROM dual
449 WHERE NOT EXISTS (SELECT 1
450 FROM mrp_schedule_items
451 WHERE inventory_item_id = X_inventory_item_id
452 AND schedule_designator = X_schedule_designator
453 AND organization_id = X_organization_id
454 AND ( (X_rowid IS NULL)
455 OR (rowid <> X_rowid) )
456 );
457
458 RETURN(TRUE);
459
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 RETURN(FALSE);
463
464 END Check_Unique;
465
466
467 END MRP_SCHEDULE_ITEMS_PKG;