[Home] [Help]
PACKAGE BODY: APPS.INV_PULLSEQUENCE_PKG
Source
1 PACKAGE BODY INV_PullSequence_PKG as
2 /* $Header: INVKPSQB.pls 120.0 2005/05/25 07:00:22 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_PullSequence_PKG';
5
6
7 FUNCTION Check_Unique( p_Pull_Sequence_Id IN OUT NOCOPY NUMBER,
8 p_Organization_Id NUMBER,
9 p_Kanban_Plan_Id NUMBER,
10 p_Inventory_item_id NUMBER,
11 p_Subinventory_Name VARCHAR2,
12 p_Locator_Id NUMBER)
13 RETURN BOOLEAN IS
14 l_Dummy Varchar2(1);
15 BEGIN
16 Select 'x'
17 Into l_Dummy
18 From MTL_KANBAN_PULL_SEQUENCES
19 Where organization_id = p_Organization_Id
20 And kanban_plan_id = p_kanban_plan_id
21 And inventory_item_id = p_inventory_item_id
22 And subinventory_name = p_Subinventory_Name
23 And nvl(locator_id,-1)= nvl(p_locator_id,-1)
24 And ((p_Pull_Sequence_Id is NULL ) Or
25 (Pull_Sequence_Id <> p_Pull_Sequence_Id));
26 Raise too_Many_Rows;
27
28 Exception
29 When No_Data_found
30 Then
31 return True;
32 When Too_Many_Rows
33 Then
34 FND_MESSAGE.SET_NAME('INV', 'INV_PULLSEQ_EXISTS');
35 Return FALSE;
36 When Others
37 Then
38 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
39 THEN
40 FND_MSG_PUB.Add_Exc_Msg
41 ( G_PKG_NAME
42 , 'Check_Unique'
43 );
44 END IF;
45 Return FALSE;
46
47 END Check_Unique;
48
49 Procedure commit_row is
50 BEGIN
51 commit;
52 end commit_row;
53
54 Procedure rollback_row is
55 BEGIN
56 rollback;
57 end rollback_row;
58
59 FUNCTION Query_Row
60 ( p_pull_sequence_id IN NUMBER
61 ) RETURN INV_Kanban_PVT.Pull_Sequence_Rec_Type
62 IS
63 l_pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
64 BEGIN
65
66 SELECT PULL_SEQUENCE_ID
67 , INVENTORY_ITEM_ID
68 , ORGANIZATION_ID
69 , SUBINVENTORY_NAME
70 , KANBAN_PLAN_ID
71 , SOURCE_TYPE
72 , LAST_UPDATE_DATE
73 , LAST_UPDATED_BY
74 , CREATION_DATE
75 , CREATED_BY
76 , LOCATOR_ID
77 , SUPPLIER_ID
78 , SUPPLIER_SITE_ID
79 , SOURCE_ORGANIZATION_ID
80 , SOURCE_SUBINVENTORY
81 , SOURCE_LOCATOR_ID
82 , WIP_LINE_ID
83 , REPLENISHMENT_LEAD_TIME
84 , RELEASE_KANBAN_FLAG
85 , CALCULATE_KANBAN_FLAG
86 , KANBAN_SIZE
87 , FIXED_LOT_MULTIPLIER
88 , SAFETY_STOCK_DAYS
89 , NUMBER_OF_CARDS
90 , MINIMUM_ORDER_QUANTITY
91 , AGGREGATION_TYPE
92 , ALLOCATION_PERCENT
93 , LAST_UPDATE_LOGIN
94 , UPDATED_FLAG
95 , ATTRIBUTE_CATEGORY
96 , ATTRIBUTE1
97 , ATTRIBUTE2
98 , ATTRIBUTE3
99 , ATTRIBUTE4
100 , ATTRIBUTE5
101 , ATTRIBUTE6
102 , ATTRIBUTE7
103 , ATTRIBUTE8
104 , ATTRIBUTE9
105 , ATTRIBUTE10
106 , ATTRIBUTE11
107 , ATTRIBUTE12
108 , ATTRIBUTE13
109 , ATTRIBUTE14
110 , ATTRIBUTE15
111 , REQUEST_ID
112 , PROGRAM_APPLICATION_ID
113 , PROGRAM_ID
114 , PROGRAM_UPDATE_DATE
115 , POINT_OF_USE_X
116 , POINT_OF_USE_Y
117 , POINT_OF_SUPPLY_X
118 , POINT_OF_SUPPLY_Y
119 , PLANNING_UPDATE_STATUS
120 , AUTO_REQUEST
121 , AUTO_ALLOCATE_FLAG --Added for 3905884
122 INTO l_pull_sequence_rec.pull_sequence_id
123 , l_pull_sequence_rec.inventory_item_id
124 , l_pull_sequence_rec.organization_id
125 , l_pull_sequence_rec.subinventory_name
126 , l_pull_sequence_rec.Kanban_plan_id
127 , l_pull_sequence_rec.source_type
128 , l_pull_sequence_rec.last_update_date
129 , l_pull_sequence_rec.last_updated_by
130 , l_pull_sequence_rec.creation_date
131 , l_pull_sequence_rec.created_by
132 , l_pull_sequence_rec.locator_id
133 , l_pull_sequence_rec.supplier_id
134 , l_pull_sequence_rec.supplier_site_id
135 , l_pull_sequence_rec.source_organization_id
136 , l_pull_sequence_rec.source_subinventory
137 , l_pull_sequence_rec.source_locator_id
138 , l_pull_sequence_rec.Wip_line_id
139 , l_pull_sequence_rec.replenishment_lead_time
140 , l_pull_sequence_rec.Release_Kanban_Flag
141 , l_pull_sequence_rec.Calculate_Kanban_Flag
142 , l_pull_sequence_rec.kanban_size
143 , l_pull_sequence_rec.fixed_lot_multiplier
144 , l_pull_sequence_rec.safety_stock_days
145 , l_pull_sequence_rec.number_of_cards
146 , l_pull_sequence_rec.minimum_order_quantity
147 , l_pull_sequence_rec.aggregation_Type
148 , l_pull_sequence_rec.Allocation_Percent
149 , l_pull_sequence_rec.last_update_login
150 , l_pull_sequence_rec.updated_flag
151 , l_pull_sequence_rec.attribute_category
152 , l_pull_sequence_rec.attribute1
153 , l_pull_sequence_rec.attribute2
154 , l_pull_sequence_rec.attribute3
155 , l_pull_sequence_rec.attribute4
156 , l_pull_sequence_rec.attribute5
157 , l_pull_sequence_rec.attribute6
158 , l_pull_sequence_rec.attribute7
159 , l_pull_sequence_rec.attribute8
160 , l_pull_sequence_rec.attribute9
161 , l_pull_sequence_rec.attribute10
162 , l_pull_sequence_rec.attribute11
163 , l_pull_sequence_rec.attribute12
164 , l_pull_sequence_rec.attribute13
165 , l_pull_sequence_rec.attribute14
166 , l_pull_sequence_rec.attribute15
167 , l_pull_sequence_rec.request_id
168 , l_pull_sequence_rec.program_application_id
169 , l_pull_sequence_rec.program_id
170 , l_pull_sequence_rec.program_update_date
171 , l_pull_sequence_rec.point_of_use_x
172 , l_pull_sequence_rec.point_of_use_y
173 , l_pull_sequence_rec.point_of_supply_x
174 , l_pull_sequence_rec.point_of_supply_y
175 , l_pull_sequence_rec.planning_update_status
176 , l_pull_sequence_rec.auto_request
177 , l_pull_sequence_rec.auto_allocate_flag --Added for3905884
178 FROM MTL_KANBAN_PULL_SEQUENCES
179 WHERE PULL_SEQUENCE_ID = p_pull_sequence_id
180 ;
181
182 RETURN l_pull_sequence_rec;
183
184 EXCEPTION
185
186 WHEN OTHERS THEN
187
188 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 FND_MSG_PUB.Add_Exc_Msg
191 ( G_PKG_NAME
192 , 'Query_Row'
193 );
194 END IF;
195
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197
198 END Query_Row;
199
200 FUNCTION Convert_Miss_To_Null
201 ( p_pull_sequence_rec IN INV_Kanban_PVT.Pull_Sequence_Rec_Type
202 ) RETURN INV_Kanban_PVT.Pull_Sequence_Rec_Type
203 IS
204 l_pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type := p_pull_sequence_rec;
205 BEGIN
206
207 IF l_pull_sequence_rec.pull_sequence_id = FND_API.G_MISS_NUM THEN
208 l_pull_sequence_rec.pull_sequence_id := NULL;
209 END IF;
210
211 IF l_pull_sequence_rec.inventory_item_id = FND_API.G_MISS_NUM THEN
212 l_pull_sequence_rec.inventory_item_id := NULL;
213 END IF;
214
215 IF l_pull_sequence_rec.organization_id = FND_API.G_MISS_NUM THEN
216 l_pull_sequence_rec.organization_id := NULL;
217 END IF;
218
219 IF l_pull_sequence_rec.subinventory_name = FND_API.G_MISS_CHAR THEN
220 l_pull_sequence_rec.subinventory_name := NULL;
221 END IF;
222
223 IF l_pull_sequence_rec.kanban_plan_id = FND_API.G_MISS_NUM THEN
224 l_pull_sequence_rec.kanban_plan_id := NULL;
225 END IF;
226
227 IF l_pull_sequence_rec.source_type = FND_API.G_MISS_NUM THEN
228 l_pull_sequence_rec.source_type := NULL;
229 END IF;
230
231 IF l_pull_sequence_rec.last_update_date = FND_API.G_MISS_DATE THEN
232 l_pull_sequence_rec.last_update_date := NULL;
233 END IF;
234
235 IF l_pull_sequence_rec.last_updated_by = FND_API.G_MISS_NUM THEN
236 l_pull_sequence_rec.last_updated_by := NULL;
237 END IF;
238
239 IF l_pull_sequence_rec.creation_date = FND_API.G_MISS_DATE THEN
240 l_pull_sequence_rec.creation_date := NULL;
241 END IF;
242
243 IF l_pull_sequence_rec.created_by = FND_API.G_MISS_NUM THEN
244 l_pull_sequence_rec.created_by := NULL;
245 END IF;
246
247 IF l_pull_sequence_rec.last_update_login = FND_API.G_MISS_NUM THEN
248 l_pull_sequence_rec.last_update_login := NULL;
249 END IF;
250
251 IF l_pull_sequence_rec.locator_id = FND_API.G_MISS_NUM THEN
252 l_pull_sequence_rec.locator_id := NULL;
253 END IF;
254
255 IF l_pull_sequence_rec.supplier_id = FND_API.G_MISS_NUM THEN
256 l_pull_sequence_rec.supplier_id := NULL;
257 END IF;
258
259 IF l_pull_sequence_rec.source_organization_id = FND_API.G_MISS_NUM THEN
260 l_pull_sequence_rec.source_organization_id := NULL;
261 END IF;
262
263 IF l_pull_sequence_rec.source_subinventory = FND_API.G_MISS_CHAR THEN
264 l_pull_sequence_rec.source_subinventory := NULL;
265 END IF;
266
267 IF l_pull_sequence_rec.source_locator_id = FND_API.G_MISS_NUM THEN
268 l_pull_sequence_rec.source_locator_id := NULL;
269 END IF;
270
271 IF l_pull_sequence_rec.wip_line_id = FND_API.G_MISS_NUM THEN
272 l_pull_sequence_rec.wip_line_id := NULL;
273 END IF;
274
275 IF l_pull_sequence_rec.release_kanban_flag = FND_API.G_MISS_NUM THEN
276 l_pull_sequence_rec.release_kanban_flag := NULL;
277 END IF;
278
279 IF l_pull_sequence_rec.calculate_kanban_flag = FND_API.G_MISS_NUM THEN
280 l_pull_sequence_rec.calculate_kanban_flag := NULL;
281 END IF;
282
283 IF l_pull_sequence_rec.kanban_size = FND_API.G_MISS_NUM THEN
284 l_pull_sequence_rec.kanban_size := NULL;
285 END IF;
286
287 IF l_pull_sequence_rec.replenishment_lead_time = FND_API.G_MISS_NUM THEN
288 l_pull_sequence_rec.replenishment_lead_time := NULL;
289 END IF;
290
291 IF l_pull_sequence_rec.fixed_lot_multiplier = FND_API.G_MISS_NUM THEN
292 l_pull_sequence_rec.fixed_lot_multiplier := NULL;
293 END IF;
294
295 IF l_pull_sequence_rec.safety_stock_days = FND_API.G_MISS_NUM THEN
296 l_pull_sequence_rec.safety_stock_days := NULL;
297 END IF;
298
299 IF l_pull_sequence_rec.number_of_cards = FND_API.G_MISS_NUM THEN
300 l_pull_sequence_rec.number_of_cards := NULL;
301 END IF;
302
303 IF l_pull_sequence_rec.minimum_order_quantity = FND_API.G_MISS_NUM THEN
304 l_pull_sequence_rec.minimum_order_quantity := NULL;
305 END IF;
306
307 IF l_pull_sequence_rec.aggregation_type = FND_API.G_MISS_NUM THEN
308 l_pull_sequence_rec.aggregation_type := NULL;
309 END IF;
310
311 IF l_pull_sequence_rec.allocation_percent = FND_API.G_MISS_NUM THEN
312 l_pull_sequence_rec.allocation_percent := NULL;
313 END IF;
314
315 IF l_pull_sequence_rec.last_update_login = FND_API.G_MISS_NUM THEN
316 l_pull_sequence_rec.last_update_login := NULL;
317 END IF;
318
319 IF l_pull_sequence_rec.updated_flag = FND_API.G_MISS_NUM THEN
320 l_pull_sequence_rec.updated_flag := NULL;
321 END IF;
322
323 IF l_pull_sequence_rec.attribute_category = FND_API.G_MISS_CHAR THEN
324 l_pull_sequence_rec.attribute_category := NULL;
325 END IF;
326
327 IF l_pull_sequence_rec.attribute1 = FND_API.G_MISS_CHAR THEN
328 l_pull_sequence_rec.attribute1 := NULL;
329 END IF;
330
331 IF l_pull_sequence_rec.attribute2 = FND_API.G_MISS_CHAR THEN
332 l_pull_sequence_rec.attribute2 := NULL;
333 END IF;
334
335 IF l_pull_sequence_rec.attribute3 = FND_API.G_MISS_CHAR THEN
336 l_pull_sequence_rec.attribute3 := NULL;
337 END IF;
338
339 IF l_pull_sequence_rec.attribute4 = FND_API.G_MISS_CHAR THEN
340 l_pull_sequence_rec.attribute4 := NULL;
341 END IF;
342
343 IF l_pull_sequence_rec.attribute5 = FND_API.G_MISS_CHAR THEN
344 l_pull_sequence_rec.attribute5 := NULL;
345 END IF;
346
347 IF l_pull_sequence_rec.attribute6 = FND_API.G_MISS_CHAR THEN
348 l_pull_sequence_rec.attribute6 := NULL;
349 END IF;
350
351 IF l_pull_sequence_rec.attribute7 = FND_API.G_MISS_CHAR THEN
352 l_pull_sequence_rec.attribute7 := NULL;
353 END IF;
354
355 IF l_pull_sequence_rec.attribute8 = FND_API.G_MISS_CHAR THEN
356 l_pull_sequence_rec.attribute8 := NULL;
357 END IF;
358
359 IF l_pull_sequence_rec.attribute9 = FND_API.G_MISS_CHAR THEN
360 l_pull_sequence_rec.attribute9 := NULL;
361 END IF;
362
363 IF l_pull_sequence_rec.attribute10 = FND_API.G_MISS_CHAR THEN
364 l_pull_sequence_rec.attribute10 := NULL;
365 END IF;
366
367 IF l_pull_sequence_rec.attribute11 = FND_API.G_MISS_CHAR THEN
368 l_pull_sequence_rec.attribute11 := NULL;
369 END IF;
370
371 IF l_pull_sequence_rec.attribute12 = FND_API.G_MISS_CHAR THEN
372 l_pull_sequence_rec.attribute12 := NULL;
373 END IF;
374
375 IF l_pull_sequence_rec.attribute13 = FND_API.G_MISS_CHAR THEN
376 l_pull_sequence_rec.attribute13 := NULL;
377 END IF;
378
379 IF l_pull_sequence_rec.attribute14 = FND_API.G_MISS_CHAR THEN
380 l_pull_sequence_rec.attribute14 := NULL;
381 END IF;
382
383 IF l_pull_sequence_rec.attribute15 = FND_API.G_MISS_CHAR THEN
384 l_pull_sequence_rec.attribute15 := NULL;
385 END IF;
386
387 IF l_pull_sequence_rec.request_id = FND_API.G_MISS_NUM THEN
388 l_pull_sequence_rec.request_id := NULL;
389 END IF;
390
391 IF l_pull_sequence_rec.program_application_id = FND_API.G_MISS_NUM THEN
392 l_pull_sequence_rec.program_application_id := NULL;
393 END IF;
394
395 IF l_pull_sequence_rec.program_id = FND_API.G_MISS_NUM THEN
396 l_pull_sequence_rec.program_id := NULL;
397 END IF;
398
399 IF l_pull_sequence_rec.program_update_date = FND_API.G_MISS_DATE THEN
400 l_pull_sequence_rec.program_update_date := NULL;
401 END IF;
402
403 IF l_pull_sequence_rec.point_of_use_x= FND_API.G_MISS_NUM THEN
404 l_pull_sequence_rec.point_of_use_x := NULL;
405 END IF;
406
407 IF l_pull_sequence_rec.point_of_use_y = FND_API.G_MISS_NUM THEN
408 l_pull_sequence_rec.point_of_use_y := NULL;
409 END IF;
410
411 IF l_pull_sequence_rec.point_of_supply_x = FND_API.G_MISS_NUM THEN
412 l_pull_sequence_rec.point_of_supply_x := NULL;
413 END IF;
414
415 IF l_pull_sequence_rec.point_of_supply_y = FND_API.G_MISS_NUM THEN
416 l_pull_sequence_rec.point_of_supply_y := NULL;
417 END IF;
418
419 IF l_pull_sequence_rec.planning_update_status = FND_API.G_MISS_NUM THEN
420 l_pull_sequence_rec.planning_update_status := NULL;
421 END IF;
422
423 IF l_pull_sequence_rec.auto_request = FND_API.G_MISS_CHAR THEN
424 l_pull_sequence_rec.auto_request := NULL;
425 END IF;
426
427 /*Added the following IF statement for 3905884.*/
428 IF l_pull_sequence_rec.auto_allocate_flag = FND_API.G_MISS_NUM THEN
429 l_pull_sequence_rec.auto_allocate_flag := NULL;
430 END IF;
431
432 RETURN l_pull_sequence_rec;
433
434 END Convert_Miss_To_Null;
435
436 FUNCTION Complete_Record
437 ( p_pull_sequence_rec IN INV_Kanban_PVT.Pull_Sequence_Rec_Type
438 , p_old_pull_sequence_rec IN INV_Kanban_PVT.Pull_Sequence_Rec_Type
439 ) RETURN INV_Kanban_PVT.Pull_Sequence_Rec_Type
440 IS
441 l_pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type := p_pull_sequence_rec;
442 BEGIN
443
444 IF l_pull_sequence_rec.pull_sequence_id = FND_API.G_MISS_NUM THEN
445 l_pull_sequence_rec.pull_sequence_id := p_old_pull_sequence_rec.pull_sequence_id;
446 END IF;
447
448 IF l_pull_sequence_rec.inventory_item_id = FND_API.G_MISS_NUM THEN
449 l_pull_sequence_rec.inventory_item_id := p_old_pull_sequence_rec.inventory_item_id;
450 END IF;
451
452 IF l_pull_sequence_rec.organization_id = FND_API.G_MISS_NUM THEN
453 l_pull_sequence_rec.organization_id := p_old_pull_sequence_rec.organization_id;
454 END IF;
455
456 IF l_pull_sequence_rec.subinventory_name = FND_API.G_MISS_CHAR THEN
457 l_pull_sequence_rec.subinventory_name := p_old_pull_sequence_rec.subinventory_name;
458 END IF;
459
460 IF l_pull_sequence_rec.Kanban_plan_id = FND_API.G_MISS_NUM THEN
461 l_pull_sequence_rec.Kanban_plan_id := p_old_pull_sequence_rec.Kanban_plan_id;
462 END IF;
463
464 IF l_pull_sequence_rec.source_type = FND_API.G_MISS_NUM THEN
465 l_pull_sequence_rec.source_type := p_old_pull_sequence_rec.source_type;
466 END IF;
467
468 IF l_pull_sequence_rec.last_update_date = FND_API.G_MISS_DATE THEN
469 l_pull_sequence_rec.last_update_date := p_old_pull_sequence_rec.last_update_date;
470 END IF;
471
472 IF l_pull_sequence_rec.last_updated_by = FND_API.G_MISS_NUM THEN
473 l_pull_sequence_rec.last_updated_by := p_old_pull_sequence_rec.last_updated_by;
474 END IF;
475
476 IF l_pull_sequence_rec.creation_date = FND_API.G_MISS_DATE THEN
477 l_pull_sequence_rec.creation_date := p_old_pull_sequence_rec.creation_date;
478 END IF;
479
480 IF l_pull_sequence_rec.created_by = FND_API.G_MISS_NUM THEN
481 l_pull_sequence_rec.created_by := p_old_pull_sequence_rec.created_by;
482 END IF;
483
484 IF l_pull_sequence_rec.locator_id = FND_API.G_MISS_NUM THEN
485 l_pull_sequence_rec.locator_id := p_old_pull_sequence_rec.locator_id;
486 END IF;
487
488 IF l_pull_sequence_rec.supplier_id = FND_API.G_MISS_NUM THEN
489 l_pull_sequence_rec.supplier_id := p_old_pull_sequence_rec.supplier_id;
490 END IF;
491
492 IF l_pull_sequence_rec.supplier_site_id = FND_API.G_MISS_NUM THEN
493 l_pull_sequence_rec.supplier_site_id := p_old_pull_sequence_rec.supplier_site_id;
494 END IF;
495
496 IF l_pull_sequence_rec.source_organization_id = FND_API.G_MISS_NUM THEN
497 l_pull_sequence_rec.source_organization_id := p_old_pull_sequence_rec.source_organization_id;
498 END IF;
499
500 IF l_pull_sequence_rec.source_subinventory = FND_API.G_MISS_CHAR THEN
501 l_pull_sequence_rec.source_subinventory := p_old_pull_sequence_rec.source_subinventory;
502 END IF;
503
504 IF l_pull_sequence_rec.source_locator_id = FND_API.G_MISS_NUM THEN
505 l_pull_sequence_rec.source_locator_id := p_old_pull_sequence_rec.source_locator_id;
506 END IF;
507
508 IF l_pull_sequence_rec.wip_line_id = FND_API.G_MISS_NUM THEN
509 l_pull_sequence_rec.wip_line_id := p_old_pull_sequence_rec.wip_line_id;
510 END IF;
511
512 IF l_pull_sequence_rec.replenishment_lead_time = FND_API.G_MISS_NUM THEN
513 l_pull_sequence_rec.replenishment_lead_time := p_old_pull_sequence_rec.replenishment_lead_time;
514 END IF;
515
516 IF l_pull_sequence_rec.Release_Kanban_Flag = FND_API.G_MISS_NUM THEN
517 l_pull_sequence_rec.Release_Kanban_Flag := p_old_pull_sequence_rec.Release_Kanban_Flag;
518
519 END IF;
520 IF l_pull_sequence_rec.Calculate_Kanban_Flag = FND_API.G_MISS_NUM THEN
521 l_pull_sequence_rec.Calculate_Kanban_Flag := p_old_pull_sequence_rec.Calculate_Kanban_Flag;
522 END IF;
523
524 IF l_pull_sequence_rec.kanban_size = FND_API.G_MISS_NUM THEN
525 l_pull_sequence_rec.kanban_size := p_old_pull_sequence_rec.kanban_size;
526 END IF;
527
528 IF l_pull_sequence_rec.fixed_lot_multiplier = FND_API.G_MISS_NUM THEN
529 l_pull_sequence_rec.fixed_lot_multiplier := p_old_pull_sequence_rec.fixed_lot_multiplier;
530 END IF;
531
532 IF l_pull_sequence_rec.safety_stock_days = FND_API.G_MISS_NUM THEN
533 l_pull_sequence_rec.safety_stock_days := p_old_pull_sequence_rec.safety_stock_days;
534 END IF;
535
536 IF l_pull_sequence_rec.number_of_cards = FND_API.G_MISS_NUM THEN
537 l_pull_sequence_rec.number_of_cards := p_old_pull_sequence_rec.number_of_cards;
538 END IF;
539
540 IF l_pull_sequence_rec.minimum_order_quantity = FND_API.G_MISS_NUM THEN
541 l_pull_sequence_rec.minimum_order_quantity := p_old_pull_sequence_rec.minimum_order_quantity;
542 END IF;
543
544 IF l_pull_sequence_rec.aggregation_type = FND_API.G_MISS_NUM THEN
545 l_pull_sequence_rec.aggregation_type := p_old_pull_sequence_rec.aggregation_type;
546 END IF;
547
548 IF l_pull_sequence_rec.Allocation_Percent = FND_API.G_MISS_NUM THEN
549 l_pull_sequence_rec.Allocation_Percent := p_old_pull_sequence_rec.Allocation_Percent;
550 END IF;
551
552 IF l_pull_sequence_rec.last_update_login = FND_API.G_MISS_NUM THEN
553 l_pull_sequence_rec.last_update_login := p_old_pull_sequence_rec.last_update_login;
554 END IF;
555
556 IF l_pull_sequence_rec.updated_flag = FND_API.G_MISS_NUM THEN
557 l_pull_sequence_rec.updated_flag := p_old_pull_sequence_rec.updated_flag;
558 END IF;
559
560 IF l_pull_sequence_rec.attribute_category = FND_API.G_MISS_CHAR THEN
561 l_pull_sequence_rec.attribute_category := p_old_pull_sequence_rec.attribute_category;
562 END IF;
563
564 IF l_pull_sequence_rec.attribute1 = FND_API.G_MISS_CHAR THEN
565 l_pull_sequence_rec.attribute1 := p_old_pull_sequence_rec.attribute1;
566 END IF;
567
568 IF l_pull_sequence_rec.attribute2 = FND_API.G_MISS_CHAR THEN
569 l_pull_sequence_rec.attribute2 := p_old_pull_sequence_rec.attribute2;
570 END IF;
571
572 IF l_pull_sequence_rec.attribute3 = FND_API.G_MISS_CHAR THEN
573 l_pull_sequence_rec.attribute3 := p_old_pull_sequence_rec.attribute3;
574 END IF;
575
576 IF l_pull_sequence_rec.attribute4 = FND_API.G_MISS_CHAR THEN
577 l_pull_sequence_rec.attribute4 := p_old_pull_sequence_rec.attribute4;
578 END IF;
579
580 IF l_pull_sequence_rec.attribute5 = FND_API.G_MISS_CHAR THEN
581 l_pull_sequence_rec.attribute5 := p_old_pull_sequence_rec.attribute5;
582 END IF;
583
584 IF l_pull_sequence_rec.attribute6 = FND_API.G_MISS_CHAR THEN
585 l_pull_sequence_rec.attribute6 := p_old_pull_sequence_rec.attribute6;
586 END IF;
587
588 IF l_pull_sequence_rec.attribute7 = FND_API.G_MISS_CHAR THEN
589 l_pull_sequence_rec.attribute7 := p_old_pull_sequence_rec.attribute7;
590 END IF;
591
592 IF l_pull_sequence_rec.attribute8 = FND_API.G_MISS_CHAR THEN
593 l_pull_sequence_rec.attribute8 := p_old_pull_sequence_rec.attribute8;
594 END IF;
595
596 IF l_pull_sequence_rec.attribute9 = FND_API.G_MISS_CHAR THEN
597 l_pull_sequence_rec.attribute9 := p_old_pull_sequence_rec.attribute9;
598 END IF;
599
600 IF l_pull_sequence_rec.attribute10 = FND_API.G_MISS_CHAR THEN
601 l_pull_sequence_rec.attribute10 := p_old_pull_sequence_rec.attribute10;
602 END IF;
603
604 IF l_pull_sequence_rec.attribute11 = FND_API.G_MISS_CHAR THEN
605 l_pull_sequence_rec.attribute11 := p_old_pull_sequence_rec.attribute11;
606 END IF;
607
608 IF l_pull_sequence_rec.attribute12 = FND_API.G_MISS_CHAR THEN
609 l_pull_sequence_rec.attribute12 := p_old_pull_sequence_rec.attribute12;
610 END IF;
611
612 IF l_pull_sequence_rec.attribute13 = FND_API.G_MISS_CHAR THEN
613 l_pull_sequence_rec.attribute13 := p_old_pull_sequence_rec.attribute13;
614 END IF;
615
616 IF l_pull_sequence_rec.attribute14 = FND_API.G_MISS_CHAR THEN
617 l_pull_sequence_rec.attribute14 := p_old_pull_sequence_rec.attribute14;
618 END IF;
619
620 IF l_pull_sequence_rec.attribute15 = FND_API.G_MISS_CHAR THEN
621 l_pull_sequence_rec.attribute15 := p_old_pull_sequence_rec.attribute15;
622 END IF;
623
624 IF l_pull_sequence_rec.request_id = FND_API.G_MISS_NUM THEN
625 l_pull_sequence_rec.request_id := p_old_pull_sequence_rec.request_id;
626 END IF;
627
628 IF l_pull_sequence_rec.program_application_id = FND_API.G_MISS_NUM THEN
629 l_pull_sequence_rec.program_application_id := p_old_pull_sequence_rec.program_application_id;
630 END IF;
631
632 IF l_pull_sequence_rec.program_id = FND_API.G_MISS_NUM THEN
633 l_pull_sequence_rec.program_id := p_old_pull_sequence_rec.program_id;
634 END IF;
635
636 IF l_pull_sequence_rec.program_update_date = FND_API.G_MISS_DATE THEN
637 l_pull_sequence_rec.program_update_date := p_old_pull_sequence_rec.program_update_date;
638 END IF;
639
640 IF l_pull_sequence_rec.point_of_use_x = FND_API.G_MISS_NUM THEN
641 l_pull_sequence_rec.point_of_use_x := p_old_pull_sequence_rec.point_of_use_x;
642 END IF;
643
644 IF l_pull_sequence_rec.point_of_use_y = FND_API.G_MISS_NUM THEN
645 l_pull_sequence_rec.point_of_use_y := p_old_pull_sequence_rec.point_of_use_y;
646 END IF;
647
648 IF l_pull_sequence_rec.point_of_supply_x = FND_API.G_MISS_NUM THEN
649 l_pull_sequence_rec.point_of_supply_x := p_old_pull_sequence_rec.point_of_supply_x;
650 END IF;
651
652 IF l_pull_sequence_rec.point_of_supply_y = FND_API.G_MISS_NUM THEN
653 l_pull_sequence_rec.point_of_supply_y := p_old_pull_sequence_rec.point_of_supply_y;
654 END IF;
655
656 IF l_pull_sequence_rec.planning_update_status = FND_API.G_MISS_NUM THEN
657 l_pull_sequence_rec.planning_update_status := p_old_pull_sequence_rec.planning_update_status;
658 END IF;
659
660 IF l_pull_sequence_rec.auto_request = FND_API.G_MISS_CHAR THEN
661 l_pull_sequence_rec.auto_request := p_old_pull_sequence_rec.auto_request;
662 END IF;
663
664 /*Added the following IF statement for 3905884.*/
665 IF l_pull_sequence_rec.auto_allocate_flag = FND_API.G_MISS_NUM THEN
666 l_pull_sequence_rec.auto_allocate_flag := p_old_pull_sequence_rec.auto_allocate_flag;
667 END IF;
668
669 RETURN l_pull_sequence_rec;
670
671 END Complete_Record;
672
673 PROCEDURE Insert_Row(x_return_status OUT NOCOPY Varchar2,
674 p_pull_sequence_id IN Out NOCOPY NUMBER,
675 p_Inventory_item_id NUMBER,
676 p_Organization_id NUMBER,
677 p_Subinventory_name VARCHAR2,
678 p_Kanban_Plan_id NUMBER,
679 p_Source_type NUMBER,
680 p_Last_Update_Date DATE,
681 p_Last_Updated_By NUMBER,
682 p_Creation_Date DATE,
683 p_Created_By NUMBER,
684 p_Last_Update_Login NUMBER,
685 p_Locator_id NUMBER,
686 p_Supplier_id NUMBER,
687 p_Supplier_site_id NUMBER,
688 p_Source_Organization_id NUMBER,
689 p_Source_Subinventory VARCHAR2,
690 p_Source_Locator_id NUMBER,
691 p_Wip_Line_id NUMBER,
692 p_Release_Kanban_Flag NUMBER,
693 p_Calculate_Kanban_Flag NUMBER,
694 p_Kanban_size NUMBER,
695 p_Number_of_cards NUMBER,
696 p_Minimum_order_quantity NUMBER,
697 p_Aggregation_type NUMBER,
698 p_Allocation_Percent NUMBER,
699 p_Replenishment_lead_time NUMBER,
700 p_Fixed_Lot_multiplier NUMBER,
701 p_Safety_Stock_Days NUMBER,
702 p_Updated_Flag NUMBER,
703 p_Attribute_Category VARCHAR2,
704 p_Attribute1 VARCHAR2,
705 p_Attribute2 VARCHAR2,
706 p_Attribute3 VARCHAR2,
707 p_Attribute4 VARCHAR2,
708 p_Attribute5 VARCHAR2,
709 p_Attribute6 VARCHAR2,
710 p_Attribute7 VARCHAR2,
711 p_Attribute8 VARCHAR2,
712 p_Attribute9 VARCHAR2,
713 p_Attribute10 VARCHAR2,
714 p_Attribute11 VARCHAR2,
715 p_Attribute12 VARCHAR2,
716 p_Attribute13 VARCHAR2,
717 p_Attribute14 VARCHAR2,
718 p_Attribute15 VARCHAR2,
719 p_Request_Id NUMBER,
720 p_Program_application_Id NUMBER,
721 p_Program_Id NUMBER,
722 p_Program_Update_date DATE,
723 p_point_of_use_x NUMBER DEFAULT NULL,
724 p_point_of_use_y NUMBER DEFAULT NULL,
725 p_point_of_supply_x NUMBER DEFAULT NULL,
726 p_point_of_supply_y NUMBER DEFAULT NULL,
727 p_planning_update_status NUMBER DEFAULT NULL,
728 p_auto_request VARCHAR2 DEFAULT NULL,
729 p_Auto_Allocate_Flag NUMBER ) --Bug3905884
730 is
731 l_pull_sequence_Id MTL_Kanban_Pull_Sequences.Pull_Sequence_Id%Type;
732 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
733 BEGIN
734 FND_MSG_PUB.Initialize;
735 If nvl(p_Pull_sequence_Id,0) <= 0
736 Then
737 Select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL
738 into l_pull_sequence_Id
739 from dual;
740 Else
741 l_pull_sequence_Id := p_Pull_sequence_Id;
742 End If;
743
744 INSERT INTO MTL_KANBAN_PULL_SEQUENCES
745 (
746 Pull_sequence_id,
747 Inventory_item_id,
748 Organization_id,
749 Subinventory_name,
750 Kanban_Plan_id,
751 Source_type,
752 Last_Update_Date,
753 Last_Updated_By,
754 Creation_Date,
755 Created_By,
756 Last_Update_Login,
757 Locator_id,
758 Supplier_id,
759 Supplier_site_id,
760 Source_Organization_id,
761 Source_Subinventory,
762 Source_Locator_id,
763 Wip_Line_id,
764 Release_Kanban_flag,
765 Calculate_Kanban_flag,
766 Kanban_size,
767 Number_of_cards,
768 Minimum_order_quantity,
769 Aggregation_type,
770 Allocation_Percent,
771 Replenishment_lead_time,
772 Fixed_Lot_multiplier,
773 Safety_Stock_Days,
774 Updated_Flag,
775 Attribute_Category,
776 Attribute1,
777 Attribute2,
778 Attribute3,
779 Attribute4,
780 Attribute5,
781 Attribute6,
782 Attribute7,
783 Attribute8,
784 Attribute9,
785 Attribute10,
786 Attribute11,
787 Attribute12,
788 Attribute13,
789 Attribute14,
790 Attribute15,
791 Request_Id,
792 Program_application_Id,
793 Program_Id,
794 Program_Update_date,
795 point_of_use_x,
796 point_of_use_y,
797 point_of_supply_x,
798 point_of_supply_y,
799 planning_update_status,
800 auto_request,
801 Auto_Allocate_Flag) --Bug3905884
802 Values
803 (
804 l_Pull_sequence_id,
805 p_Inventory_item_id,
806 p_Organization_id,
807 p_Subinventory_name,
808 p_Kanban_Plan_id,
809 p_Source_type,
810 p_Last_Update_Date,
811 p_Last_Updated_By,
812 p_Creation_Date,
813 p_Created_By,
814 p_Last_Update_Login,
815 p_Locator_id,
816 p_Supplier_id,
817 p_Supplier_site_id,
818 p_Source_Organization_id,
819 p_Source_Subinventory,
820 p_Source_Locator_id,
821 p_Wip_Line_id,
822 p_Release_Kanban_flag,
823 p_Calculate_Kanban_flag,
824 p_Kanban_size,
825 p_Number_of_cards,
826 p_Minimum_order_quantity,
827 p_Aggregation_type,
828 p_Allocation_Percent,
829 p_Replenishment_lead_time,
830 p_Fixed_Lot_multiplier,
831 p_Safety_Stock_Days,
832 p_Updated_Flag,
833 p_Attribute_Category,
834 p_Attribute1,
835 p_Attribute2,
836 p_Attribute3,
837 p_Attribute4,
838 p_Attribute5,
839 p_Attribute6,
840 p_Attribute7,
841 p_Attribute8,
842 p_Attribute9,
843 p_Attribute10,
844 p_Attribute11,
845 p_Attribute12,
846 p_Attribute13,
847 p_Attribute14,
848 p_Attribute15,
849 p_Request_Id,
850 p_Program_application_Id,
851 p_Program_Id,
852 p_Program_Update_Date,
853 p_point_of_use_x,
854 p_point_of_use_y,
855 p_point_of_supply_x,
856 p_point_of_supply_y,
857 p_planning_update_status,
858 p_auto_request,
859 p_Auto_Allocate_Flag --Bug3905884
860 );
861
862 p_pull_sequence_id := l_Pull_Sequence_id;
863
864 x_return_status := l_return_status;
865 EXCEPTION
866 WHEN FND_API.G_EXC_ERROR THEN
867
868 x_return_status := FND_API.G_RET_STS_ERROR;
869
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873
874 WHEN OTHERS THEN
875 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
876 THEN
877 FND_MSG_PUB.Add_Exc_Msg
878 ( G_PKG_NAME
879 , 'Insert_Row'
880 );
881 END IF;
882
883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
884
885 End Insert_Row;
886
887 Procedure Insert_Row(p_pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type)
888 Is
889 l_Pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type :=
890 p_pull_sequence_rec;
891 l_return_status Varchar2(1);
892
893 Begin
894 FND_MSG_PUB.Initialize;
895 INV_PullSequence_PKG.Insert_Row(
896 x_return_status =>l_return_status,
897 p_pull_sequence_id =>l_Pull_sequence_Rec.pull_sequence_id,
898 p_Inventory_item_id =>l_Pull_sequence_Rec.Inventory_item_id,
899 p_Organization_id =>l_Pull_sequence_Rec.Organization_id,
900 p_Subinventory_name =>l_Pull_sequence_Rec.Subinventory_name,
901 p_Kanban_Plan_id =>l_Pull_sequence_Rec.Kanban_Plan_id,
902 p_Source_type =>l_Pull_sequence_Rec.Source_type,
903 p_Last_Update_Date =>l_Pull_sequence_Rec.Last_Update_Date,
904 p_Last_Updated_By =>l_Pull_sequence_Rec.Last_Updated_By,
905 p_Creation_Date =>l_Pull_sequence_Rec.Creation_Date,
906 p_Created_By =>l_Pull_sequence_Rec.Created_By,
907 p_Last_Update_Login =>l_Pull_sequence_Rec.Last_Update_Login,
908 p_Locator_id =>l_Pull_sequence_Rec.Locator_id,
909 p_Supplier_id =>l_Pull_sequence_Rec.Supplier_id,
910 p_Supplier_site_id =>l_Pull_sequence_Rec.Supplier_site_id,
911 p_Source_Organization_id=>l_Pull_sequence_Rec.Source_Organization_id,
912 p_Source_Subinventory =>l_Pull_sequence_Rec.Source_Subinventory,
913 p_Source_Locator_id =>l_Pull_sequence_Rec.Source_Locator_id,
914 p_Wip_Line_id =>l_Pull_Sequence_Rec.Wip_Line_id,
915 p_Release_Kanban_Flag =>l_Pull_sequence_Rec.Release_Kanban_Flag,
916 p_Calculate_Kanban_Flag =>l_Pull_sequence_Rec.Calculate_Kanban_Flag,
917 p_Kanban_size =>l_Pull_sequence_Rec.Kanban_size,
918 p_Number_of_cards =>l_Pull_sequence_Rec.Number_of_cards,
919 p_Minimum_order_quantity=>l_Pull_sequence_Rec.Minimum_order_quantity,
920 p_Aggregation_type =>l_Pull_sequence_Rec.Aggregation_type,
921 p_Allocation_Percent =>l_Pull_sequence_Rec.Allocation_Percent,
922 p_Replenishment_lead_time=>l_Pull_sequence_Rec.Replenishment_lead_time,
923 p_Fixed_Lot_multiplier =>l_Pull_sequence_Rec.Fixed_Lot_multiplier,
924 p_Safety_Stock_Days =>l_Pull_sequence_Rec.Safety_Stock_Days,
925 p_Updated_Flag =>l_Pull_sequence_Rec.Updated_Flag,
926 p_Attribute_Category =>l_Pull_sequence_Rec.Attribute_Category,
927 p_Attribute1 =>l_Pull_sequence_Rec.Attribute1,
928 p_Attribute2 =>l_Pull_sequence_Rec.Attribute2,
929 p_Attribute3 =>l_Pull_sequence_Rec.Attribute3,
930 p_Attribute4 =>l_Pull_sequence_Rec.Attribute4,
931 p_Attribute5 =>l_Pull_sequence_Rec.Attribute5,
932 p_Attribute6 =>l_Pull_sequence_Rec.Attribute6,
933 p_Attribute7 =>l_Pull_sequence_Rec.Attribute7,
934 p_Attribute8 =>l_Pull_sequence_Rec.Attribute8,
935 p_Attribute9 =>l_Pull_sequence_Rec.Attribute9,
936 p_Attribute10 =>l_Pull_sequence_Rec.Attribute10,
937 p_Attribute11 =>l_Pull_sequence_Rec.Attribute11,
938 p_Attribute12 =>l_Pull_sequence_Rec.Attribute12,
939 p_Attribute13 =>l_Pull_sequence_Rec.Attribute13,
940 p_Attribute14 =>l_Pull_sequence_Rec.Attribute14,
941 p_Attribute15 =>l_Pull_sequence_Rec.Attribute15,
942 p_Request_Id =>l_Pull_sequence_Rec.Request_Id,
943 p_Program_application_Id=>l_Pull_sequence_Rec.Program_application_Id,
944 p_Program_Id =>l_Pull_sequence_Rec.Program_Id,
945 p_Program_Update_date =>l_Pull_sequence_Rec.Program_Update_date,
946 p_point_of_use_x =>l_Pull_sequence_Rec.point_of_use_x,
947 p_point_of_use_y =>l_Pull_sequence_Rec.point_of_use_y,
948 p_point_of_supply_x =>l_Pull_sequence_Rec.point_of_supply_x,
949 p_point_of_supply_y =>l_Pull_sequence_Rec.point_of_supply_y,
950 p_planning_update_status=>l_Pull_sequence_Rec.planning_update_status,
951 p_auto_request =>l_Pull_sequence_Rec.auto_request,
952 p_Auto_Allocate_Flag =>l_Pull_sequence_Rec.Auto_Allocate_Flag); --Added for 3905884.
953 if l_return_status = FND_API.G_RET_STS_ERROR
954 Then
955 Raise FND_API.G_EXC_ERROR;
956 End if;
957
958 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
959 Then
960 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
961 End If;
962
963 EXCEPTION
964 WHEN FND_API.G_EXC_ERROR THEN
965
966 Raise FND_API.G_EXC_ERROR;
967
968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
969
970 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
971
972 WHEN OTHERS THEN
973
974 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
975 THEN
976 FND_MSG_PUB.Add_Exc_Msg
977 ( G_PKG_NAME
978 , 'Insert_Row'
979 );
980 END IF;
981
982 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
983
984 End Insert_Row;
985
986 PROCEDURE Lock_Row (p_Pull_Sequence_Id NUMBER,
987 p_Inventory_item_id NUMBER,
988 p_Organization_id NUMBER,
989 p_Subinventory_name VARCHAR2,
990 p_Kanban_Plan_id NUMBER,
991 p_Source_type NUMBER,
992 p_Locator_id NUMBER,
993 p_Supplier_id NUMBER,
994 p_Supplier_site_id NUMBER,
995 p_Source_Organization_id NUMBER,
996 p_Source_Subinventory VARCHAR2,
997 p_Source_Locator_id NUMBER,
998 p_Wip_Line_id NUMBER,
999 p_Release_Kanban_flag NUMBER,
1000 p_Calculate_Kanban_flag NUMBER,
1001 p_Kanban_size NUMBER,
1002 p_Number_of_cards NUMBER,
1003 p_Minimum_order_quantity NUMBER,
1004 p_Aggregation_type NUMBER,
1005 p_Allocation_Percent NUMBER,
1006 p_Replenishment_lead_time NUMBER,
1007 p_Fixed_Lot_multiplier NUMBER,
1008 p_Safety_Stock_Days NUMBER,
1009 p_Updated_Flag NUMBER,
1010 p_Attribute_Category VARCHAR2,
1011 p_Attribute1 VARCHAR2,
1012 p_Attribute2 VARCHAR2,
1013 p_Attribute3 VARCHAR2,
1014 p_Attribute4 VARCHAR2,
1015 p_Attribute5 VARCHAR2,
1016 p_Attribute6 VARCHAR2,
1017 p_Attribute7 VARCHAR2,
1018 p_Attribute8 VARCHAR2,
1019 p_Attribute9 VARCHAR2,
1020 p_Attribute10 VARCHAR2,
1021 p_Attribute11 VARCHAR2,
1022 p_Attribute12 VARCHAR2,
1023 p_Attribute13 VARCHAR2,
1024 p_Attribute14 VARCHAR2,
1025 p_Attribute15 VARCHAR2,
1026 p_point_of_use_x NUMBER DEFAULT NULL,
1027 p_point_of_use_y NUMBER DEFAULT NULL,
1028 p_point_of_supply_x NUMBER DEFAULT NULL,
1029 p_point_of_supply_y NUMBER DEFAULT NULL,
1030 p_planning_update_status NUMBER DEFAULT NULL,
1031 p_auto_request VARCHAR2 DEFAULT NULL,
1032 p_Auto_Allocate_Flag NUMBER)--Added for 3905884.
1033 IS
1034 CURSOR Get_Current_Row IS
1035 SELECT *
1036 FROM MTL_KANBAN_PULL_SEQUENCES
1037 WHERE pull_sequence_id = p_pull_sequence_id
1038 FOR UPDATE of Organization_Id NOWAIT;
1039
1040 Recinfo MTL_KANBAN_PULL_SEQUENCES%ROWTYPE;
1041 RECORD_CHANGED EXCEPTION;
1042 BEGIN
1043 OPEN Get_Current_Row;
1044 FETCH Get_Current_Row INTO Recinfo;
1045 if (Get_Current_Row%NOTFOUND) then
1046 CLOSE Get_Current_Row;
1047 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1048 APP_EXCEPTION.Raise_Exception;
1049 end if;
1050 CLOSE Get_Current_Row;
1051 if not (
1052 Recinfo.Inventory_item_id = p_Inventory_item_id and
1053 Recinfo.Organization_Id = p_Organization_Id and
1054 Recinfo.Subinventory_name = p_Subinventory_name and
1055 Recinfo.Kanban_Plan_id = p_Kanban_Plan_id and
1056 Recinfo.Source_type = p_Source_type and
1057 ((Recinfo.Locator_id = p_Locator_id)
1058 or (Recinfo.Locator_id is null and p_Locator_id is null)) and
1059 ((Recinfo.Supplier_id = p_Supplier_id)
1060 or (Recinfo.Supplier_id is null and p_Supplier_id is null)) and
1061 ((Recinfo.Supplier_site_id = p_Supplier_site_id)
1062 or (Recinfo.Supplier_site_id is null and p_Supplier_site_id is null)) and
1063 ((Recinfo.Source_Organization_id= p_Source_Organization_id)
1064 or (Recinfo.Source_Organization_id is null and p_Source_Organization_id is null)) and
1065 ((Recinfo.Source_Subinventory = p_Source_Subinventory)
1066 or (Recinfo.Source_Subinventory is null and p_Source_Subinventory is null)) and
1067 ((Recinfo.Source_Locator_id = p_Source_Locator_id)
1068 or (Recinfo.Source_Locator_id is null and p_Source_Locator_id is null)) and
1069 ((Recinfo.Wip_Line_id = p_Wip_Line_id)
1070 or (Recinfo.Wip_Line_id is null and p_Wip_Line_id is null)) and
1071 ((Recinfo.Release_Kanban_Flag = p_Release_Kanban_flag)
1072 or (Recinfo.Release_Kanban_flag is null and p_Release_Kanban_flag is null)) and
1073 ((Recinfo.Calculate_Kanban_Flag = p_Calculate_Kanban_flag)
1074 or (Recinfo.Calculate_Kanban_flag is null and p_Calculate_Kanban_flag is null)) and
1075 ((Recinfo.Kanban_size = p_Kanban_size)
1076 or (Recinfo.Kanban_size is null and p_Kanban_size is null)) and
1077 ((Recinfo.Number_of_cards = p_Number_of_cards)
1078 or (Recinfo.Number_of_cards is null and p_Number_of_cards is null)) and
1079 ((Recinfo.Minimum_order_quantity = p_Minimum_order_quantity)
1080 or (Recinfo.Minimum_order_quantity is null and p_Minimum_order_quantity is null)) and
1081 ((Recinfo.Aggregation_Type = p_Aggregation_Type)
1082 or (Recinfo.Aggregation_Type is null and p_Aggregation_Type is null)) and
1083 ((Recinfo.Allocation_Percent = p_Allocation_Percent)
1084 or (Recinfo.Allocation_Percent is null and p_Allocation_Percent is null)) and
1085 ((Recinfo.Replenishment_lead_time = p_Replenishment_lead_time)
1086 or (Recinfo.Replenishment_lead_time is null and p_Replenishment_lead_time is null)) and
1087 ((Recinfo.fixed_lot_multiplier = p_fixed_lot_multiplier)
1088 or (Recinfo.fixed_lot_multiplier is null and p_fixed_lot_multiplier is null)) and
1089 ((Recinfo.Safety_Stock_Days = p_Safety_Stock_Days)
1090 or (Recinfo.Safety_Stock_Days is null and p_Safety_Stock_Days is null)) and
1091 ((Recinfo.Updated_Flag = p_Updated_Flag)
1092 or (Recinfo.Updated_Flag is null and p_Updated_Flag is null)) and
1093 ((Recinfo.Attribute_Category = p_Attribute_Category)
1094 or (Recinfo.Attribute_Category is null and p_Attribute_Category is null))
1095 /*Fix for 3905884.*/
1096 and ((Recinfo.Auto_Allocate_Flag = p_Auto_Allocate_Flag)
1097 or (Recinfo.Auto_Allocate_Flag is null and p_Auto_Allocate_Flag is null)) /*End of fix for 3905884*/
1098 ) then
1099 RAISE RECORD_CHANGED;
1100 end if;
1101 if not (
1102 ( (Recinfo.attribute1 = p_Attribute1)
1103 OR ( (Recinfo.attribute1 IS NULL)
1104 AND (p_Attribute1 IS NULL)))
1105 AND ( (Recinfo.attribute2 = p_Attribute2)
1106 OR ( (Recinfo.attribute2 IS NULL)
1107 AND (p_Attribute2 IS NULL)))
1108 AND ( (Recinfo.attribute3 = p_Attribute3)
1109 OR ( (Recinfo.attribute3 IS NULL)
1110 AND (p_Attribute3 IS NULL)))
1111 AND ( (Recinfo.attribute4 = p_Attribute4)
1112 OR ( (Recinfo.attribute4 IS NULL)
1113 AND (p_Attribute4 IS NULL)))
1114 AND ( (Recinfo.attribute5 = p_Attribute5)
1115 OR ( (Recinfo.attribute5 IS NULL)
1116 AND (p_Attribute5 IS NULL)))
1117 AND ( (Recinfo.attribute6 = p_Attribute6)
1118 OR ( (Recinfo.attribute6 IS NULL)
1119 AND (p_Attribute6 IS NULL)))
1120 AND ( (Recinfo.attribute7 = p_Attribute7)
1121 OR ( (Recinfo.attribute7 IS NULL)
1122 AND (p_Attribute7 IS NULL)))
1123 AND ( (Recinfo.attribute8 = p_Attribute8)
1124 OR ( (Recinfo.attribute8 IS NULL)
1125 AND (p_Attribute8 IS NULL)))
1126 AND ( (Recinfo.attribute9 = p_Attribute9)
1127 OR ( (Recinfo.attribute9 IS NULL)
1128 AND (p_Attribute9 IS NULL)))
1129 AND ( (Recinfo.attribute10 = p_Attribute10)
1130 OR ( (Recinfo.attribute10 IS NULL)
1131 AND (p_Attribute10 IS NULL)))
1132 AND ( (Recinfo.attribute11 = p_Attribute11)
1133 OR ( (Recinfo.attribute11 IS NULL)
1134 AND (p_Attribute11 IS NULL)))
1135 AND ( (Recinfo.attribute12 = p_Attribute12)
1136 OR ( (Recinfo.attribute12 IS NULL)
1137 AND (p_Attribute12 IS NULL)))
1138 AND ( (Recinfo.attribute13 = p_Attribute13)
1139 OR ( (Recinfo.attribute13 IS NULL)
1140 AND (p_Attribute13 IS NULL)))
1141 AND ( (Recinfo.attribute14 = p_Attribute14)
1142 OR ( (Recinfo.attribute14 IS NULL)
1143 AND (p_Attribute14 IS NULL)))
1144 AND ( (Recinfo.attribute15 = p_Attribute15)
1145 OR ( (Recinfo.attribute15 IS NULL)
1146 AND (p_Attribute15 IS NULL)))
1147 ) then
1148 RAISE RECORD_CHANGED;
1149 end if;
1150
1151 IF NOT (
1152 ( (Recinfo.point_of_use_x = p_point_of_use_x)
1153 OR ( (Recinfo.point_of_use_x IS NULL)
1154 AND (p_point_of_use_x IS NULL)))
1155 AND ( (Recinfo.point_of_use_y = p_point_of_use_y)
1156 OR ( (Recinfo.point_of_use_y IS NULL)
1157 AND (p_point_of_use_y IS NULL)))
1158 AND ( (Recinfo.point_of_supply_x = p_point_of_supply_x)
1159 OR ( (Recinfo.point_of_supply_x IS NULL)
1160 AND (p_point_of_supply_x IS NULL)))
1161 AND ( (Recinfo.point_of_supply_x = p_point_of_supply_x)
1162 OR ( (Recinfo.point_of_supply_x IS NULL)
1163 AND (p_point_of_supply_x IS NULL)))
1164 AND ( (Recinfo.point_of_supply_y = p_point_of_supply_y)
1165 OR ( (Recinfo.point_of_supply_y IS NULL)
1166 AND (p_point_of_supply_y IS NULL)))
1167 AND ( (Recinfo.planning_update_status= p_planning_update_status)
1168 OR ( (Recinfo.planning_update_status IS NULL)
1169 AND (p_planning_update_status IS NULL)))
1170 AND ( (Recinfo.auto_request = p_auto_request)
1171 OR ( (Recinfo.auto_request IS NULL)
1172 AND (p_auto_request IS NULL)))
1173 ) THEN
1174 RAISE RECORD_CHANGED;
1175 END IF;
1176 exception
1177 WHEN RECORD_CHANGED THEN
1178 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1179 APP_EXCEPTION.Raise_Exception;
1180 when others then
1181 raise;
1182 END Lock_Row;
1183
1184 PROCEDURE Update_Row(x_return_status OUT NOCOPY VARCHAR2,
1185 p_Pull_Sequence_Id NUMBER,
1186 p_Inventory_item_id NUMBER,
1187 p_Organization_id NUMBER,
1188 p_Subinventory_name VARCHAR2,
1189 p_Kanban_Plan_id NUMBER,
1190 p_Source_type NUMBER,
1191 p_Last_Update_Date DATE,
1192 p_Last_Updated_By NUMBER,
1193 p_Creation_Date DATE,
1194 p_Created_By NUMBER,
1195 p_Last_Update_Login NUMBER,
1196 p_Locator_id NUMBER,
1197 p_Supplier_id NUMBER,
1198 p_Supplier_site_id NUMBER,
1199 p_Source_Organization_id NUMBER,
1200 p_Source_Subinventory VARCHAR2,
1201 p_Source_Locator_id NUMBER,
1202 p_Wip_Line_id NUMBER,
1203 p_Release_Kanban_flag NUMBER,
1204 p_Calculate_Kanban_flag NUMBER,
1205 p_Kanban_size NUMBER,
1206 p_Number_of_cards NUMBER,
1207 p_Minimum_order_quantity NUMBER,
1208 p_Aggregation_Type NUMBER,
1209 p_Allocation_Percent NUMBER,
1210 p_Replenishment_lead_time NUMBER,
1211 p_Fixed_Lot_multiplier NUMBER,
1212 p_Safety_Stock_Days NUMBER,
1213 p_Updated_Flag NUMBER,
1214 p_Attribute_Category VARCHAR2,
1215 p_Attribute1 VARCHAR2,
1216 p_Attribute2 VARCHAR2,
1217 p_Attribute3 VARCHAR2,
1218 p_Attribute4 VARCHAR2,
1219 p_Attribute5 VARCHAR2,
1220 p_Attribute6 VARCHAR2,
1221 p_Attribute7 VARCHAR2,
1222 p_Attribute8 VARCHAR2,
1223 p_Attribute9 VARCHAR2,
1224 p_Attribute10 VARCHAR2,
1225 p_Attribute11 VARCHAR2,
1226 p_Attribute12 VARCHAR2,
1227 p_Attribute13 VARCHAR2,
1228 p_Attribute14 VARCHAR2,
1229 p_Attribute15 VARCHAR2,
1230 p_point_of_use_x NUMBER DEFAULT NULL,
1231 p_point_of_use_y NUMBER DEFAULT NULL,
1232 p_point_of_supply_x NUMBER DEFAULT NULL,
1233 p_point_of_supply_y NUMBER DEFAULT NULL,
1234 p_planning_update_status NUMBER DEFAULT NULL,
1235 p_auto_request VARCHAR2 DEFAULT NULL,
1236 p_Auto_Allocate_Flag NUMBER ) --Added for 3905884
1237 IS
1238
1239 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1240
1241 BEGIN
1242 FND_MSG_PUB.Initialize;
1243 UPDATE MTL_KANBAN_PULL_SEQUENCES
1244 SET
1245 Inventory_item_id = p_Inventory_item_id,
1246 Organization_Id = p_Organization_Id,
1247 Subinventory_name = p_Subinventory_name,
1248 Kanban_Plan_id = p_Kanban_Plan_id,
1249 Source_type = p_Source_type,
1250 Last_Update_Date = p_Last_Update_Date,
1251 Last_Updated_By = p_Last_Updated_By,
1252 Creation_Date = p_Creation_Date,
1253 Created_By = p_Created_By,
1254 Last_Update_Login = p_Last_Update_Login,
1255 Locator_id = p_Locator_id,
1256 Supplier_id = p_Supplier_id,
1257 Supplier_site_id = p_Supplier_site_id,
1258 Source_Organization_id = p_Source_Organization_id,
1259 Source_Subinventory = p_Source_Subinventory,
1260 Source_Locator_id = p_Source_Locator_id,
1261 Wip_Line_id = p_Wip_Line_id,
1262 Release_Kanban_Flag = p_Release_Kanban_flag,
1263 Calculate_Kanban_Flag = p_Calculate_Kanban_flag,
1264 Kanban_size = p_Kanban_size,
1265 Number_of_cards = p_Number_of_cards,
1266 Minimum_order_quantity = p_Minimum_order_quantity,
1267 Aggregation_Type = p_Aggregation_Type,
1268 Allocation_Percent = p_Allocation_Percent,
1269 Replenishment_lead_time = p_Replenishment_lead_time,
1270 Fixed_Lot_multiplier = p_Fixed_Lot_multiplier,
1271 Safety_Stock_Days = p_Safety_Stock_Days,
1272 Updated_Flag = p_Updated_Flag,
1273 Attribute_Category = p_Attribute_Category,
1274 Attribute1 = p_Attribute1,
1275 Attribute2 = p_Attribute2,
1276 Attribute3 = p_Attribute3,
1277 Attribute4 = p_Attribute4,
1278 Attribute5 = p_Attribute5,
1279 Attribute6 = p_Attribute6,
1280 Attribute7 = p_Attribute7,
1281 Attribute8 = p_Attribute8,
1282 Attribute9 = p_Attribute9,
1283 Attribute10 = p_Attribute10,
1284 Attribute11 = p_Attribute11,
1285 Attribute12 = p_Attribute12,
1286 Attribute13 = p_Attribute13,
1287 Attribute14 = p_Attribute14,
1288 Attribute15 = p_Attribute15,
1289 point_of_use_x = p_point_of_use_x,
1290 point_of_use_y = p_point_of_use_y,
1291 point_of_supply_x = p_point_of_supply_x,
1292 point_of_supply_y = p_point_of_supply_y,
1293 planning_update_status = p_planning_update_status,
1294 auto_request = p_auto_request,
1295 Auto_Allocate_Flag = p_Auto_Allocate_Flag --Added for 3905884
1296 WHERE pull_sequence_id = p_pull_sequence_id;
1297
1298 if (SQL%NOTFOUND) then
1299 Raise NO_DATA_FOUND;
1300 end if;
1301 x_return_status := l_return_status;
1302
1303 EXCEPTION
1304
1305 WHEN FND_API.G_EXC_ERROR THEN
1306
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308
1309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1310
1311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1312
1313 WHEN OTHERS THEN
1314
1315 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1316 THEN
1317 FND_MSG_PUB.Add_Exc_Msg
1318 ( G_PKG_NAME
1319 , 'Update_Row'
1320 );
1321 END IF;
1322
1323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1324
1325 END Update_Row;
1326
1327 PROCEDURE Update_Row(
1328 p_pull_sequence_rec INV_Kanban_PVT.Pull_sequence_Rec_Type)
1329 Is
1330
1331 l_return_status Varchar2(1);
1332
1333 Begin
1334 FND_MSG_PUB.Initialize;
1335 Update_Row(
1336 x_return_status =>l_return_status,
1337 p_pull_sequence_id =>p_Pull_sequence_Rec.pull_sequence_id,
1338 p_Inventory_item_id =>p_Pull_sequence_Rec.Inventory_item_id,
1339 p_Organization_id =>p_Pull_sequence_Rec.Organization_id,
1340 p_Subinventory_name =>p_Pull_sequence_Rec.Subinventory_name,
1341 p_Kanban_Plan_id =>p_Pull_sequence_Rec.Kanban_Plan_id,
1342 p_Source_type =>p_Pull_sequence_Rec.Source_type ,
1343 p_Last_Update_Date =>p_Pull_sequence_Rec.Last_Update_Date,
1344 p_Last_Updated_By =>p_Pull_sequence_Rec.Last_Updated_By,
1345 p_Creation_Date =>p_Pull_sequence_Rec.Creation_Date,
1346 p_Created_By =>p_Pull_sequence_Rec.Created_By,
1347 p_Last_Update_Login =>p_Pull_sequence_Rec.Last_Update_Login ,
1348 p_Locator_id =>p_Pull_sequence_Rec.Locator_id,
1349 p_Supplier_id =>p_Pull_sequence_Rec.Supplier_id,
1350 p_Supplier_site_id =>p_Pull_sequence_Rec.Supplier_site_id ,
1351 p_Source_Organization_id=>p_Pull_sequence_Rec.Source_Organization_id,
1352 p_Source_Subinventory =>p_Pull_sequence_Rec.Source_Subinventory,
1353 p_Source_Locator_id =>p_Pull_sequence_Rec.Source_Locator_id ,
1354 p_Wip_Line_id =>p_Pull_Sequence_Rec.Wip_Line_id ,
1355 p_Release_Kanban_Flag =>p_Pull_sequence_Rec.Release_Kanban_Flag,
1356 p_Calculate_Kanban_Flag =>p_Pull_sequence_Rec.Calculate_Kanban_Flag,
1357 p_Kanban_size =>p_Pull_sequence_Rec.Kanban_size ,
1358 p_Number_of_cards =>p_Pull_sequence_Rec.Number_of_cards ,
1359 p_Minimum_order_quantity=>p_Pull_sequence_Rec.Minimum_order_quantity,
1360 p_Aggregation_type =>p_Pull_sequence_Rec.Aggregation_type ,
1361 p_Allocation_Percent =>p_Pull_sequence_Rec.Allocation_Percent,
1362 p_Replenishment_lead_time=>p_Pull_sequence_Rec.Replenishment_lead_time,
1363 p_Fixed_Lot_multiplier =>p_Pull_sequence_Rec.Fixed_Lot_multiplier,
1364 p_Safety_Stock_Days =>p_Pull_sequence_Rec.Safety_Stock_Days ,
1365 p_Updated_Flag =>p_Pull_sequence_Rec.Updated_Flag ,
1366 p_Attribute_Category =>p_Pull_sequence_Rec.Attribute_Category,
1367 p_Attribute1 =>p_Pull_sequence_Rec.Attribute1 ,
1368 p_Attribute2 =>p_Pull_sequence_Rec.Attribute2 ,
1369 p_Attribute3 =>p_Pull_sequence_Rec.Attribute3 ,
1370 p_Attribute4 =>p_Pull_sequence_Rec.Attribute4 ,
1371 p_Attribute5 =>p_Pull_sequence_Rec.Attribute5 ,
1372 p_Attribute6 =>p_Pull_sequence_Rec.Attribute6 ,
1373 p_Attribute7 =>p_Pull_sequence_Rec.Attribute7 ,
1374 p_Attribute8 =>p_Pull_sequence_Rec.Attribute8 ,
1375 p_Attribute9 =>p_Pull_sequence_Rec.Attribute9 ,
1376 p_Attribute10 =>p_Pull_sequence_Rec.Attribute10 ,
1377 p_Attribute11 =>p_Pull_sequence_Rec.Attribute11 ,
1378 p_Attribute12 =>p_Pull_sequence_Rec.Attribute12 ,
1379 p_Attribute13 =>p_Pull_sequence_Rec.Attribute13 ,
1380 p_Attribute14 =>p_Pull_sequence_Rec.Attribute14 ,
1381 p_Attribute15 =>p_Pull_sequence_Rec.Attribute15 ,
1382 p_point_of_use_x =>p_Pull_sequence_Rec.point_of_use_x ,
1383 p_point_of_use_y =>p_Pull_sequence_Rec.point_of_use_y ,
1384 p_point_of_supply_x =>p_Pull_sequence_Rec.point_of_supply_x ,
1385 p_point_of_supply_y =>p_Pull_sequence_Rec.point_of_supply_y ,
1386 p_planning_update_status =>p_Pull_sequence_Rec.planning_update_status,
1387 p_auto_request =>p_Pull_sequence_Rec.auto_request,
1388 p_Auto_Allocate_Flag =>p_Pull_sequence_Rec.Auto_Allocate_Flag ); --Added for 3905884.
1389
1390 if l_return_status = FND_API.G_RET_STS_ERROR
1391 Then
1392 Raise FND_API.G_EXC_ERROR;
1393 End if;
1394
1395 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1396 Then
1397 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1398 End If;
1399
1400 EXCEPTION
1401
1402 WHEN FND_API.G_EXC_ERROR THEN
1403
1404 Raise FND_API.G_EXC_ERROR;
1405
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1407
1408 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1409
1410 WHEN OTHERS THEN
1411
1412 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1413 THEN
1414 FND_MSG_PUB.Add_Exc_Msg
1415 ( G_PKG_NAME
1416 , 'Update_Row'
1417 );
1418 END IF;
1419 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1420
1421 END Update_Row;
1422
1423 PROCEDURE Delete_Row(x_return_status OUT NOCOPY VARCHAR2,
1424 p_Pull_Sequence_Id Number)
1425 IS
1426 l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR;
1427
1428 BEGIN
1429 FND_MSG_PUB.Initialize;
1430 if INV_Kanban_PVT.Ok_To_Delete_Pull_Sequence(p_pull_sequence_Id)
1431 then
1432
1433 DELETE FROM MTL_KANBAN_PULL_SEQUENCES
1434 WHERE pull_sequence_id = p_pull_sequence_id;
1435
1436 if (SQL%NOTFOUND) then
1437 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1438 else
1439 l_return_status := FND_API.G_RET_STS_SUCCESS;
1440 end if;
1441 else
1442 Raise FND_API.G_EXC_ERROR;
1443 end if;
1444
1445 x_return_status := l_return_status;
1446
1447 EXCEPTION
1448 WHEN FND_API.G_EXC_ERROR THEN
1449
1450 x_return_status := FND_API.G_RET_STS_ERROR;
1451
1452 WHEN OTHERS THEN
1453
1454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1455 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1456 THEN
1457 FND_MSG_PUB.Add_Exc_Msg
1458 ( G_PKG_NAME
1459 , 'Delete_Row'
1460 );
1461 END IF;
1462
1463 END Delete_Row;
1464
1465 END INV_PullSequence_PKG;