[Home] [Help]
PACKAGE BODY: APPS.MRP_ASSIGNMENT_HANDLERS
Source
1 PACKAGE BODY MRP_Assignment_Handlers AS
2 /* $Header: MRPHASNB.pls 115.3 99/07/16 12:21:47 porting ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Assignment_Handlers';
7
8 -- Procedure Update_Row
9
10 PROCEDURE Update_Row
11 ( p_Assignment_rec IN MRP_Src_Assignment_PUB.Assignment_Rec_Type
12 )
13 IS
14 BEGIN
15
16 UPDATE MRP_SR_ASSIGNMENTS
17 SET ASSIGNMENT_ID = p_Assignment_rec.Assignment_Id
18 , ASSIGNMENT_SET_ID = p_Assignment_rec.Assignment_Set_Id
19 , ASSIGNMENT_TYPE = p_Assignment_rec.Assignment_Type
20 , ATTRIBUTE1 = p_Assignment_rec.Attribute1
21 , ATTRIBUTE10 = p_Assignment_rec.Attribute10
22 , ATTRIBUTE11 = p_Assignment_rec.Attribute11
23 , ATTRIBUTE12 = p_Assignment_rec.Attribute12
24 , ATTRIBUTE13 = p_Assignment_rec.Attribute13
25 , ATTRIBUTE14 = p_Assignment_rec.Attribute14
26 , ATTRIBUTE15 = p_Assignment_rec.Attribute15
27 , ATTRIBUTE2 = p_Assignment_rec.Attribute2
28 , ATTRIBUTE3 = p_Assignment_rec.Attribute3
29 , ATTRIBUTE4 = p_Assignment_rec.Attribute4
30 , ATTRIBUTE5 = p_Assignment_rec.Attribute5
31 , ATTRIBUTE6 = p_Assignment_rec.Attribute6
32 , ATTRIBUTE7 = p_Assignment_rec.Attribute7
33 , ATTRIBUTE8 = p_Assignment_rec.Attribute8
34 , ATTRIBUTE9 = p_Assignment_rec.Attribute9
35 , ATTRIBUTE_CATEGORY = p_Assignment_rec.Attribute_Category
36 , CATEGORY_ID = p_Assignment_rec.Category_Id
37 , CATEGORY_SET_ID = p_Assignment_rec.Category_Set_Id
38 , CREATED_BY = p_Assignment_rec.Created_By
39 , CREATION_DATE = p_Assignment_rec.Creation_Date
40 , CUSTOMER_ID = p_Assignment_rec.Customer_Id
41 , INVENTORY_ITEM_ID = p_Assignment_rec.Inventory_Item_Id
42 , LAST_UPDATED_BY = p_Assignment_rec.Last_Updated_By
43 , LAST_UPDATE_DATE = p_Assignment_rec.Last_Update_Date
44 , LAST_UPDATE_LOGIN = p_Assignment_rec.Last_Update_Login
45 , ORGANIZATION_ID = p_Assignment_rec.Organization_Id
46 , PROGRAM_APPLICATION_ID = p_Assignment_rec.Program_Application_Id
47 , PROGRAM_ID = p_Assignment_rec.Program_Id
48 , PROGRAM_UPDATE_DATE = p_Assignment_rec.Program_Update_Date
49 , REQUEST_ID = p_Assignment_rec.Request_Id
50 , SECONDARY_INVENTORY = p_Assignment_rec.Secondary_Inventory
51 , SHIP_TO_SITE_ID = p_Assignment_rec.Ship_To_Site_Id
52 , SOURCING_RULE_ID = p_Assignment_rec.Sourcing_Rule_Id
53 , SOURCING_RULE_TYPE = p_Assignment_rec.Sourcing_Rule_Type
54 WHERE ASSIGNMENT_ID = p_Assignment_rec.Assignment_Id
55 ;
56
57 EXCEPTION
58
59 WHEN OTHERS THEN
60
61 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
62 THEN
63 FND_MSG_PUB.Add_Exc_Msg
64 ( G_PKG_NAME
65 , 'Update_Row'
66 );
67 END IF;
68
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70
71 END Update_Row;
72
73 -- Procedure Insert_Row
74
75 PROCEDURE Insert_Row
76 ( p_Assignment_rec IN MRP_Src_Assignment_PUB.Assignment_Rec_Type
77 )
78 IS
79 BEGIN
80
81 INSERT INTO MRP_SR_ASSIGNMENTS
82 ( ASSIGNMENT_ID
83 , ASSIGNMENT_SET_ID
84 , ASSIGNMENT_TYPE
85 , ATTRIBUTE1
86 , ATTRIBUTE10
87 , ATTRIBUTE11
88 , ATTRIBUTE12
89 , ATTRIBUTE13
90 , ATTRIBUTE14
91 , ATTRIBUTE15
92 , ATTRIBUTE2
93 , ATTRIBUTE3
94 , ATTRIBUTE4
95 , ATTRIBUTE5
96 , ATTRIBUTE6
97 , ATTRIBUTE7
98 , ATTRIBUTE8
99 , ATTRIBUTE9
100 , ATTRIBUTE_CATEGORY
101 , CATEGORY_ID
102 , CATEGORY_SET_ID
103 , CREATED_BY
104 , CREATION_DATE
105 , CUSTOMER_ID
106 , INVENTORY_ITEM_ID
107 , LAST_UPDATED_BY
108 , LAST_UPDATE_DATE
109 , LAST_UPDATE_LOGIN
110 , ORGANIZATION_ID
111 , PROGRAM_APPLICATION_ID
112 , PROGRAM_ID
113 , PROGRAM_UPDATE_DATE
114 , REQUEST_ID
115 , SECONDARY_INVENTORY
116 , SHIP_TO_SITE_ID
117 , SOURCING_RULE_ID
118 , SOURCING_RULE_TYPE
119 )
120 VALUES
121 ( p_Assignment_rec.Assignment_Id
122 , p_Assignment_rec.Assignment_Set_Id
123 , p_Assignment_rec.Assignment_Type
124 , p_Assignment_rec.Attribute1
125 , p_Assignment_rec.Attribute10
126 , p_Assignment_rec.Attribute11
127 , p_Assignment_rec.Attribute12
128 , p_Assignment_rec.Attribute13
129 , p_Assignment_rec.Attribute14
130 , p_Assignment_rec.Attribute15
131 , p_Assignment_rec.Attribute2
132 , p_Assignment_rec.Attribute3
133 , p_Assignment_rec.Attribute4
134 , p_Assignment_rec.Attribute5
135 , p_Assignment_rec.Attribute6
136 , p_Assignment_rec.Attribute7
137 , p_Assignment_rec.Attribute8
138 , p_Assignment_rec.Attribute9
139 , p_Assignment_rec.Attribute_Category
140 , p_Assignment_rec.Category_Id
141 , p_Assignment_rec.Category_Set_Id
142 , p_Assignment_rec.Created_By
143 , p_Assignment_rec.Creation_Date
144 , p_Assignment_rec.Customer_Id
145 , p_Assignment_rec.Inventory_Item_Id
146 , p_Assignment_rec.Last_Updated_By
147 , p_Assignment_rec.Last_Update_Date
148 , p_Assignment_rec.Last_Update_Login
149 , p_Assignment_rec.Organization_Id
150 , p_Assignment_rec.Program_Application_Id
151 , p_Assignment_rec.Program_Id
152 , p_Assignment_rec.Program_Update_Date
153 , p_Assignment_rec.Request_Id
154 , p_Assignment_rec.Secondary_Inventory
155 , p_Assignment_rec.Ship_To_Site_Id
156 , p_Assignment_rec.Sourcing_Rule_Id
157 , p_Assignment_rec.Sourcing_Rule_Type
158 );
159
160 EXCEPTION
161
162 WHEN OTHERS THEN
163
164 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
165 THEN
166 FND_MSG_PUB.Add_Exc_Msg
167 ( G_PKG_NAME
168 , 'Insert_Row'
169 );
170 END IF;
171
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173
174 END Insert_Row;
175
176 -- Procedure Delete_Row
177
178 PROCEDURE Delete_Row
179 ( p_Assignment_Id IN NUMBER
180 )
181 IS
182 BEGIN
183
184 DELETE FROM MRP_SR_ASSIGNMENTS
185 WHERE ASSIGNMENT_ID = p_Assignment_Id
186 ;
187
188 EXCEPTION
189
190 WHEN OTHERS THEN
191
192 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
193 THEN
194 FND_MSG_PUB.Add_Exc_Msg
195 ( G_PKG_NAME
196 , 'Delete_Row'
197 );
198 END IF;
199
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201
202 END Delete_Row;
203
204 -- Procedure lock_Row
205 --
206
207 PROCEDURE Lock_Row
208 ( x_return_status OUT VARCHAR2
209 , p_Assignment_rec IN MRP_Src_Assignment_PUB.Assignment_Rec_Type
210 , x_Assignment_rec OUT MRP_Src_Assignment_PUB.Assignment_Rec_Type
211 )
212 IS
213 l_Assignment_rec MRP_Src_Assignment_PUB.Assignment_Rec_Type;
214 BEGIN
215
216 SELECT ASSIGNMENT_ID
217 , ASSIGNMENT_SET_ID
218 , ASSIGNMENT_TYPE
219 , ATTRIBUTE1
220 , ATTRIBUTE10
221 , ATTRIBUTE11
222 , ATTRIBUTE12
223 , ATTRIBUTE13
224 , ATTRIBUTE14
225 , ATTRIBUTE15
226 , ATTRIBUTE2
227 , ATTRIBUTE3
228 , ATTRIBUTE4
229 , ATTRIBUTE5
230 , ATTRIBUTE6
231 , ATTRIBUTE7
232 , ATTRIBUTE8
233 , ATTRIBUTE9
234 , ATTRIBUTE_CATEGORY
235 , CATEGORY_ID
236 , CATEGORY_SET_ID
237 , CREATED_BY
238 , CREATION_DATE
239 , CUSTOMER_ID
240 , INVENTORY_ITEM_ID
241 , LAST_UPDATED_BY
242 , LAST_UPDATE_DATE
243 , LAST_UPDATE_LOGIN
244 , ORGANIZATION_ID
245 , PROGRAM_APPLICATION_ID
246 , PROGRAM_ID
247 , PROGRAM_UPDATE_DATE
248 , REQUEST_ID
249 , SECONDARY_INVENTORY
250 , SHIP_TO_SITE_ID
251 , SOURCING_RULE_ID
252 , SOURCING_RULE_TYPE
253 INTO l_Assignment_rec.Assignment_Id
254 , l_Assignment_rec.Assignment_Set_Id
255 , l_Assignment_rec.Assignment_Type
256 , l_Assignment_rec.Attribute1
257 , l_Assignment_rec.Attribute10
258 , l_Assignment_rec.Attribute11
259 , l_Assignment_rec.Attribute12
260 , l_Assignment_rec.Attribute13
261 , l_Assignment_rec.Attribute14
262 , l_Assignment_rec.Attribute15
263 , l_Assignment_rec.Attribute2
264 , l_Assignment_rec.Attribute3
265 , l_Assignment_rec.Attribute4
266 , l_Assignment_rec.Attribute5
267 , l_Assignment_rec.Attribute6
268 , l_Assignment_rec.Attribute7
269 , l_Assignment_rec.Attribute8
270 , l_Assignment_rec.Attribute9
271 , l_Assignment_rec.Attribute_Category
272 , l_Assignment_rec.Category_Id
273 , l_Assignment_rec.Category_Set_Id
274 , l_Assignment_rec.Created_By
275 , l_Assignment_rec.Creation_Date
276 , l_Assignment_rec.Customer_Id
277 , l_Assignment_rec.Inventory_Item_Id
278 , l_Assignment_rec.Last_Updated_By
279 , l_Assignment_rec.Last_Update_Date
280 , l_Assignment_rec.Last_Update_Login
281 , l_Assignment_rec.Organization_Id
282 , l_Assignment_rec.Program_Application_Id
283 , l_Assignment_rec.Program_Id
284 , l_Assignment_rec.Program_Update_Date
285 , l_Assignment_rec.Request_Id
286 , l_Assignment_rec.Secondary_Inventory
287 , l_Assignment_rec.Ship_To_Site_Id
288 , l_Assignment_rec.Sourcing_Rule_Id
289 , l_Assignment_rec.Sourcing_Rule_Type
290 FROM MRP_SR_ASSIGNMENTS
291 WHERE ASSIGNMENT_ID = p_Assignment_rec.Assignment_Id
292 FOR UPDATE NOWAIT;
293
294 -- Row locked. Compare IN attributes to DB attributes.
295
296 IF ( (l_Assignment_rec.Assignment_Id =
297 p_Assignment_rec.Assignment_Id) OR
298 ((p_Assignment_rec.Assignment_Id = FND_API.G_MISS_NUM) OR
299 ( (l_Assignment_rec.Assignment_Id IS NULL) AND
300 (p_Assignment_rec.Assignment_Id IS NULL))))
301 AND ( (l_Assignment_rec.Assignment_Set_Id =
302 p_Assignment_rec.Assignment_Set_Id) OR
303 ((p_Assignment_rec.Assignment_Set_Id = FND_API.G_MISS_NUM) OR
304 ( (l_Assignment_rec.Assignment_Set_Id IS NULL) AND
305 (p_Assignment_rec.Assignment_Set_Id IS NULL))))
306 AND ( (l_Assignment_rec.Assignment_Type =
307 p_Assignment_rec.Assignment_Type) OR
308 ((p_Assignment_rec.Assignment_Type = FND_API.G_MISS_NUM) OR
309 ( (l_Assignment_rec.Assignment_Type IS NULL) AND
310 (p_Assignment_rec.Assignment_Type IS NULL))))
311 AND ( (l_Assignment_rec.Attribute1 =
312 p_Assignment_rec.Attribute1) OR
313 ((p_Assignment_rec.Attribute1 = FND_API.G_MISS_CHAR) OR
314 ( (l_Assignment_rec.Attribute1 IS NULL) AND
315 (p_Assignment_rec.Attribute1 IS NULL))))
316 AND ( (l_Assignment_rec.Attribute10 =
317 p_Assignment_rec.Attribute10) OR
318 ((p_Assignment_rec.Attribute10 = FND_API.G_MISS_CHAR) OR
319 ( (l_Assignment_rec.Attribute10 IS NULL) AND
320 (p_Assignment_rec.Attribute10 IS NULL))))
321 AND ( (l_Assignment_rec.Attribute11 =
322 p_Assignment_rec.Attribute11) OR
323 ((p_Assignment_rec.Attribute11 = FND_API.G_MISS_CHAR) OR
324 ( (l_Assignment_rec.Attribute11 IS NULL) AND
325 (p_Assignment_rec.Attribute11 IS NULL))))
326 AND ( (l_Assignment_rec.Attribute12 =
327 p_Assignment_rec.Attribute12) OR
328 ((p_Assignment_rec.Attribute12 = FND_API.G_MISS_CHAR) OR
329 ( (l_Assignment_rec.Attribute12 IS NULL) AND
330 (p_Assignment_rec.Attribute12 IS NULL))))
331 AND ( (l_Assignment_rec.Attribute13 =
332 p_Assignment_rec.Attribute13) OR
333 ((p_Assignment_rec.Attribute13 = FND_API.G_MISS_CHAR) OR
334 ( (l_Assignment_rec.Attribute13 IS NULL) AND
335 (p_Assignment_rec.Attribute13 IS NULL))))
336 AND ( (l_Assignment_rec.Attribute14 =
337 p_Assignment_rec.Attribute14) OR
338 ((p_Assignment_rec.Attribute14 = FND_API.G_MISS_CHAR) OR
339 ( (l_Assignment_rec.Attribute14 IS NULL) AND
340 (p_Assignment_rec.Attribute14 IS NULL))))
341 AND ( (l_Assignment_rec.Attribute15 =
342 p_Assignment_rec.Attribute15) OR
343 ((p_Assignment_rec.Attribute15 = FND_API.G_MISS_CHAR) OR
344 ( (l_Assignment_rec.Attribute15 IS NULL) AND
345 (p_Assignment_rec.Attribute15 IS NULL))))
346 AND ( (l_Assignment_rec.Attribute2 =
347 p_Assignment_rec.Attribute2) OR
348 ((p_Assignment_rec.Attribute2 = FND_API.G_MISS_CHAR) OR
349 ( (l_Assignment_rec.Attribute2 IS NULL) AND
350 (p_Assignment_rec.Attribute2 IS NULL))))
351 AND ( (l_Assignment_rec.Attribute3 =
352 p_Assignment_rec.Attribute3) OR
356 AND ( (l_Assignment_rec.Attribute4 =
353 ((p_Assignment_rec.Attribute3 = FND_API.G_MISS_CHAR) OR
354 ( (l_Assignment_rec.Attribute3 IS NULL) AND
355 (p_Assignment_rec.Attribute3 IS NULL))))
357 p_Assignment_rec.Attribute4) OR
358 ((p_Assignment_rec.Attribute4 = FND_API.G_MISS_CHAR) OR
359 ( (l_Assignment_rec.Attribute4 IS NULL) AND
360 (p_Assignment_rec.Attribute4 IS NULL))))
361 AND ( (l_Assignment_rec.Attribute5 =
362 p_Assignment_rec.Attribute5) OR
363 ((p_Assignment_rec.Attribute5 = FND_API.G_MISS_CHAR) OR
364 ( (l_Assignment_rec.Attribute5 IS NULL) AND
365 (p_Assignment_rec.Attribute5 IS NULL))))
366 AND ( (l_Assignment_rec.Attribute6 =
367 p_Assignment_rec.Attribute6) OR
368 ((p_Assignment_rec.Attribute6 = FND_API.G_MISS_CHAR) OR
369 ( (l_Assignment_rec.Attribute6 IS NULL) AND
370 (p_Assignment_rec.Attribute6 IS NULL))))
371 AND ( (l_Assignment_rec.Attribute7 =
372 p_Assignment_rec.Attribute7) OR
373 ((p_Assignment_rec.Attribute7 = FND_API.G_MISS_CHAR) OR
374 ( (l_Assignment_rec.Attribute7 IS NULL) AND
375 (p_Assignment_rec.Attribute7 IS NULL))))
376 AND ( (l_Assignment_rec.Attribute8 =
377 p_Assignment_rec.Attribute8) OR
378 ((p_Assignment_rec.Attribute8 = FND_API.G_MISS_CHAR) OR
379 ( (l_Assignment_rec.Attribute8 IS NULL) AND
380 (p_Assignment_rec.Attribute8 IS NULL))))
381 AND ( (l_Assignment_rec.Attribute9 =
382 p_Assignment_rec.Attribute9) OR
383 ((p_Assignment_rec.Attribute9 = FND_API.G_MISS_CHAR) OR
384 ( (l_Assignment_rec.Attribute9 IS NULL) AND
385 (p_Assignment_rec.Attribute9 IS NULL))))
386 AND ( (l_Assignment_rec.Attribute_Category =
387 p_Assignment_rec.Attribute_Category) OR
388 ((p_Assignment_rec.Attribute_Category = FND_API.G_MISS_CHAR) OR
389 ( (l_Assignment_rec.Attribute_Category IS NULL) AND
390 (p_Assignment_rec.Attribute_Category IS NULL))))
391 AND ( (l_Assignment_rec.Category_Id =
392 p_Assignment_rec.Category_Id) OR
393 ((p_Assignment_rec.Category_Id = FND_API.G_MISS_NUM) OR
394 ( (l_Assignment_rec.Category_Id IS NULL) AND
395 (p_Assignment_rec.Category_Id IS NULL))))
396 AND ( (l_Assignment_rec.Category_Set_Id =
397 p_Assignment_rec.Category_Set_Id) OR
398 ((p_Assignment_rec.Category_Set_Id = FND_API.G_MISS_NUM) OR
399 ( (l_Assignment_rec.Category_Set_Id IS NULL) AND
400 (p_Assignment_rec.Category_Set_Id IS NULL))))
401 AND ( (l_Assignment_rec.Created_By =
402 p_Assignment_rec.Created_By) OR
403 ((p_Assignment_rec.Created_By = FND_API.G_MISS_NUM) OR
404 ( (l_Assignment_rec.Created_By IS NULL) AND
405 (p_Assignment_rec.Created_By IS NULL))))
406 AND ( (l_Assignment_rec.Creation_Date =
407 p_Assignment_rec.Creation_Date) OR
408 ((p_Assignment_rec.Creation_Date = FND_API.G_MISS_DATE) OR
409 ( (l_Assignment_rec.Creation_Date IS NULL) AND
410 (p_Assignment_rec.Creation_Date IS NULL))))
411 AND ( (l_Assignment_rec.Customer_Id =
412 p_Assignment_rec.Customer_Id) OR
413 ((p_Assignment_rec.Customer_Id = FND_API.G_MISS_NUM) OR
414 ( (l_Assignment_rec.Customer_Id IS NULL) AND
415 (p_Assignment_rec.Customer_Id IS NULL))))
416 AND ( (l_Assignment_rec.Inventory_Item_Id =
417 p_Assignment_rec.Inventory_Item_Id) OR
418 ((p_Assignment_rec.Inventory_Item_Id = FND_API.G_MISS_NUM) OR
419 ( (l_Assignment_rec.Inventory_Item_Id IS NULL) AND
420 (p_Assignment_rec.Inventory_Item_Id IS NULL))))
421 AND ( (l_Assignment_rec.Last_Updated_By =
422 p_Assignment_rec.Last_Updated_By) OR
423 ((p_Assignment_rec.Last_Updated_By = FND_API.G_MISS_NUM) OR
424 ( (l_Assignment_rec.Last_Updated_By IS NULL) AND
425 (p_Assignment_rec.Last_Updated_By IS NULL))))
426 AND ( (l_Assignment_rec.Last_Update_Date =
427 p_Assignment_rec.Last_Update_Date) OR
428 ((p_Assignment_rec.Last_Update_Date = FND_API.G_MISS_DATE) OR
429 ( (l_Assignment_rec.Last_Update_Date IS NULL) AND
430 (p_Assignment_rec.Last_Update_Date IS NULL))))
431 AND ( (l_Assignment_rec.Last_Update_Login =
432 p_Assignment_rec.Last_Update_Login) OR
433 ((p_Assignment_rec.Last_Update_Login = FND_API.G_MISS_NUM) OR
434 ( (l_Assignment_rec.Last_Update_Login IS NULL) AND
435 (p_Assignment_rec.Last_Update_Login IS NULL))))
436 AND ( (l_Assignment_rec.Organization_Id =
437 p_Assignment_rec.Organization_Id) OR
438 ((p_Assignment_rec.Organization_Id = FND_API.G_MISS_NUM) OR
439 ( (l_Assignment_rec.Organization_Id IS NULL) AND
440 (p_Assignment_rec.Organization_Id IS NULL))))
441 AND ( (l_Assignment_rec.Program_Application_Id =
442 p_Assignment_rec.Program_Application_Id) OR
443 ((p_Assignment_rec.Program_Application_Id = FND_API.G_MISS_NUM) OR
444 ( (l_Assignment_rec.Program_Application_Id IS NULL) AND
445 (p_Assignment_rec.Program_Application_Id IS NULL))))
446 AND ( (l_Assignment_rec.Program_Id =
447 p_Assignment_rec.Program_Id) OR
448 ((p_Assignment_rec.Program_Id = FND_API.G_MISS_NUM) OR
449 ( (l_Assignment_rec.Program_Id IS NULL) AND
450 (p_Assignment_rec.Program_Id IS NULL))))
451 AND ( (l_Assignment_rec.Program_Update_Date =
452 p_Assignment_rec.Program_Update_Date) OR
456 AND ( (l_Assignment_rec.Request_Id =
453 ((p_Assignment_rec.Program_Update_Date = FND_API.G_MISS_DATE) OR
454 ( (l_Assignment_rec.Program_Update_Date IS NULL) AND
455 (p_Assignment_rec.Program_Update_Date IS NULL))))
457 p_Assignment_rec.Request_Id) OR
458 ((p_Assignment_rec.Request_Id = FND_API.G_MISS_NUM) OR
459 ( (l_Assignment_rec.Request_Id IS NULL) AND
460 (p_Assignment_rec.Request_Id IS NULL))))
461 AND ( (l_Assignment_rec.Secondary_Inventory =
462 p_Assignment_rec.Secondary_Inventory) OR
463 ((p_Assignment_rec.Secondary_Inventory = FND_API.G_MISS_CHAR) OR
464 ( (l_Assignment_rec.Secondary_Inventory IS NULL) AND
465 (p_Assignment_rec.Secondary_Inventory IS NULL))))
466 AND ( (l_Assignment_rec.Ship_To_Site_Id =
467 p_Assignment_rec.Ship_To_Site_Id) OR
468 ((p_Assignment_rec.Ship_To_Site_Id = FND_API.G_MISS_NUM) OR
469 ( (l_Assignment_rec.Ship_To_Site_Id IS NULL) AND
470 (p_Assignment_rec.Ship_To_Site_Id IS NULL))))
471 AND ( (l_Assignment_rec.Sourcing_Rule_Id =
472 p_Assignment_rec.Sourcing_Rule_Id) OR
473 ((p_Assignment_rec.Sourcing_Rule_Id = FND_API.G_MISS_NUM) OR
474 ( (l_Assignment_rec.Sourcing_Rule_Id IS NULL) AND
475 (p_Assignment_rec.Sourcing_Rule_Id IS NULL))))
476 AND ( (l_Assignment_rec.Sourcing_Rule_Type =
477 p_Assignment_rec.Sourcing_Rule_Type) OR
478 ((p_Assignment_rec.Sourcing_Rule_Type = FND_API.G_MISS_NUM) OR
479 ( (l_Assignment_rec.Sourcing_Rule_Type IS NULL) AND
480 (p_Assignment_rec.Sourcing_Rule_Type IS NULL))))
481 THEN
482
483 -- Row has not changed. Set out parameter.
484
485 x_Assignment_rec := l_Assignment_rec;
486
487 -- Set return status
488
489 x_return_status := FND_API.G_RET_STS_SUCCESS;
490 x_Assignment_rec.return_status := FND_API.G_RET_STS_SUCCESS;
491
492 ELSE
493
494 -- Row has changed by another user.
495
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 x_Assignment_rec.return_status := FND_API.G_RET_STS_ERROR;
498
499 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
500 THEN
501
502 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_CHANGED');
503 FND_MSG_PUB.Add;
504
505 END IF;
506
507 END IF;
508
509 EXCEPTION
510
511 WHEN NO_DATA_FOUND THEN
512
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 x_Assignment_rec.return_status := FND_API.G_RET_STS_ERROR;
515
516 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
517 THEN
518
519 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_DELETED');
520 FND_MSG_PUB.Add;
521
522 END IF;
523 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
524
525 x_return_status := FND_API.G_RET_STS_ERROR;
526 x_Assignment_rec.return_status := FND_API.G_RET_STS_ERROR;
527
528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
529 THEN
530
531 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_ALREADY_LOCKED');
532 FND_MSG_PUB.Add;
533
534 END IF;
535 WHEN OTHERS THEN
536
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 x_Assignment_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539
540 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
541 THEN
542 FND_MSG_PUB.Add_Exc_Msg
543 ( G_PKG_NAME
544 , 'Lock_Row'
545 );
546 END IF;
547
548 END Lock_Row;
549
550 -- Function Query_Row
551
552 FUNCTION Query_Row
553 ( p_Assignment_Id IN NUMBER
554 ) RETURN MRP_Src_Assignment_PUB.Assignment_Rec_Type
555 IS
556 BEGIN
557
558 RETURN Query_Rows
559 ( p_Assignment_Id => p_Assignment_Id
560 )(1);
561
562
563 EXCEPTION
564
565 WHEN OTHERS THEN
566
567 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
568 THEN
569 FND_MSG_PUB.Add_Exc_Msg
570 ( G_PKG_NAME
571 , 'Query_Row'
572 );
573 END IF;
574
575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
576
577 END Query_Row;
578
579 -- Procedure Query_Entity
580
581 PROCEDURE Query_Entity
582 ( p_Assignment_Id IN NUMBER
583 , x_Assignment_rec OUT MRP_Src_Assignment_PUB.Assignment_Rec_Type
584 , x_Assignment_val_rec OUT MRP_Src_Assignment_PUB.Assignment_Val_Rec_Type
585 )
586 IS
587 l_Assignment_tbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
588 l_Assignment_val_tbl MRP_Src_Assignment_PUB.Assignment_Val_Tbl_Type;
589 BEGIN
590
591 Query_Entities
592 ( p_Assignment_Id => p_Assignment_Id
593 , x_Assignment_tbl => l_Assignment_tbl
594 , x_Assignment_val_tbl => l_Assignment_val_tbl
595 );
596
597 -- Assign out records
598
599 x_Assignment_rec := l_Assignment_tbl(1);
600 x_Assignment_val_rec := l_Assignment_val_tbl(1);
601
602
603 EXCEPTION
604
605 WHEN OTHERS THEN
606
610 ( G_PKG_NAME
607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
608 THEN
609 FND_MSG_PUB.Add_Exc_Msg
611 , 'Query_Entity'
612 );
613 END IF;
614
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616
617 END Query_Entity;
618
619 -- Function Query_Rows
620
621 --
622
623 FUNCTION Query_Rows
624 ( p_Assignment_Id IN NUMBER :=
625 FND_API.G_MISS_NUM
626 , p_Assignment_Set_Id IN NUMBER :=
627 FND_API.G_MISS_NUM
628 ) RETURN MRP_Src_Assignment_PUB.Assignment_Tbl_Type
629 IS
630 l_Assignment_rec MRP_Src_Assignment_PUB.Assignment_Rec_Type;
631 l_Assignment_tbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
632
633 CURSOR l_Assignment_csr IS
634 SELECT ASSIGNMENT_ID
635 , ASSIGNMENT_SET_ID
636 , ASSIGNMENT_TYPE
637 , ATTRIBUTE1
638 , ATTRIBUTE10
639 , ATTRIBUTE11
640 , ATTRIBUTE12
641 , ATTRIBUTE13
642 , ATTRIBUTE14
643 , ATTRIBUTE15
644 , ATTRIBUTE2
645 , ATTRIBUTE3
646 , ATTRIBUTE4
647 , ATTRIBUTE5
648 , ATTRIBUTE6
649 , ATTRIBUTE7
650 , ATTRIBUTE8
651 , ATTRIBUTE9
652 , ATTRIBUTE_CATEGORY
653 , CATEGORY_ID
654 , CATEGORY_SET_ID
655 , CREATED_BY
656 , CREATION_DATE
657 , CUSTOMER_ID
658 , INVENTORY_ITEM_ID
659 , LAST_UPDATED_BY
660 , LAST_UPDATE_DATE
661 , LAST_UPDATE_LOGIN
662 , ORGANIZATION_ID
663 , PROGRAM_APPLICATION_ID
664 , PROGRAM_ID
665 , PROGRAM_UPDATE_DATE
666 , REQUEST_ID
667 , SECONDARY_INVENTORY
668 , SHIP_TO_SITE_ID
669 , SOURCING_RULE_ID
670 , SOURCING_RULE_TYPE
671 FROM MRP_SR_ASSIGNMENTS
672 WHERE ( ASSIGNMENT_ID = p_Assignment_Id
673 )
674 OR ( ASSIGNMENT_SET_ID = p_Assignment_Set_Id
675 );
676
677 BEGIN
678
679 IF
680 (p_Assignment_Id IS NOT NULL
681 AND
682 p_Assignment_Id <> FND_API.G_MISS_NUM)
683 AND
684 (p_Assignment_Set_Id IS NOT NULL
685 AND
686 p_Assignment_Set_Id <> FND_API.G_MISS_NUM)
687 THEN
688 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
689 THEN
690 FND_MSG_PUB.Add_Exc_Msg
691 ( G_PKG_NAME
692 , 'Query Rows'
693 , 'Keys are mutually exclusive: Assignment_Id = '|| p_Assignment_Id || ', Assignment_Set_Id = '|| p_Assignment_Set_Id
694 );
695 END IF;
696
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698
699 END IF;
700
701
702 -- Loop over fetched records
703
704 FOR l_implicit_rec IN l_Assignment_csr LOOP
705
706 l_Assignment_rec.Assignment_Id := l_implicit_rec.ASSIGNMENT_ID;
707 l_Assignment_rec.Assignment_Set_Id := l_implicit_rec.ASSIGNMENT_SET_ID;
708 l_Assignment_rec.Assignment_Type := l_implicit_rec.ASSIGNMENT_TYPE;
709 l_Assignment_rec.Attribute1 := l_implicit_rec.ATTRIBUTE1;
710 l_Assignment_rec.Attribute10 := l_implicit_rec.ATTRIBUTE10;
711 l_Assignment_rec.Attribute11 := l_implicit_rec.ATTRIBUTE11;
712 l_Assignment_rec.Attribute12 := l_implicit_rec.ATTRIBUTE12;
713 l_Assignment_rec.Attribute13 := l_implicit_rec.ATTRIBUTE13;
714 l_Assignment_rec.Attribute14 := l_implicit_rec.ATTRIBUTE14;
715 l_Assignment_rec.Attribute15 := l_implicit_rec.ATTRIBUTE15;
716 l_Assignment_rec.Attribute2 := l_implicit_rec.ATTRIBUTE2;
717 l_Assignment_rec.Attribute3 := l_implicit_rec.ATTRIBUTE3;
718 l_Assignment_rec.Attribute4 := l_implicit_rec.ATTRIBUTE4;
719 l_Assignment_rec.Attribute5 := l_implicit_rec.ATTRIBUTE5;
720 l_Assignment_rec.Attribute6 := l_implicit_rec.ATTRIBUTE6;
721 l_Assignment_rec.Attribute7 := l_implicit_rec.ATTRIBUTE7;
722 l_Assignment_rec.Attribute8 := l_implicit_rec.ATTRIBUTE8;
723 l_Assignment_rec.Attribute9 := l_implicit_rec.ATTRIBUTE9;
724 l_Assignment_rec.Attribute_Category := l_implicit_rec.ATTRIBUTE_CATEGORY;
725 l_Assignment_rec.Category_Id := l_implicit_rec.CATEGORY_ID;
726 l_Assignment_rec.Category_Set_Id := l_implicit_rec.CATEGORY_SET_ID;
727 l_Assignment_rec.Created_By := l_implicit_rec.CREATED_BY;
728 l_Assignment_rec.Creation_Date := l_implicit_rec.CREATION_DATE;
729 l_Assignment_rec.Customer_Id := l_implicit_rec.CUSTOMER_ID;
730 l_Assignment_rec.Inventory_Item_Id := l_implicit_rec.INVENTORY_ITEM_ID;
731 l_Assignment_rec.Last_Updated_By := l_implicit_rec.LAST_UPDATED_BY;
732 l_Assignment_rec.Last_Update_Date := l_implicit_rec.LAST_UPDATE_DATE;
733 l_Assignment_rec.Last_Update_Login := l_implicit_rec.LAST_UPDATE_LOGIN;
734 l_Assignment_rec.Organization_Id := l_implicit_rec.ORGANIZATION_ID;
735 l_Assignment_rec.Program_Application_Id := l_implicit_rec.PROGRAM_APPLICATION_ID;
736 l_Assignment_rec.Program_Id := l_implicit_rec.PROGRAM_ID;
737 l_Assignment_rec.Program_Update_Date := l_implicit_rec.PROGRAM_UPDATE_DATE;
738 l_Assignment_rec.Request_Id := l_implicit_rec.REQUEST_ID;
739 l_Assignment_rec.Secondary_Inventory := l_implicit_rec.SECONDARY_INVENTORY;
740 l_Assignment_rec.Ship_To_Site_Id := l_implicit_rec.SHIP_TO_SITE_ID;
741 l_Assignment_rec.Sourcing_Rule_Id := l_implicit_rec.SOURCING_RULE_ID;
745
742 l_Assignment_rec.Sourcing_Rule_Type := l_implicit_rec.SOURCING_RULE_TYPE;
743
744 l_Assignment_tbl(l_Assignment_tbl.COUNT + 1) := l_Assignment_rec;
746 END LOOP;
747
748
749 -- PK sent and no rows found
750
751 IF
752 (p_Assignment_Id IS NOT NULL
753 AND
754 p_Assignment_Id <> FND_API.G_MISS_NUM)
755 AND
756 (l_Assignment_tbl.COUNT = 0)
757 THEN
758 RAISE NO_DATA_FOUND;
759 END IF;
760
761
762 -- Return fetched table
763
764 RETURN l_Assignment_tbl;
765
766 EXCEPTION
767
768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771
772 WHEN OTHERS THEN
773
774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 FND_MSG_PUB.Add_Exc_Msg
777 ( G_PKG_NAME
778 , 'Query_Rows'
779 );
780 END IF;
781
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783
784 END Query_Rows;
785
786 -- Procedure Query_Entities
787
788 --
789
790 PROCEDURE Query_Entities
791 ( p_Assignment_Id IN NUMBER :=
792 FND_API.G_MISS_NUM
793 , p_Assignment_Set_Id IN NUMBER :=
794 FND_API.G_MISS_NUM
795 , x_Assignment_tbl OUT MRP_Src_Assignment_PUB.Assignment_Tbl_Type
796 , x_Assignment_val_tbl OUT MRP_Src_Assignment_PUB.Assignment_Val_Tbl_Type
797 )
798 IS
799 l_Assignment_rec MRP_Src_Assignment_PUB.Assignment_Rec_Type;
800 l_Assignment_val_rec MRP_Src_Assignment_PUB.Assignment_Val_Rec_Type;
801 l_rows_fetched BOOLEAN := FALSE;
802
803 CURSOR l_Assignment_csr IS
804 SELECT ASSIGNMENT_ID
805 , ASSIGNMENT_SET_ID
806 , ASSIGNMENT_TYPE
807 , ATTRIBUTE1
808 , ATTRIBUTE10
809 , ATTRIBUTE11
810 , ATTRIBUTE12
811 , ATTRIBUTE13
812 , ATTRIBUTE14
813 , ATTRIBUTE15
814 , ATTRIBUTE2
815 , ATTRIBUTE3
816 , ATTRIBUTE4
817 , ATTRIBUTE5
818 , ATTRIBUTE6
819 , ATTRIBUTE7
820 , ATTRIBUTE8
821 , ATTRIBUTE9
822 , ATTRIBUTE_CATEGORY
823 , CATEGORY_ID
824 , CATEGORY_SET_ID
825 , CREATED_BY
826 , CREATION_DATE
827 , CUSTOMER_ID
828 , INVENTORY_ITEM_ID
829 , LAST_UPDATED_BY
830 , LAST_UPDATE_DATE
831 , LAST_UPDATE_LOGIN
832 , ORGANIZATION_ID
833 , PROGRAM_APPLICATION_ID
834 , PROGRAM_ID
835 , PROGRAM_UPDATE_DATE
836 , REQUEST_ID
837 , SECONDARY_INVENTORY
838 , SHIP_TO_SITE_ID
839 , SOURCING_RULE_ID
840 , SOURCING_RULE_TYPE
841 FROM MRP_SR_ASSIGNMENTS
842 WHERE ( ASSIGNMENT_ID = p_Assignment_Id
843 )
844 OR ( ASSIGNMENT_SET_ID = p_Assignment_Set_Id
845 );
846
847 BEGIN
848
849 IF
850 (p_Assignment_Id IS NOT NULL
851 AND
852 p_Assignment_Id <> FND_API.G_MISS_NUM)
853 AND
854 (p_Assignment_Set_Id IS NOT NULL
855 AND
856 p_Assignment_Set_Id <> FND_API.G_MISS_NUM)
857 THEN
858 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
859 THEN
860 FND_MSG_PUB.Add_Exc_Msg
861 ( G_PKG_NAME
862 , 'Query Entities'
863 , 'Keys are mutually exclusive: Assignment_Id = '|| p_Assignment_Id || ', Assignment_Set_Id = '|| p_Assignment_Set_Id
864 );
865 END IF;
866
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868
869 END IF;
870
871
872 -- Loop over fetched records
873
874 FOR l_implicit_rec IN l_Assignment_csr LOOP
875
876
877 l_Assignment_rec.Assignment_Id := l_implicit_rec.ASSIGNMENT_ID;
878 l_Assignment_rec.Assignment_Set_Id := l_implicit_rec.ASSIGNMENT_SET_ID;
879 l_Assignment_rec.Assignment_Type := l_implicit_rec.ASSIGNMENT_TYPE;
880 l_Assignment_rec.Attribute1 := l_implicit_rec.ATTRIBUTE1;
881 l_Assignment_rec.Attribute10 := l_implicit_rec.ATTRIBUTE10;
882 l_Assignment_rec.Attribute11 := l_implicit_rec.ATTRIBUTE11;
883 l_Assignment_rec.Attribute12 := l_implicit_rec.ATTRIBUTE12;
884 l_Assignment_rec.Attribute13 := l_implicit_rec.ATTRIBUTE13;
885 l_Assignment_rec.Attribute14 := l_implicit_rec.ATTRIBUTE14;
886 l_Assignment_rec.Attribute15 := l_implicit_rec.ATTRIBUTE15;
887 l_Assignment_rec.Attribute2 := l_implicit_rec.ATTRIBUTE2;
888 l_Assignment_rec.Attribute3 := l_implicit_rec.ATTRIBUTE3;
889 l_Assignment_rec.Attribute4 := l_implicit_rec.ATTRIBUTE4;
890 l_Assignment_rec.Attribute5 := l_implicit_rec.ATTRIBUTE5;
891 l_Assignment_rec.Attribute6 := l_implicit_rec.ATTRIBUTE6;
892 l_Assignment_rec.Attribute7 := l_implicit_rec.ATTRIBUTE7;
893 l_Assignment_rec.Attribute8 := l_implicit_rec.ATTRIBUTE8;
894 l_Assignment_rec.Attribute9 := l_implicit_rec.ATTRIBUTE9;
895 l_Assignment_rec.Attribute_Category := l_implicit_rec.ATTRIBUTE_CATEGORY;
896 l_Assignment_rec.Category_Id := l_implicit_rec.CATEGORY_ID;
897 l_Assignment_rec.Category_Set_Id := l_implicit_rec.CATEGORY_SET_ID;
901 l_Assignment_rec.Inventory_Item_Id := l_implicit_rec.INVENTORY_ITEM_ID;
898 l_Assignment_rec.Created_By := l_implicit_rec.CREATED_BY;
899 l_Assignment_rec.Creation_Date := l_implicit_rec.CREATION_DATE;
900 l_Assignment_rec.Customer_Id := l_implicit_rec.CUSTOMER_ID;
902 l_Assignment_rec.Last_Updated_By := l_implicit_rec.LAST_UPDATED_BY;
903 l_Assignment_rec.Last_Update_Date := l_implicit_rec.LAST_UPDATE_DATE;
904 l_Assignment_rec.Last_Update_Login := l_implicit_rec.LAST_UPDATE_LOGIN;
905 l_Assignment_rec.Organization_Id := l_implicit_rec.ORGANIZATION_ID;
906 l_Assignment_rec.Program_Application_Id := l_implicit_rec.PROGRAM_APPLICATION_ID;
907 l_Assignment_rec.Program_Id := l_implicit_rec.PROGRAM_ID;
908 l_Assignment_rec.Program_Update_Date := l_implicit_rec.PROGRAM_UPDATE_DATE;
909 l_Assignment_rec.Request_Id := l_implicit_rec.REQUEST_ID;
910 l_Assignment_rec.Secondary_Inventory := l_implicit_rec.SECONDARY_INVENTORY;
911 l_Assignment_rec.Ship_To_Site_Id := l_implicit_rec.SHIP_TO_SITE_ID;
912 l_Assignment_rec.Sourcing_Rule_Id := l_implicit_rec.SOURCING_RULE_ID;
913 l_Assignment_rec.Sourcing_Rule_Type := l_implicit_rec.SOURCING_RULE_TYPE;
914
915 l_rows_fetched := l_Assignment_csr%FOUND;
916
917 x_Assignment_tbl(l_Assignment_csr%ROWCOUNT) := l_Assignment_rec;
918 x_Assignment_val_tbl(l_Assignment_csr%ROWCOUNT) := l_Assignment_val_rec;
919
920 END LOOP;
921
922
923 -- PK sent and no rows found
924
925 IF
926 (p_Assignment_Id IS NOT NULL
927 AND
928 p_Assignment_Id <> FND_API.G_MISS_NUM)
929 AND
930 (NOT l_rows_fetched)
931 THEN
932 RAISE NO_DATA_FOUND;
933 END IF;
934
935 EXCEPTION
936
937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
938
939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
940
941 WHEN OTHERS THEN
942
943 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
944 THEN
945 FND_MSG_PUB.Add_Exc_Msg
946 ( G_PKG_NAME
947 , 'Query_Entities'
948 );
949 END IF;
950
951 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
952
953 END Query_Entities;
954
955 END MRP_Assignment_Handlers;