[Home] [Help]
PACKAGE BODY: APPS.FLM_PULLSEQUENCE
Source
1 PACKAGE BODY FLM_PullSequence as
2 /* $Header: FLMKBPSB.pls 115.4 2002/11/27 11:24:22 nrajpal noship $ */
3
4
5
6 FUNCTION Check_Unique( p_Organization_Id NUMBER,
7 p_Kanban_Plan_Id NUMBER,
8 p_Inventory_Item_Id NUMBER,
9 p_Subinventory_Name VARCHAR2,
10 p_Locator_Id NUMBER)
11 RETURN BOOLEAN IS
12 l_Dummy Varchar2(1);
13 BEGIN
14 Select 'x'
15 Into l_Dummy
16 From MTL_KANBAN_PULL_SEQUENCES
17 Where organization_id = p_Organization_Id
18 And kanban_plan_id = p_kanban_plan_id
19 And inventory_item_id = p_inventory_item_id
20 And subinventory_name = p_Subinventory_Name
21 And nvl(locator_id,-1)= nvl(p_locator_id,-1);
22
23 Return False;
24
25 Exception
26 When No_Data_found Then
27 Return True;
28
29 END Check_Unique;
30
31
32 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY Varchar2,
33 x_pull_sequence_id IN Out NOCOPY NUMBER,
34 p_Inventory_item_id NUMBER,
35 p_Organization_id NUMBER,
36 p_Subinventory_name VARCHAR2,
37 p_Kanban_Plan_id NUMBER,
38 p_Source_type NUMBER,
39 p_Last_Update_Date DATE,
40 p_Last_Updated_By NUMBER,
41 p_Creation_Date DATE,
42 p_Created_By NUMBER,
43 p_Last_Update_Login NUMBER,
44 p_Locator_id NUMBER,
45 p_Supplier_id NUMBER,
46 p_Supplier_site_id NUMBER,
47 p_Source_Organization_id NUMBER,
48 p_Source_Subinventory VARCHAR2,
49 p_Source_Locator_id NUMBER,
50 p_Wip_Line_id NUMBER,
51 p_Release_Kanban_Flag NUMBER,
52 p_Calculate_Kanban_Flag NUMBER,
53 p_Kanban_size NUMBER,
54 p_Number_of_cards NUMBER,
55 p_Minimum_order_quantity NUMBER,
56 p_Aggregation_type NUMBER,
57 p_Allocation_Percent NUMBER,
58 p_Replenishment_lead_time NUMBER,
59 p_Fixed_Lot_multiplier NUMBER,
60 p_Safety_Stock_Days NUMBER,
61 p_Updated_Flag NUMBER,
62 p_Attribute_Category VARCHAR2,
63 p_Attribute1 VARCHAR2,
64 p_Attribute2 VARCHAR2,
65 p_Attribute3 VARCHAR2,
66 p_Attribute4 VARCHAR2,
67 p_Attribute5 VARCHAR2,
68 p_Attribute6 VARCHAR2,
69 p_Attribute7 VARCHAR2,
70 p_Attribute8 VARCHAR2,
71 p_Attribute9 VARCHAR2,
72 p_Attribute10 VARCHAR2,
73 p_Attribute11 VARCHAR2,
74 p_Attribute12 VARCHAR2,
75 p_Attribute13 VARCHAR2,
76 p_Attribute14 VARCHAR2,
77 p_Attribute15 VARCHAR2,
78 p_Request_Id NUMBER,
79 p_Program_application_Id NUMBER,
80 p_Program_Id NUMBER,
81 p_Program_Update_date DATE,
82 p_point_of_use_x NUMBER,
83 p_point_of_use_y NUMBER,
84 p_point_of_supply_x NUMBER,
85 p_point_of_supply_y NUMBER --,
86 -- p_update_status NUMBER
87 ) IS
88
89 CURSOR C1 IS SELECT rowid FROM MTL_KANBAN_PULL_SEQUENCES
90 WHERE pull_sequence_id = x_pull_sequence_id;
91 CURSOR C2 IS SELECT mtl_kanban_pull_sequences_s.nextval FROM sys.dual;
92
93 BEGIN
94 if (x_pull_sequence_id is NULL) then
95 OPEN C2;
96 FETCH C2 INTO x_pull_sequence_id;
97 CLOSE C2;
98 end if;
99
100 INSERT INTO MTL_KANBAN_PULL_SEQUENCES(
101 Pull_sequence_id,
102 Inventory_item_id,
103 Organization_id,
104 Subinventory_name,
105 Kanban_Plan_id,
106 Source_type,
107 Last_Update_Date,
108 Last_Updated_By,
109 Creation_Date,
110 Created_By,
111 Last_Update_Login,
112 Locator_id,
113 Supplier_id,
114 Supplier_site_id,
115 Source_Organization_id,
116 Source_Subinventory,
117 Source_Locator_id,
118 Wip_Line_id,
119 Release_Kanban_flag,
120 Calculate_Kanban_flag,
121 Kanban_size,
122 Number_of_cards,
123 Minimum_order_quantity,
124 Aggregation_type,
125 Allocation_Percent,
126 Replenishment_lead_time,
127 Fixed_Lot_multiplier,
128 Safety_Stock_Days,
129 Updated_Flag,
130 Attribute_Category,
131 Attribute1,
132 Attribute2,
133 Attribute3,
134 Attribute4,
135 Attribute5,
136 Attribute6,
137 Attribute7,
138 Attribute8,
139 Attribute9,
140 Attribute10,
141 Attribute11,
142 Attribute12,
143 Attribute13,
144 Attribute14,
145 Attribute15,
146 Request_Id,
147 Program_application_Id,
148 Program_Id,
149 Program_Update_date,
150 Point_of_use_x,
151 Point_of_use_y,
152 Point_of_supply_x,
153 Point_of_supply_y --,
154 -- Update_status
155 ) Values (
156 x_Pull_sequence_id,
157 p_Inventory_item_id,
158 p_Organization_id,
159 p_Subinventory_name,
160 p_Kanban_Plan_id,
161 p_Source_type,
162 p_Last_Update_Date,
163 p_Last_Updated_By,
164 p_Creation_Date,
165 p_Created_By,
166 p_Last_Update_Login,
167 p_Locator_id,
168 p_Supplier_id,
169 p_Supplier_site_id,
170 p_Source_Organization_id,
171 p_Source_Subinventory,
172 p_Source_Locator_id,
173 p_Wip_Line_id,
174 p_Release_Kanban_flag,
175 p_Calculate_Kanban_flag,
176 p_Kanban_size,
177 p_Number_of_cards,
178 p_Minimum_order_quantity,
179 p_Aggregation_type,
180 p_Allocation_Percent,
181 p_Replenishment_lead_time,
182 p_Fixed_Lot_multiplier,
183 p_Safety_Stock_Days,
184 p_Updated_Flag,
185 p_Attribute_Category,
186 p_Attribute1,
187 p_Attribute2,
188 p_Attribute3,
189 p_Attribute4,
190 p_Attribute5,
191 p_Attribute6,
192 p_Attribute7,
193 p_Attribute8,
194 p_Attribute9,
195 p_Attribute10,
196 p_Attribute11,
197 p_Attribute12,
198 p_Attribute13,
199 p_Attribute14,
200 p_Attribute15,
201 p_Request_Id,
202 p_Program_application_Id,
203 p_Program_Id,
204 p_Program_Update_Date,
205 p_Point_of_use_x,
206 p_Point_of_use_y,
207 p_Point_of_supply_x,
208 p_Point_of_supply_y --,
209 -- p_Update_status
210 );
211
212 OPEN C1;
213 FETCH C1 INTO x_rowid;
214 if (C1%NOTFOUND) then
215 CLOSE C1;
216 RAISE NO_DATA_FOUND;
217 end if;
218 CLOSE C1;
219
220 End Insert_Row;
221
222 PROCEDURE Lock_Row (p_Pull_Sequence_Id NUMBER,
223 p_Inventory_item_id NUMBER,
224 p_Organization_id NUMBER,
225 p_Subinventory_name VARCHAR2,
226 p_Kanban_Plan_id NUMBER,
227 p_Source_type NUMBER,
228 p_Locator_id NUMBER,
229 p_Supplier_id NUMBER,
230 p_Supplier_site_id NUMBER,
231 p_Source_Organization_id NUMBER,
232 p_Source_Subinventory VARCHAR2,
233 p_Source_Locator_id NUMBER,
234 p_Wip_Line_id NUMBER,
235 p_Release_Kanban_flag NUMBER,
236 p_Calculate_Kanban_flag NUMBER,
237 p_Kanban_size NUMBER,
238 p_Number_of_cards NUMBER,
239 p_Minimum_order_quantity NUMBER,
240 p_Aggregation_type NUMBER,
241 p_Allocation_Percent NUMBER,
242 p_Replenishment_lead_time NUMBER,
243 p_Fixed_Lot_multiplier NUMBER,
244 p_Safety_Stock_Days NUMBER,
245 p_Updated_Flag NUMBER,
246 p_Attribute_Category VARCHAR2,
247 p_Attribute1 VARCHAR2,
248 p_Attribute2 VARCHAR2,
249 p_Attribute3 VARCHAR2,
250 p_Attribute4 VARCHAR2,
251 p_Attribute5 VARCHAR2,
252 p_Attribute6 VARCHAR2,
253 p_Attribute7 VARCHAR2,
254 p_Attribute8 VARCHAR2,
255 p_Attribute9 VARCHAR2,
256 p_Attribute10 VARCHAR2,
257 p_Attribute11 VARCHAR2,
258 p_Attribute12 VARCHAR2,
259 p_Attribute13 VARCHAR2,
260 p_Attribute14 VARCHAR2,
261 p_Attribute15 VARCHAR2,
262 p_point_of_use_x NUMBER,
263 p_point_of_use_y NUMBER,
264 p_point_of_supply_x NUMBER,
265 p_point_of_supply_y NUMBER --,
266 -- p_update_status NUMBER
267 ) IS
268 CURSOR C IS
269 SELECT *
270 FROM MTL_KANBAN_PULL_SEQUENCES
271 WHERE pull_sequence_id = p_pull_sequence_id
272 FOR UPDATE of source_type NOWAIT;
273
274 Recinfo C%ROWTYPE;
275 BEGIN
276 OPEN C;
277 FETCH C INTO Recinfo;
278 if (C%NOTFOUND) then
279 CLOSE C;
280 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
281 APP_EXCEPTION.Raise_Exception;
282 end if;
283 CLOSE C;
284
285 if (
286 Recinfo.Inventory_item_id = p_Inventory_item_id and
287 Recinfo.Organization_Id = p_Organization_Id and
288 Recinfo.Subinventory_name = p_Subinventory_name and
289 Recinfo.Kanban_Plan_id = p_Kanban_Plan_id and
290 Recinfo.Source_type = p_Source_type and
291 ((Recinfo.Locator_id = p_Locator_id)
292 or ((Recinfo.Locator_id is null) and (p_Locator_id is null))) and
293 ((Recinfo.Supplier_id = p_Supplier_id)
294 or ((Recinfo.Supplier_id is null) and (p_Supplier_id is null))) and
295 ((Recinfo.Supplier_site_id = p_Supplier_site_id)
296 or ((Recinfo.Supplier_site_id is null) and (p_Supplier_site_id is null))) and
297 ((Recinfo.Source_Organization_id = p_Source_Organization_id)
298 or ((Recinfo.Source_Organization_id is null) and (p_Source_Organization_id is null))) and
299 ((Recinfo.Source_Subinventory = p_Source_Subinventory)
300 or ((Recinfo.Source_Subinventory is null) and (p_Source_Subinventory is null))) and
301 ((Recinfo.Source_Locator_id = p_Source_Locator_id)
302 or ((Recinfo.Source_Locator_id is null) and (p_Source_Locator_id is null))) and
303 ((Recinfo.Wip_Line_id = p_Wip_Line_id)
304 or ((Recinfo.Wip_Line_id is null) and (p_Wip_Line_id is null))) and
305 ((Recinfo.Release_Kanban_Flag = p_Release_Kanban_flag)
306 or ((Recinfo.Release_Kanban_flag is null) and (p_Release_Kanban_flag is null))) and
307 ((Recinfo.Calculate_Kanban_Flag = p_Calculate_Kanban_flag)
308 or ((Recinfo.Calculate_Kanban_flag is null) and (p_Calculate_Kanban_flag is null))) and
309 ((Recinfo.Kanban_size = p_Kanban_size)
310 or ((Recinfo.Kanban_size is null) and (p_Kanban_size is null))) and
311 ((Recinfo.Number_of_cards = p_Number_of_cards)
312 or ((Recinfo.Number_of_cards is null) and (p_Number_of_cards is null))) and
313 ((Recinfo.Minimum_order_quantity = p_Minimum_order_quantity)
314 or ((Recinfo.Minimum_order_quantity is null) and (p_Minimum_order_quantity is null))) and
315 ((Recinfo.Aggregation_Type = p_Aggregation_Type)
316 or ((Recinfo.Aggregation_Type is null) and (p_Aggregation_Type is null))) and
317 ((Recinfo.Allocation_Percent = p_Allocation_Percent)
318 or ((Recinfo.Allocation_Percent is null) and (p_Allocation_Percent is null))) and
319 ((Recinfo.Replenishment_lead_time = p_Replenishment_lead_time)
320 or ((Recinfo.Replenishment_lead_time is null) and (p_Replenishment_lead_time is null))) and
321 ((Recinfo.fixed_lot_multiplier = p_fixed_lot_multiplier)
322 or ((Recinfo.fixed_lot_multiplier is null) and (p_fixed_lot_multiplier is null))) and
323 ((Recinfo.Safety_Stock_Days = p_Safety_Stock_Days)
324 or ((Recinfo.Safety_Stock_Days is null) and (p_Safety_Stock_Days is null))) and
325 ((Recinfo.Updated_Flag = p_Updated_Flag)
326 or ((Recinfo.Updated_Flag is null) and (p_Updated_Flag is null)))
327 AND ( (Recinfo.point_of_use_x = p_point_of_use_x)
328 OR ( (Recinfo.point_of_use_x IS NULL)
329 AND (p_point_of_use_x IS NULL)))
330 AND ( (Recinfo.point_of_use_y = p_point_of_use_y)
331 OR ( (Recinfo.point_of_use_y IS NULL)
332 AND (p_point_of_use_y IS NULL)))
333 AND ( (Recinfo.point_of_supply_x = p_point_of_supply_x)
334 OR ( (Recinfo.point_of_supply_x IS NULL)
335 AND (p_point_of_supply_x IS NULL)))
336 AND ( (Recinfo.point_of_supply_y = p_point_of_supply_y)
337 OR ( (Recinfo.point_of_supply_y IS NULL)
338 AND (p_point_of_supply_y IS NULL)))
339 /*
340 AND ( (Recinfo.update_status = p_update_status)
341 OR ( (Recinfo.update_status IS NULL)
342 AND (p_update_status IS NULL)))
343 */
344 AND ( (Recinfo.Attribute_Category = p_Attribute_Category)
345 OR ( (Recinfo.Attribute_Category is null)
346 AND (p_Attribute_Category is null)))
350 AND ( (Recinfo.attribute2 = p_Attribute2)
347 AND ( (Recinfo.attribute1 = p_Attribute1)
348 OR ( (Recinfo.attribute1 IS NULL)
349 AND (p_Attribute1 IS NULL)))
351 OR ( (Recinfo.attribute2 IS NULL)
352 AND (p_Attribute2 IS NULL)))
353 AND ( (Recinfo.attribute3 = p_Attribute3)
354 OR ( (Recinfo.attribute3 IS NULL)
355 AND (p_Attribute3 IS NULL)))
356 AND ( (Recinfo.attribute4 = p_Attribute4)
357 OR ( (Recinfo.attribute4 IS NULL)
358 AND (p_Attribute4 IS NULL)))
359 AND ( (Recinfo.attribute5 = p_Attribute5)
360 OR ( (Recinfo.attribute5 IS NULL)
361 AND (p_Attribute5 IS NULL)))
362 AND ( (Recinfo.attribute6 = p_Attribute6)
363 OR ( (Recinfo.attribute6 IS NULL)
364 AND (p_Attribute6 IS NULL)))
365 AND ( (Recinfo.attribute7 = p_Attribute7)
366 OR ( (Recinfo.attribute7 IS NULL)
367 AND (p_Attribute7 IS NULL)))
368 AND ( (Recinfo.attribute8 = p_Attribute8)
369 OR ( (Recinfo.attribute8 IS NULL)
370 AND (p_Attribute8 IS NULL)))
371 AND ( (Recinfo.attribute9 = p_Attribute9)
372 OR ( (Recinfo.attribute9 IS NULL)
373 AND (p_Attribute9 IS NULL)))
374 AND ( (Recinfo.attribute10 = p_Attribute10)
375 OR ( (Recinfo.attribute10 IS NULL)
376 AND (p_Attribute10 IS NULL)))
377 AND ( (Recinfo.attribute11 = p_Attribute11)
378 OR ( (Recinfo.attribute11 IS NULL)
379 AND (p_Attribute11 IS NULL)))
380 AND ( (Recinfo.attribute12 = p_Attribute12)
381 OR ( (Recinfo.attribute12 IS NULL)
382 AND (p_Attribute12 IS NULL)))
383 AND ( (Recinfo.attribute13 = p_Attribute13)
384 OR ( (Recinfo.attribute13 IS NULL)
385 AND (p_Attribute13 IS NULL)))
386 AND ( (Recinfo.attribute14 = p_Attribute14)
387 OR ( (Recinfo.attribute14 IS NULL)
388 AND (p_Attribute14 IS NULL)))
389 AND ( (Recinfo.attribute15 = p_Attribute15)
390 OR ( (Recinfo.attribute15 IS NULL)
391 AND (p_Attribute15 IS NULL)))
392 ) then
393 return;
394 else
395 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
396 APP_EXCEPTION.RAISE_EXCEPTION;
397 end if;
398
399 END Lock_Row;
400
401
402 PROCEDURE Update_Row(p_Pull_Sequence_Id NUMBER,
403 p_Inventory_item_id NUMBER,
404 p_Organization_id NUMBER,
405 p_Subinventory_name VARCHAR2,
406 p_Kanban_Plan_id NUMBER,
407 p_Source_type NUMBER,
408 p_Last_Update_Date DATE,
409 p_Last_Updated_By NUMBER,
410 p_Creation_Date DATE,
411 p_Created_By NUMBER,
412 p_Last_Update_Login NUMBER,
413 p_Locator_id NUMBER,
414 p_Supplier_id NUMBER,
415 p_Supplier_site_id NUMBER,
416 p_Source_Organization_id NUMBER,
417 p_Source_Subinventory VARCHAR2,
418 p_Source_Locator_id NUMBER,
419 p_Wip_Line_id NUMBER,
420 p_Release_Kanban_flag NUMBER,
421 p_Calculate_Kanban_flag NUMBER,
422 p_Kanban_size NUMBER,
423 p_Number_of_cards NUMBER,
424 p_Minimum_order_quantity NUMBER,
425 p_Aggregation_Type NUMBER,
426 p_Allocation_Percent NUMBER,
427 p_Replenishment_lead_time NUMBER,
428 p_Fixed_Lot_multiplier NUMBER,
429 p_Safety_Stock_Days NUMBER,
430 p_Updated_Flag NUMBER,
431 p_Attribute_Category VARCHAR2,
432 p_Attribute1 VARCHAR2,
433 p_Attribute2 VARCHAR2,
434 p_Attribute3 VARCHAR2,
435 p_Attribute4 VARCHAR2,
436 p_Attribute5 VARCHAR2,
437 p_Attribute6 VARCHAR2,
438 p_Attribute7 VARCHAR2,
439 p_Attribute8 VARCHAR2,
440 p_Attribute9 VARCHAR2,
441 p_Attribute10 VARCHAR2,
442 p_Attribute11 VARCHAR2,
443 p_Attribute12 VARCHAR2,
444 p_Attribute13 VARCHAR2,
445 p_Attribute14 VARCHAR2,
446 p_Attribute15 VARCHAR2,
447 p_point_of_use_x NUMBER,
448 p_point_of_use_y NUMBER,
449 p_point_of_supply_x NUMBER,
450 p_point_of_supply_y NUMBER --,
454 BEGIN
451 -- p_update_status NUMBER
452 ) IS
453
455
456 UPDATE MTL_KANBAN_PULL_SEQUENCES
457 SET
458 Inventory_item_id = p_Inventory_item_id,
459 Organization_Id = p_Organization_Id,
460 Subinventory_name = p_Subinventory_name,
461 Kanban_Plan_id = p_Kanban_Plan_id,
462 Source_type = p_Source_type,
463 Last_Update_Date = p_Last_Update_Date,
464 Last_Updated_By = p_Last_Updated_By,
465 Creation_Date = p_Creation_Date,
466 Created_By = p_Created_By,
467 Last_Update_Login = p_Last_Update_Login,
468 Locator_id = p_Locator_id,
469 Supplier_id = p_Supplier_id,
470 Supplier_site_id = p_Supplier_site_id,
471 Source_Organization_id = p_Source_Organization_id,
472 Source_Subinventory = p_Source_Subinventory,
473 Source_Locator_id = p_Source_Locator_id,
474 Wip_Line_id = p_Wip_Line_id,
475 Release_Kanban_Flag = p_Release_Kanban_flag,
476 Calculate_Kanban_Flag = p_Calculate_Kanban_flag,
477 Kanban_size = p_Kanban_size,
478 Number_of_cards = p_Number_of_cards,
479 Minimum_order_quantity = p_Minimum_order_quantity,
480 Aggregation_Type = p_Aggregation_Type,
481 Allocation_Percent = p_Allocation_Percent,
482 Replenishment_lead_time = p_Replenishment_lead_time,
483 Fixed_Lot_multiplier = p_Fixed_Lot_multiplier,
484 Safety_Stock_Days = p_Safety_Stock_Days,
485 Updated_Flag = p_Updated_Flag,
486 Point_of_use_x = p_Point_of_use_x,
487 Point_of_use_y = p_Point_of_use_y,
488 Point_of_supply_x = p_Point_of_supply_x,
489 Point_of_supply_y = p_Point_of_supply_y,
490 -- Update_Status = p_Update_Status,
491 Attribute_Category = p_Attribute_Category,
492 Attribute1 = p_Attribute1,
493 Attribute2 = p_Attribute2,
494 Attribute3 = p_Attribute3,
495 Attribute4 = p_Attribute4,
496 Attribute5 = p_Attribute5,
497 Attribute6 = p_Attribute6,
498 Attribute7 = p_Attribute7,
499 Attribute8 = p_Attribute8,
500 Attribute9 = p_Attribute9,
501 Attribute10 = p_Attribute10,
502 Attribute11 = p_Attribute11,
503 Attribute12 = p_Attribute12,
504 Attribute13 = p_Attribute13,
505 Attribute14 = p_Attribute14,
506 Attribute15 = p_Attribute15
507 WHERE pull_sequence_id = p_pull_sequence_id;
508
509 if (SQL%NOTFOUND) then
510 Raise NO_DATA_FOUND;
511 end if;
512
513 END Update_Row;
514
515
516 PROCEDURE Delete_Row(p_Pull_Sequence_Id Number) IS
517
518 BEGIN
519
520 DELETE FROM MTL_KANBAN_PULL_SEQUENCES
521 WHERE pull_sequence_id = p_pull_sequence_id;
522
523 if (SQL%NOTFOUND) then
524 Raise NO_DATA_FOUND;
525 end if;
526
527 END Delete_Row;
528
529 END FLM_PullSequence;